MySQL database backup

The Mysql5.7 installation was recorded earlier, so here’s a backup of the data

1. Database version

Database: Mysql 5.7.31

Storage engine :InnoDB

2. Backup preparation

You can use the mysqldump tool of mysql to back up the database. It supports full database backup and can also specify a database for backup. The backup files are backed up in the form of files and the contents of the files are SQL statements.

Grammar:

#Mysqldump syntax formatMysqldump [options] -u username -p password Database name > backup filesCopy the code

Mysql doesn’t allow you to type your account password directly on the command line. Therefore, we need to set the account password in the configuration file in advance and modify the my.cnf file

#Opening a Configuration file
vim /etc/my.cnf
#Add in [mysqldump], if not manually
[mysqldump]
#account
user=username
#password
password=password
Copy the code

3. Start backup

Here is the need to backup the specified database, respectively to do a full backup and remove some table backup

#Perform full database backup
mysqldump --databases databasename --default-character-set=utf8 > home/dbback.sql 
#Note: If you want to remove multiple tables, use multiple --ignore-table=databasename. Tablename flags
mysqldump --databases databasename  --ignore-table=databasename.tablename  --default-character-set=utf8 > home/dbback.sql 
#Only a few tables are backed up
mysqldump databasename tablename  --default-character-set=utf8 > home/dbback.sql 
Copy the code

Above is a simple backup forecast, here defines a simple SHLL script, so that it can be executed daily with scheduled tasks

Remove some backups of tables

#! /bin/bash
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
export PATH
#Databases to be backed up. Separate multiple databases with Spaces
dbname='dbname'
#The backup time
backtime=`date +%Y%m%d%H%M%S`
#Log Backup Path
logpath='/home/dbback/log'
#Data Backup Path
datapath='/home/dbback/data'
#Ignore the table
ignore='--ignore-table=${table}.tablename'
#Logging headerEcho ${dbname} >> ${logpath}/mysqllog.log#Official Backup database
for table in $dbname; do
source=`mysqldump --databases ${table} ${ignore} --default-character-set=utf8 > ${datapath}/${backtime}ignore.sql` 2>> ${logpath}/mysqllog.log;
#The following operations are performed successfullyif [ "$?" == 0 ]; then cd $datapath#To save disk space, compress the database
tar jcf ${table}${backtime}ignore.tar.bz2 ${backtime}ignore.sql > /dev/null
#Delete the original file, leaving only the compressed file
rm -f ${datapath}/${backtime}ignore.sql
#Delete the backup generated seven days ago, that is, save only the backup generated within seven daysfind $datapath -name "*.tar.bz2" -type f -mtime +25 -exec rm -rf {} \; > /dev/null 2>&1 echo "${dbname}" >> ${logpath}/mysqllog.log else#If the backup fails, perform the following operations${dbname} = ${dbname} >> ${logpath}/mysqllog.log fi doneCopy the code

Full amount of backup

#! /bin/bash
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
export PATH
#Databases to be backed up. Separate multiple databases with Spaces
dbname='dbname'
#The backup time
backtime=`date +%Y%m%d%H%M%S`
#Log Backup Path
logpath='/home/dbback/log'
#Data Backup Path
datapath='/home/dbback/data'
#Logging headerEcho ${dbname} >> ${logpath}/mysqllog.log#Official Backup database
for table in $dbname; do
source=`mysqldump --databases ${table} --default-character-set=utf8 > ${datapath}/${backtime}.sql` 2>> ${logpath}/mysqllog.log;
#The following operations are performed successfullyif [ "$?" == 0 ]; then cd $datapath#To save disk space, compress the database
tar jcf ${table}${backtime}.tar.bz2 ${backtime}.sql > /dev/null
#Delete the original file, leaving only the compressed file
rm -f ${datapath}/${backtime}.sql
#Delete the backup generated seven days ago, that is, save only the backup generated within seven daysfind $datapath -name "*.tar.bz2" -type f -mtime +25 -exec rm -rf {} \; > /dev/null 2>&1 echo "${dbname} back up successfully!!" >> ${logpath}/mysqllog.log else#If the backup fails, perform the following operationsEcho "${dbname} failed!!" >> ${logpath}/mysqllog.log fi doneCopy the code

4. Common parameters of mysqldump are described

