Detailed example of how to implement daily automatic backup and scheduled backup of mysql database in Linux-Mysql Tutorial-php.cn

Backup is the basis of disaster recovery. It refers to the process of copying all or part of the data set from the hard disk or array of the application host to other storage media in order to prevent data loss caused by operating errors or system failures. This article mainly introduces the daily automatic backup and scheduled backup of mysql database in Linux. Friends who need it can refer to the following

Overview

Backup is the capacity The basis of disaster refers to the process of copying all or part of the data set from the hard disk or array of the application host to other storage media in order to prevent data loss caused by operating errors or system failures. For some websites and systems, the database is everything, so it is crucial to back up the database!

What is backup?

Detailed example of how to implement daily automatic backup and scheduled backup of mysql database in Linux

##Why back up

Detailed example of how to implement daily automatic backup and scheduled backup of mysql database in Linux

Disaster recovery plan construction

Detailed example of how to implement daily automatic backup and scheduled backup of mysql database in Linux

##Storage mediaOptical Disc

Tape

Hard Disk

Disk Array

DAS: Direct Attached Storage

NAS: Network Attached Storage

SAN: Storage Area Network

Cloud Storage

Here we mainly use the local disk as the storage medium to talk about the addition and use of scheduled tasks, basic backup scripts, and other storage media are just media access The method may be different.

1. Check the disk space:Since it is a scheduled backup, it is necessary to choose a disk space with sufficient space to avoid problems caused by space. Insufficiency leads to backup failure and data loss!

Store to the current disk This is the simplest, but the least recommended; the server has multiple hard disks, it is best to store the backup on another hard disk; if possible, choose better and safer storage Media;

# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup-lv_root 50G 46G 1.6G 97% / tmpfs 1.9G 92K 1.9G 1% /dev/shm /dev/sda1 485M 39M 421M 9% /boot /dev/mapper/VolGroup-lv_home 534G 3.6G 503G 1% /home

Copy after login

2. Create a backup directory:We used the command above to see /home There is sufficient space, so you can consider saving the backup file in /home;

cd /home mkdir backup cd backup

Copy after login

3. Create a backup Shell script:Please replace the DatabaseName in the following command with the actual database name;

Of course, you can also use the actual naming rules!

vi bkDatabaseName.sh

Copy after login

Enter/paste the following:

#!/bin/bash mysqldump -uusername -ppassword DatabaseName > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql

Copy after login

Compress the backup:

#!/bin/bash mysqldump -uusername -ppassword DatabaseName | gzip > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql.gz

Copy after login

Note:

Replace username with the actual username;

Replace password with the actual password;

Replace DatabaseName with Actual database name;

4. Add executable permissions:

chmod u+x bkDatabaseName.sh

Copy after login

Add executable permissions Then execute it first to see if there are any errors in the script and whether it can be used normally;

./bkDatabaseName.sh

5. Add scheduled tasksDetect or install crontab

Confirm whether crontab is installed:

Execute the crontab command. If command not found is reported, it means it is not installed

# crontab -bash: crontab: command not found

Copy after login

If crontab is not installed, you need to install it first. For specific steps, please refer to:

Use the yum command to install the scheduled task program crontab under CentOS

Use the rpm command from CentOS Install the scheduled task program crontab on the system disk

Add a scheduled task

Execute the command:

crontab -e

Copy after login

This is just like using the vi editor , you can edit scheduled tasks.

Enter the following content and save:

*/1 * * * * /home/backup/bkDatabaseName.sh

Copy after login

What does it mean specifically?

means that the shell script "/home/backup/bkDatabaseName.sh" is executed every minute.

6. Test whether the task is executed.

It’s very simple. We just execute the “ls” command several times and see if the file is there after one minute. It's ok without being created!

If the task execution fails, you can view the task log through the following command:

# tail -f /var/log/cron

Copy after login

The output is similar to the following:

Sep 30 14:01:01 bogon run-parts(/etc/cron.hourly)[2503]: starting 0anacron Sep 30 14:01:01 bogon run-parts(/etc/cron.hourly)[2512]: finished 0anacron Sep 30 15:01:01 bogon CROND[3092]: (root) CMD (run-parts /etc/cron.hourly) Sep 30 15:01:01 bogon run-parts(/etc/cron.hourly)[3092]: starting 0anacron Sep 30 15:01:02 bogon run-parts(/etc/cron.hourly)[3101]: finished 0anacron Sep 30 15:50:44 bogon crontab[3598]: (root) BEGIN EDIT (root) Sep 30 16:01:01 bogon CROND[3705]: (root) CMD (run-parts /etc/cron.hourly) Sep 30 16:01:01 bogon run-parts(/etc/cron.hourly)[3705]: starting 0anacron Sep 30 16:01:01 bogon run-parts(/etc/cron.hourly)[3714]: finished 0anacron Sep 30 16:15:29 bogon crontab[3598]: (root) END EDIT (root)

Copy after login

Summary

The above is the detailed content of Detailed example of how to implement daily automatic backup and scheduled backup of mysql database in Linux. For more information, please follow other related articles on the PHP Chinese website!