天天看點

MySQL 5.7 的多源複制

 基于5.7的多源複制

GTID複制的好處: 對dba來說是很happy的,在傳統的方式裡,你需要找到binlog和POS點,然後change master to指向,初學者不熟悉可能會找錯,造成主從同步複制報錯,在mysql5.6裡,你不用再知道binlog和POS點, 你隻需要知道master的IP、端口,賬号密碼即可,因為同步複制是自動的,mysql通過内部機制GTID自動找點同步。

多源複制:即多個主,複制到1個從庫,所有db彙總到一個slave的執行個體 注意:多源資料庫不能有同名庫,否則會導緻多源複制失敗

環境:

master 兩個執行個體, slave 一個執行個體

           master             master       slave  ip   :  192.168.26.233  192.168.26.233   192.168.26.108  port :      3306               3307        3306 

1.參數檔案 

3306參數檔案

server-id = 3306100 port = 3306 log-bin = /data/mysql/mysql_3306/logs/mysql-bin #gtid  gtid-mode = on enforce-gtid-consistency = 1

3307參數檔案

server-id = 3307100 port = 3307 log-bin = /data/mysql/mysql_3307/logs/mysql-bin #gtid  gtid-mode = on enforce-gtid-consistency = 1

2.把資料庫保持在剛建立的狀态   在3306 和 3307 master 

show master status; reset master; reset slave all; 

3.建立測試db

3306 建立 zw3306 3307 建立 zw3307 

#3306  mysql> create database zw3306; Query OK, 1 row affected (0.00 sec)

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | sys                | | zw3306             | +--------------------+ 5 rows in set (0.00 sec)

mysql> use zw3306 Database changed mysql> show tables; Empty set (0.00 sec)

mysql> create table t1(id int(5)); Query OK, 0 rows affected (0.25 sec)

mysql> insert into t1 values(1); Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(2); Query OK, 1 row affected (0.00 sec)

mysql> select * from t1; +------+ | id   | +------+ |    1 | |    2 | +------+ 2 rows in set (0.00 sec)

#3307

mysql> create database zw3307; Query OK, 1 row affected (0.00 sec)

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | sys                | | zw3307             | +--------------------+ 5 rows in set (0.00 sec)

mysql> use zw3307 Database changed mysql> show tables; Empty set (0.00 sec)

mysql> create table t2(id int(5)); Query OK, 0 rows affected (0.18 sec)

mysql> insert into t2 values(1); Query OK, 1 row affected (0.03 sec)

mysql> insert into t2 values(2); Query OK, 1 row affected (0.00 sec)

mysql> select * from t2; +------+ | id   | +------+ |    1 | |    2 | +------+ 2 rows in set (0.00 sec)

4. master建立複制賬号

mysql> create user 'repl'@'192.168.26.%' identified by 'repl'; Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.26.%'; Query OK, 0 rows affected (0.00 sec)

mysql> create user 'repl3307'@'192.168.26.%' identified by 'repl3307'; Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'repl3307'@'192.168.26.%'; Query OK, 0 rows affected (0.00 sec)

5. slave端配置檔案

server-id = 3306101 port = 3306 log-bin = /data/mysql/mysql_3306/logs/mysql-bin #gtid  gtid-mode = on enforce-gtid-consistency = 1 #資訊存在table裡面 master_info_repository = table  relay_log_info_repository = table 

可動态修改 set global master_info_repository = 'table';

注意: 基于gtid有兩種方法初始化同步

方法一: dump出資料

------------------------------------------------------------------------------------------------------------------------------- 1.将資料庫導出來

[[email protected] ~]# mysqldump -S /tmp/mysql3306.sock -uroot -p --single-transaction --master-data=2 zw3306 > /tmp/backup_3306.sql  [[email protected] ~]# mysqldump -S /tmp/mysql3307.sock -uroot -p --single-transaction --master-data=2 zw3307 > /tmp/backup_3307.sql    2. scp 到slave端

