mysql复制原理与机制一
2024-09-30 18:03:56
复制原理:复制需要二进制日志记录数据库上的改变
slave的IO线程复制把master上的Binary log读取到本地的relay log里
SQL线程负责把relay log恢复到数据库数据里
show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 10.105.7.108
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 630
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 793
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes 关闭slave的io线程
stop slave io_thread;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.105.7.108
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 630
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 793
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: No
Slave_SQL_Running: Yes
到master
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 367 |
| mysql-bin.000003 | 120 |
| mysql-bin.000004 | 630 |
+------------------+-----------+
4 rows in set (0.00 sec)
日志轮换
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec) mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 367 |
| mysql-bin.000003 | 120 |
| mysql-bin.000004 | 677 |
| mysql-bin.000005 | 120 |
+------------------+-----------+
mysql> use hk;
Database changed
mysql> show tables;
+--------------+
| Tables_in_hk |
+--------------+
| test |
+--------------+
1 row in set (0.01 sec) mysql> select * from test;
+----+---------------------+
| id | time |
+----+---------------------+
| 1 | 2017-02-04 10:04:12 |
| 2 | 2017-02-04 13:53:35 |
| 3 | 2017-02-08 19:46:49 |
+----+---------------------+
3 rows in set (0.00 sec) mysql> insert into test values(4,now());
Query OK, 1 row affected (0.05 sec) mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 367 |
| mysql-bin.000003 | 120 |
| mysql-bin.000004 | 677 |
| mysql-bin.000005 | 344 |
+------------------+-----------+
5 rows in set (0.00 sec) mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 108 | 120 | Server ver: 5.6.35-log, Binlog ver: 4 |
| mysql-bin.000005 | 120 | Query | 108 | 203 | BEGIN |
| mysql-bin.000005 | 203 | Query | 108 | 313 | use `hk`; insert into test values(4,now()) |
| mysql-bin.000005 | 313 | Xid | 108 | 344 | COMMIT /* xid=110 */ |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
可以看到一件记录到二进制日志里 mysql> select * from test where id=4;
+----+---------------------+
| id | time |
+----+---------------------+
| 4 | 2017-02-08 20:34:49 |
+----+---------------------+
到从库
Database changed
mysql> select * from test;
+----+---------------------+
| id | time |
+----+---------------------+
| 1 | 2017-02-04 10:04:12 |
| 2 | 2017-02-04 13:53:35 |
| 3 | 2017-02-08 19:46:49 |
+----+---------------------+
查看此时 Relay_Log_File: mysqld-relay-bin.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.105.7.108
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 630
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 793
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: No
Slave_SQL_Running: Yes
开启io线程 mysql> start slave io_thread;
Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.105.7.108
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 344
Relay_Log_File: mysqld-relay-bin.000009
Relay_Log_Pos: 507
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
发现中继日志发生变化
Relay_Log_File: mysqld-relay-bin.
mysql> select * from test where id=4;
+----+---------------------+
| id | time |
+----+---------------------+
| 4 | 2017-02-08 20:34:49 |
+----+---------------------+
1 row in set (0.00 sec) 此时sql进程 从中继日志 读取数据 写入数据库
在mysql外执行
mysqlbinlog mysqld-relay-bin.000009
[root@bogon mysql]# mysqlbinlog mysqld-relay-bin.000009
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170208 20:39:46 server id 163 end_log_pos 120 CRC32 0xbdb725c4 Start: binlog v 4, server v 5.6.35 created 170208 20:39:46
BINLOG '
khGbWA+jAAAAdAAAAHgAAABAAAQANS42LjM1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcQl
t70=
'/*!*/;
# at 120
#170208 20:39:46 server id 108 end_log_pos 0 CRC32 0x0649f72c Rotate to mysql-bin.000005 pos: 4
# at 167
#170208 20:33:47 server id 108 end_log_pos 120 CRC32 0x045c4ff1 Start: binlog v 4, server v 5.6.35-log created 170208 20:33:47
BINLOG '
KxCbWA9sAAAAdAAAAHgAAAAAAAQANS42LjM1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAfFP
XAQ=
'/*!*/;
# at 283
#170208 20:34:49 server id 108 end_log_pos 203 CRC32 0xb10230cc Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1486557289/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 366
#170208 20:34:49 server id 108 end_log_pos 313 CRC32 0x59ee4c4e Query thread_id=9 exec_time=0 error_code=0
use `hk`/*!*/;
SET TIMESTAMP=1486557289/*!*/;
insert into test values(4,now())
/*!*/;
# at 476
#170208 20:34:49 server id 108 end_log_pos 344 CRC32 0x1735772f Xid = 110
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
复制特点
1.sql更新,没写binary log 不可能复制
2.正在执行的sql更新,也不可能被复制
3.只有执行完成而且成功的语句才会被复制
最新文章
- MVC模型的理解
- [Editor]Unity Editor类常用方法
- SQL with PL/SQL
- C#局域网桌面共享软件制作（二）
- 学习java随笔第九篇：java异常处理
- BZOJ1609: [Usaco2008 Feb]Eating Together麻烦的聚餐
- nginx+memcached+ftp上传图片+iis
- PHP分页类,支持自定义样式,中间5页
- 怎么样刷新frameset的整个页面
- MyEclipse设置编码格式
- VS2017 docker部署工具的使用
- 莫烦theano学习自修第四天【激励函数】
- Mysql 计划任务
- ecmall 移动端 微信分享
- 【BZOJ】4011: [HNOI2015]落忆枫音
- 【C#小知识】C#中一些易混淆概念总结（五）---------继承 分类： C# 2014-02-06 22:05 1106人阅读 评论(0) 收藏
- JAVA项目将 Oracle 转 MySQL 数据库转换（Hibernate 持久层）
- iPhone应用提交流程：如何将App程序发布到App Store-转
- LintCode - Copy List with Random Pointer
- VMWare -- 工作模式