mysql主从数据库配置

MySQL主从数据库配置

1. 数据同步大致流程

1、主服务器(master)将变更事件(更新、删除、表结构改变等等)写入二进制日志(master log)。

2、从服务器(slave)的IO线程从主服务器(binlog dump线程)获取二进制日志，并在本地保存一份自己的二进制日志(relay log)。

3、从服务器的SQL线程读取本地日志(relay log)，并重演变更事件。

2. 主(master)数据库配置

• 修改mysql配置文件，默认配置文件路径为 /etc/my.cnf，在配置文件中追加一下配置

# 服务的唯一编号(主从数据库的编号不同即可)
server-id = 1
# 开启mysql binlog功能，并指定文件名
log-bin = local-bin
# binlog记录内容的方式，记录被操作的每一行(binlog的格式有三种：STATEMENT，ROW，MIXED。)
binlog_format = ROW
# 减少记录日志的内容，只记录受影响的列
binlog_row_image = minimal
# 指定需要复制的数据库名为sms_local
binlog-do-db = sms_local

• 重启mysql使配置生效（service mysqld restart）

# 查看主数据库的状态
mysql> show master status; //(一会配从数据库会使用到，File,Position字段)
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| local-bin.000001 |   103771 | sms_local    |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

• 在主数据库中创建主从数据库通讯使用账号

# 创建主从数据库通讯账号 (要指定密码验证规则mysql_native_password,mysql8中使用的ssl规则，不指定的话slave数据库连接master数据库需要额外配置加密插件)
mysql> create user 'mysqlbackup'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'Qeoten_1234';
# 把创建的账号设置为，主从数据库通讯的账号(*.*意思为所有表 %意思为所有id都可以连接master数据库)
mysql> grant replication slave on *.* to 'mysqlbackup'@'%';
# 刷新权限
flush privileges;

3. 从(slave)数据库配置

• 修改配置文件，追加以下配置

#主从数据库配置核心部分
# 设置同步的binary log二进制日志文件名前缀，默认是binlog
log-bin=local-bin
# 服务器唯一ID  主数据库和从数据库的server-id不能重复
server-id=2

###可选配置
# 需要主从复制的数据库 
# replicate-do-db=sms_local
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步） 
# binlog-ignore-db=mysql
# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存 
# binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主从复制时数据更加能够统一） 
# binlog_format=row
# 设置二进制日志自动删除/过期的天数，避免占用磁盘空间。默认值为0，表示不自动删除。 
# expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。 
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致 
# slave_skip_errors=1062
# relay_log配置中继日志，默认采用 主机名-relay-bin 的方式保存日志文件 
# relay_log=replicas-mysql-relay-bin
# log_slave_updates表示slave将复制事件写进自己的二进制日志
# log_slave_updates=1
# 防止改变数据(只读操作，除了特殊的线程)
# read_only=1 

• 重启数据库使配置文件生效（service mysqld restart）
• 设置连接master数据库的连接信息

# MASTER_LOG_FILE MASTER_LOG_POS 两个字段信息在master数据库执行show master status可以查到
mysql> change master to MASTER_HOST='192.168.1.254',MASTER_PORT=3306,MASTER_USER='mysqlbackup',MASTER_PASSWORD='Qeoten_1234',MASTER_LOG_FILE='local-bin.000001',MASTER_LOG_POS=142394;
# 开启从服务 //停止 stop slave;
start slave;

• 查看从数据库的连接状态

  mysql> show slave status\G; //Slave_IO_Running和Slave_SQL_Running 都为Yes表示主从数据库运行正常
  *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: 192.168.1.254
                    Master_User: mysqlbackup
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: local-bin.000001
            Read_Master_Log_Pos: 146744
                 Relay_Log_File: MicroWin10-0617-relay-bin.000002
                  Relay_Log_Pos: 4674
          Relay_Master_Log_File: local-bin.000001
               Slave_IO_Running: Yes  									// Yes为正常状态
              Slave_SQL_Running: Yes  									// 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: 146744
                Relay_Log_Space: 4893
                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									// IO日志失败的错误代码
                  Last_IO_Error:										// IO日志失败的错误信息
                 Last_SQL_Errno: 0
                 Last_SQL_Error:
    Replicate_Ignore_Server_Ids:
               Master_Server_Id: 1
                    Master_UUID: 9504c198-15cd-11ec-85d3-004238a2ffb2
               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:
  

4. 验证

• 在slave数据库同步master数据库的数据，同步完成后，修改master数据库的数据查看slave数据库的数据是否同步修改了，如果同步修改了则配置成功