MySQL scheduled backup script

Today the database has been hung, really hurt! It took another afternoon to learn to write a script file to save the data regularly, so record it!

1, backup MySQL data at 4 points per day;

2, in order to save space, delete more than 3 months of all backup data;

3, delete more than 7 days of backup data, retain 3 months of 10th number 20th number 30th of the backup data;

[[Email protected] ~]# CD data

[Email protected] ~]# Cd/data/dbdata

[Email protected] dbdata]# vim backup_mysq.sh

#输入脚本文件

#

/alidata/server/mysql/bin/mysqldump--all-databases >/data/dbdata/mysqlbak/' Date +%y%m%d '. sql
find/data/dbdata/mysqlbak/-mtime +7-name ' *[1-9].sql '-exec rm-rf {} \;
find/data/dbdata/mysqlbak/-mtime +92-name ' *.sql '-exec rm-rf {} \;

#这里原来是这样的!

##### #这个mysql. 5.6 will report an error

mysqldump -uroot -p123456 --all-databases  >  /data/dbdata/mysqlbak/ ` date  +%Y%m%d`.sql find  /data/dbdata/mysqlbak/  -mtime +7 -name  ‘*[1-9].sql‘  - exec  rm  -rf {} \; find  /data/dbdata/mysqlbak/  -mtime +92 -name  ‘*.sql‘  - exec  rm  -rf {} \;

#######

Test it.

[Email protected] dbdata]#./backup_mysql.sh

-bash:./backup_mysql.sh:permission denied

############## #原因是 #####################

In the official website documents found the reason, you can click here to see: http://dev.mysql.com/doc/refman/5.1/en/password-security-user.html

MySQL users should use the following guidelines to keep passwords secure.

When you run a client program to connect to the MySQL server, it's inadvisable to specify your password in a-the-that exp OSes it to discovery by other users. The methods specify your password when you run client programs is listed here, along with an assessment of The risks of each method. In short, the safest methods is to has the client program prompt for the password or to specify the password in a proper LY protected option file.

English is a bit rotten, but probably read the meaning, translation came to the effect is under the command line if you want to use a password can be in the prompt input after the command to enter the password, or in the specified security file to specify the password. Which is the security file? The document gives an answer to this:

Store your password in an option file. For example, on Unix, you can list your password in the [client] sections of the. my.cnf file in your home directory:

Can be specified within the MY.CNF, so open my my.cnf, under [mysqldump] Add:

User=root
Password=root

Then save it and run it, I'll take it.

And then write a timer.

[Email protected] ~]# CRONTAB-E

###

0 4 * * * /data/dbdata/backup_mysql.sh

####

Crontab:installing New Crontab

And then it's OK.

MySQL scheduled backup script