主从复制

主从环境


主服务器192.168.56.171
从服务器192.168.56.173

测试需要:

从服务器能够同步复制主firstdb库中的所有表

从服务器能够同步复制主testdb库中的表testa,testb,testc，其他表不复制。

主配置 

binlog-do-db=firstdbbinlog-ignore-db=mysql replicate-do-db=firstdbbinlog-do-db=testdbreplicate-do-db=testdbreplicate_do_table=testdb.testareplicate_do_table=testdb.testbreplicate_do_table=testdb.testc

从配置

binlog-ignore-db=mysql replicate_wild_do_table=firstdb.%replicate-do-db=firstdbreplicate-do-db=testdbreplicate_do_table=testdb.testareplicate_do_table=testdb.testbreplicate_do_table=testdb.testcbinlog-do-db=test

mysql> show master status;+-------------------------+----------+---------------------------+------------------+| File                    | Position | Binlog_Do_DB              | Binlog_Ignore_DB |+-------------------------+----------+---------------------------+------------------+| smart_master-bin.000012 |     1958 | smart,firstdb,testdb,test | mysql            | +-------------------------+----------+---------------------------+------------------+1 row in set (0.02 sec)mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.56.171                  Master_User: chris                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: smart_master-bin.000012          Read_Master_Log_Pos: 1958               Relay_Log_File: small-relay-log.000021                Relay_Log_Pos: 1715        Relay_Master_Log_File: smart_master-bin.000012             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: firstdb,testdb          Replicate_Ignore_DB:            Replicate_Do_Table: testdb.testb,testdb.testa,testdb.testc       Replicate_Ignore_Table:       Replicate_Wild_Do_Table: firstdb.%  Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 1958              Relay_Log_Space: 1871              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 1711 row in set (0.00 sec)ERROR: No query specified主服务器192.168.56.171mysql> use firstdb;Database changedmysql> show tables;+-------------------+| Tables_in_firstdb |+-------------------+| test              | | test1             | | test12            | +-------------------+3 rows in set (0.00 sec)mysql> select * from test12;+------+------+| ix   | a    |+------+------+|  888 |  999 | +------+------+1 row in set (0.00 sec)mysql> insert into test12 values (123,'123bohai');Query OK, 1 row affected, 1 warning (0.05 sec)mysql> select * from test12;+------+------+| ix   | a    |+------+------+|  888 |  999 | |  123 |  123 | +------+------+2 rows in set (0.00 sec)从173mysql> use firstdb;Database changedmysql> show tables;+-------------------+| Tables_in_firstdb |+-------------------+| test1             | | test12            | +-------------------+2 rows in set (0.01 sec)mysql> select * from test12;+------+------+| ix   | a    |+------+------+|  888 |  999 | |  123 |  123 | +------+------+2 rows in set (0.00 sec) 看到，从库 能成功复制主库上firstdb库的表的数据主mysql> create table test88  (id int,name varchar(10)); ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id:    8Current database: firstdbQuery OK, 0 rows affected (0.03 sec)mysql> insert into test88 values (88,'88bohai');Query OK, 1 row affected (0.00 sec)mysql> select * from test88;+------+---------+| id   | name    |+------+---------+|   88 | 88bohai | +------+---------+1 row in set (0.00 sec)从mysql> show tables;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id:    8Current database: firstdb+-------------------+| Tables_in_firstdb |+-------------------+| test1             | | test12            | | test88            | +-------------------+3 rows in set (0.00 sec)mysql> select * from test88;+------+---------+| id   | name    |+------+---------+|   88 | 88bohai | +------+---------+1 row in set (0.00 sec)

看到，从库 能成功复制主库上firstdb库的所有表的数据


测试结论，从库能同步复制主服务器firstdb库的所有表


主171


testdb库下 testa表 插入一条新记录

 