[[email protected] tmp]# scp *.sql [email protected]:/tmp reverse mapping checking getaddrinfo for bogon [192.168.26.108] failed - POSSIBLE BREAK-IN ATTEMPT! [email protected]'s password:  backup_3306.sql                        100% 2183     2.1KB/s   00:00     backup_3307.sql                        100% 2183     2.1KB/s   00:00

SET @@GLOBAL.GTID_PURGED='7e354a2c-6f5f-11e6-997d-005056a36f08:1-4'; --3306  SET @@GLOBAL.GTID_PURGED='fed5c075-7009-11e6-954d-005056a36f08:1-6'; --3307  也可也用 show master status; 指令檢視gtid

3.恢複到slave

4. 然後 change master  ----------------------------------------------------------------------------------------------------------------------------

方法二:也可以直接change過來

基于GTID:   change master to master_host='192.168.26.233', master_port=3306, master_user='repl',master_password='repl', master_auto_position=1 for channel 'master-3306100'; change master to master_host='192.168.26.233', master_port=3307, master_user='repl3307',master_password='repl3307', master_auto_position=1 for channel 'master-3307100';

for channel 多通道

基于binlog+postion

change master to master_host='192.168.26.233', master_port=3306, master_user='repl',master_password='repl',master_log_file='mysql-bin.000016',master_log_pos=154 for channel 'master-3306100'; change master to master_host='192.168.26.233', master_port=3307, master_user='repl3307',master_password='repl3307',master_log_file='mysql-bin.000016',master_log_pos=154 for channel 'master-3307100';

[email protected] [(none)]>show slave status\G *************************** 1. row ***************************                Slave_IO_State:                    Master_Host: 192.168.26.233                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File:            Read_Master_Log_Pos: 4                Relay_Log_File: [email protected]                 Relay_Log_Pos: 4         Relay_Master_Log_File:               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: 0               Relay_Log_Space: 154               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:               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: ba0d5587-74d6-11e6-ab5c-005056a3f46e:1                 Auto_Position: 1          Replicate_Rewrite_DB:                   Channel_Name: master-3306100            Master_TLS_Version:  *************************** 2. row ***************************                Slave_IO_State:                    Master_Host: 192.168.26.233                   Master_User: repl                   Master_Port: 3307                 Connect_Retry: 60               Master_Log_File:            Read_Master_Log_Pos: 4                Relay_Log_File: [email protected]                 Relay_Log_Pos: 4         Relay_Master_Log_File:               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: 0               Relay_Log_Space: 154               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:               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: ba0d5587-74d6-11e6-ab5c-005056a3f46e:1                 Auto_Position: 1          Replicate_Rewrite_DB:                   Channel_Name: master-3307100            Master_TLS_Version:  2 rows in set (0.00 sec)

可以看到有兩個同步資訊

6. 常用一些指令

可以用以下指令隻看一個 show slave status for channel 'master-3306100'\G; show slave status for channel 'master-3307100'\G;

  開關複制: start/stop slave; start/stop slave io_thread/sql_thread  for channel;

檢視slave

show slave io_thread/sql_thread for channel channel_name;   校驗環境

master1 :3306  create databse zw3306

use performance_schema 下面的一張表

[email protected] [performance_schema]>select * from replication_connection_configuration \G; *************************** 1. row ***************************                  CHANNEL_NAME: master-3306100                          HOST: 192.168.26.233                          PORT: 3306                          USER: repl             NETWORK_INTERFACE:                  AUTO_POSITION: 1                   SSL_ALLOWED: NO                   SSL_CA_FILE:                    SSL_CA_PATH:                SSL_CERTIFICATE:                     SSL_CIPHER:                        SSL_KEY:  SSL_VERIFY_SERVER_CERTIFICATE: NO                  SSL_CRL_FILE:                   SSL_CRL_PATH:      CONNECTION_RETRY_INTERVAL: 60        CONNECTION_RETRY_COUNT: 86400            HEARTBEAT_INTERVAL: 30.000                   TLS_VERSION:  *************************** 2. row ***************************                  CHANNEL_NAME: master-3307100                          HOST: 192.168.26.233                          PORT: 3307                          USER: repl             NETWORK_INTERFACE:                  AUTO_POSITION: 1                   SSL_ALLOWED: NO                   SSL_CA_FILE:                    SSL_CA_PATH:                SSL_CERTIFICATE:                     SSL_CIPHER:                        SSL_KEY:  SSL_VERIFY_SERVER_CERTIFICATE: NO                  SSL_CRL_FILE:                   SSL_CRL_PATH:      CONNECTION_RETRY_INTERVAL: 60        CONNECTION_RETRY_COUNT: 86400            HEARTBEAT_INTERVAL: 30.000                   TLS_VERSION:  2 rows in set (0.00 sec)

