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

The following brings to you about the use of xtrabackup tools to achieve Mysql 5.7.12 multi-source replication, interested words to see this article, I believe that after reading the use of xtrabackup tools to achieve Mysql 5.7.12 multi-source replication for everyone how much help.

Experimental environment

system environment

To check the system version command: cat /etc/redhat-release

This experiment is to compile and install Mysql, pit too many, it is not recommended to use the way of compilation and installation in the future

Before starting Mysql, execute the following command to create a link

ln -s /usr/local/mysql/bin/mysql /usr/binMysql The initial root password is stored in/root/.mysql_secret

After finding the password, connect to the database. Be sure to change the root password and restart Mysql.

Building 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 master and slave to each other, Slave1 is Master1 and slave to Master2 (multi-source replication)

First of all, Master1 and Master2 are mutually active and standby.

Modify/etc/my.cnf to add the following:

Multi-source replication adds a concept called Channel, which only needs to be distinguished by using For Channel at the end of each statement. Since the replication principle has not changed, master-info-repository and relay-log-info-repository need to be set to table when GTID is not enabled, otherwise an error will be reported:

Authorize synchronization account

Find files and Pos values for M1 and M2

Execute the command to set M1 and M2 to be active and standby for each other.

View slave status

Implement Slave1 multi-source replication function, i.e. S1 is M1,M2 shared backup authorized synchronization account

Execute command to use S1 as backup for M1 and M2

View Slave Status

Note: Up to now, the configuration of multi-master and single-standby has been realized, also known as multi-source replication. Msql version 5.7 and above can use GTID mode to achieve this function

Database backup is a very important job for DBAs. In this case, we use xtrabackup to complete the data backup requirements.

Install xtrabackup in Slave1

2. After installation, you will see a percona-release.repo file under/etc/yum.repos.d

3. Test the Repository to make sure Percona XtraBackup dependencies are already in the Repository

4. Install Percona XtraBackup

5. review the installation results

Create backup user

The user who backs up the database needs to have the appropriate permissions. If you want to use a user with minimum permissions for backup, you can create such a user based on the following command:

Perform a full backup:

innobackupex --user=root --password=cbichina_123 /extrabackup

When execution is complete, a folder for the current time is automatically created

The effect of using--apply-log is to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to the data file.

innobackupex --apply-log /extrabackup/2018-03-06_18-15-40/

perform an incremental backup

When this command completes, innobackupex creates a new time-named directory in the/backup directory to store all incremental backup data. Also, when you perform an incremental backup after an incremental backup, its--incremental-basedir should point to the directory where the previous incremental backup was performed.

innobackupex --incremental /extrabackup/ --user=root--password=cbichina_123

reduction

Please make sure the last line of the message appears "innobackupex: completed OK! "。

innobackupex --copy-back /extrabackup/2018-03-06_18-15-40/Read the above details about using xtrabackup tool to realize Mysql 5.7.12 multi-source replication, whether you have gained anything. If you want to know more about it, you can continue to pay attention to our industry information section.

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.