Mysql5.7.12 Multi-source replication Xtrabackup

? Experimental environment

1. System environment
   View System version command: Cat/etc/redhat-release
   
2. This experiment is compiled installation of MySQL, too many pits, not recommended in the future by the way of compiling installation
   
3. Before starting MySQL, first execute the following command to create a link
   Ln-s/usr/local/mysql/bin/mysql/usr/bin
4. MySQL initial root password is stored in/root/.mysql_secret
   To connect to the database after the password is found, be sure to change the root password and restart MySQL

? Build Master-master-slave
mysql01:192.168.4.63 Master1
mysql02:192.168.4.64 Master2
mysql03:192.168.4.65 Slave1
Master1 and Master2 are mainly prepared for each other, Slave1 is the master1,master2 of the standby machine (multi-source replication)

1. First, realize the function of MASTER1,MASTER2 Mutual primary preparation

   1. To modify/ETC/MY.CNF, add the following:
      

      Multi-source replication adds a concept called channel, which only needs to be differentiated at the end of each statement using the for channel. Because the principle of replication has not changed, when the Gtid is not opened from the library needs master-info-repository, relay-log-info-repository set to table, otherwise will error:
      

   2. Authorize Sync Account
      
      
   3. Detecting file and POS values for M1 and M2
      
      

   4. The Execute command sets the M1,M2 to mutual master
      
      

   5. View slave status
      
      
2. Realize Slave1 Multi-source replication function, that is, S1 is m1,m2 of the co-standby machine
   1. Authorize Sync Account
      
      
   2. Execute the command to S1 as the m1,m2 of the device.
      
   3. View slave status
      
      
      Note: The configuration of the multi-master itinerary has been implemented, and the source replication is also multiprocessing. Msql5.7 the above version can be implemented with Gtid mode

Database backup is a very important task for DBAs, in this case we use Xtrabackup to complete the data backup requirements

1. Installing Xtrabackup in Slave1
   
   2. Once the installation is complete, you will be able to see a Percona-release.repo file under/ETC/YUM.REPOS.D
   
   3. Test repository to ensure that Percona Xtrabackup related packages are already in repository
   
   4. Install Percona Xtrabackup Package
   
   5. View the installation results
   
   6. Create a backup user
      The user who backs up the database needs to have the appropriate permissions, and if you want to back up with a user with minimal permissions, you can create such a user based on the following command:
      
      
2. Perform a full backup:
   Innobackupex--user=root--password=cbichina_123/extrabackup
   When execution is complete, the current time folder is automatically created
   
   The role of--apply-log is to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing the committed transactions to the data file.
   Innobackupex--apply-log/extrabackup/2018-03-06_18-15-40/

3. Performing an incremental backup
   At the end of this command execution, the Innobackupex command creates a new time-named directory in the/backup directory to hold all incremental backup data. Also, when incremental backups are performed after an incremental backup, their--incremental-basedir should point to the same directory as the last incremental backup.
   Innobackupex--incremental/extrabackup/--user=root--password=cbichina_123

4. Restores
   Make sure that "innobackupex:completed ok!" appears on the top line of information.
   Innobackupex--copy-back/extrabackup/2018-03-06_18-15-40/

Mysql5.7.12 Multi-source replication Xtrabackup