晓波的站站

争做优秀原创个人博客。

小记最简单的mysql8.0主从同步。

xiaobo 9997 0 条评论

前期准备：

1.保持主从2端或者多端的，mysql版本一致，如果不能保持一致就要确保主库比从库低。尽量保持一致，特别是在5.7以下的mysql版本可能会与5.7及以上产生不兼容问题。
2.做主从时关闭selinux，如果不关闭可能会导致mysql起不来。
3.关闭防火墙，如果不关闭请确保放通mysql的端口。

一.my.cnf相关设置和参数解析

datadir=/data/mysql
#数据目录设置，根据实际需要进行
server-id=36
#binlog的id一般取主机地址最后3位，主从id一定不能一样。
binlog_do_db=dbzc,blogdb;
#设置binlog需要记录的数据库，也就是主从需要同步的数据库用逗号隔开。设置了这个就说明只同步这2个库。可以不设置ingore_db。
binlog_ingore_db=performance_schema,sys;#
#设置忽略的数据库，就是不需要同步的数据库。如果只设置了忽略的数据库，那么在忽略列表里面的不会同步，其他不在列表里的都将同步。
replicate_do_table="blogdb.user"; 
 #设置需要复制表. （多数据库使用逗号隔开或重复设置多行同样的参数），设置了这个后说明只同步数据库的某个表。而不是整个数据库
relicate_ingore_table="blogdb.uw";
#设置需要忽略的表。
slave-skip-errors=all
#跳过所有错误，如果不设置一般主库如果执行了错误的sql主从关系就会掉，从库遇到这个错误，需要在从库确认错误才能继续同步。也可以按照下面的跳过指定类型的错误。
slave-skip-errors=1007,1008,1032,1050,1051,1054,1060,1061,1062,1064,1049
#跳过指定类型的错误，2个选一个即可
binlog_format = mixed
#binlog日志文件的个，可以用来防止主键重复。有3种格式，这种就是前2种的自动选择方式。
sync_binlog=1
#每次同步都提交数据库到磁盘上，这样会保证数据的安全性，即使突然down也还会有记录。但是损失的性能非常大，在高并发数据实时要求高的不要设置。要不然会影响性能。
relay-log=relay-log1
#定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录，文件名为host_name-relay-bin.nnnnnn
relay-log-index=relay-log.index
relay_log_recovery=1
#当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。

character-set-server=utf8
#设置默认字符集
query_cache_size=128M
query_cache_type=1
#设置查询缓存的大小，这参数在后期的mysql8中将不再被支持。
#下面2个参数是主主要用到的。主从可以不用设置
auto-increment-increment=2
#增长量，一般都设置为2
auto-increment-offset=1
#  分别配置为1和2。这是序号，第一台从1开始，第二台就是2，以此类推!这样效果就是：master的数据id是1,3,5,7..., slave的数据id是2,4,6,8....这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。（针对的是有自增长属性的字段)
ssl
ssl-ca=/data/mysql/ca.pem
ssl-cert=/data/mysql/server-cert.pem
ssl-key=/data/mysql/server-key.pem
#ssl相关设置，一般5.7及以后的版本在mysql的数据目录都会有默认的证书，开启后基线扫描会少点不合格项目。

二、主从同步实例

1.先设置主库（master）的my.cnf
vim /etc/my.cnf
default-authentication-plugin=mysql_native_password  
#指定默认的密码认证插件，8.0的默认的密码插件可能会与客户端和应用有些兼容性问题。如果不改这里，那么新建账户就要加上with mysql_native
#所以这里改一下。如果不改的话主从同步可能会导致连接不上，也可能导致某些客户端也连接不上。
#5.7的可以不用改。
server-id=240
log-bin=mysql-bin
replicate-do-db=blogdb
replicate-ignore-db=sys,performance_schema
slave-skip-errors=all  #这个如果只是主从，主库可以不设置。
binlog_format = mixed
ssl
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem
配置完毕后重启mysql或者启动mysql。
systemctl restart mysqld
重启mysql
systemctl start mysqld
启动mysql，个只需要运行其中一个即可。

2.从库的设置

vim /etc/my.cnf
default-authentication-plugin=mysql_native_password
server-id=241
log-bin=mysql-bin
replicate-do-db=blogdb  
#如果主库设置了过滤规则，从库的这2个可以不做。只需要设置一个serverid和log-bin就行了。
replicate-ignore-db=sys,performance_schema
slave-skip-errors=all 
#跳过所有错误，建议把这个设置加上，否则一旦有sql错误，不确认的话数据不会继续同步。
ssl
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem
重启mysql

3.在主库上创建用户和数据库。

