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

How to set up Master-Master replication between MySQL servers? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

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

Setup details:

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

Step 1: set up the MySQL Master-1 server

Edit the MySQL configuration file and add the following code under the [mysqld] section.

# vim / etc/ my.cnf[mysqld] log-bin=mysql-binbinlog-do-db=mydbserver-id=1

Restart the MySQL server for the changes to take effect.

# service mysqld restart

Create a mysql account with REPLICATION SLAVE privileges 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 the 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) values.

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 uses mysql-bin.000003 with an offset value 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 the backup is complete, remove the READ LOCK from the table so that changes can be made.

Mysql > UNLOCK TABLES

Step 2: set up the MySQL 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 are never similar to other master or slave servers.

Restart the MySQL server and, if replication is configured, use-skip-slave-start at startup and do not connect to the primary server immediately.

# service mysqld restart

Restore the database backup obtained from the primary server.

# mysql-u root-p mydb 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) values.

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 above output shows that the current binary uses mysql-bin.000001 with an offset value of 847. Write down these values for use in step 3.

Use the following command to set option values on the secondary 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 MySQL Master-1

Log in to the MySQL 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 command on both servers to start the replication slave process.

Mysql > SLAVE START

MySQL Master-Master Replication has been successfully configured on your system and in operating mode. To test whether replication is normal, make changes on either server and check that the changes are reflected on other servers.

Thank you for reading! After reading the above, do you have a general idea of how to set up Master-Master replication between MySQL servers? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.