Mysql Automatic backup Script

Turn from:

Mysql Automatic Backup Script 2--Iteye technology website
http://kangh.iteye.com/blog/2309091

Backup scenarios:

Backup Host: 192.168.10.11

Database server: 192.168.10.22

Backup content: Remote backup of XXXX and YYYY libraries in MySQL server, daily 2:30, each library backed up as a standalone. sql file, and then compressed into. tar.gz format, the file name embedded in the date and time the backup was performed.

Work Preparation:

1, ensure that the network between the backup host and the database server to maintain a smooth connection, there can be no network link failure, firewalls and other obstacles.

2, the MySQL service must allow the backup host remote access, and authorized users to query the XXXX and YYYY library.

Here you can create a dedicated database backup account "KangHui" Allow the backup host 192.168.10.11 to connect to the MySQL database and authorize read access to the XXXX and YYYY libraries, and when using the logical Backup tool mysqldump, you need to set select and lock and tables permissions on the library.

Java code

1. Mysql>grant select,lock tables on xxxx.*to ' KangHui ' @' 192.168.10.11 ' identified by ' 123456 ';
3. Mysql>grant select,lock tables on yyyy.*to ' KangHui ' @' 192.168.10.11 ' identified by ' 123456 ';

After you set access permissions, test database access on the backup server, and query authorization is valid.

Java code

1. [Root@localhost ~] #mysqldump-u root-p123456-h 192.168. 10.22--databases xxxx > ceshi.sql
3. [Root@localhost ~] #ls-lh ceshi.sql

Below is a MySQL backup script that can be modified to suit your needs.

Java code

2. [Root@localhost ~] #mkdir-P/opt/mysql_bak/
4. [Root@localhost ~] #cd/opt/mysql_bak/
6. [Root@localhost mysql_bak]# vim mysql_bak.sh
8. #!/bin/bash
10. #auto Backup MySQLdb
12. #by authors KangHui 2016-06-
14. #这是一个简单化到MYSQL数据库逻辑备份脚本
16. #1. Define database links, Target library information
18. my_user="Root"
20. my_pass="123456"
22. my_host="192.168.10.22"
24. my_conn="-u $MY _user-p$my_pass-h $MY _host"
26. my_db1="xxxx"
28. my_db2="yyyy"
30. #2. Define backup directory, tool, time, file name
32. bf_dir="/opt/mysql_bak"
34. bf_cmd="/usr/bin/mysqldump"
36. Bf_time="Date +%y%m%d-%h%m"
38. name_1="$MY _db1-$BF _time"
40. name_2="$MY _db2-$Bf _time"
42. #3 pilot out for. SQL script, then compress (remove source files after packaging)
44. CD $BF _dir
46. $BF _cmd $MY _conn--databases $MY _db1 > $name _1.sql
48. $BF _cmd $MY _conn--databases $MY _db2 > $name _2.sql
50. /bin/tar zcf $name _1.tar.gz $name _1.sql--remove &>/dev/null
52. /bin/tar zcf $name _2.tar.gz $name _2.sql--remove &>/dev/null

Set Permissions and test:

Java code

1. [Root@localhost Mysql_bak] #chmod +x mysql_bak.sh
3. [Root@localhost mysql_bak]#./mysql_bak.sh
5. [Root@localhost Mysql_bak] #ls-lh/opt/mysql_bak/*.gz

Finally, add the script to the scheduled task.

Java code

1. [Root@localhost ~] #crontab-e
2. 2 * * * /opt/mysql_bak/mysql_bak.sh

Mysql Automatic backup Script