On the backup scheme of MySQL database

Since the work of the IT Internet has been a few years ago, I was really lazy, never write blog, never summed up the work experience, led to their own technical upgrading is very slow, growth is very slow, serious began to write blog is from the beginning of last year, write something, summed up, really good, So look back on their own past, feel that they are still more or less left something, not to recall too much emptiness, too sorry. Okay, nonsense, don't say much, next share your little experience with MySQL data backup for nearly 4 years.

data is the lifeblood of an internet company, the security of the database and the integrity of the record is crucial, so we need to be skilled in the work of data backup and recovery, which is a qualified operation DBA must have the professional skills .

Here's a simple summary of MySQL backup is divided into 3 types
Divided into cold backup, logical backup, hot backup

1. Cold backup:

Generally used mainly for non-core business, this kind of business is generally allowed business interruption, cold backup is characterized by several fast, recovery is also the simplest. Usually directly re-physical files to achieve cold backup

1.1 Backup process:

First shutdown MySQL Service
The second step is to copy the Datas Data directory (including the ibdata1) and the log directory (containing the ib_logfile0,ib_logfile1,ib_logfile2) to the disk, or to another disk on the local

1.2 Recovery process:

First replace the original directory with the copied data directory and log directory
The second is to start MySQL

2. Logical Backup Mysqldump

Commands for production scenario backup:
Production scenario different engine mysqldump backup command
MyISAM Engine Enterprise Production Backup command (for all engines or hybrid engines):

mysqldump -uroot -p123456 -A -B -F -R --master-data=2 -x --events|gzip >/opt/all.sql.gz

Tip:-F can also be used, with--master-data some repetition.
InnoDB Engine Enterprise Production Backup command: Recommended use

mysqldump -uroot -p123456 -A -B -F -R --master-data=2  --default-character-set=utf8  --events --single-transaction |gzip >/opt/all.sql.gz

Tip:-F can also not be used. There are some repetitions with--master-data.
--DEFAULT-CHARACTER-SET=UTF8 specifies that the character set of the SQL data to be backed up is utf8, of course, this should be known in advance that the MySQL library on the line all adopt the character set is UTF8, otherwise in the restore to the online library will appear character set inconsistency problem garbled

Tip: Logical backups are typically data migrations or small amounts of data, and logical backups take the form of data export

2.1mysqldump Library tables and other backup instructions:

Fully prepared:

Mysqldump –uroot –p  -q –single-transaction –A >>all.sql

To export multiple databases:

Mysqldump –uroot –p  -q –single-transaction –B  test01 wjw01 test02 >test01_wjw01_test02.sql

Export a wjw01 table of a test library:

Mysqldump –uroot –p  -q –single-transaction –b  test  wjw01>>test_wjw01.sql

To export only table structures:

Mysqldump –uroot –q –d  --skip-triggers

To export a stored procedure only:

Mysqldump –uroot –q -Rtdn  

You only need to export the trigger:

Mysqldump –uroot –p –q –tdn –triggers

You only need to export events:

Mysqldump –uroot –p –q –Etdn –skip-triggers

You only need to export the data:

Mysqldump –uroot –p –q  --skip-transaction --skip-triggers –t

To create a new slave online, execute the following command on master:

Mysqldump –uroot –p –q –single-transaction –master-data=2 -A >>all.sql

Tip: A new parameter is added to the mysql5.5:--dump-slave, which can be used to dump data at the slave end to create a new slave, which is designed to prevent excessive pressure on the main library.

Execute the following command on the slave:

Mysqldump  -uroot –p  -A –dump-slave=2 –q –single-transaction >/tmp/all.sql查看alls.sql 里面会记录slave上的那个点。

Note:--dump-slave is used to dump data on slave, creating a new slave

2.2Mysqldump pros and cons and usage scenarios

Advantages:
1, restore simple, you can use the pipeline to input them to MySQL
2, regardless of the storage engine, because it is extracted from the MySQL server to generate data, so eliminate the underlying data storage differences
3, help to avoid data corruption. If the disk drive is faulty and you want to copy the original file, you will get a damaged backup
Disadvantages:
1, must have the database server to complete the logic work, need more CPU cycle
2. Slow logical backup: Requires MySQL to load and interpret statements, convert storage formats, rebuild engines

