Just took the time to do a little MySQL master synchronization.
Write down the steps, and as to what will happen, update at any time. The database I synchronized here is test
1, environment description.
Host: 192.168.0.231 (A)
Host: 192.168.0.232 (B)
MYSQL version for 5.1.21
2, authorized users.
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)
The MySQL server is then stopped.
3, configuration file.
Binary logs are turned on in the my.cnf on two machines.
A:
user = MySQL
Log-bin=mysql-bin
Server-id = 1
Binlog-do-db=test
Binlog-ignore-db=mysql
Replicate-do-db=test
Replicate-ignore-db=mysql
Log-slave-updates
Slave-skip-errors=all
Sync_binlog=1
auto_increment_increment=2
Auto_increment_offset=1
B:
user = MySQL
Log-bin=mysql-bin
Server-id = 2
Binlog-do-db=test
Binlog-ignore-db=mysql
Replicate-do-db=test
Replicate-ignore-db=mysql
Log-slave-updates
Slave-skip-errors=all
Sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
The instructions for these parameters are detailed in the manual.
The red part is very important, if a master hangs up, the other one takes over immediately.
The magenta section refers to the frequent refresh of logs by the server. This ensures that when one of the units is hung, the log is refreshed to another. Thus ensuring the synchronization of data.
4, restart the MySQL server.
Perform the same steps on A and B
[Root@localhost ~]#/usr/local/mysql/bin/mysqld_safe &
[1] 4264
[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 expected
071213 14:53:20 Mysqld_safe starting mysqld daemon with databases From/usr/local/mysql/data
5, into the MySQL shell.
A:
Mysql> flush tables with Read lock\g
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show Master Status\g
1. Row ***************************
file:mysql-bin.000007
position:528
Binlog_do_db:test
Binlog_ignore_db:mysql
1 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:test
Binlog_ignore_db:mysql
1 row in Set (0.00 sec)
Then back up your data and keep the data consistent for two machines.
There are many ways. When you're done, look next.
6. Execute the Change MASTER to command on the respective machine.
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;
Query OK, 0 rows Affected (0.00 sec)
7. See whether the IO process and the slave process on their respective machines are open.
A:
Mysql> Show Processlist\g
1. Row ***************************
Id:2
User:repl
host:192.168.0.232:54475
Db:null
Command:binlog Dump
time:1590
State:has sent all binlog to slave; Waiting for Binlog to is updated
Info:null
2. Row ***************************
Id:3
User:system User
Host:
Db:null
Command:connect
time:1350
State:waiting for Master to send event
Info:null
3. Row ***************************
Id:4
User:system User
Host:
Db:null
Command:connect
time:1149
State:has read all relay log; Waiting for the slave I/O thread to update it
Info:null
4. Row ***************************
Id:5
User:root
Host:localhost
Db:test
Command:query
time:0
State:null
Info:show processlist
4 rows in Set (0.00 sec)
B:
Mysql> Show Processlist\g
1. Row ***************************
Id:1
User:system User
Host:
Db:null
Command:connect
time:2130
State:waiting for Master to send event
Info:null
2. Row ***************************
Id:2
User:system User
Host:
Db:null
Command:connect
time:1223
State:has read all relay log; Waiting for the slave I/O thread to update it
Info:null
3. Row ***************************
Id:4
User:root
Host:localhost
Db:test
Command:query
time:0
State:null
Info:show processlist
4. Row ***************************
Id:5
User:repl2
host:192.168.0.231:50718
Db:null
Command:binlog Dump
time:1398
State:has sent all binlog to slave; Waiting for Binlog to is updated
Info:null
4 rows in Set (0.00 sec)
If the red section does not appear, check the error file in the data directory.
8, the release of their own locks, and then insert data test.
mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)
Insert a comparison of the previous two machine tables:
A:
Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| T11_innodb |
| t22 |
+----------------+
B:
Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| T11_innodb |
| t22 |
+----------------+
Insert from Machine A
A:
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
-> (' 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 look at the 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 (0.00 sec)
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 a Test 2 |
+----+---------------------------------------+
2 rows in Set (0.00 sec)
Let's see a
a:
mysql> select * from T11_replicas;
+----+---------------------------------------+
| id | str |
+----+---------------------------------------+
| 1 | This is the master to master Test table |
| 2 | This is a test 2 |
+----+---------------------------------------+
2 rows in Set (0.00 sec)
OK. Now two tables are master to each other.
