Configure the MySQL server for Master-master Replication

Configure the MySQL server for Master-master Replication

Statement
Author: Last Night stars
Blog: http://yestreenstars.blog.51cto.com/
This article was created by myself. If you need to reprint it, please indicate the source. Thank you for your cooperation!

Steps for setting up Master-master replication for MySQL (see the link)

Purpose

Configure the MYSQL server to implement master-master replication.

Lab Environment

Server 1 (S1 ):

IP: 192.168.1.88

Server 2 (S2 ):

IP: 192.168.1.89

OS: CentOS 6.2 32

MYSQL: 5.1.73

Configuration

# Stop the iptables firewall of S1 and S2 first.
Service iptables stop
 
# Modify the/etc/my. cnf file of S1 and add the following content under the [mysqld] field:
Log-bin = mysql-bin
Log-slave-updates
Server-id = 1
 
# Similarly, modify the/etc/my. cnf file of S2 and add the following content under the [mysqld] field (note that the server-id cannot be the same ):
Log-bin = mysql-bin
Log-slave-updates
Server-id = 2
 
# Restart the mysqld service of S1 and S2.
Service mysqld restart
 
# Log on to the database of S2 and run the lock table command to prevent users from modifying the data when viewing the binary log file. Then, run the command to record the binary log file name and current location.
Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000003 | 191 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
 
# Log on to the S1 database, create a user for replication, and configure S2 as the master replication server (the reader must modify the specific parameters according to the actual situation ).
Mysql> grant replication slave on *. * to 'slave '@ '192. 168.1.89' identified by '20140901 ';
Query OK, 0 rows affected (0.00 sec)
Mysql> change master to master_host = '192. 168.1.89 ', master_user = 'slave', master_password = '000000', master_log_file = 'mysql-bin.000003', master_log_pos = 192;
Query OK, 0 rows affected (0.05 sec)
 
# Execute the lock table command and the command to view binary log file information on the S1 database.
Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000003 | 260 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
 
# Run the unlock table command in the S2 database, create a user for replication, and configure S1 as the master replication server.
Mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Mysql> grant replication slave on *. * to 'slave '@ '192. 168.1.88' identified by '20140901 ';
Query OK, 0 rows affected (0.00 sec)
Mysql> change master to master_host = '192. 168.1.88 ', master_user = 'slave', master_password = '000000', master_log_file = 'mysql-bin.000003', master_log_pos = 192;
Query OK, 0 rows affected (0.03 sec)
 
# Run the unlock table command in the database of S1 and start replication.
Mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
# Start replication in the S2 database.
Mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
# Finally, run the show slave status command to check whether the configuration is successful. If the two values are YES, the configuration is successful.
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes

# You can also perform operations such as creating a database to verify synchronization.

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

MySQL 5.5 master-slave synchronization troubleshooting

MySQL master-slave replication asynchronous semi-sync instance

This article permanently updates the link address: