MySQL Master Master Sync configuration and master and slave configuration steps

MySQL Master sync configuration Step reprint: http://www.jb51.net/article/33624.htm Create a sync user, modify the/ETC/MY.CNF profile, add the following to it, restart the server odd even MySQL service respectively MySQL Master Master sync configuration

Server name	Ip	System	Mysql
Odd.example.com	192.168.1.116	rhel-5.8	5.5.16
Even.example.com	192.168.1.115	rhel-5.8	5.5.16

Suppose the library to be synchronized is Db_rocky
㈠ Creating a synchronization user
On the odd.
Copy CodeThe code is as follows:
mysql> grant replication Slave on * * to ' water ' @ ' 192.168.1.115 ' identified by ' cdio2010 ';
Query OK, 0 rows Affected (0.00 sec)
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)
On the even.
Copy CodeThe code is as follows:
mysql> grant replication Slave on * * to ' water ' @ ' 192.168.1.116 ' identified by ' cdio2010 ';
Query OK, 0 rows affected (0.11 sec)
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)
㈡ Modify the/ETC/MY.CNF configuration file to add the following to it:
On the ODD.
Copy CodeThe code is as follows:
[Mysqld]
Binlog-do-db=db_rocky #需要记录进制日志的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options
Binlog-ignore-db=mysql #不需要记录进制日志的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options
Replicate-do-db=db_rocky #需要进行同步的数据库. If you have multiple databases that can be separated by commas, or use multiple replicate-do-db options
Replicate-ignore-db=mysql,information_schema #不需要同步的数据库. If you have multiple databases that can be separated by commas, or use multiple replicate-ignore-db options
#同步参数:
#保证slave挂在任何一台master上都会接收到另一个master的写入信息
Log-slave-updates
Sync_binlog=1
Auto_increment_offset=1
auto_increment_increment=2
Slave-skip-errors=all #过滤掉一些没啥大问题的错误
On the even.
Copy CodeThe code is as follows:
[Mysqld]
server-id=2 #设置一个不同的id, notice here in my.cnf there is a default value of 1, change the default value, and cannot add a Server-id
Binlog-do-db=db_rocky #需要记录二进制日志的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options
Binlog-ignore-db=mysql #不需要记录进制日志的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-ignore-db options
#需要同步的数据库
Replicate-do-db=db_rocky #需要进行同步的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options
Replicate-ignore-db=mysql,information_schema #不需要同步的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options
#同步参数:
#保证slave挂在任何一台master上都会接收到另一个master的写入信息
Log-slave-updates
Sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
Slave-skip-errors=all #过滤掉一些没啥大问题的错误
㈢ Restart the MySQL service on the server odd even, respectively
㈣ on server odd, even view as the primary server status
In odd
Copy CodeThe code is as follows:
Mysql> flush tables with read lock; #防止进入新的数据
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show Master Status\g;
1. Row ***************************
file:mysql-bin.000007
position:438
Binlog_do_db:db_rocky
Binlog_ignore_db:mysql
1 row in Set (0.00 sec)
In even
Copy CodeThe code is as follows:
Mysql> flush tables with read lock;
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show Master Status\g;
1. Row ***************************
file:mysql-bin.000008
position:107
Binlog_do_db:db_rocky
Binlog_ignore_db:mysql
1 row in Set (0.01 sec)
㈤ Specify the synchronization location with the Change master statement on the server odd, even, respectively:
In odd
Copy CodeThe code is as follows:
mysql> Change Master to master_host= ' 192.168.1.115 ', master_user= ' water ', master_password= ' cdio2010 ',
-master_log_file= ' mysql-bin.000008 ', master_log_pos=107;
Query OK, 0 rows affected (0.05 sec)
In even
Copy CodeThe code is as follows:
mysql> Change Master to master_host= ' 192.168.1.116 ', master_user= ' water ', master_password= ' cdio2010 ',
-master_log_file= ' mysql-bin.000007 ', master_log_pos=438;
Query OK, 0 rows affected (0.15 sec)
Note: The master_log_file,master_log_pos is determined by the status value identified by the primary server above
Master_log_file corresponds to file,master_log_pos corresponding position
On the odd even.
Copy CodeThe code is as follows:
mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)
㈥ starting from the server thread on server odd, even, respectively
Copy CodeThe code is as follows:
mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)
View from server status on server odd, even, respectively:
Copy CodeThe code is as follows:
Odd on
Mysql> show Slave status\g;
1. Row ***************************
The main focus is on the following 2 parameters:
...
...
Slave_io_running:yes
Slave_sql_running:yes
...
...
On even:
Mysql> show Slave status\g;
1. Row ***************************
The main focus is on the following 2 parameters:
...
...
Slave_io_running:yes
Slave_sql_running:yes
...
...
㈦ Test
Copy CodeThe code is as follows:
Even on
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Db_rocky |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
5 rows in Set (0.00 sec)
mysql> use Db_rocky;
Database changed
Mysql> Show tables;
Empty Set (0.00 sec)
Mysql> CREATE TABLE water (id int);
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT into water values (1);
Query OK, 1 row affected (0.01 sec)
Mysql> commit;
Query OK, 0 rows Affected (0.00 sec)
On the ODD.
Mysql> Show tables;
+--------------------+
| Tables_in_db_rocky |
+--------------------+
| T_rocky |
| Water |
+--------------------+
2 rows in Set (0.00 sec)
Mysql> select * from water;
+------+
| ID |
+------+
| 1 |
+------+
1 row in Set (0.00 sec)-------------------------------------------------------------------------------------------- -------------------------------------------MySQL master-slave configuration