mysql> use testdb;Database changedmysql> show tables;+------------------+| Tables_in_testdb |+------------------+| testa            | | testb            | | testc            | | testz            | +------------------+4 rows in set (0.01 sec)mysql> select * from testa;+------+------+| id   | ix   |+------+------+|    1 |    0 | |    1 |   88 | |  890 |  890 | +------+------+3 rows in set (0.00 sec)mysql> insert into testa values (100,'100bohai');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from testa;+------+------+| id   | ix   |+------+------+|    1 |    0 | |    1 |   88 | |  890 |  890 | |  100 |  100 | +------+------+4 rows in set (0.00 sec)从173mysql> use testdb;Database changedmysql> show tables;+------------------+| Tables_in_testdb |+------------------+| testa            | | testb            | | testc            | +------------------+3 rows in set (0.01 sec)mysql> select * from testa;+------+------+| id   | ix   |+------+------+|    1 |    0 | |    1 |   88 | |  890 |  890 | +------+------+3 rows in set (0.00 sec)   //主服务器库testdb，表testa 插入新记录前mysql> select * from testa;+------+------+| id   | ix   |+------+------+|    1 |    0 | |    1 |   88 | |  890 |  890 | |  100 |  100 | +------+------+4 rows in set (0.00 sec)     //主服务器库testdb，表testa 插入新记录后

经测试，表testb、 testc同上结论
经测试，主服务器创建表testd，从服务器show tables 不能获取到testd，现象正确，符合预期 


测试结论，从服务器能同步主服务器上testdb库中指定表testa,testb,testc，对于未指定表testd是不能进行同步复制的。




查看从服务器173的relay日志


 

[root@mic2 relaylog]# mysqlbinlog --no-defaults small-relay-log.000021use firstdbinsert into test12 values (123,'123bohai')create table test88  (id int,name varchar(10))insert into test88 values (88,'88bohai')use testdbinsert into testa values (100,'100bohai')insert into testb values (100,'100bohai')insert into testc values (100,'100bohai')create table testd (id int,idx int)    

 
看到，主服务器上的操作，在从服务器的relaylog日志中全部记录下来！

在从服务器的二进制日志中没有记录这些信息，因为配置文件中没有配置记录firstdb和testdb的二进制日志信息。

下面我们通过实验来进行验证

在从服务器配置文件中修改配置文件
 

[root@mic2 ~]# cat /etc/my_small.cnf | grep binloglog-bin=/database/small/binlog/small_bin     //经测试，这个必须打开，否则不会记录log-bin二进制日志binlog-do-db=firstdb                         //这个必须打开，否则不会记录 二进制日志中记录firstdb库的记录binlog-do-db=testdb 

重启从173
 

#mysqladmin -S /tmp/mysql_small.sock shutdown# ./bin/mysqld_safe --defaults-file=/etc/my_small.cnf --user=mysql &

 
主服务器192.168.56.171
 

mysql> use firstdb;Database changedmysql> show tables;+-------------------+| Tables_in_firstdb |+-------------------+| test              | | test1             | | test12            | +-------------------+3 rows in set (0.00 sec)mysql> select * from test12;+------+------+| ix   | a    |+------+------+|  888 |  999 | +------+------+1 row in set (0.00 sec)mysql> insert into test12 values (88888,'88888');Query OK, 1 row affected, 1 warning (0.05 sec)mysql> select * from test12;+-------+-------+| ix    | a     |+-------+-------+|   888 |   999 | |   123 |   123 | | 88888 | 88888 | +-------+-------+3 rows in set (0.00 sec)从173mysql> use firstdb;Database changedmysql> show tables;+-------------------+| Tables_in_firstdb |+-------------------+| test1             | | test12            | +-------------------+2 rows in set (0.01 sec)mysql> select * from test12;+-------+-------+| ix    | a     |+-------+-------+|   888 |   999 | |   123 |   123 | | 88888 | 88888 | +-------+-------+3 rows in set (0.00 sec)

看到，从库 能成功复制主库上firstdb库的表的数据


主
 