[root@dbmaster ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 
mysql> create database blogdb default character set='utf8';   
#创建blogdb数据库指定默认字符集
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> create user 'dbzc'@'%' identified by 'Test@1234'; 
 #创建dbzc同步用户，这里的%代表所有地方，如果是限定客户端就填写客户端的ip如
 #'dbzc'@'192.168.81.101'这样限定会安全些。如果授权成localhost或者127.0.0.1
 #就不能远程连接是不能通过客户端和应用连接，除非客户端和应用都在数据库主机上。请悉知。
Query OK, 0 rows affected (0.02 sec)
mysql> grant replication slave on *.* to 'dbzc'@'%';  #授权slave权限给这个用户
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;  #立即刷新权限
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;  #查看master的信息，记住post和file的值。
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1045 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4.从库做相关连接参数：

[root@dbslave ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database blogdb default character set='utf8';  
 #创建blogdb数据库指定默认字符集
Query OK, 1 row affected, 1 warning (0.03 sec)
#设置master的参数（如果mysql的默认端口改变就需要加上master_port=端口号）：
mysql> change master to 
    -> master_host='192.168.81.240',
    -> master_user='dbzc',
    -> master_password='Test@1234',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=1045;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
#启动slave
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
#查看slave状态。
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.81.240  #主库IP地址
                  Master_User: dbzc  #主从同步用户
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001 #主库的binlog需要，与主库的一模一样。
          Read_Master_Log_Pos: 1045  #pos增长量
               Relay_Log_File: localhost-relay-bin.000002  #
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000001  #
             Slave_IO_Running: Yes   #在从库IO和SQL都要是yes状态少一个主从就不正常。
            Slave_SQL_Running: Yes
              Replicate_Do_DB: blogdb  #同步的数据库
          Replicate_Ignore_DB: sys,performance_schema   #忽略的数据库
           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: 1045
              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 
        #主从延迟时间，单位是秒。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: 240
                  Master_UUID: bc6ebcf8-a208-11e9-b885-005056b009d2
             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

5。验证阶段

主库创建表和数据：
[root@testdb ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use blogdb;
Database changed
mysql> create table t(id int(10),name varchar(10));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t value('001','test1');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t value('002','test2'); 
Query OK, 1 row affected (0.01 sec)

从库查询数据:
[root@dbslave ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use blogdb;
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_blogdb |
+------------------+
| t                |
+------------------+
1 row in set (0.00 sec)
mysql> select * from t;
+------+-------+
| id   | name  |
+------+-------+
|    1 | test1 |
|    2 | test2 |
+------+-------+
2 rows in set (0.00 sec)

能够正常看到数据同步过来，主从已经建立完成。我们可以查看slave的pos增长量。
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.81.240
                  Master_User: dbzc
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2195  #已经和刚搭建时产生变化，说明已经产生数据变化
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 1472  #这个和master_pos一起变动。如果没变可能主从有问题

6.判断主从关系。

如果上面的 Slave_IO_Running:和Slave_SQL_Running:状态都是yes,Master_Log_File与主库相同:Read_Master_Log_Pos:在变化的话，并且Seconds_Behind_Master延迟正常。这个主从同步的状态应该是正常的。可以通过查看从库数据，或者du -sh统计mysql数据库目录与主库的大小来判断。如果主库有的数据库不同步给从库那么du -sh的方法就不适用了。
如果主从关系断掉，可以从mysqld日志或者binlog日志查看。

7.错误相关：

在8.0中有可能主从会报：
Last_IO_Error: error connecting to master 'dbzc@192.168.81.240:3306
#造成这个原因很多，有可能是密码错误，也有可能是主库防火墙没有关闭，或者3306端口没有监听，或者只监听了localhost的地址。
#这个也有可能是密码的插件没有更改，可以在创建用户的时候加入这样的语句：
create user 'dbzc'@'%' IDENTIFIED with mysql_native_password  by 'Test@1234';
#这里指定了密码认证模式为mysql_native_password，然后在进行主从的授权，记得flush刷新权限。

如果指定了上面的密码认证还是不行那就用sha256_password进行认证试试
create user 'dbzc'@'%' IDENTIFIED with sha256_password  by 'Test@1234';
然后授权slave，刷新权限，从库进行连接。
主从同步在任何时候只能主库写入数据，从库读取数据。如果从库写入了数据可能会导致主从关系断掉，或者数据量不正常，特别是在那些有自增字段的表中，这些都会给日常维护带来麻烦。所以在使用主从的时候，要知道为什么使用主从，目的是什么。主从的最基本使用方法和使用需要注意的事项。如果这些不知道盲目的使用2个库，那样跟单机跑本质上没啥区别，只是名义上的主从。

本文由 xiaobo 创作，采用 知识共享署名 3.0，可自由转载、引用，但需署名作者且注明文章出处。

添加新评论