Back up MySQL databases using shell scripts

We need to sort out some script programs near the end of the year. If we find a MySQL database backup script, we can share with you the idea that the environment is ubuntu10.04server.

1. Obtain the names of all databases on the MySQL server and filter out databases that do not need to be backed up.
2. Export SQL files of all databases through mysqldump For Loop
3. Compress all SQL files with zip Encryption
4. Regular data cleaning

Shell code Database Export Code

#! /Bin/bash #1. database Information definition mysql_host = "192.168.1.1" mysql_user = "root" mysql_passwd = "root" # SQL Backup Directory root_dir = "/backup" back_dir = "/backup/databases" data_dir = "Databases "store_dir =" Database "If [! -D $ back_dir]; thenmkdir-p $ back_dirfi # backup database array db_arr = $ (echo "show databases; "| mysql-U $ mysql_user-p $ mysql_passwd-h $ mysql_host) # nodeldb = "test1" # current date = $ (date-d '+ 0 days' + % Y % m % d) # zip package password zippasswd = "passwd" zipname = "lczh _" $ date ". zip "#2. go to the backup directory CD $ back_dir #3. cyclic backup for dbname in $ {db_arr} doif [$ dbname! = $ Nodeldb]; thensqlfile = $ dbname-$ date ". SQL "mysqldump-U $ mysql_user-p $ mysql_passwd-h $ mysql_host $ dbname> export package all SQL files tar-zcppf $ root_dir/$ store_dir/$ zipname -- directory/$ root_dir /$ data_dir # Delete the SQL file after successful packaging if [$? = 0]; thenrm-r $ data_dirfi

Regular data cleanup scripts

Regularly clears the shell code of the backup file 14 days ago.

#! /Bin/bash-#1. parameter configuration # MySQL file backup directory backup_dir1 = "/backup/test1/" backup_dir2 = "/backup/Test2/" backdir_arr = ($ backup_dir1 $ backup_dir2) # keep_time = 14 # indicates the expiration time of the file. In the current week, crontab executes week =$ (date + % W) in the week 7 of the odd number) flag = 'expr $ week % 2' #2. clear expired files and only execute if [$ flag-EQ 1]; thenfor dir in $ {backdir_arr [*]} doif [-d $ dir] on the 7th day of an odd number of days. then # search for file data other than 14 days clean_arr = 'Find $ Dir-type F-mtime + $ keep_time-exec ls {}\; 'For cleanfile in $ {clean_arr} dorm $ cleanfiledonefidonefi

Crontab Configuration

0 5 ** 7 run the cleanup script

Note that if you have better database backup methods or insufficient shell scripts, you can leave a message with me. I promise to reply and cheer up!