mysql的主从同步详解-配置篇（mysql8.0.16）

上一章节详细讲解了myslq的主从原理，本章节将为大家演示下主从同步的配置。

环境规划

节点属性	系统版本	数据库版本
master	CentOS 7.6.1810	mysql 8.0.16
slave	CentOS 7.6.1810	mysql 8.0.16

1 部署mysql

部署参考mysql8.0.11源码安装，稍后会在文章的结尾给大家奉上mysql8.0.16的安装脚本。

2 配置主从

master上配置

配置文件修改(/etc/my.cnf)

server_id = 10                       #必配
log_bin = /u01/mysql/binlog/binlog   #必配
binlog_format = mixed                #非必配
binlog_cache_size = 4M               #非必配
max_binlog_size = 1G				 #非必配
binlog_expire_logs_seconds = 604800  #非必配
expire_logs_days = 7				 #非必配

创建复制用户

CREATE USER 'repl'@'%' identified by 'repl@123';
GRANT REPLICATION SLAVE ON *.*  TO  'repl'@'%';
flush privileges;

重启myql，查看status

mysql -uroot -p*******

show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 155 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+

slave上配置

配置文件修改(/etc/my.cnf)

server_id = 10						 #必配
########如果需要GTID的话，可以配置以下配置项，本篇默认使用异步复制########
log_bin = /u01/mysql/binlog/binlog   #必配
binlog_format = mixed                #非必配
binlog_cache_size = 4M               #非必配
max_binlog_size = 1G				 #非必配
binlog_expire_logs_seconds = 604800  #非必配
expire_logs_days = 7				 #非必配
#################################################################

重启mysql，开始同步配置

mysql -uroot -p*******

change master to 
master_host='10.99.200.110',
master_user='repl',
master_password='repl@123',
master_log_file='mysql-bin.000001',
master_log_pos=155;

start slave；

show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.99.200.110
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 994977537
               Relay_Log_File: node2-relay-bin.000005
                Relay_Log_Pos: 994977751
        Relay_Master_Log_File: mysql-bin.000002
             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: mysql.%,test.%,information_schema.%,performance_schema.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 994977537
              Relay_Log_Space: 994978006
              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: 10
                  Master_UUID: 21614331-905b-11eb-a7e3-5254006900b3
             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
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

说明：
由于我事先已经配置好了主从，且有数据的更新，所以通过show slave status\G;命令查看的时候，Master_Log_File和Read_Master_Log_Pos已经变化了，但是并不影响；只要Slave_IO_Running和Slave_SQL_Running状态时Yes，就说明主从正常。

3 导入数据

master上导入数据，slave上查看

mysql -uroot -p******  #登录数据库

source /root/20210329_020001-external_service.sql; #导入提前准备好的sql文件

验证数据复制的结果

4 安装包和安装脚本

链接：https://pan.baidu.com/s/1Xz16LCnIjFi-8O4UWSunWQ 
提取码：njok 

都到这儿了，更多文章，详见个人微信公众号ALL In Linux，来扫一扫吧！