Automatic Backup of linux unix mysql database

This article introduces how to use shell commands in linux and unix to implement the script code for automatic backup of mysql databases. If you need to know about it, you can simply modify it.

File Name: mysql_data_backup.sh (Execution permission required)

Mysql database table automatic backup shell script (version 2)

Call example:

The Code is as follows:	Copy code
Mysql_data_backup.sh

Features:

1. Generate backup logs

2. automatically checks the Database List in the specified database, backs up each database separately, and finally packs it as a tar file.

3.you can specify the number of saved files (.tar) and clear the previous archives in chronological order.

4. log on to mysql and use source xxx. SQL to automatically delete and create databases and restore data.

Variable to be modified:

The Code is as follows:	Copy code
Db_host // Database Host Db_user // database account Db_passwd // Database Password BackupFileNum // The number of backup archive files that are retained. If this number is exceeded, the files are automatically cleared. BackupDir // directory where the backup file is stored Logfile // Log File Path

----------------------------------------

How to restore data:

See mysql database table automatic backup shell script (first version)

The Code is as follows:

Copy code

#! /Bin/bash # Mysq link user config Db_host = 192.168.0.222 Db_user = admin DB _ passwd = 123456 # Backup File number BackupFileNum = 4 # Log file Logfile = '/sqlbackup/backup. Log' # Backup of directory Do not bring/ BackupDir = "/sqlbackup"   # Check database name Echo 'backup begin' Begin_time = 'date' + % s'' # Get databases list Databases_content = 'mysqlshow-u $ db_user-p $ db_passwd-h $ db_host | sed's/| // G' | sed's // G' | sed '1, 4d '| sed' $ d'' # Check databases if emptyed then exit Databases_content_check = 'echo-n $ databases_content>/tmp/mysql_backup_check.tmp' If [! -S/tmp/mysql_backup_check.tmp]; then Echo 'database Emptyed Backup stop .' Exit Fi # Plan backup of SQL databases Databases = ($ {databases_content }) # Get databases length Databases_len =$ {# databases [*]} # Backup of directory for date Datestamp = $ (date "+ % Y % m % d ") # Final backup directroy FileDir = "$ backupDir/$ datestamp" # Auto create target directory If [! -D $ backupDir]; then Mkdir $ backupDir Fi If [! -D $ fileDir]; then Mkdir $ fileDir Fi If [! -D $ logfile]; then Touch $ logfile Fi Echo "total $ databases_len databases ." For (I = 0; I <$ databases_len; I ++ )) Do DbName = "$ {databases [$ I]}" FileName = "$ {databases [$ I]}. SQL" FilePath = "$ fileDir/$ fileName" Echo "Database [$ dbName] Backup ..." Echo "drop database if exists '$ dbname';"> $ filePath Echo "create database '$ dbName 'character SET 'utf8';"> $ filePath Echo "use $ dbName;" >>$ filePath Mysqldump-h $ db_host-u $ db_user-p $ db_passwd $ dbName >>$ filePath Done       # Tar files Cd $ backupDir Tar cvf extends datestamp;.tar./$ {datestamp} # Delete source archive Rm-rf./$ {datestamp} End_time = 'date' + % s'' Total_time = $ [end_time-begin_time] Echo 'backup done' Echo "Total time: $ {total_time} second" Now_date = 'date' + % Y-% m-% d % k: % M: % s'' # Save log Echo "$ {now_date}: Backup Databases: Total time [$ {total_time} s]" >>$ logfile   # Begin clean excess of backup file # Count backup dir name of length; BackupDir_length = 'expr length "$ backupDir "' # Get tar file list BackupFile_list = 'Find $ backupDir-name "*. tar" | sort-n-r-k 1. $ backupDir_length' # Set array BackupFiles = ($ {backupFile_list }) BackupFile_length =$ {# backupFiles [*]} If [$ backupFile_length-gt $ backupFileNum]; then For (I = $ backupFile_length; I> $ backupFileNum; I --)) Do FileName = "$ {backupFiles [$ I-1]}" Rm-rf $ fileName # Save log Echo "file: $ fileName deleted" Echo "file: $ fileName deleted" >>$ logfile Done Else Echo "backup file number normal ." Fi