How to set up MySQL master-master replication

How to set up MySQL master-master replication Master-Slave replication is to set the slave server to update immediately after the master server completes the changes. But if any changes are made on the slave server, it will not update the Master. This article will introduce setting up Master-Master replication between How to set up MySQL master-master replication servers.

#In this setup, any changes made on either server will be updated on the other server.

Setup details:

Master-1: 192.168.1.15
Master-2: 192.168.1.16
Database: mydb

Step 1: Set up the How to set up MySQL master-master replication Master-1 server

Edit the How to set up MySQL master-master replication configuration file and in [ mysqld] section add the following code.

# vim /etc/my.cnf

[mysqld]
log-bin=mysql-bin
binlog-do-db=mydb
server-id=1

Restart the How to set up MySQL master-master replication server for the changes to take effect.

# service mysqld restart

Create a mysql account with REPLICATION SLAVE permissions on the Master-1 server, and the replication client will connect to the master.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'secretpassword';
mysql> FLUSH PRIVILEGES;

There are block write statements on all tables, so no changes can be made after backup.

mysql> use mydb;
mysql> FLUSH TABLES WITH READ LOCK;

Use the following command to check the current binary log file name (File) and current offset (Position) value.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      332 | mydb         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

The above output shows that the current binary file is using mysql-bin.000003 with an offset of 332. Make a note of these values ​​for use on the master-2 server in the next step.

Back up the database and copy it to another mysql server.

# mysqldump -u root -p mydb > mydb.sql
# scp mydb.sql 192.168.1.16:/opt/

After completing the backup, remove the READ LOCK from the table so that changes can be made.

mysql> UNLOCK TABLES;

Step 2: Set up the How to set up MySQL master-master replication Master-2 server

Edit the mysql Master-2 configuration file and add the following values ​​under the [mysqld] section.

#vim /etc/my.cnf
[mysqld] 
log-bin = mysql-bin 
binlog-do-db = mydb 
server-id = 1

server-id is always a non-zero value. These values ​​will never be similar to other masters or slaves.

Restart the How to set up MySQL master-master replication server. If replication is configured, use -skip-slave-start at startup and do not connect to the master server immediately.

#service mysqld restart

Restore the database backup obtained from the primary server.

#mysql -u root -p mydb </opt/mydb.sql

Create a mysql account with REPLICATION SLAVE permissions on the Master-1 server, and the replication client will connect to the master.

mysql> GRANT REPLICATION SLAVE ON *.*''repl_user'@'％'IDENTIFIED BY'secretpassword'; 
mysql> FLUSH PRIVILEGES;

Use the following command to check the current binary log file name (File) and current offset (Position) value.

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      847 | mydb         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

The output above shows that the current binary uses mysql-bin.000001 with an offset of 847. Note these values ​​for use in step 3.

Use the following commands to set option values ​​on the slave server.

mysql> CHANGE MASTER TO MASTER_HOST ='192.168.1.15'，
    - > MASTER_USER ='repl_user'，
    - > MASTER_PASSWORD ='secretpassword'，
    - > MASTER_LOG_FILE ='mysql-bin.000003'，
    - > MASTER_LOG_POS = 332;

Step 3: Complete the installation of How to set up MySQL master-master replication Master-1

Log in to the How to set up MySQL master-master replication Master-1 server and execute the following command.

mysql> CHANGE MASTER TO MASTER_HOST ='192.168.1.16'，
     MASTER_USER ='repl_user'，
     MASTER_PASSWORD ='secretpassword'，
     MASTER_LOG_FILE ='mysql-bin.000001'，
     MASTER_LOG_POS = 847;

Step 4: Start SLAVE on both servers

Execute the following commands on both servers to start the replication slave process.

mysql> SLAVE START;

How to set up MySQL master-master replication Master-Master Replication has been successfully configured on your system and work mode. To test that replication is working properly, make changes on either server and check whether the changes are reflected on the other servers.

The above is the detailed content of How to set up MySQL master-master replication. For more information, please follow other related articles on the PHP Chinese website!