Remote Backup of MySQL database scripts

There have been several online databases recently, and the hard disk is relatively tight, but it is not convenient to resize and transfer to other machines. However, data is important and needs to be backed up frequently.

The earliest practice was to back up the complete database locally and push the backup database and binlog together to a remote backup host. However, due to the recent tight space, the backup database needs to occupy a large amount of space. Is there any other solution?

After checking that mysqldump has the remote backup function, you can directly back up the remote data to a local device, which greatly saves the disk space of the remote database.

So I tried to write a backup script and run it regularly with crontab. Currently, it seems that the results are not bad.

You can directly use the online environment with a slight modification. If you have any questions, contact O (always _ blank) O ~

Note: This method is applicable to the Intranet. For Internet, you need to consider network factors such as the data size and bandwidth.

The script content is as follows:

1. #ByCuiyuanrong 
2. # Remote backup of mysql Data and binlog to local!
3.  
4. Mysql_user ="Admin" 
5. Mysql_password ="Admin_password" 
6. Remote_ips = 'cat/data/shell_files/ips.txt'
7. Mysql_port ="3306" 
8. TIME='Date '+ % Y % m % d'' 
9.  
10. Source/etc/profile
11. Rm-f/data/shell_files/db. list 
12. # Loop each IP Address
13. ForRemote_ipIn$ Remote_ips
14. Do
15. # Read the database name of each remote mysql Server
16. Echo"=============$ Remote_ip 'date' + % Y _ % m _ % d-% H: % M: % s' ============">/Data/shell_files/history_mysql_backup.log
17. /App/mysql/bin/mysql-- Host = "$ remote_ip" -- port = "$ mysql_port" -- user = "$ mysql_user" -- password = "$ mysql_password" <EOF>/data/shell_files/db. list 
18. Show databases;
19. Exit
20. EOF
21. # Use sed to filter out databases without backup
22. Sed-I'/Database \ | information_schema \ | performance_schema \ | test/d'/Data/shell_files/db. list
23. # Create a backup directory
24. Mkdir-p/data/mysql_backup/$TIME/$ Remote_ip/data/
25. Mkdir-p/data/mysql_backup/$TIME/$ Remote_ip/binlog/
26. # Backing up each database in a loop
27. ForDBIn'Cat/data/shell_files/db. list'
28. Do
29. Echo"Begin dump $ DB 'date' + % Y _ % m _ % d-% H: % M: % S ''">/Data/shell_files/history_mysql_backup.log
30. /App/mysql/bin/mysqldump-- Host = "$ remote_ip" -- port = "$ mysql_port" -- user = "$ mysql_user" -- password = "$ mysql_password" -- opt -- flush-logs -- master-data = 2 -- single-transaction -- default-character-set = utf8 $ DB | gzip-f>/data/mysql_backup/$ TIME/$ remote_ip/data/mongodb. SQL .gz 
31. Echo"End dump $ DB">/Data/shell_files/history_mysql_backup.log
32. Done
33. # Backing up binlog log files
34. Echo"Begin rsync binlogs $ remote_ip">/Data/shell_files/history_mysql_backup.log
35. Rsync-e'Ssh-p 22-o StrictHostKeyChecking = no'-Avz-- Progress -- delete root @ $ remote_ip:/data/mysql/binlog/data/mysql_backup/$ TIME/$ remote_ip/binlog/ 
36. Echo"End rsync binlogs $ remote_ip">/Data/shell_files/history_mysql_backup.log
38. Sleep 60
39. Done