ERROR:  No query specified

[email protected] [performance_schema]>select * from replication_connection_status\G; *************************** 1. row ***************************              CHANNEL_NAME: master-3306100                GROUP_NAME:                SOURCE_UUID:                  THREAD_ID: NULL             SERVICE_STATE: OFF  --還沒開啟 COUNT_RECEIVED_HEARTBEATS: 0  LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00  RECEIVED_TRANSACTION_SET:          LAST_ERROR_NUMBER: 0        LAST_ERROR_MESSAGE:       LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 *************************** 2. row ***************************              CHANNEL_NAME: master-3307100                GROUP_NAME:                SOURCE_UUID:                  THREAD_ID: NULL             SERVICE_STATE: OFF COUNT_RECEIVED_HEARTBEATS: 0  LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00  RECEIVED_TRANSACTION_SET:          LAST_ERROR_NUMBER: 0        LAST_ERROR_MESSAGE:       LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 2 rows in set (0.00 sec)

ERROR:  No query specified

還沒開啟服務!

7. 開啟服務:

[email protected] [performance_schema]>start slave; Query OK, 0 rows affected (0.00 sec)

[email protected] [performance_schema]>select * from replication_connection_status\G; *************************** 1. row ***************************              CHANNEL_NAME: master-3306100                GROUP_NAME:                SOURCE_UUID: 7e354a2c-6f5f-11e6-997d-005056a36f08                 THREAD_ID: 30             SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0  LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00  RECEIVED_TRANSACTION_SET: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-4         LAST_ERROR_NUMBER: 0        LAST_ERROR_MESSAGE:       LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 *************************** 2. row ***************************              CHANNEL_NAME: master-3307100                GROUP_NAME:                SOURCE_UUID: fed5c075-7009-11e6-954d-005056a36f08                 THREAD_ID: 32             SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0  LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00  RECEIVED_TRANSACTION_SET: fed5c075-7009-11e6-954d-005056a36f08:1-6         LAST_ERROR_NUMBER: 0        LAST_ERROR_MESSAGE:       LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 2 rows in set (0.00 sec)

ERROR:  No query specified

有gtid的資訊了!

[email protected] [performance_schema]>show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.26.233                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 1002                Relay_Log_File: [email protected]                 Relay_Log_Pos: 1215         Relay_Master_Log_File: mysql-bin.000001              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: 1002               Relay_Log_Space: 1435               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: 3306100                   Master_UUID: 7e354a2c-6f5f-11e6-997d-005056a36f08              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: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-4             Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-4, ba0d5587-74d6-11e6-ab5c-005056a3f46e:1, fed5c075-7009-11e6-954d-005056a36f08:1                 Auto_Position: 1          Replicate_Rewrite_DB:                   Channel_Name: master-3306100            Master_TLS_Version:  *************************** 2. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.26.233                   Master_User: repl                   Master_Port: 3307                 Connect_Retry: 60               Master_Log_File: mysql-bin.000011           Read_Master_Log_Pos: 194                Relay_Log_File: [email protected]                 Relay_Log_Pos: 532         Relay_Master_Log_File: mysql-bin.000010              Slave_IO_Running: Yes             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: 1396                    Last_Error: Error 'Operation CREATE USER failed for 'repl'@'192.168.26.%'' on query. Default database: ''. Query: 'CREATE USER 'repl'@'192.168.26.%' IDENTIFIED WITH 'mysql_native_password' AS '*A424E797037BF97C19A2E88CF7891C5C2038C039''                  Skip_Counter: 0           Exec_Master_Log_Pos: 319               Relay_Log_Space: 2678               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: 1396                Last_SQL_Error: Error 'Operation CREATE USER failed for 'repl'@'192.168.26.%'' on query. Default database: ''. Query: 'CREATE USER 'repl'@'192.168.26.%' IDENTIFIED WITH 'mysql_native_password' AS '*A424E797037BF97C19A2E88CF7891C5C2038C039''   Replicate_Ignore_Server_Ids:               Master_Server_Id: 3307100                   Master_UUID: fed5c075-7009-11e6-954d-005056a36f08              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: 160914 15:04:09                Master_SSL_Crl:             Master_SSL_Crlpath:             Retrieved_Gtid_Set: fed5c075-7009-11e6-954d-005056a36f08:1-6             Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-4, ba0d5587-74d6-11e6-ab5c-005056a3f46e:1, fed5c075-7009-11e6-954d-005056a36f08:1                 Auto_Position: 1          Replicate_Rewrite_DB:                   Channel_Name: master-3307100            Master_TLS_Version:  2 rows in set (0.00 sec)

