mysql主从同步

贪玩的小金魚

已于 2024-05-14 09:17:41 修改

阅读量308

点赞数 8

文章标签： mysql 数据库

于 2024-05-13 20:48:48 首次发布

本文链接：https://blog.csdn.net/qq_41710802/article/details/138784603

版权

mysql实现主从同步，需开启binlog日志。
binlog日志开启方式

1、配置文件

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
[mysqld]
log-bin=E:/mysql/mysql-5.7.28-winx64/data/mysql-bin/mysql-bin 	#指定文件名和路径，这里是绝对路径
binlog-format=Row   #文件类型
#设置binlog清理时间
expire_logs_days = 14
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m
#server_id 主从数据库不能相同
server_id=106
# 设置mysql的安装目录
basedir= E:/mysql/mysql-5.7.28-winx64
# 设置mysql数据库的数据的存放目录
datadir= E:/mysql/mysql-5.7.28-winx64/data
# 允许最大连接数
max_connections=20000
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 最大允许的数据包大小
max_allowed_packet=200M
#大小写不敏感
lower_case_table_names=2

此处配置时完成遇到的坑
（原因不明）如果在winserver 2019下修改配置文件会导致mysql服务无法启动。
	解决方法通过本地电脑修改后，将my.ini配置文件直接复制覆盖再运行。

2、配置完成启动后，就要通过从服务器连接主服务

数据库操作命令

1）首先连接主数据库 mysql -hlocalhost -uroot -p123456
2）查询主数据库 ，记录file

 mysql>show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      308 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)

3)配置从数据库连接

   mysql>change master to master_host='192.168.145.222',master_user='root',master_password='123456',
         master_log_file='mysql-bin.000001',master_log_pos=308;   //注意不要断开，308数字前后无单引号。
   Mysql>start slave;    //启动从服务器复制功能

4)查询从数据库运行状态。

 mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 110.41.47.15
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 70333047
               Relay_Log_File: WIN-RQHQ79863LQ-relay-bin.000009
                Relay_Log_Pos: 694
        Relay_Master_Log_File: mysql-bin.000001
        #这两个参数YES代表运行成功
             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: 70333047
              Relay_Log_Space: 2573
              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: 106
                  Master_UUID: 9455ea88-bf5e-11ee-9c14-fa163e419979
             Master_Info_File: D:\mysql\mysql-5.7.28-winx64\data\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:

3、配置可能遇到的坑

通常我们都是将正式环境的copy过来进行配置从数据库，当我们启动slave时可能会出现  Slave_IO_Running: NO
可能由于复制过来的主从两个数据库 UUID相同，可以使用函数uuid();重新生成，然后复制到DATA文件下 auto.cnf文件中。

#可能由于复制过来的主从两个数据库 UUID相同， 
Slave_IO_Running: NO
mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 48d16720-1123-11ef-ad7e-6c3c8c747531 |
+--------------------------------------+
1 row in set (0.01 sec)

#文件内容，将UUID替换
[auto]
server-uuid=9455ea88-bf5e-11ee-9c14-fa163e419979

注意：从数据库开启后不能直接对从数据库进行写入操作，可能造成同步失败，表现为 Slave_SQL_Running: No

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 110.41.47.15
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 70330803
               Relay_Log_File: WIN-RQHQ79863LQ-relay-bin.000007
                Relay_Log_Pos: 68877181
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1032
                   Last_Error: Could not execute Update_rows event on table hxrdp.hxrdpsystem_tb_user; Can't find record in 'hxrdpsystem_tb_user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 69231846
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 69231216
              Relay_Log_Space: 69977525
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table hxrdp.hxrdpsystem_tb_user; Can't find record in 'hxrdpsystem_tb_user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 69231846

可以通过执行以下命令修复

mysql> stop slave ;
Query OK, 0 rows affected (0.01 sec)

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)