- GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。
3.X
版本就开始支持复制功能,期间多次完善、改进、新增功能,比如并行复制的改进、GTID复制的推出,多源复制的完善等众多功能的实现,目前该技术已基本成熟。[root@GreatSQL][(none)]>show processlist; +----+-----------------+-----------------+------+-------------+------+-----------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+------+-------------+------+-----------------------------------------------------------------+------------------+ | 8 | sync | 127.0.0.1:35424 | NULL | Binlog Dump | 5092 | Source has sent all binlog to replica; waiting for more updates | NULL | +----+-----------------+-----------------+------+-------------+------+-----------------------------------------------------------------+------------------+ 3 rows in set (0.00 sec)
[root@GreatSQL][(none)]>show processlist; +----+-----------------+-----------------+------+---------+-------+----------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+------+---------+-------+----------------------------------------------------------+------------------+ | 16 | system user | connecting host | NULL | Connect | 5279 | Waiting for source to send event | NULL | | 17 | system user | | NULL | Query | 5010 | Replica has read all relay log; waiting for more updates | NULL | +----+-----------------+-----------------+------+---------+-------+----------------------------------------------------------+------------------+ 5 rows in set (0.00 sec)
mysql> CREATE USER sync@'127.0.0.1' identified by 'GreatSQL'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'127.0.0.1'; mysql> FLUSH PRIVILEGES;
# 创建库表 [root@GreatSQL][(none)]>create database test; [root@GreatSQL][(none)]>use test Database changed [root@GreatSQL][test]>create table t1 (id int auto_increment, name varchar(64), primary key(`id`)); Query OK, 0 rows affected (0.42 sec) # 创建数据 [root@GreatSQL][test]>insert into t1 values(1,'小明'),(2,'小林'),(3,'小王'); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0
[root@GreatSQL]# mysqldump -uroot -p --single-transaction --master_data=2 --set-gtid-purged=OFF -S /tmp/mysql57_3310.sock test > ./3310.sql Enter password:
[root@GreatSQL]# cat 3310.sql|grep log-bin -- CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000005', MASTER_LOG_POS=4111;
mysql -uroot -p -S /tmp/mysql57_3306.sock test < ./3310.sql
# 采用传统 position 方式建立同步 [root@GreatSQL][test]>change master to master_host="127.0.0.1",master_port=3310,MASTER_USER='sync',MASTER_PASSWORD='GreatSQL',MASTER_LOG_FILE='log-bin.000005', MASTER_LOG_POS=4111; Query OK, 0 rows affected, 9 warnings (0.16 sec) [root@GreatSQL][test]>start slave; Query OK, 0 rows affected, 1 warning (0.01 sec)
[root@GreatSQL][(none)]>show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 127.0.0.1 Master_User: sync Master_Port: 3310 Connect_Retry: 60 Master_Log_File: log-bin.000006 Read_Master_Log_Pos: 196 Relay_Log_File: mgr3-relay-bin.000003 Relay_Log_Pos: 367 Relay_Master_Log_File: log-bin.000006 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: 196 Relay_Log_Space: 741 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: 2013712 Master_UUID: 68420c27-77ff-11ec-825e-00155dcff90e Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: 68420c27-77ff-11ec-825e-00155dcff90e:6-9, 9f18f115-4621-11ec-8e5b-00155dcff902:1-43 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.08 sec) ERROR: No query specified
# 主节点写入1条测试数据 [root@GreatSQL][(none)]>insert into test.t1 values(4,'小清'); Query OK, 1 row affected (0.15 sec) # 从节点查验,正常同步了 [root@GreatSQL][(none)]>select * from test.t1; +------+--------+ | id | name | +------+--------+ | 1 | 小明 | | 2 | 小林 | | 3 | 小王 | | 4 | 小清 | +------+--------+ 4 rows in set (0.15 sec)
参数名 | 含义介绍 |
---|---|
Slave_IO_State | 从节点IO线程状态 |
Master_Host | 主节点地址 |
Master_User | 复制用户 |
Master_Port | 主节点端口 |
Connect_Retry | 异常中断后,每隔多长时间重连 |
Master_Log_File | 当前主节点正在写入的 binlog 文件 |
Read_Master_Log_Pos | 当前从节点正在读取的 pos 值 |
Relay_Log_File | 当前从节点SQL线程正在读取的中继日志文件 |
Relay_Log_Pos | 当前从节点SQL线程正在读取中继日志的 pos 值 |
Relay_Master_Log_File | 从节点SQL线程执行到了相对于主节点的binlog文件 |
Slave_IO_Running | 从节点IO线程是否正常 |
Slave_SQL_Running | 从节点SQL线程是否正常 |
Exec_Master_Log_Pos | 从节点SQL线程执行到了相对于主节点Pos值的位置 |
Seconds_Behind_Master | 从节点延迟情况,参考值 |
Master_Server_Id | 主节点 server-id,主从节点需不一致,否则会提示冲突造成异常 |
Master_UUID | 主节点UUID值,主从节点需不一致,否则会提示冲突造成异常 |
Master_Info_File | 记录连接主节点的配置信息,如账户密码等 |
Slave_SQL_Running_State | 当前从节点读取主节点binlog文件状态,是否已经完成读取 |
Master_Retry_Count | 异常断开后重试次数 |
binlog文件有3种格式,分别为 statement
、mixed
、row
3 种格式存在部分差异。
statement
row
mixed
mysql> show global variables like '%log_bin%'; +---------------------------------+-----------------------------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------------------------+ | log_bin | ON | | log_bin_basename | /data/logs/mysql57/3310/log-bin/log-bin | | log_bin_index | /data/logs/mysql57/3310/log-bin/log-bin.index | | +---------------------------------+-----------------------------------------------+ 5 rows in set (0.00 sec)
global
参数,否则同步记录都会出问题。# 临时关闭当前session的log-bin mysql> set session sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) # 开启当前session的log-bin mysql> set session sql_log_bin=1; Query OK, 0 rows affected (0.00 sec)
# 查看当前bin-log保留的时长 mysql> show global variables like '%expire_logs_days%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 30 | +------------------+-------+ 1 row in set (0.00 sec) # 动态调整时长,调整后记得配置文件也需要同步更新 mysql> set global expire_logs_days=10; Query OK, 0 rows affected, 1 warning (0.00 sec)
# 查看当前的binlog文件 mysql> show binary logs; +----------------+-----------+-----------+ | Log_name | File_size | Encrypted | +----------------+-----------+-----------+ | log-bin.000001 | 784 | No | | log-bin.000002 | 1779 | No | | log-bin.000003 | 241 | No | | log-bin.000004 | 196 | No | | log-bin.000005 | 1337 | No | +----------------+-----------+-----------+ 5 rows in set (0.02 sec) # 使用 purge 进行切割,这样之前的binlog就会都被清理掉 mysql> purge binary logs to 'log-bin.000003'; Query OK, 0 rows affected (0.03 sec) mysql> show binary logs; +----------------+-----------+-----------+ | Log_name | File_size | Encrypted | +----------------+-----------+-----------+ | log-bin.000003 | 241 | No | | log-bin.000004 | 196 | No | | log-bin.000005 | 1337 | No | +----------------+-----------+-----------+ 3 rows in set (0.00 sec)
Enjoy GreatSQL