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