MySQL 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 you want to sync is db_rocky
㈠ Create a synchronization user
On the odd.
Copy Code code 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 Code code 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:
On the ODD.
Copy Code code as follows:
[Mysqld]
Binlog-do-db=db_rocky #需要记录进制日志的数据库. If you have more than one database separated by commas, or you can use multiple binlog-do-db options
Binlog-ignore-db=mysql #不需要记录进制日志的数据库. If you have more than one database separated by commas, or you can use multiple binlog-do-db options
Replicate-do-db=db_rocky #需要进行同步的数据库. If you have more than one database separated by commas, or you can use multiple replicate-do-db options
Replicate-ignore-db=mysql,information_schema #不需要同步的数据库. If you have more than one database separated by commas, or you can 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 Code code as follows:
[Mysqld]
server-id=2 #设置一个不同的id, note here in my.cnf, there is a default value of 1, to change the default value, and not add a Server-id
Binlog-do-db=db_rocky #需要记录二进制日志的数据库. If you have more than one database separated by commas, or you can use multiple binlog-do-db options
Binlog-ignore-db=mysql #不需要记录进制日志的数据库. If you have more than one database separated by commas, or you can use multiple binlog-ignore-db options
#需要同步的数据库
Replicate-do-db=db_rocky #需要进行同步的数据库. If you have more than one database separated by commas, or you can use multiple binlog-do-db options
Replicate-ignore-db=mysql,information_schema #不需要同步的数据库. If you have more than one database separated by commas, or you can 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 #过滤掉一些没啥大问题的错误
㈢ the MySQL service on the server odd even respectively
㈣ on server odd, even on the main server state
In odd
Copy Code code 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 Code code 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 sync location with the Change master statement on the server odd, even, respectively:
In odd
Copy Code code 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 Code code 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: Master_log_file,master_log_pos is determined by the state value identified by the above master server
Master_log_file corresponding File,master_log_pos corresponding position
On the odd even.
Copy Code code as follows:
mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)
㈥ boot from server threads on server odd, even, respectively
Copy Code code as follows:
mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)
View from server status on server odd, even, respectively:
Copy Code code as follows:
On odd
Mysql> show Slave status\g;
1. Row ***************************
The main focus is on the following 2 parameters:
...
...
Slave_io_running:yes
Slave_sql_running:yes
...
...
Even 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
...
...
㈦ Test
Copy Code code as follows:
On even
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)
