Shulou(Shulou.com)05/31 Report--

I believe many inexperienced people don't know what to do about the backup scheme in MySQL database. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

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:

* disable 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:

* replace the original directory with the replicated 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

Checking the alls.sql will record the 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:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Recovery is simple, and you can use pipes to import them into mysql

Independent of the storage engine, because it is generated by extracting data from the MySQL server, it eliminates the differences in the underlying data storage

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

Disadvantages:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

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

Slow logical backup restore: need MySQL to load and interpret statements, convert storage format, 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 the slave slave library It is recommended to enable the parameter log_slave_updates=1 mkdir / backup cd / backup dateDIR=$ (date + "% Y-%m-%d") mkdir-p $dateDIR/data path=/data/mysql/data for n in `mysql-uroot-p123456-e "show databases" from the library slave | grep-v "Database" `do mysqldump-uroot-p123456-default-character-set=utf8-Q-lock-all-tables-flush-logs-E-R-triggers-B $n | gzip > / backup/$dateDIR/data/$ {n} _ $dateDIR.sql.gz done binlog_rm=$ (tail-n 1$ path/mysql-bin.index | sed's mysql- uroot.\ / /') mysql- uroot-p123456-e "purge binary logs to'$binlog_rm'"

Note: this full backup script will lock the global table during export, and a new bin-log will be generated by refreshing at this time, during which a wait for a write operation will occur, and the newly generated bin-log file will not be written until the end of the export, and then the old bin-log file will be 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 binlog files just generated by * * from the directory where the current mysql database is stored in binlog, and cp all the old binlog files to the backup directory of the local server / backup/binlog binlog.

* * the number of current binlog files cleared when logging in to MySQL is reduced by one.

Mysql- uroot-p123456-e "purge binary logs to 'mysql-bin. (NMUI 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. InnoDB Hot Backup, the community version of Mysql's hot backup tool, is paid for and can only be tried for 30 days before the commercial version can be used.

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:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Start the redo log copy thread and copy the redo log sequentially from the checkpoint of *.

Start the idb file copy thread to copy the data of the innodb table

When the copy of idb file ends, call FTWRL to get the consistency site.

Back up non-innodb tables (system tables) and frm files

Since no new transaction is committed at this time, wait for the redo log copy to complete

After the redo log copy of * is completed, the data of both the innodb table and the non-innodb table are *.

Get the binlog locus, and the state of the database is consistent.

Release the lock and the backup ends.

After reading the above, do you know what backup methods are available in the MySQL database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.