mysql> create table test88888  (id int,name varchar(10));  mysql> insert into test88888 values (88888,'888888bohai'); mysql> select * from test88888;+-------+------------+| id    | name       |+-------+------------+| 88888 | 888888boha | +-------+------------+1 row in set (0.00 sec)从mysql> show tables;+-------------------+| Tables_in_firstdb |+-------------------+| test1             | | test12            | | test88            | | test88888         | +-------------------+4 rows in set (0.00 sec)mysql> select * from test88888;+-------+------------+| id    | name       |+-------+------------+| 88888 | 888888boha | +-------+------------+1 row in set (0.00 sec)

看到，从库 能成功复制主库上firstdb库的所有表的数据




主171


testdb库下 testa表 插入一条新记录


 

mysql> use testdb;Database changedmysql> show tables;+------------------+| Tables_in_testdb |+------------------+| testa            | | testb            | | testc            | | testz            | +------------------+4 rows in set (0.01 sec)mysql> select * from testa;+------+------+| id   | ix   |+------+------+|    1 |    0 | |    1 |   88 | |  890 |  890 | +------+------+3 rows in set (0.00 sec)mysql> insert into testa values (88888,'88888bohai');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from testa;+-------+-------+| id    | ix    |+-------+-------+|     1 |     0 | |     1 |    88 | |   890 |   890 | |   100 |   100 | | 88888 | 88888 | +-------+-------+5 rows in set (0.00 sec)从173mysql> use testdb;Database changedmysql> show tables;+------------------+| Tables_in_testdb |+------------------+| testa            | | testb            | | testc            | +------------------+3 rows in set (0.01 sec)mysql> select * from testa;+-------+-------+| id    | ix    |+-------+-------+|     1 |     0 | |     1 |    88 | |   890 |   890 | |   100 |   100 | | 88888 | 88888 | +-------+-------+     //主服务器库testdb，表testa 插入新记录后

经测试，表testb、 testc同上结论
经测试，主服务器创建表testd，从服务器show tables 不能获取到testd，现象正确，符合预期 


查看从服务器173的relay日志

[root@mic2 relaylog]# mysqlbinlog --no-defaults small-relay-log.000021use firstdb;insert into test12 values (88888,'88888');create table test88888  (id int,name varchar(10));  insert into test88888 values (88888,'888888bohai');use testdb;insert into testa values (88888,'88888bohai'); 

看到，主服务器上的操作，在从服务器的relaylog日志中全部记录下来！

查看从服务器173的binlog日志
 # mysqlbinlog --no-defaults small_bin.000003

use firstdb;insert into test12 values (88888,'88888');create table test88888  (id int,name varchar(10));  insert into test88888 values (88888,'888888bohai');use testdb;insert into testa values (88888,'88888bohai'); 

看到，主服务器上的操作，在从服务器的binlog日志中全部记录下来！

--     --   --

主
 

mysql> use testdb;mysql> create table testy (id int,name varchar(10));Query OK, 0 rows affected (0.03 sec)mysql> insert into testy values (1,'11023');Query OK, 1 row affected (0.00 sec)mysql> select * from testy;+------+-------+| id   | name  |+------+-------+|    1 | 11023 | +------+-------+1 row in set (0.00 sec)

从
 

mysql> use testdb;mysql> show tables;+------------------+| Tables_in_testdb |+------------------+| testa            | | testb            | | testc            | +------------------+3 rows in set (0.00 sec)

从的relay log 有记录信息
 

# mysqlbinlog --no-defaults small-relay-log.000025create table testy (id int,name varchar(10))insert into testy values (1,'11023')

从的bin log   没有记录信息
]# mysqlbinlog --no-defaults small_bin.000003
因为从服务器中  由relay-写入-->binlog这一步过程中，已经被规则过滤掉了。

 

replicate_do_table=testdb.testa  replicate_do_table=testdb.testb  replicate_do_table=testdb.testc  

这是从的配置文件，可以理解成过滤的规则。