ERROR:  No query specified

注意:上面這個蛋疼的問題折騰了我一天,原因是建立賬号的時候master端和slave全部都建立了相同名字的賬戶。蛋疼的折騰了兩天!

[email protected] [zw3307]>show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.26.233                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 2030                Relay_Log_File: [email protected]                 Relay_Log_Pos: 454         Relay_Master_Log_File: mysql-bin.000001              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: 2030               Relay_Log_Space: 974               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: 3306100                   Master_UUID: 7e354a2c-6f5f-11e6-997d-005056a36f08              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: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-8             Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-8, ba0d5587-74d6-11e6-ab5c-005056a3f46e:1-4, fed5c075-7009-11e6-954d-005056a36f08:1-15                 Auto_Position: 1          Replicate_Rewrite_DB:                   Channel_Name: master-3306100            Master_TLS_Version:  *************************** 2. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.26.233                   Master_User: repl3307                   Master_Port: 3307                 Connect_Retry: 60               Master_Log_File: mysql-bin.000011           Read_Master_Log_Pos: 1931                Relay_Log_File: [email protected]                 Relay_Log_Pos: 454         Relay_Master_Log_File: mysql-bin.000011              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: 1931               Relay_Log_Space: 974               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: 3307100                   Master_UUID: fed5c075-7009-11e6-954d-005056a36f08              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: fed5c075-7009-11e6-954d-005056a36f08:1-15             Executed_Gtid_Set: 7e354a2c-6f5f-11e6-997d-005056a36f08:1-8, ba0d5587-74d6-11e6-ab5c-005056a3f46e:1-4, fed5c075-7009-11e6-954d-005056a36f08:1-15                 Auto_Position: 1          Replicate_Rewrite_DB:                   Channel_Name: master-3307100            Master_TLS_Version:  2 rows in set (0.00 sec)

ERROR:  No query specified

删除多餘的賬戶後,沒有報錯了!

8.測試同步

[email protected] [zw3307]>show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | sys                | | zw3306             | | zw3307             | +--------------------+ 6 rows in set (0.00 sec)

可以看到兩個資料庫已經過來了!

[email protected] [zw3306]>show tables; +------------------+ | Tables_in_zw3306 | +------------------+ | t1               | +------------------+ 1 row in set (0.00 sec)

[email protected] [zw3306]>select * from t1; +------+ | id   | +------+ |    1 | |    2 | +------+ 2 rows in set (0.00 sec) 有兩條資料

master 3306 插入兩條資料

mysql> insert into t1 values(3); Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(4); Query OK, 1 row affected (0.00 sec)

檢視slave 3306的庫 [email protected] [zw3306]>select * from t1; +------+ | id   | +------+ |    1 | |    2 | |    3 | |    4 | +------+ 4 rows in set (0.00 sec)

可以看到同步過來了。

繼續閱讀