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

This article mainly introduces the principle and advantages of XtraBackup backup, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

Principle of MySQL master-slave synchronization

MySQL master-slave synchronization is implemented on the basis of MySQL master-slave replication (Master-Slave Replication). Through binlog set on Master MySQL (making it open), binlog is read from Master MySQL through an I / O thread on Slave MySQL and transferred to the relay log of Slave MySQL, and then the SQL thread of Slave MySQL reads the relay log from the relay log and applies it to the database of Slave MySQL. In this way, the master-slave data synchronization function is realized.

Principle of XtraBackup backup

Innobackupex keeps tracking InnoDB's log files in the background thread, and then copies InnoDB's data files. After the data file is copied, the log replication thread also ends. This results in a copy of the data that is not at the same point in time and the transaction log after the backup starts. After completing the above steps, you can use the InnoDB crash recovery code to perform transaction logs (redo log) to achieve data consistency.

XtraBackup advantages:

There is no need to stop the database for InnoDB hot backup incremental backup MySQL stream compression to transfer to other cloud servers can easily create a master-slave synchronous backup MySQL without increasing the server load

Backup is divided into two processes:

Backup, backup phase, tracking transaction logs and copying data files (physical backup). Preparing, replay the transaction log so that all data are at the same point in time and reach a consistent state

Why do you want to copy from master?

I think this is a question that needs to be thought out before implementation. Is it to achieve read-write separation, lighten the load of the main library or data analysis? Backup and restore for the sake of data security? Master-slave switch for high availability?

In most scenarios, the above three question marks can be solved by one master and one slave, and it is recommended that you have at least one slave library in any production environment. If your read operation is particularly stressful, or even one master and multiple slaves, different slave can play different roles, such as using different indexes, or different storage engines, or using a small memory server as slave only for backup. (of course, too many slaves will also put pressure on the load and network bandwidth of master. Cascade replication can be considered at this time, that is, A-> B-> C)

Replication types supported by mysql:

(1): statement-based replication: a SQL statement executed on the master server and the same statement executed on the slave server. MySQL uses statement-based replication by default, which is more efficient.

Row-based replication is automatically selected when it is found that exact replication is not possible.

(2): line-based replication: copy the changes instead of executing the command from the server. Support starts with mysql5.0

(3): mixed-type replication: statement-based replication is used by default, and row-based replication is used once it is found that statement-based replication cannot be accurately replicated.

Replication types can also be divided into asynchronous replication and semi-synchronous replication.

It usually refers to asynchronism, that is, after the master library executes the Commit, it can successfully return to the client after the Binlog log is written by the master database. There is no need to wait for the Binlog log to be sent to the slave database. If the master database goes down, the log may be lost. On the other hand, semi-synchronous replication waits for one of the Binlog transactions to be successfully written to Relay Log from the slave database before returning the Commit operation to the client successfully. In this way, semi-synchronous replication ensures that there are at least two log records after the transaction is successfully committed, one on the master database Binlog and the other on the Relay Log of the slave database, thus further ensuring data integrity. Semi-synchronous replication depends largely on the master-slave network RTT (round trip delay), which exists in the form of plug-in semisync_master/semisync_slave.

Add:

Mysql 5.7 began to add multi-source replication, which is useful for having many mysql instances at the same time, and Aliyun RDS (migration) implements a similar approach. Starting with MySQL 5.6.2, mysql binlog supports checksum parity, and 5.6.6 is enabled by default (CRC32), which has an impact on your own simulation of mysql replication scenarios.

MySQL replication technology has the following characteristics:

(1) data distribution (Data distribution)

(2) load balancing (load balancing)

(3) backup (Backups)

(4) High availability and fault tolerant line High availability and failover

Start the XtraBackup backup operation as follows

The first thing to explain is: mysql master-slave data synchronization, real-time synchronization of master mysql data from mysql. Here, it is generally required that the master msyql is the same as the slave mysql version or that the master msyql is not higher than the slave mysql version (version query > select version ()). The general robust approach is to make the version the same, because the binlog (binary log) format may be different between different mysql versions, which may lead to synchronization exceptions.

