Example of how shell automatically backs up mysql database every day

This article mainly shares with you examples of how shell automatically backs up mysql database every day. I hope it can help you.

Recommended related mysql video tutorials: "mysql tutorial"

##1. Mysql provides a mysqldump tool that can Conveniently export and import database information;

2. Use the command line shell to test and execute mysqldump, understand the necessary parameters, and check whether the generated sql backup file meets the requirements;

/usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /mnt/mysqlBackup/db_`date +%F`.sql

Notes:

1. Execute /usr/bin/mysqldump;
2. --opt is the collective name of quick, add-drop-table, add-locks, extended-insert and lock-tables parameters, which are generally used. , search for the specific meaning by yourself;
3. -u database user name -p database user password -h database address database name> exported file path;
4. `date +%F` is the current generated in the shell The date is in the format of 2015-11-05, so the file name generated when exported successfully is db_2015-11-05.sql;
5. Download the generated sql file, open it with a text editor for inspection, and import the test database locally. Check to see if there are any problems;

3. Organize and write more flexible shell scripts for easy reuse;

#!/bin/sh# Database infoDB_USER="batsing"DB_PASS="batsingpw"DB_HOST="localhost"DB_NAME="timepusher"# Others varsBIN_DIR="/usr/bin"           
 #the mysql bin pathBCK_DIR="/mnt/mysqlBackup"    
 #the backup file directoryDATE=`date +%F`
 # TODO# /usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > 
 /mnt/mysqlBackup/db_`date +%F`.sql$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME > 
 $BCK_DIR/db_$DATE.sql#还原数据库#用mysql-front导入前一天的 *.sql 文件即可恢复数据

Save to a file or upload to /usr/local/apache/htdocs/timepusher/sqlBak/ sqlAutoBak.sh

4. Test the Shell script

1. Enter the script file directory chmod +x sqlAutoBak.sh to add execution permission, otherwise an error Permission denied

2../ sqlAutoBak.sh, if the uploaded file is written in Windows, an error may be reported

/bin/sh^M: bad interpreter: No such file or directory

This is caused by different system encoding formats: .sh files edited in Windows systems may have invisible characters, so in Linux systems The above exception information will be reported when executed. You can use Notepad++ on Windows to convert to Unix format (select in the menu: Edit>File Format Conversion>Convert to UNIX)

3. After modification, upload and continue executing ./sqlAutoBak.sh, no error is reported. Check the exported sql file again.

5. Compress mysql backup data

1. Check the exported sql file and find that the file size is very large. Mysqldump also provides parameter settings for generating gzip compressed files

2 , modify sqlAutoBak.sh to the following

#!/bin/sh# Database infoDB_USER="batsing"DB_PASS="batsingpw"DB_HOST="localhost"DB_NAME="timepusher"# Others varsBIN_DIR="/usr/bin"            
#the mysql bin pathBCK_DIR="/mnt/mysqlBackup"    
#the backup file directoryDATE=`date +%F`
# TODO# /usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > 
/mnt/mysqlBackup/db_`date +%F`.sql# $BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME > 
$BCK_DIR/db_$DATE.sql$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME ｜ gzip > 
$BCK_DIR/db_$DATE.sql.gz# 还原数据库# 把 *.sql.gz 使用gunzip 或 本地的解压软件 
解压为 *.sql 文件# 用mysql-front导入前一天的 *.sql 文件即可恢复数据

3. After modification, upload and continue execution./sqlAutoBak.sh, no error is reported. If you use a text editor to view the exported *.sql.gz file, it should be a bunch of garbled characters.

4. Download it locally and open it with decompression software. After decompression, you can see the real *.sql file inside.

6. Set up linux scheduled tasks to execute the script;

1. Edit the scheduled task list

crontab -e

2. Insert The following line is because usually the website has the lowest traffic at 5 o'clock.

00 05 *   * * /bin/sh /usr/local/apache/htdocs/timepusher/sqlBak/sqlAutoBak.sh#每天早上 5:00am 执行

3. Check whether the task is created successfully

crontab -l

7. Check whether the automatically generated sql file meets the requirements the next day

If the generated file is decompressed, check whether it is question, then the script for automatic scheduled database backup is completed. Because too many generated files will take up a certain amount of space, it is recommended to clean up the files regularly (for example, once a month).

$, Supplement

1. If the user of the database is not assigned the permission to lock tables, the backup will report an error when using LOCK TABLES. That's because the mysqldump command locks the table by default when exporting, so there are two solutions. One is to give the user the permission to open the lock table; the other is to add the --skip-lock-tables parameter to the command. That is:

$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME --skip-lock-tables｜ gzip > $BCK_DIR/db_$DATE.sql.gz

Related recommendations:

3 steps to teach you how to automatically back up mysql database with one click_MySQL

Linux Automatic Backup MySQL database script code_MySQL

Use shell to automatically back up mysql database regularly_MySQL

The above is the detailed content of Example of how shell automatically backs up mysql database every day. For more information, please follow other related articles on the PHP Chinese website!