mysql'sbinarylog结构简介_mysql8基于binarylog的主从复制实践

主从架构介绍

mysql自带的副本机制允许从一个mysql数据库(称之为主或者源库)复制一个或多个mysql数据库(称之为从或者副本库)。副本机制默认情况下是异步进行的&＃xff0c;不需要永久连接从源库接收更新。副本机制作用的范围可以为所有数据库&＃xff0c;选定的数据库或者选定的表。

MySQL 8.0支持不同的两种复制方法&＃xff1a;

1)基于从源库的二进制日志(binary log)复制事件实现&＃xff0c;并要求在源库和副本库之间同步日志文件和日志文件中的位置&＃xff0c;异步实现&＃xff0c;不具有事务性。

2)基于全局事务标识符(GTIDs)实现&＃xff0c;具有事务性&＃xff0c;因此不需要处理日志文件及其位置&＃xff0c;这极大地简化了许多常见的复制任务。只要在源库上提交的所有事务也应用于副本库上&＃xff0c;使用GTIDs的复制就可以保证源和副本之间的一致性。

主从架构优点可以实现读写分离架构&＃xff0c;对主库进行写&＃xff0c;对从库进行读&＃xff0c;可以工作负载到副本库进而提高整体性能。

可以保障数据安全&＃xff0c;因为副本可以暂停复制过程&＃xff0c;所以可以在副本上运行备份服务&＃xff0c;而不会损坏相应的源数据。

可以实现数据的备份&＃xff0c;实现容灾

基于binary log的主从架构实践

主库设置

首先在在 MySQL 中增加一个可以进行主从复制权限的用户。在 mysql 交互环境中执行如下命令

# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 14

Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.

mysql> create user &＃39;repl&＃39;&＃64;&＃39;10.20.131.38&＃39; identified by &＃39;Inspur123!&＃64;#&＃39;;

Query OK, 0 rows affected (0.07 sec)

mysql> grant replication slave on *.* to &＃39;repl&＃39;&＃64;&＃39;10.20.131.38&＃39;;

Query OK, 0 rows affected (0.05 sec)

mysql> show master status;

&＃43;---------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;-------------------&＃43;

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

&＃43;---------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;-------------------&＃43;

| binlog.000001 | 2324 | | | |

&＃43;---------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;-------------------&＃43;

1 row in set (0.00 sec)

mysql>

命令**show master status **获取到当前归档日志的名字和位置&＃xff0c;后面从服务器设置主从复制的时候需要从这个位置开始。

从库设置

# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.

mysql> change master to

-> master_host&＃61;&＃39;10.20.131.37&＃39;,

-> master_port&＃61;3306,

-> master_user&＃61;&＃39;repl&＃39;,

-> master_password&＃61;&＃39;Test123!&＃64;#&＃39;,

-> master_log_file&＃61;&＃39;binlog.000001&＃39;,

-> master_log_pos&＃61;2324;

Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql> start slave;

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.20.131.37

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000001

Read_Master_Log_Pos: 2324

Relay_Log_File: h10p20p131p38-relay-bin.000002

Relay_Log_Pos: 319

Relay_Master_Log_File: binlog.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: 2324

Relay_Log_Space: 535

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: 37

Master_UUID: 109ae7ea-1d7c-11eb-980d-48f97cff3ddf

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:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

1 row in set (0.00 sec)

ERROR:

No query specified

Slave_IO_State 的值为 Waiting for master to send event &＃xff0c;表示已经准备好接受主库发送过来的归档日志进行处理了。

测试主从复制

主库建立名字为test的database&＃xff0c;如下&＃xff1a;

mysql> create database test default character set utf8;

Query OK, 1 row affected, 1 warning (0.03 sec)

从库确认是否已经同步备份&＃xff0c;如下&＃xff1a;

mysql> show databases;

&＃43;--------------------&＃43;

| Database |

&＃43;--------------------&＃43;

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

&＃43;--------------------&＃43;

5 rows in set (0.00 sec)

mysql>

根据以上输出&＃xff0c;可以确认主从复制已经生效。

参照