usage Scenario: recommended for scenarios where the amount of data in a MySQL database is not very large. Simple and flexible, easy to operate because of easy backup
When the database is special, such as more than 30G, when backing up MySQL data, it is also recommended not to adopt mysqldump, because the time of backup and the time to recover the database is too long, will create a lock on the library table, the business impact on the line is relatively large

2.3 Simple Introduction to MySQL full-scale backup + incremental backup Binlog

Note: It is strongly recommended that MySQL turn on the row format to record Binlog, although the disk's IO and disk space consumption is relatively large, but compared to the security and integrity of the data, disk resources that is trivial

Full amount of Backup script content:

#!/bin/bash#mysql全量备份脚本建议在slave从库上运行，从库slave上建议开启参数log_slave_updates=1mkdir /backupcd /backupdateDIR=$(date +"%Y-%m-%d")mkdir -p $dateDIR/datapath=/data/mysql/datafor n in `mysql -uroot -p123456 -e "show databases"|grep -v "Database"`domysqldump -uroot -p123456 --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B $n|gzip >/backup/$dateDIR/data/${n}_$dateDIR.sql.gzdonebinlog_rm=$(tail -n 1 $path/mysql-bin.index|sed ‘s/.\///‘)mysql -uroot -p123456 -e "purge binary logs to ‘$binlog_rm‘"说明:这个全量备份脚本，会在导出的时候锁住全局表，并且此时刷新产生一个新的bin-log,期间会产生写操作的等待，直到导出结束后才会写入新产生的bin-log文件，然后旧的bin-log 文件会被删除删除掉，一般在晚上2:00业务低峰期执行操作

Make a local Bin-log incremental backup

After you perform a full-scale backup script, you can perform an incremental backup script.
The incremental backup script idea is simple:

先mysqladmin -uroot -p123456 flush-logs 刷新新的binlog文件，此时mysql写入到新的binlog文件中。然后把当前mysql数据库存放binlog的目录中抛去刚才最新生成的binlog文件，其余旧的binlog文件全部cp到本地服务器/backup/binlog  binlog的备份目录下。最后在登录MySQL清除当前的binlog文件数减一mysql -uroot -p123456 -e "purge binary logs to ‘mysql-bin.(n-1)‘"

3. Hot Backup and Recovery

Hot backup is also a direct copy of the data physical files, and cold backup, but hot backup can be non-stop direct replication, generally used for the important core business of uninterrupted hours. MySQL community version of the hot Backup tool is paid for a 30-day trial, the commercial version can be used permanently, InnoDB
Percona company released a xtrabackup hot standby tool, like a paid tool, support online backup (without affecting the data read and write) is a good alternative to business tools InnoDB warm backup.
The Xtrabackup hot standby tool is an open source tool that enables very fast backup and recovery of MySQL databases. Because Xtrabackup supports backing up the InnoDB table, the tool we use in the actual production environment is Innobackupex, which is a layer of xtrabackup encapsulation. The Innobackupex script is used to back up non-InnoDB tables, and the Xtrabackup command is called to back up the InnoDB table, and the basic flow of Innobackupex is as follows:

1.开启redo日志拷贝线程，从最新的检查点开始顺序拷贝redo日志；2.开启idb文件拷贝线程，拷贝innodb表的数据3.idb文件拷贝结束，通知调用FTWRL，获取一致性位点4.备份非innodb表(系统表)和frm文件5.由于此时没有新事务提交，等待redo日志拷贝完成6.最新的redo日志拷贝完成后，相当于此时的innodb表和非innodb表数据都是最新的7.获取binlog位点，此时数据库的状态是一致的。8.释放锁，备份结束。

For specific use, please refer to the blog: http://blog.51cto.com/wujianwei/1934084

About the MySQL database backup plan to here is finished, Bo master technical level is limited, if not, please timely pointed out. Welcome to Exchange Study together

On the backup scheme of MySQL database