Master database mysql > select version (); +-+ | version () | +-+ | 5.6.35-log | +-+ 1 row in set (0.00 sec) ip address: 192.168.212.11 Slave database: MariaDB [(none)] > select version () +-+ | version () | +-+ | 10.2.6-MariaDB | +-+ 1 row in set (0.00 sec) ip address: 192.168.212.10 first step: mysql > grant replication slave on *. * to 'ff'@'192.168.212.10' identified by' chylinux' Query OK Step 2: (the operation on the master database is as follows) the second step is to use only full backup (restore), not incremental backup (restore) [root@chy01 ~] # rpm-ivh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm to get. Http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm warning: / var/tmp/rpm-tmp.tIRhy2: header V4 DSA/SHA1 Signature Key ID cd2efd2a: preparing for NOKEY. # # [100%] upgrading / installing... 1:percona-release-0.1-3 # # [100%] (first rpm installs the extension of yum Zhanyuan) [root@chy01 ~] # yum list | grep percona [root@chy01 ~] # yum install percona-xtrabackup (package for installing backup tools) mysql > GRANT RELOAD LOCK TABLES, REPLICATION CLIENT ON *. * TO 'chy'@localhost identified by' aminglinux' Query OK, 0 rows affected (0.00 sec) (create a user in the master database and authorize) mysql > flush privileges Query OK, 0 rows affected (0.01sec) (refresh mysql) [root@chy01 ~] # mkdir / data/backup (create backup directory The database where mysql backup is stored) [root@chy01 ~] # ls-l / data/backup/ Total usage 4drwx-9 root root 4096 September 2 18:08 2017-09-02 18-08-29 (as above is the backup database) [root@chy01 backup] # tar-zcvf 2017-09-02 18-08-29.tar.gz 2017-09-02 18-08-29 [root@chy01 backup] # du-sh 2017-09- 02 percent 18-08-29.tar.gz540K 2017-09-02 18-08-29.tar.gz [root@chy01 backup] # rsync-avP / data/backup/2017-09-02 18-08-29.tar.gz 192.168.212.10:/data/backup/root@192.168.212.10's password:sending incremental file list2017-09-02 18-08-29.tar.gz 551406 100 40.40MB/s 0:00:00 (xfer#1 To-check=0/1) sent 3090 bytes received 4531 bytes 1172.46 bytes/sectotal size is 551406 speedup is 72.35 (synchronize with rsync) step 3 (copy the data from the backup master database + restore the database) [root@chy ~] # mkdir / data/backup (you also need to create a directory from the database) [root@chy ~] # ls / data/backup/2017-09-02 synchronization 18-08-29.tar.gz (you can see that it has been the same as Step succeeded) [root@chy] # du-sh / data/backup/2017-09-02 "18-08-29.tar.gz540K / data/backup/2017-09-02" 18-08-29.tar.gz (same size) [root@chy backup] # tar-xzvf / data/backup/2017-09-02 "18-08-29.tar.gz (decompression) [root@chy backup] # innobackupex-- use-memory=512M-- apply-log 2017-09-02 _ 18-08-29 (for initialization) [root@chy ~] # / etc/init.d/mariadb stopStopping mariadb (via systemctl): [OK] (close the database Then restore) [root@chy backup] # rm-rf / data/mariadb/ [root@chy backup] # mkdir / data/mariadb [root@chy backup] # chown mysql1:mysql1 / data/mariadb [root@chy backup] # innobackupex-defaults-file=/etc/my.cnf-datadir=/data/mariadb/-use-memory=512M-copy-back 2017-09-02 18-08-29 (I actually reported an error here Innobackupex version 2.3.9 based on MySQL server 5.6.24 Linux (x86 datadir 64) (revision id: fde0e3e) Error: datadir must be specified. (the error here is that you must specify the directory of the new datadir You can restore successfully after you specify) [root@chy backup] # ls / data/mariadb/db1 ib_logfile0 mysql performance_schema xtrabackup_binlog_pos_innodb zhucongibdata1 ib_logfile1 mysql2 test xtrabackup_info zrlog (view the restored database) [root@chy backup] # / etc/init.d/mariadb startStarting mariadb (via systemctl): Warning: mariadb.service changed on disk. Run 'systemctl daemon-reload' to reload units. [OK] [root@chy backup] # systemctl daemon-reload [root@chy backup] # / etc/init.d/mariadb start

Starting mariadb (via systemctl): [OK]

Read the above about the principle and advantages of XtraBackup backup, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.