标签：log SSL 7.3 master rhel mysql Master Log
文档课题：rhel 7.3搭建mysql一主两从——非单机环境.
数据库：mysql 8.0.27
系统：rhel 7.3
安装包：mysql-8.0.27-linux-glibc2.12-x86_64.tar.xz
环境介绍：
1、 理论知识
MySQL主从复制主要三个线程：
master(binlog dump thread）、slave(I/O thread、SQL thread），Master一条线程和Slave两条线程.
master(binlog dump thread)主要负责Master库中有数据更新时，会按照binlog格式将更新的事件类型写入到主库的binlog文件中，并且Master会创建log dump线程通知Slave主库中存在数据更新，这就是主库binlog日志要开启的原因.
I/O thread线程在Slave中创建，该线程用于请求Master，Master会返回binlog的名称以及当前数据更新的位置、binlog文件位置的副本，然后将binlog保存在relay log(中继日志)中，中继日志也是记录数据更新的信息.
SQL线程在Slave中创建，当Slave检测到中继日志有更新，就会将更新的内容同步到Slave数据库中，如此保证主从数据的同步.
以上为主从复制的过程，该过程有不同的策略方式来进行数据同步，主要为以下几种：
同步策略：Master会等待所有Slave都回应后才提交，此策略会严重影响数据库性能.
半同步策略：Master至少会等待一个Slave回应后才提交.
异步策略：Master不等待Slave回应就可以提交.
延迟策略：Slave要落后于Master指定的时间.
2、主从搭建
2.1、安装mysql
准备三台安装好mysql 8.0.27的服务器.
2.2、master配置
2.2.1、修改配置文件
--修改主节点配置文件.
[root@leo-mysql-master ~]# cd /etc/
[root@leo-mysql-master etc]# vi my.cnf
添加如下：
[mysqld]
basedir=/opt/mysql
datadir=/opt/mysql/data
socket=/tmp/mysql.sock
log-error=/opt/mysql/data/error.log
pid-file=/opt/mysql/data/mysql.pid
port=3306
max_allowed_packet=32M
server-id=1
log-bin=mysql-bin
read-only=0 #可选，0(默认)表示读写(主机)，1表示只读(从机)
binlog_expire_logs_seconds=6000 #设置日志文件保留的时长，单位:秒
max_binlog_size=200M #控制单个二进制日志大小，最大和默认值是1GB
binlog-ignore-db=information_schema,mysql #可选，设置不要复制的数据库
binlog_format=STATEMENT #可选，设置binlog格式
sync_binlog=0 #写缓存多少次刷一次磁盘.默认0表示该操作由操作系统根据自身负载自行决定多久写一次磁盘，1表示每一条事务提交都会立即写磁盘，n表示n个事务提交后才会写磁盘
#character config
character_set_server=utf8mb4
explicit_defaults_for_timestamp=true
symbolic-links=0
innodb_flush_log_at_trx_commit=1 #每次commit日志缓存中的数据刷到磁盘中，通常设置为1，表示事务提交前日志已被写入磁盘
2.2.2、重启mysql服务
[root@leo-mysql-master etc]# service mysql stop
Shutting down MySQL... SUCCESS!
[root@leo-mysql-master etc]# service mysql start
Starting MySQL. SUCCESS!
2.2.3、相关查询
--查看当前server-id，bin-log开启状态以及master节点上的日志名称和position.
[root@leo-mysql-master etc]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| character_set_server | utf8mb4 |
| collation_server | utf8mb4_0900_ai_ci |
| innodb_dedicated_server | OFF |
| innodb_ft_server_stopword_table | |
| server_id | 1 |
| server_id_bits | 32 |
| server_uuid | 8595bc2f-fd19-11ed-8033-000c291140b1 |
+---------------------------------+--------------------------------------+
7 rows in set (0.01 sec)
mysql> show global variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| log_bin | ON |
| log_bin_basename | /opt/mysql/data/mysql-bin |
| log_bin_index | /opt/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+---------------------------------+
5 rows in set (0.00 sec)
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 156 | No |
+------------------+-----------+-----------+
1 row in set (0.00 sec)
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 716
Binlog_Do_DB:
Binlog_Ignore_DB: information_schema,mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)
2.2.4、创建复制用户
mysql> create user 'repluser'@'%' identified by 'repluser';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave,replication client on *.* to 'repluser'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'repluser'@'%';
+----------------------------------------------------------------------+
| Grants for repluser@% |
+----------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repluser`@`%` |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
2.3、从库配置
2.3.1、修改配置文件
--修改slave01从库.
[root@leo-mysql-slave01 etc]# cp /etc/my.cnf /etc/my_bak.cnf
[root@leo-mysql-slave01 etc]# vi my.cnf
[mysqld]
basedir=/opt/mysql
datadir=/opt/mysql/data
socket=/tmp/mysql.sock
log-error=/opt/mysql/data/error.log
pid-file=/opt/mysql/data/mysql.pid
port=3306
max_allowed_packet=32M
server-id=2 --当前节点全局性ID号
relay-log=mysql-slave1-log --relay log的文件路径
log-bin=slave_log --开启二进制日志
read_only=ON --设置从库只读，对root用户无效
relay_log_index=relay-log.index --默认值hostname-relay-bin.index
--修改slave02从库.
[root@leo-mysql-slave02 etc]# cp /etc/my.cnf /etc/my_bak.cnf
[root@leo-mysql-slave02 etc]# vi my.cnf
[mysqld]
basedir=/opt/mysql
datadir=/opt/mysql/data
socket=/tmp/mysql.sock
log-error=/opt/mysql/data/error.log
pid-file=/opt/mysql/data/mysql.pid
port=3306
max_allowed_packet=32M
server-id=3
relay-log=mysql-slave1-log
log-bin=slave_log
read_only=ON
relay_log_index=relay-log.index
2.3.2、重启mysql服务
--重启slave01 mysql服务.
[root@leo-mysql-slave01 etc]# service mysql stop
Shutting down MySQL.... SUCCESS!
[root@leo-mysql-slave01 etc]# service mysql start
Starting MySQL. SUCCESS!
--重启slave02 mysql服务.
[root@leo-mysql-slave02 etc]# service mysql stop
Shutting down MySQL.. SUCCESS!
[root@leo-mysql-slave02 etc]# service mysql start
Starting MySQL. SUCCESS!
2.3.3、启动复制线程
--启动slave01复制线程.
[root@leo-mysql-slave01 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.133.111',master_user='repluser',master_password='repluser',master_log_file='mysql-bin.000001',MASTER_LOG_POS=716,get_master_public_key=1;
Query OK, 0 rows affected, 9 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
--查看状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.133.111
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 716
Relay_Log_File: mysql-slave1-log.000002
Relay_Log_Pos: 324
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: 716
Relay_Log_Space: 534
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: 1
Master_UUID: 8595bc2f-fd19-11ed-8033-000c291140b1
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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SHOW SLAVE STATUS' is deprecated and will be removed in a future release. Please use SHOW REPLICA STATUS instead |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
--启动slave02复制线程.
[root@leo-mysql-slave02 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.133.111',master_user='repluser',master_password='repluser',master_log_file='mysql-bin.000001',MASTER_LOG_POS=716,get_master_public_key=1;
Query OK, 0 rows affected, 9 warnings (0.03 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'CHANGE MASTER' is deprecated and will be removed in a future release. Please use CHANGE REPLICATION SOURCE instead |
| Warning | 1287 | 'MASTER_HOST' is deprecated and will be removed in a future release. Please use SOURCE_HOST instead |
| Warning | 1287 | 'MASTER_USER' is deprecated and will be removed in a future release. Please use SOURCE_USER instead |
| Warning | 1287 | 'MASTER_PASSWORD' is deprecated and will be removed in a future release. Please use SOURCE_PASSWORD instead |
| Warning | 1287 | 'MASTER_LOG_FILE' is deprecated and will be removed in a future release. Please use SOURCE_LOG_FILE instead |
| Warning | 1287 | 'MASTER_LOG_POS' is deprecated and will be removed in a future release. Please use SOURCE_LOG_POS instead |
| Warning | 1287 | 'GET_MASTER_PUBLIC_KEY' is deprecated and will be removed in a future release. Please use GET_SOURCE_PUBLIC_KEY instead |
| Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. |
| Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.133.111
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 716
Relay_Log_File: mysql-slave1-log.000002
Relay_Log_Pos: 324
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: 716
Relay_Log_Space: 534
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: 1
Master_UUID: 8595bc2f-fd19-11ed-8033-000c291140b1
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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
3、数据测试
3.1、建测试数据
现在主库上创建测试库booksDB，然后创建表books并插入记录.测试数据是否会同步到两个从库.
mysql> create database booksDB;
Query OK, 1 row affected (0.00 sec)
mysql> use booksDB;
Database changed
mysql> create table books
-> (
-> bk_id int not null primary key,
-> bk_title varchar(50) not null,
-> copyright year not null
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into books values
-> (11078,'Learning MYSQL',2010),
-> (11033,'Study Html',2011),
-> (11035,'How to use php',2003),
-> (11072,'Teach yourself javascript',2005),
-> (11028,'Learning C++',2005),
-> (11069,'MYSQL professional',2009),
-> (11026,'Guide to MySQL 5.7',2008),
-> (11041,'Inside VC++',2011);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title | copyright |
+-------+---------------------------+-----------+
| 11026 | Guide to MySQL 5.7 | 2008 |
| 11028 | Learning C++ | 2005 |
| 11033 | Study Html | 2011 |
| 11035 | How to use php | 2003 |
| 11041 | Inside VC++ | 2011 |
| 11069 | MYSQL professional | 2009 |
| 11072 | Teach yourself javascript | 2005 |
| 11078 | Learning MYSQL | 2010 |
+-------+---------------------------+-----------+
8 rows in set (0.00 sec)
3.2、验证数据
在slave01执行以下命令，查看数据是否已经同步.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| booksDB |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use booksDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_booksDB |
+-------------------+
| books |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title | copyright |
+-------+---------------------------+-----------+
| 11026 | Guide to MySQL 5.7 | 2008 |
| 11028 | Learning C++ | 2005 |
| 11033 | Study Html | 2011 |
| 11035 | How to use php | 2003 |
| 11041 | Inside VC++ | 2011 |
| 11069 | MYSQL professional | 2009 |
| 11072 | Teach yourself javascript | 2005 |
| 11078 | Learning MYSQL | 2010 |
+-------+---------------------------+-----------+
8 rows in set (0.00 sec)
mysql> show replica status \G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.133.111
Source_User: repluser
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000001
Read_Source_Log_Pos: 1755
Relay_Log_File: mysql-slave1-log.000002
Relay_Log_Pos: 1363
Relay_Source_Log_File: mysql-bin.000001
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 1755
Relay_Log_Space: 1573
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: 8595bc2f-fd19-11ed-8033-000c291140b1
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace:
1 row in set (0.00 sec)
说明：按如上方式分别在slave01和slave02进行验证，主节点数据成功同步到两个从节点.
4、bin-log相关说明
根据MySQL官方文档介绍，开启binlog后，会有1%的性能损失，binlog主要用作数据恢复和主从复制，MySQL 5.7.3以后版本必须配置server-id，否则无法启用MySQL二进制日志.
binlog格式说明：MySQL默认采用的是STATEMENT，建议使用MIXED
binlog_format = MIXED
4.1、STATEMENT模式(SBR)
基于SQL语句的复制(statement-based replication），每一条会修改数据的sql语句都会记录到binlog中.
优点：
不需要记录每一条sql语句和每一行的数据变化，减少binlog日志量，节约IO，提高性能.
缺点：
某些情况会导致master-slave中的数据不一致，例如sleep()，last_insert_id()等
4.2、ROW模式(RBR)
基于行的复制(row-based replication），不记录每条sql语句的上下文信息，仅记录具体被修改的数据.
优点：
任何情况都可以复制，并且不会出现特定情况下存储过程、function等调用或者触发无法被正确复制的问题.
缺点：
binlog日志文件会非常大；
master上执行update语句时，所有变化都会写到binlog里面，所以会导致频繁发生binlog的并发写问题
4.3、MIXED模式
上面两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式.
参考网址：
https://blog.csdn.net/weixin_52324111/article/details/128058857
标签：log,
SSL,
7.3,
master,
rhel,
mysql,
Master,
Log
From： https://blog.51cto.com/u_12991611/6366520