mysql主从数据库配置

最新推荐文章于 2024-10-01 14:22:25 发布

Mr_LiYYD

最新推荐文章于 2024-10-01 14:22:25 发布

阅读量372

点赞数

文章标签： mysql big data 数据库

本文链接：https://blog.csdn.net/mr_liyyang/article/details/120842494

版权

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: