Detailed explanation of examples of automatic backup of MySQL database under Linux

1. Create a user backup role for mysql and grant the role SELECT, RELOAD, SHOW DATABASES, LOCK TABLES and other permissions.

mysql> create user 'backuper'@'localhost' identified by '********';
Query OK, 0 rows affected (0.00 sec)

mysql> grant SELECT, RELOAD, SHOW DATABASES, LOCK TABLES on *.* to backuper@localhost;
Query OK, 0 rows affected (0.00 sec)

2. Find a hard disk with relatively large storage space in the system, create a backup directory, and create a shell script

[root@qxyw backup]# vim backup_qianyu_veeker_db.sh

#!/bin/bash
mysqldump -ubackuper -p******** qianyu_veeker_db > /home/mysql/backup/qianyu_veeker_db_$(date +%Y%m%d_%H%M%S).sql

Note: There is no space between -u and the username, and the same is true for -p and the password.

3. To add scheduled tasks, you need to install crontab
vixie-cron package is the main program of cron;
crontabs package is used to install, uninstall, or list drivers A program for the cron daemon process.

[root@qxyw ~]# yum -y install vixie-cron
[root@qxyw ~]# yum -y install crontabs

#4. Set up startup

[root@qxyw ~]# chkconfig --level 345 crond on

5. Modify the /etc/crontab file and add the need for periodic execution Task

[root@qxyw etc]# vim crontab

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/# For details see man 4 crontabs

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |# *  *  *  *  * user-name command to be executed0 0 * * * /home/mysql/backup/backup_qianyu_veeker_db.sh

6. Start the crontab service

[root@qxyw etc]# service crond start
Starting crond: [  OK  ]

,

The above is the detailed content of Detailed explanation of examples of automatic backup of MySQL database under Linux. For more information, please follow other related articles on the PHP Chinese website!