时间：2021-07-01 10:21:17 帮助过：12人阅读
从库1和2配置相同
从库 mysql> show variables like ‘%log_bin%‘; +---------------------------------+------------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------------+ | log_bin | ON | | log_bin_basename | /usr/local/mysql/data/binlog | | log_bin_index | /usr/local/mysql/data/binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+------------------------------------+ 6 rows in set (0.03 sec) mysql> show variables like ‘%server_id%‘; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | server_id | 2 | | server_id_bits | 32 | +----------------+-------+ 2 rows in set (0.00 sec) mysql> show variables like ‘%binlog_format%‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec)
在主库创建一个专门用来做复制的数据库用户，这样所有从库都可以用这个用户来连接主库，也可以确保这个用户只有复制的权限虽然可以用任何拥有复制权限的MySQL用户来建立复制关系，但由于被使用的用户名和密码会明文保存在备库的master.info文件中，所以为安全起见，最好是使用仅有复制权限的独立用户
创建主从复制用户 主库 mysql> create user ‘repl‘@‘192.168.204.%‘ identified by ‘mysql‘; Query OK, 0 rows affected (0.12 sec) mysql> grant replication slave on *.* to ‘repl‘@‘192.168.204.%‘; Query OK, 0 rows affected (0.07 sec)
mysql 基于binlog 复制，初始化方法1
主库上锁 mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000008 | 709 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 主库 [root@centos7 ~]# mysqldump --all-databases --master-data -u root -p > dbdump.db Enter password:
mysql> unlock tables; [root@centos7 ~]# ls anaconda-ks.cfg dbdump.db mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz [root@centos7 ~]# sftp -oPort=22 192.168.204.133 root@192.168.204.133‘s password: Connected to 192.168.204.133. sftp> put dbdump.db Uploading dbdump.db to /root/dbdump.db dbdump.db 100% 906KB 905.8KB/s 00:00 从库导入 mysql> source dbdump.db; mysql> show databases; +--------------------+ | Database | +--------------------+ | A1 | | A2 | | course | | course2 | | course3 | | course4 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 10 rows in set (0.01 sec)
方法2
拷文件的方式 主库和从库都关闭 主库 [root@centos7 ~]# /etc/init.d/mysql.server stop Shutting down MySQL..... SUCCESS! [root@centos7 ~]# cd /usr/local/mysql/data/ [root@centos7 data]# cd .. [root@centos7 mysql]# tar -zcvf data.tar.gz ./data/ [root@centos7 mysql]# sftp -oPort=22 root@192.168.204.133 root@192.168.204.133‘s password: Permission denied, please try again. root@192.168.204.133‘s password: Connected to 192.168.204.133. sftp> lls bin data data.tar.gz docs include lib LICENSE LICENSE.router man README README.router share support-files sftp> put data.tar.gz Uploading data.tar.gz to /root/data.tar.gz data.tar.gz 100% 5594KB 5.5MB/s 00:00 sftp> 从库 [root@centos7 mysql]# mv data data_bak [root@centos7 mysql]# tar xf data.tar.gz [root@centos7 mysql]# ll 总用量 6080 drwxr-xr-x. 2 mysql mysql 4096 2月 17 10:05 bin drwxr-xr-x. 12 mysql mysql 4096 3月 30 14:32 data drwxr-xr-x. 6 mysql mysql 4096 3月 30 19:25 data_bak -rw-r--r--. 1 mysql mysql 5751938 3月 30 14:42 data.tar.gz drwxr-xr-x. 2 mysql mysql 82 2月 17 10:05 docs drwxr-xr-x. 3 mysql mysql 4096 2月 17 10:04 include drwxr-xr-x. 6 mysql mysql 4096 2月 17 10:05 lib -rw-r--r--. 1 mysql mysql 335809 10月 7 16:44 LICENSE -rw-r--r--. 1 mysql mysql 101807 10月 7 16:44 LICENSE.router drwxr-xr-x. 4 mysql mysql 28 2月 17 10:04 man -rw-r--r--. 1 mysql mysql 687 10月 7 16:44 README -rw-r--r--. 1 mysql mysql 700 10月 7 16:44 README.router drwxr-xr-x. 28 mysql mysql 4096 2月 17 10:05 share drwxr-xr-x. 2 mysql mysql 86 2月 17 10:14 support-files [root@centos7 mysql]# cd data [root@centos7 data]# rm auto.cnf -f
创建主从关系
[root@centos7 data]# /etc/init.d/mysql.server start mysql> mysql> CHANGE MASTER TO -> MASTER_HOST=‘192.168.204.132‘, -> MASTER_PORT=3306, -> MASTER_USER=‘repl‘, -> MASTER_PASSWORD=‘mysql‘, -> MASTER_LOG_FILE=‘binlog.000008‘, -> MASTER_LOG_POS=709; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec) mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.204.132 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000009 Read_Master_Log_Pos: 155 Relay_Log_File: centos7-relay-bin.000003 Relay_Log_Pos: 363 Relay_Master_Log_File: binlog.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 155 Relay_Log_Space: 737 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: 1 Master_UUID: e30d70f7-325a-11e9-811b-000c2940fa33 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 1 row in set (0.00 sec) mysql> stop slave; Query OK, 0 rows affected (0.03 sec)
创建主从复制报错
主从复制保错 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; Last_IO_Error: error connecting to master ‘repl@192.168.204.132:3306‘ - retry-time: 60 retries: 1 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file‘ 重置主从关系 主库操作 mysql> stop slave; mysql> reset slave all;
如果主库不能锁表，主库上有业务操作
模拟操作 mysql> use A1； mysql> delimiter // mysql> create procedure proc1() -> begin -> declare n int default 1; -> while n<=20000 do -> insert into temp values(n,‘mike‘); -> set n=n+1; -> end while; -> end; -> // mysql> delimiter ; mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000009 | 1158 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> create table temp(id int,name varchar(20)); Query OK, 0 rows affected (0.03 sec) mysql> call proc1(); Query OK, 1 row affected (2 min 10.79 sec)
错误的方式创建主从关系
mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000009 | 5642155 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> call proc1(); Query OK, 1 row affected (31.77 sec) [root@centos7 ~]# mysqldump --all-databases --master-data -u root -p > dbdump.db Enter password: mysql> select count(*) from temp; +----------+ | count(*) | +----------+ | 20000 | +----------+ 1 row in set (0.02 sec) [root@centos7 ~]# sftp -oPort=22 192.168.204.133 root@192.168.204.133‘s password: Connected to 192.168.204.133. sftp> put dbdump.db Uploading dbdump.db to /root/dbdump.db dbdump.db 100% 965KB 964.6KB/s 00:00 sftp> 从库 mysql> source dbdump.db mysql> CHANGE MASTER TO -> MASTER_HOST=‘192.168.204.132‘, -> MASTER_PORT=3306, -> MASTER_USER=‘repl‘, -> MASTER_PASSWORD=‘mysql‘, -> MASTER_LOG_FILE=‘binlog.000009‘, -> MASTER_LOG_POS=5642155; Query OK, 0 rows affected, 2 warnings (0.04 sec) mysql> start slave; Query OK, 0 rows affected (0.10 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.204.132 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000009 Read_Master_Log_Pos: 11282155 Relay_Log_File: centos7-relay-bin.000002 Relay_Log_Pos: 3577207 Relay_Master_Log_File: binlog.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 9219043 Relay_Log_Space: 5640529 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: 124 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: 1 Master_UUID: e30d70f7-325a-11e9-811b-000c2940fa33 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: waiting for handler commit Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 1 row in set (0.00 sec)
从库报错数据不一致
mysql> select count(*) from temp; +----------+ | count(*) | +----------+ | 24230 | +----------+ 1 row in set (0.01 sec) mysql> select id,count(*) from temp group by id having count(*)>=2; +------+----------+ | id | count(*) | +------+----------+ | 1794 | 2 | | 1795 | 2 | | 1796 | 2 | | 1797 | 2 | | 1798 | 2 | | 1799 | 2 | | 1800 | 2 |
正确方案
正确方案 主库操作 mysqldump --all-databases --master-data=2 -u root -p > dbdump.db mysqldump --all-databases --master-data=2 --single-transaction -u root -p >dbdump.db vi dbdump.db CHANGE MASTER TO MASTER_LOG_FILE=‘binlog.000009‘, MASTER_LOG_POS=18811367; 从库操作 mysql> reset slave all; Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> CHANGE MASTER TO -> MASTER_HOST=‘192.168.204.132‘, -> MASTER_PORT=3306, -> MASTER_USER=‘repl‘, -> MASTER_PASSWORD=‘mysql‘, -> MASTER_LOG_FILE=‘binlog.000009‘, -> MASTER_LOG_POS=18811367; Query OK, 0 rows affected, 2 warnings (0.05 sec) mysql> start slave; Query OK, 0 rows affected (0.04 sec) mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.204.132 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000009 Read_Master_Log_Pos: 22964381 Relay_Log_File: centos7-relay-bin.000002 Relay_Log_Pos: 2073019 Relay_Master_Log_File: binlog.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 20884067 Relay_Log_Space: 4153543 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: 429 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: 1 Master_UUID: e30d70f7-325a-11e9-811b-000c2940fa33 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: waiting for handler commit Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 1 row in set (0.00 sec) ERROR: No query specified mysql> show databases; +--------------------+ | Database | +--------------------+ | A1 | | A2 | | A3 | | course | | course2 | | course3 | | course4 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 11 rows in set (0.00 sec) mysql> use A1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select count(*) from temp; +----------+ | count(*) | +----------+ | 20000 | +----------+ 1 row in set (0.01 sec)
第二个从节点创建主从关系方法同上
第二个从节点 [root@oldboy-mysql-slave2 ~]# vi dbdump.db #查看MASTER_LOG_FILE/MASTER_LOG_POS root@oldboy-mysql-slave2 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. mysql> source dbdump.db mysql> CHANGE MASTER TO -> MASTER_HOST=‘10.0.0.200‘, -> MASTER_PORT=3306, -> MASTER_USER=‘repl‘, -> MASTER_PASSWORD=‘mysql‘, -> MASTER_LOG_FILE=‘binlog.000043‘, -> MASTER_LOG_POS=7570317; Query OK, 0 rows affected, 2 warnings (0.07 sec) mysql> start slave; Query OK, 0 rows affected (0.06 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.200 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000043 Read_Master_Log_Pos: 11644030 Relay_Log_File: oldboy-mysql-slave2-relay-bin.000002 Relay_Log_Pos: 226135 Relay_Master_Log_File: binlog.000043 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 7796133 Relay_Log_Space: 4074254 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: 2947 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: 1 Master_UUID: fc58cc2d-164b-11e9-95af-000c29129a95 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: waiting for handler commit Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 1 row in set (0.00 sec)
方法2 利用从库1
[root@oldboy-mysql-slave1 mysql]#/etc/init.d/mysql.server stop [root@oldboy-mysql-slave1 mysql]# tar -zcvf data.tar.gz data data/ data/ibdata1 data/ib_logfile1 data/undo_001 data/undo_002 data/ib_logfile0 [root@oldboy-mysql-slave1 mysql]# sftp root@10.0.0.202 The authenticity of host ‘10.0.0.202 (10.0.0.202)‘ can‘t be established. ECDSA key fingerprint is SHA256:/yqIM0T3ZqFIt1SdWZb50q8qffjj7PbwKr+aLXFSw+4. ECDSA key fingerprint is MD5:6c:33:8b:79:5b:b3:a6:5e:78:b8:e6:4f:47:bf:0b:07. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added ‘10.0.0.202‘ (ECDSA) to the list of known hosts. root@10.0.0.202‘s password: Connected to 10.0.0.202. sftp> put data.tar.gz Uploading data.tar.gz to /root/data.tar.gz data.tar.gz 100% 285MB 27.7MB/s 00:10 sftp> exit 二从节点 [root@oldboy-mysql-slave2 mysql]# tar -zxvf data.tar.gz data/ data/ibdata1 data/ib_logfile1 data/undo_001 data/undo_002 data/ib_logfile0 data/#innodb_temp/ data/mysql.ibd [root@oldboy-mysql-slave2 data]# rm -rf auto.cnf [root@oldboy-mysql-slave2 data]# /etc/init.d/mysql.server start Starting MySQL.Logging to ‘/data/mysql/mysql/data/oldboy-mysql-slave2.err‘. .... SUCCESS! [root@oldboy-mysql-slave2 data]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.0.0.200 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000043 Read_Master_Log_Pos: 11644030 Relay_Log_File: oldboy-mysql-slave1-relay-bin.000002 Relay_Log_Pos: 4074032 Relay_Master_Log_File: binlog.000043 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 11644030 Relay_Log_Space: 0 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: NULL 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: 0 Master_UUID: fc58cc2d-164b-11e9-95af-000c29129a95 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 1 row in set (0.00 sec) 我在这里就已经成功了
联级复制
从库打开log_slave_updates，主库修改数据会记录到从库的binlog中，用于从库之间做联级复制 mysql> show variables like "%log_slave_updates%"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | log_slave_updates | ON | +-------------------+-------+ 1 row in set (0.12 sec) mysql> show variables like "%log_bin%"; +---------------------------------+------------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------------+ | log_bin | ON | | log_bin_basename | /usr/local/mysql/data/binlog | | log_bin_index | /usr/local/mysql/data/binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+------------------------------------+ 6 rows in set (0.00 sec) [root@mysql-slave1 data]# mysqlbinlog -v binlog.000007 > abc.log ### INSERT INTO `test`.`dept2` ### SET ### @1=1 ### @2=‘math‘ # at 895587 #190401 22:58:11 server id 1 end_log_pos 895618 CRC32 0xeff2d5c4 Xid = 531 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= ‘AUTOMATIC‘ /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@mysql-slave1 data]# cat /etc/my.cnf [mysqld] server-id=2 log_slave_updates=1 replicate-do-db=test
replicate-do-db：该参数用来指定需要复制的数据库。在基于语句复制的环境中，指定该参数之后，则slave的SQL thread进程只会应用在本数据库下的对象相关的语句。如果有多个数据库需要复制，则这
个参数要使用多次。但如果是涉及到跨库操作语句，则复制会丢失；
[root@mysql-slave1 data]# /etc/init.d/mysql.server restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.138.131 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000007 Read_Master_Log_Pos: 1490 Relay_Log_File: mysql-slave1-relay-bin.000006 Relay_Log_Pos: 319 Relay_Master_Log_File: binlog.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0