1. Environment

Test environment: centos6.9

MySQL version: mysql5.6

MySQL installation directory:/usr/local/mysql/

Backup directory:/mysqlbak

Script location:/mysqlbak/mysqlbak.sh

2. Script code

The content of the script is as follows. There are comments, please modify according to the content:

 #Prepared by Liu on April 15, 2018 #Define user name and password user="root" pass="root" #Set the backup directory to/mysqlbak, which can be set by yourself backup_dir="/mysqlbak" #Get system time format 2018041521 backuptime="$(date +"%Y%m%d%H")" #The deletion time is set to 2 weeks before the current time deletetime=`date -d "2 week ago" +"%Y%m%d%H"` rm -f /mysqlbak/mysqlbak_$deletetime.zip #Enter the mysql executable directory and install mysql in/usr/local/mysql cd /usr/local/mysql/bin #Execute the export full database statement ./mysqldump -u$user -p$pass --all-databases> "$backup_dir"/mysql_"$backuptime.sql" zip -r /mysqlbak/mysqlbak_$backuptime.zip  /mysqlbak/*.sql rm -rf /mysqlbak/*.sql

3. Multiple uses of mysqldump, which can be modified according to the third line of the derived reciprocal

The specific usage under the command line is as follows: (if it is executed locally, - h localhost may not be used)

Mysqldump - u username - p password - d database name table name>path/script name;

Export the entire database structure and data
mysqldump -h localhost -uroot -p123456 database > /home/dump.sql

Export single data table structure and data
mysqldump -h localhost -uroot -p123456 database table > /home/dump.sql

Export the entire database structure (excluding data)
mysqldump -h localhost -uroot -p123456 -d database > /home/dump.sql

Export single data table structure (excluding data)
mysqldump -h localhost -uroot -p123456 -d database table > /home/dump.sql

4. Set scheduled tasks

vi /etc/crontab

If there is no such file, execute the yum install crond command: yum install crontabs

Add a line at the end:

59 21 * * * root /mysqlbak/mysqlbak.sh

The above means that a backup is performed at 21:59 every day

To view the scheduled task log: tail -f /var/log/cron

Note: Be sure to execute it manually once to see whether it is successful

Reference article:

https://blog.csdn.net/happysunshineguy/article/details/77113483

http://www.cnblogs.com/xiaoliu66007/p/4661044.html

https://blog.csdn.net/wk1063645973/article/details/49562697