1. --all-databases, -a Description: Export all databases. Example: mysqldump -uroot -p --all-databases 2, --all-tablespaces, -y Description: Export all tablespaces. Example: mysqldump -uroot -p --all-databases --all-tablespaces 3, --no-tablespaces, -y Description: No tablespace information is exported. Example: mysqldump -uroot -p --all-databases --no-tablespaces 4, --add-drop-database Description: Add a DROP statement before each database is created. Example: mysqldump-uroot -p --all-databases --add-drop-database 5, --add-drop-table Description: Add a DROP table clause before each table is created. (Enable by default, use --skip-add-drop-table to deselect options.) example: Mysqldump -uroot -p -- databases -- databases -- skip-add-drop-table mysqldump -uroot -p -- databases -- databases -- skip-add-drop-table --add-locks Meaning: Add LOCK TABLES before each TABLE export and UNLOCK TABLE after each TABLE export. (This is enabled by default, use --skip-add-locks to disable the option.) Example: Mysqldump -uroot -p -- databases -- databases -- skip-add-locks mysqldump -uroot -p -- databases -- skip-locks --create-options, -a create TABLE statement includes all MySQL feature options. (Enabled by default) Example: mysqldump -uroot -p --all-databases 8, --databases, -b Description: Several databases are exported. All the name parameters following the parameter are treated as database names. Mysql dump -uroot -p --databases test mysql 9, --default-character-set Description: mysqldump -uroot -p --databases test mysql 9, --default-character-set Mysqldump -uroot -p --all-databases --default-character-set=utf8 10, --events, -e Example: mysqldump -uroot -p --all-databases --events 11, --flush-logs Meaning: Flush the logs before exporting. Note that if multiple databases are exported at once (using the options --databases or --all-databases), the logs will be flushed database by database. Except using --lock-all-tables or --master-data. In this case, the log is flushed once, and all corresponding tables are locked at the same time. Therefore, if you want to export and flush logs at the same time, you should use --lock-all-tables or --master-data and --flush-logs. Example: mysqldump-uroot -p --all-databases --flush logs 12, --flush PRIVILEGES Meaning: A FLUSH PRIVILEGES statement is issued after the MYSQL database is exported. For proper recovery, this option should be used whenever you export mysql database and rely on mysql database data. Example: mysqldump -uroot -p --all-databases --flush- PRIVILEGES 13, --help Meaning: The help information is displayed and the user exits. Mysqldump --help 14, --host, -h Meaning: Host information to be exported Example: Mysqldump -uroot -p --host=localhost --all-databases 15, --ignore-table Description: The specified table is not exported. When you specify to ignore multiple tables, you need to repeat this multiple times, one table at a time. Each table must specify both the database and the table name. For example: --ignore-table=database.table1 --ignore-table=database.table2... Mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user 16, --lock-all-tables, -x Commit requests lock all tables in all databases to ensure data consistency. This is a global read lock and the --single-transaction and --lock-tables options are automatically turned off. Mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables 17, --no-create-db, -n Export only the data without adding the CREATE DATABASE statement. Mysqldump -uroot -p --host=localhost --all-databases --no-create-db 18, --no-create-info, -t Export only data without adding CREATE TABLE statements. Example: mysqldump -uroot -p --host=localhost --all-databases --no-create-info 19, --no-data, -d Description: No data is exported, only the database table structure is exported. Mysqldump -uroot -p --host=localhost --all-databases --no-data 20, --password, -p --routines, -r 表 库 库 库 库 库 库 库 库 库 库 库 库 Mysqldump -uroot -p --host= sysdatabases -- databases --tables Overrides the --databases (-b) parameter to specify the name of the table to be exported. In later versions, tables will be used instead of tables. Example: mysqldump -uroot -p --host=localhost -- Databases test --tables test 24, --triggers Meaning: Triggers is triggered. This option is enabled by default, disable it with --skip-triggers. Example: mysqldump -uroot -p --host=localhost --all-databases --triggers 25, --user, -u Description: Specify the user name for the connection. --where, -w -- dump only the records selected by the specified WHERE condition. Note that if the condition contains special Spaces or characters for the command interpreter, be sure to reference the condition. Example: mysqldump -uroot -p --host=localhost --all-databases --where= "user= 'root'" Example: mysqldump -uroot -p --host=localhost --all-databases -- XMLCopy the code

Reference: www.cnblogs.com/flagsky/p/9…

**mysql scheduled automatic backup requires a Centos scheduled task

Centos Scheduled tasks: juejin.cn/post/685811…