主从复制测试
进行mysql的主从复制有很多方式，此处我们进行一个简单的测试。
1、在Master中创建一个新库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+——————–+
5 rows in set (0.00 sec)
mysql> create table mytbl (id varchar(16),name varchar(32));
ERROR 1046 (3D000): No database selected
mysql> use test;
Database changed
mysql> CREATE TABLE test (id VARCHAR(16), name VARCHAR(32));
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| test |
+—————-+
1 row in set (0.00 sec)
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| test |
+—————-+
1 row in set (0.00 sec)
mysql> SHOW MASTER STATUS;
+——————-+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————-+———-+————–+——————+——————-+
| master-bin.000001 | 617 | mysql | | |
+——————-+———-+————–+——————+——————-+
1 row in set (0.00 sec)
这边从库起不来
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G
* 1. row *
Slave_IO_State: Waiting for master to send event
Master_Host: 119.29.17.67
Master_User: slave
Master_Port: 10050
Connect_Retry: 30
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 617
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 321
Relay_Master_Log_File: master-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: 617
Relay_Log_Space: 695
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: 100
Master_UUID: a0a79626-5760-11ee-a8fd-0242ac110002
Master_Info_File: /var/lib/mysql/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:
1 row in set (0.00 sec)
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+
4 rows in set (0.00 sec)
这边有个大坑，就是我们
[mysqld]
同一局域网内注意要唯一
server-id=100
开启二进制日志功能，可以随便取（关键）
log-bin=master-bin
binlog-format=ROW // 二级制日志格式，有三种 row，statement，mixed
binlog-do-db=数据库名 //同步的数据库名称,如果不配置，表示同步所有的库
写的是mysql
意味着同步只能同步mysql表里面数据新建的库需要添加额外的
mysql> show tables;
+—————————+
| Tables_in_mysql |
+—————————+
| 1 |
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| mytbl |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+—————————+
33 rows in set (0.00 sec)
mysql>
mysql> show tables;
+—————————+
| Tables_in_mysql |
+—————————+
| 1 |
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| mytbl |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+—————————+
33 rows in set (0.00 sec)