主从环境
主服务器192.168.56.171
从服务器192.168.56.173
主配置
从配置
看到,从库 能成功复制主库上firstdb库的所有表的数据
测试结论,从库能同步复制主服务器firstdb库的所有表
主171
testdb库下 testa表 插入一条新记录
经测试,表testb、 testc同上结论
经测试,主服务器创建表testd,从服务器show tables 不能获取到testd,现象正确,符合预期
测试结论,从服务器能同步主服务器上testdb库中指定表testa,testb,testc,对于未指定表testd是不能进行同步复制的。
查看从服务器173的relay日志
看到,主服务器上的操作,在从服务器的relaylog日志中全部记录下来!
在从服务器配置文件中修改配置文件
主服务器192.168.56.171
看到,从库 能成功复制主库上firstdb库的表的数据
主
看到,从库 能成功复制主库上firstdb库的所有表的数据
主171
testdb库下 testa表 插入一条新记录
经测试,主服务器创建表testd,从服务器show tables 不能获取到testd,现象正确,符合预期
查看从服务器173的relay日志
查看从服务器173的binlog日志
# mysqlbinlog --no-defaults small_bin.000003
看到,主服务器上的操作,在从服务器的binlog日志中全部记录下来!
从
从的relay log 有记录信息
从的bin log 没有记录信息
]# mysqlbinlog --no-defaults small_bin.000003
因为从服务器中 由relay-写入-->binlog这一步过程中,已经被规则过滤掉了。
这是从的配置文件,可以理解成过滤的规则。
主服务器192.168.56.171
从服务器192.168.56.173
测试需要:
从服务器能够同步复制主firstdb库中的所有表
从服务器能够同步复制主testdb库中的表testa,testb,testc,其他表不复制。
主配置
binlog-do-db=firstdb
binlog-ignore-db=mysql
replicate-do-db=firstdb
binlog-do-db=testdb
replicate-do-db=testdb
replicate_do_table=testdb.testa
replicate_do_table=testdb.testb
replicate_do_table=testdb.testc
从配置
binlog-ignore-db=mysql
replicate_wild_do_table=firstdb.%
replicate-do-db=firstdb
replicate-do-db=testdb
replicate_do_table=testdb.testa
replicate_do_table=testdb.testb
replicate_do_table=testdb.testc
binlog-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: 0
Master_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: 171
1 row in set (0.00 sec)
ERROR:
No query specified
主服务器192.168.56.171
mysql> use firstdb;
Database changed
mysql> 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)
从173
mysql> use firstdb;
Database changed
mysql> 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 away
No connection. Trying to reconnect...
Connection id: 8
Current database: firstdb
Query 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 away
No connection. Trying to reconnect...
Connection id: 8
Current 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 changed
mysql> 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)
从173
mysql> use testdb;
Database changed
mysql> 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.000021
use firstdb
insert into test12 values (123,'123bohai')
create table test88 (id int,name varchar(10))
insert into test88 values (88,'88bohai')
use testdb
insert 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 binlog
log-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 changed
mysql> 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)
从173
mysql> use firstdb;
Database changed
mysql> 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 changed
mysql> 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)
从173
mysql> use testdb;
Database changed
mysql> 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.000021
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');
看到,主服务器上的操作,在从服务器的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.000025
create 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
这是从的配置文件,可以理解成过滤的规则。