2. (1) First ensure that the MySQL version on the master and slave server is the same
4. (2) on the primary server, set up an account from the database, using replication slave to give permissions, such as:
8. MySQL> GRANT REPLICATION SLAVE on * * to ' slave001 ' @ ' 10.10.10.59 ' identified by
10. ' 123123 ';
12. Query OK, 0 rows affected (0.13 sec)
16. (3) Modify the configuration file of the main database my.cnf, turn on Binlog, and set the value of Server-id, the MySQL service must be restarted after the modification
20. [Mysqld]
22. Log-bin=mysql-bin
24. binlog-ignore-db= MySQL
26. server-id=1
30. (4) You can then get the current binary log name and offset of the primary server, the purpose of this operation is to start from the database, the data recovery from this point
34. MySQL> Show master status\g;
36. 1. Row ***************************
38. file:mysql-bin.000003
40. position:243
42. binlog_do_db:
44. binlog_ignore_db:
46. 1 row in Set (0.00 sec)
50. (5) OK, now you can stop the master data Update operation, and generate a backup of the primary database, we can go through the mysqldump data to the database, of course, you can directly use the CP command to copy the data file to the database
52. Note Read LOCK is performed on the primary database before the data is exported to ensure data consistency
56. MySQL> Flush tables with read lock;
58. Query OK, 0 rows affected (0.19 sec)
62. And then the mysqldump.
66. Mysqldump-h127.0.0.1-p3306-uroot-p Test >/home/chenyz/test.sql
70. It is best to restore the write operation after the primary database has been backed up
74. MySQL> Unlock tables;
76. Query OK, 0 rows affected (0.28 sec)
80. (6) Copy the Test.sql from the primary data backup to the database and import
82. (7) then modify the my.cnf from the database, increase the Server-id parameter, specify the user to use for replication, the IP of the primary database server, the port and the file and location where the replication log begins to execute
86. [Mysqld]
88. Server-id=2
90. Log-bin=mysql-bin
92. Master-host =10.10.10.58
94. master-user=Test
96. master-pass=123123
98. Master-port =3306
100. master-connect-retry=
102. replicate-do-db =Test
106. (8) on the slave server, start the slave process
110. MySQL> start slave;
114. (9) Show Salve status verification from the server
118. MySQL> SHOW SLAVE status\g
120. 1. Row ***************************
122. Slave_io_state:waiting for Master to send event
124. Master_host:localhost
126. Master_user:root
128. master_port:3306
130. Connect_retry:3
132. master_log_file:mysql-bin.003
134. read_master_log_pos:79
136. relay_log_file:gbichot-relay-bin.003
138. relay_log_pos:548
140. Relay_master_log_file:mysql-bin. 003
142. Slave_io_running:yes
144. Slave_sql_running:yes
148. (10) OK, now you can do some updates on our master server and then check from the server if it has been updated

MySQL Master Master Sync configuration and master and slave configuration steps