MySQL dual Master configuration method details

Just took the time to do 1 MYSQL master master synchronization. Write the steps down and update them as you go along. The database I synchronized here is TEST1, environment description. Host: 192.168.0.231 (A) host: 192.168.0.232 (B) MYSQL version is 5.1.212, authorized user. A: mysql > grant replication slave,file on *.* to 'repl1'@'192.168.0.232' identified by '123456';Query OK, 0 rows affected (0.00 sec)mysql > flush privileges; Query OK, 0 rows affected (0.00 sec)B: mysql > grant replication slave,file on *.* to 'repl2'@'192.168.0.231' identified by '123456';Query OK, 0 rows affected (0.00 sec)mysql > flush privileges; Query OK, 0 rows affected (0.00 sec) then stop the MYSQL server. 3. Configuration files. Binary logging is enabled on both machines in my.cnf. A: user = mysqllog-bin = mysql-do-db = testbinlog-ignore-db = mysqlreplicate-ignore-db = mysqllog-slave-updatesslave-skip-errors =all skip - name - auto_increment_increment resolvesync_binlog = 1 = 2 auto_increment_offset B = 1: user = mysqllog-bin = mysql-binserver-id = 2testbinlog-do-db = mysqlreplicate-do-db = mysqllog-slave-updatesslave-skip-errors =all skip-name-resolvesync_binlog =1auto_increment_increment=2auto_increment_offset=2 refer to the manual for instructions on these parameters. The red part is very important. If one MASTER dies, the other one will take over immediately. The magenta section refers to the server's frequent refresh logs. This ensures that if one of them fails, the log will be refreshed to the other. This ensures data synchronization. 4. Restart the MYSQL server. Perform the same steps on A and B [root @ localhost ~] # / usr local/mysql bin/mysqld_safe & 4264 [1] [root @ localhost ~] # 071213 14:53:20 mysqld_safe Logging to '/ usr/local/mysql/data/localhost localdomain. err'. / usr/local/mysql/bin/mysqld_safe: line 366: [: - eq: unary operator expected071213 14:53:20 mysqld_safe Starting mysqld daemon with databases from usr/local/mysql/data5, into MYSQL SHELL. A: mysql > flush tables with read lock\GQuery OK, 0 rows affected (0.00 sec)mysql > show master status \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * File: mysql - bin. 000007 Position: 528 Binlog_Do_DB: testBinlog_Ignore_DB: mysql1 row in set (0.00 sec)B: mysql > flush tables with read lock;Query OK, 0 rows affected (0.00 sec)mysql > show master status \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * File: mysql - bin. 000004 Position: 595 Binlog_Do_DB: testBinlog_Ignore_DB: mysql1 row in set (0.00 sec) then back up your own data and keep the data 1 to 1 between the two machines. There are many ways. When you're done, look at step 1. 6. Execute the CHANGE MASTER TO command on their respective machines. A: mysql > change master to - > master_host='192.168.0.232', - > master_user='repl2', - > master_password='123456', - > master_log_file='mysql-bin.000004', - > master_log_pos=595;Query OK, 0 rows affected (0.01 sec)mysql > start slave; Query OK, 0 rows affected (0.00 sec)B: mysql > change master to - > master_host='192.168.0.231', - > master_user='repl1', - > master_password='123456', - > master_log_file='mysql-bin.000007', - > master_log_pos=528;Query OK, 0 rows affected (0.01 sec)mysql > start slave; 7. Check whether the IO process and the SLAVE process are both open on their respective machines. A: mysql > show processlist \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * Id: 2 User: replHost: 192.168.0.232:54475 db: NULLCommand: Binlog DumpTime: 1590 State: Has sent all binlog to slave; waiting for binlog to be updatedInfo: NULL * * * * * * * * * * * * * * * * * * * * * * * * * * * 2. row * * * * * * * * * * * * * * * * * * * * * * * * * * * Id: 3 User: system userHost: db: NULLCommand: ConnectTime: 1350 State: Waiting for master to send eventInfo: NULL * * * * * * * * * * * * * * * * * * * * * * * * * * * 3. row * * * * * * * * * * * * * * * * * * * * * * * * * * * Id: 4 User: system userHost: db: NULLCommand: ConnectTime: 1149 State: Has read all relay log; waiting for the slave I/O thread to update itInfo: NULL * * * * * * * * * * * * * * * * * * * * * * * * * * * 4. row * * * * * * * * * * * * * * * * * * * * * * * * * * * Id: 5 User: rootHost: localhostdb: testCommand: QueryTime: 0State: NULLInfo: show processlist4 rows in set (0.00 sec)B: mysql > show processlist \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * * Id: 1 User: system userHost: db: NULLCommand: ConnectTime: 2130 State: Waiting for master to send eventInfo: NULL * * * * * * * * * * * * * * * * * * * * * * * * * * * 2. row * * * * * * * * * * * * * * * * * * * * * * * * * * * Id: 2 User: system userHost: db: NULLCommand: ConnectTime: 1223 State: Has read all relay log; waiting for the slave I/O thread to update itInfo: NULL * * * * * * * * * * * * * * * * * * * * * * * * * * * 3. row * * * * * * * * * * * * * * * * * * * * * * * * * * * Id: 4 User: rootHost: localhostdb: testCommand: QueryTime: 0 State: NULLInfo: show processlist * * * * * * * * * * * * * * * * * * * * * * * * * * * 4. row * * * * * * * * * * * * * * * * * * * * * * * * * * * Id: 5 User: repl2Host: 192.168.0.231:50718 db: NULLCommand: Binlog DumpTime: 1398State: Has sent all binlog to slave; waiting for to be updatedInfo: NULL4 rows in set (0.00 sec) if the red part does not appear, check for error files in the DATA directory. 8. Release the respective locks, and then perform insert data test. mysql > unlock tables; Query OK, 0 rows affected (0.00 sec) : A: mysql > show tables; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Tables_in_test | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | t11_innodb | | t22 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + B: mysql >show tables; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Tables_in_test | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | t11_innodb | | t22 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + insert A from A machines: mysql > create table t11_replicas - > (id int not null auto_increment primary key, - > str varchar(255) not null) engine myisam;Query OK, 0 rows affected (0.01 sec)mysql > insert into t11_replicas(str) values - > ('This is a master to master test table');Query OK, 1 row affected (0.01 sec)mysql > show tables;+----------------+| Tables_in_test |+----------------+| t11_innodb | | t11_replicas | | t22 | +----------------+3 rows in set (0.00 sec)mysql > select * from t11_replicas; + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | id | str | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | This is a master to master test table | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in set (0.00 sec) now B machine: mysql > show tables;+----------------+| Tables_in_test |+----------------+| t11_innodb | | t11_replicas | | t22 | +----------------+3 rows in set (0.00 sec)mysql > select * from t11_replicas; + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | id | str | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | This is a master to master test table | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in set sec (0.00) is now, in turn, insert the data from the B machine: B: mysql > insert into t11_replicas(str) values('This is a test 2');Query OK, 1 row affected (0.00 sec)mysql > select * from t11_replicas; + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | id | str | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | This is a master to master test table | | 2 | This is test 2 | +-- +----+ 2 rows in set (0.00 sec) AA: mysql > select * from t11_replicas; + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | id | str | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | This is a master to master test table | | 2 | This is a test 2 | +-- +-- Now the two tables are MASTER to each other.