首页
IT
登录
6mi
u
盘
搜
搜 索
IT
MySQL 5.7 的多源复制
MySQL 5.7 的多源复制
xiaoxiao
2023-03-16
6
基于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.将数据库导出来
[root@zw-test-db ~]# mysqldump -S /tmp/mysql3306.sock -uroot -p --single-transaction --master-data=2 zw3306 > /tmp/backup_3306.sql
[root@zw-test-db ~]# mysqldump -S /tmp/mysql3307.sock -uroot -p --single-transaction --master-data=2 zw3307 > /tmp/backup_3307.sql
2. scp 到slave端
[root@zw-test-db tmp]# scp *.sql root@192.168.26.108:/tmp
reverse mapping checking getaddrinfo for bogon [192.168.26.108] failed - POSSIBLE BREAK-IN ATTEMPT!
root@192.168.26.108'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';
root@localhost [(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: relay-bin-master@002d3306100.000001
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: relay-bin-master@002d3307100.000001
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 下面的一张表
root@localhost [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
root@localhost [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. 开启服务：
root@localhost [performance_schema]>start slave;
Query OK, 0 rows affected (0.00 sec)
root@localhost [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的信息了！
root@localhost [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: relay-bin-master@002d3306100.000002
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: relay-bin-master@002d3307100.000002
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全部都创建了相同名字的账户。蛋疼的折腾了两天！
root@localhost [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: relay-bin-master@002d3306100.000004
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: relay-bin-master@002d3307100.000006
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.测试同步
root@localhost [zw3307]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zw3306 |
| zw3307 |
+--------------------+
6 rows in set (0.00 sec)
可以看到两个数据库已经过来了！
root@localhost [zw3306]>show tables;
+------------------+
| Tables_in_zw3306 |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)
root@localhost [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的库
root@localhost [zw3306]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
可以看到同步过来了。
转载请注明原文地址: https://ju.6miu.com/read-1152702.html
最新回复
(
0
)
