Mysql主从同步配置

奔跑的大象

已于 2022-10-19 15:16:06 修改

阅读量2.7k

点赞数

分类专栏： mysql 文章标签： mysql 数据库 java

于 2022-10-19 14:57:35 首次发布

本文链接：https://blog.csdn.net/zhaobangyu/article/details/127406538

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

注意事项1：主从配置首先要确保主从DB的数据一致，这样后面的主从同步才能顺利进行，如果主从DB一开始的DB结构或数据不一致，后面的主从同步就无法正常进行。

注意事项2：在进行主从同步配置时，应当停止与DB相关的所有业务，主库进行锁表操作，配置期间主库不能有任何结构或数据变化。

名词解释：

master 主库，线上业务运行使用的mysql

salve 从库，数据备份使用的mysql

主库配置

1、主库锁表，禁止写入新数据

flush tables with read lock;

2、配置ini文件

#增加如下配置

[mysqld]

# 节点ID,确保唯一
server_id = 1

# log config
log-bin = mysql-bin #开启mysql的binlog日志功能
sync_binlog = 1 #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7 #binlog过期清理时间
max_binlog_size = 100m #binlog每个日志文件大小
binlog_cache_size = 4m #binlog缓存大小
max_binlog_cache_size= 512m #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者复制这句话，写多行
auto-increment-offset = 1 # 自增值的偏移量
auto-increment-increment = 1 # 自增值的自增量
slave-skip-errors = all #跳过从库错误
log_bin_trust_function_creators = TRUE #若涉及及同步函数或者存储过程需要配置，否则主备会产生异常不能同步

3、重启Mysql服务

4、创建主库复制用户

#登录mysql
mysql -u root -P 3306 -p

#切换MySQL库

use mysql;

#创建用户

CREATE USER db_sync_user IDENTIFIED BY 'zxcv1314';

#@% 表示不限制连接的IP，可以更换为指定IP才能连接

grant replication slave on *.* to 'db_sync_user'@'%' identified by '^zxcv1314';

#刷新权限

FLUSH PRIVILEGES;

#查看用户列表

select host,user from user;

5、查询master状态

#查看master状态

show master status; #记录查询结果中的File、Position,配置从库时会用到

6、将需要同步的库进行备份

mysql备份与还原https://blog.csdn.net/zhaobangyu/article/details/127408586?csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22127408586%22%2C%22source%22%3A%22zhaobangyu%22%7D

从库配置

1、还原从库备份的文件

2、打开从库的ini配置文件

#增加如下配置

[mysqld]

server_id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

#需要同步的库
replicate-do-db=accounts

#需要同步的库
replicate-do-db=game

#需要同步的库
replicate-do-db=log

#需要同步的库
replicate-do-db=config

3、重启Mysql服务

4、配置连接

#登录mysql
mysql -u root -P 3306 -p

#切换MySQL库

use mysql;

#配置从DB连接主DB
CHANGE MASTER TO
MASTER_HOST = '主DB的IP',
MASTER_USER = 'db_sync_user',
MASTER_PASSWORD = 'zxcv1314',
MASTER_PORT = 3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=3172468,
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000;

#这两项配置的值：主库配置->5.查询master状态查询出来的值：

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=3172468,

5、启动从库同步

slave start; #有些版本执行这个命令可能会报错，无法识别，使用start slave;即可

5、查看同步状态

show slave status\G;

查询结果

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 主DB地址
                  Master_User: db_sync_user
                  Master_Port: 8306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 5615738
               Relay_Log_File: mysql-relay-bin.000778
                Relay_Log_Pos: 871267
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: accounts,game,log,config
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: mysql.%,test.%,information_schema.%
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 5615738
              Relay_Log_Space: 902709
              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: 1
                  Master_UUID: ae0a8ec4-6fc1-11e9-821a-4ccc6a4d7344
             Master_Info_File: D:\phpstudy_pro\Extensions\MySQL5.7.26\data_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: 60
                  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:
1 row in set (0.00 sec)

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

这两项结果为Yes表示配置成功，如果出现失败之类的，可以根据查询结果里的Slave_SQL_Running_State排查问题。

从库配置完成。

续-主库配置

1、登录主库，解锁表锁定

unlock tables;

2、测试同步

在同步的库中表增删改查，表数据增删改查，方法增删改查，存储过程增删改查都能正常同步。

常用命令

#启动服务
net stop serverName
#停止服务
net stop serverName

#查看主DB状态（数据发生不同步时，查看数据再次同步即可）
show master status;
#查看从DB状态
show slave status;

#启动slave进程（从DB）
slave start 或 start slave
#停止slave进程（从DB）
slave stop; 停止salve进程（从DB）

#删除用户
DROP USER 'jack'@'localhost';

#锁表禁止写入新数据
flush tables with read lock;
#解锁表锁定
unlock tables;