Shulou(Shulou.com)06/02 Report--

It has been some years since I joined the IT Internet. Before, I was really lazy. I never wrote blog posts or summed up work experience. As a result, my skills improved very slowly, and my growth was also very slow. I started to write blog articles only last year. Writing something, summing up, is really good. Looking back on my past, I feel that I have left something behind more or less. In retrospect, it will not be too empty and too regrettable. All right, let's cut the crap and share some of my experience in the past 4 years when I came into contact with MySQL data backup.

Data is the lifeblood of an Internet company, the security of the database and the integrity of the record are very important, so we need to be proficient in data backup and recovery, which is also a professional skill that a qualified operation and maintenance DBA must have.

I briefly summarize here that there are three types of MySQL backups.

Divided into cold backup, logical backup, hot backup

1. Cold backup:

Generally, it is mainly used for non-core business, which generally allows business interruption. Cold backup is characterized by fast degrees and the simplest recovery. Usually replicate physical files directly to achieve cold backup

1.1 backup process:

First, turn off the mysql service

The second step is to copy the datas data directory (including ibdata1) and log directory (including ib_logfile0,ib_logfile1,ib_logfile2) to disk or to another local disk

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 scene backup:

Mysqldump backup commands for different engines in production scenarios

Myisam engine Enterprise produces backup commands (suitable for all engines or hybrid engines):

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

Hint:-F may not be used, and there is some repetition with-- master-data.

Innodb engine enterprise production backup command: recommended

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

Hint:-F may not be used. There is some repetition with-- master-data.

-- default-character-set=utf8 specifies that the character set of the backed up sql data is utf8. Of course, this should be known in advance that all online mysql libraries use utf8 character sets, otherwise there will be inconsistent character sets and garbled codes in the restored online libraries.

Tip: logical backup is generally used for data migration or when the amount of data is very small, and logical backup uses the way of data export.

Backup instructions such as 2.1mysqldump database tables:

Complete:

Mysqldump-uroot-p-Q-single-transaction-A > > all.sql

Export multiple databases:

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

Export a wjw01 table from a test library:

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

Export only the table structure:

Mysqldump-uroot-Q-d-- skip-triggers

When you only need to export the save process:

Mysqldump-uroot-Q-Rtdn-skip-triggers

You just need to export the trigger:

Mysqldump-uroot-p-Q-tdn-triggers

You only need to export the event:

Mysqldump-uroot-p-Q-Etdn-skip-triggers

You just 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 has been added to the mysql5.5:-- dump-slave, which can be used to dump data on the slave side and establish a new slave, in order to prevent excessive pressure on the main database.

Execute the following command on slave:

Mysqldump-uroot-p-A-dump-slave=2-Q-single-transaction > / tmp/all.sql look inside the alls.sql and record that point on the slave.

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

Advantages and disadvantages and usage scenarios of 2.2Mysqldump

Advantages:

1. It is easy to recover. You can input them to mysql using pipes.

2. It has nothing to do with the storage engine, because it is generated by extracting data from the MySQL server, so the difference of the underlying data storage is eliminated.

3. Help to avoid data corruption. If the disk drive fails and you want to copy the original file, you will get a corrupted backup

Disadvantages:

1. There must be a database server to complete the logical work, and more cpu cycles are needed.

2. Slow speed of logical backup and restore: need MySQL to load and interpret statements, convert storage format, and rebuild engine

Usage scenarios: it is recommended for scenarios where the amount of data in the MySQL database is not very large. Because backup is convenient, simple, flexible and easy to operate.

When the database is special, such as more than 30G, it is recommended not to use mysqldump when backing up MySQL data, because the backup time and the time to restore the database are too long, which will cause locks to the database tables and have a great impact on the online business.

2.3 A brief introduction to MySQL full backup + incremental backup binlog

Note: MySQL is strongly recommended to turn on Row format to record binlog in production. Although this consumes a lot of disk IO and disk space, disk resources are trivial compared with data security and integrity.

Full backup script contents:

#! / bin/bash#mysql full backup script is recommended to run on slave slave library It is recommended to enable the parameter 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" from the library slave | 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 binlog_rm'.\ /') mysql- uroot-p123456-e "purge binary logs to'$binlog_rm'" description: this full backup script The global table is locked during export, and a new bin-log is generated during the refresh, during which the wait for write operations occurs. The newly generated bin-log file is not written until the end of the export, and then the old bin-log file is deleted. The operation is usually performed at 2:00 in the evening during the business trough.

Perform local bin-log incremental backups

After the full backup script is executed, the incremental backup script can be executed.

The idea of an incremental backup script is simple:

First mysqladmin-uroot-p123456 flush-logs refreshes the new binlog file, and then mysql is written to the new binlog file. Then discard the newly generated binlog files from the directory where the current mysql database is stored in binlog, and cp all the old binlog files to the local server / backup/binlog binlog backup directory. Finally, after logging in to MySQL, clear the current number of binlog files minus one mysql- uroot-p123456-e "purge binary logs to 'mysql-bin. (NMel 1)'" 3. Hot backup and recovery

Hot backup is also used to directly copy physical data files, just like cold backup, but hot backup can be copied directly without downtime, which is generally used for important core businesses that are uninterrupted 24 hours a day. Mysql's community version of hot backup tool InnoDB Hot Backup is paid for and can only be tried for 30 days before the commercial version can be used permanently.

Percona has released a xtrabackup hot backup tool, which, like paid tools, supports online hot backup (backup does not affect the reading and writing of data) is a good alternative to InnoDB Hot Backup, a commercial tool.

Xtrabackup Hot standby tool is an open source tool that can back up and restore mysql databases very quickly. Because Xtrabackup supports backing up innodb tables, the tool we use in the actual production environment is innobackupex, which is a layer of encapsulation of xtrabackup. The innobackupex script is used to back up non-InnoDB tables, and the xtrabackup command is called to back up InnoDB tables. The basic process of innobackupex is as follows:

1. Start the redo log copy thread and copy the redo log sequentially from the latest checkpoint; 2. Start the idb file copy thread, copy the data of the innodb table, end the 3.idb file copy, notify the call FTWRL, and obtain the consistency point 4. Back up non-innodb tables (system tables) and frm files 5. Since there is no new transaction commit at this time, wait for the redo log copy to complete 6. 5. After the latest redo log copy is completed, the innodb table and non-innoDB table data at this time are both the latest 7. 5%. Get the binlog locus, and the state of the database is consistent. 8. Release the lock and the backup ends.

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

This is the end of the introduction of the backup scheme for Mysql database. The blogger's technical level is limited. If there is anything wrong, please point it out in time. Welcome to exchange and study together.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.