MySQL database automatic backup under Linux

1. Create a role for MySQL user backup, and grant the role Select, RELOAD, SHOW DATABASES, LOCK tables and other permissions.

' Backuper '@'localhost'********'0 Rows affected (0.00  sec) MySQL> Grant SELECT, RELOAD, SHOW DATABASES, LOCK TABLES on *. * to B Ackuper@localhost0 rows affected (0.00 sec)

2. Locate the hard disk in the system to create a backup directory, and create a shell script

[email protected] backup]# vim backup_qianyu_veeker_db.sh#!/bin/-ubackuper-p******** qianyu_ veeker_db >/home/mysql/backup/qianyu_veeker_db_$ (Date +%y%m%d_%h%m%s). sql

Note: There are no spaces between-U and the user name, and the-p and password are also the same.

3. Add a scheduled task, you need to install Crontab
Vixie-cron package is the main program of Cron;
The Crontabs package is a program used to install, uninstall, or list tables used to drive the cron daemon.

[Email protected] ~]# yum-y install vixie-~]# yum-y Install Crontabs

4. Set Boot up

[Email protected] ~]# chkconfig--level 345 crond on

5. Modify the/etc/crontab file to add tasks that require periodic execution

[email protected] etc]# vim Crontabshell=/bin/Bashpath=/sbin:/bin:/usr/sbin:/usr/Binmailto=Roothome=/# for details see Mans4crontabs# 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

[[Email protected] etc]# service Crond startstarting crond: [  OK  ]

，

MySQL database automatic backup under Linux