windows下实现mysql的主主(主-从)复制

灰太狼 2022-12-17 13:58 147阅读 0赞

*  mysql 服务器允许将一个服务器设置为主服务器，另外设置多个从数据库服务器允许将来自一个mysql数据库服务器的数据复制到一个或多个mysql数据库的服务器上
 *  mysql主从是异步复制的过程
 *  底层是什么东西在支撑这主从复制？

答： master 开启bin-log功能，日志文件用于记录数据库的读写增删

### 在主从配置中一共需要三个线程： ###

*  master： IO线程：

主服务器的上面的任何修改都会通过自己的IO 线程保存在二进制日志里面 bin-log

*  slave 开启： IO线程：

从服务器上也会开启一个IO Thread 通过配置好的用户名和密码，连接到主服务器上面请求读取二进制日志，然后把读取到的内容存在本地的一个Realy log (中继日志) 里面

*  slave开启：SQL线程：

从服务器上面同时开启一个sql Theard 定时检查Realy log 如果有发现有更新立即把更新的内容在本机的数据库上面执行一遍

### 三个线程的工作原理： ###

slave开启 IO线程 和 sql 线程 ，slave 负责通过IO线程连接master 并且请求某个bin-log position之后的内容，master 服务器收到slave IO线程发来的日志的请求信息，io线程将bin-log内容和position返回给slave IO线程

slave服务器收到bin-log日志内容，将bin-log日志内容写入relay-log中继日志，创建一个master.info的文件，该文件记录了master ip 用户名 密码 master bin-log名称，bin-log position。

slave端开启SQL线程，实时监控relay-log日志内容是否有更新，解析文件中的SQL语句，在slave数据库中去执行。

### 上图： ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwNjMxMDYz_size_16_color_FFFFFF_t_70][]

### 下面我们来看一下如何配置，在配置之前我们要保证mysql是安装在两个服务器上的， ###

**这里我用的两台服务器分别是**

master: 10.55.185.80

slave: 10.55.185.81

### 第一步： ###

配置mysql文件 my.ini 解释：

配置说明：
    #[必须]服务器唯一ID，每台服务器需不同
    server-id = 1
    #[必须]启用二进制文件
    log-bin = /home/mysql/mysql-bin
    #[不是必须]二进制文件启用混合模式
    binlog_format = mixed
    #[不是必须]二进制文件过期时间，单位是天
    expire-logs-days = 14
    #[不是必须]当每进行1次事务提交之后，MySQL将进行一次磁盘同步指令来将binlog_cache中的数据强制写入磁盘
    sync-binlog = 1
    #[不是必须]只将对应的数据库变动写入二进制文件。如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
    binlog-do-db = test,androidpnserver
    #[必须]不需要记录二进制日志的数据库。如果有多个数据库可用逗号分隔，或者使用多个binlog-do-db选项。一般为了保证主主同步不冲突，会忽略mysql数据库。
    binlog-ignore-db = mysql,information_schema,performance_schema
    #[必须]做主主备份的时候，因为每台数据库服务器都可能在同一个表中插入数据，如果表有一个自动增长的主键，那么就会在多服务器上出现主键冲突。
    #解决这个问题的办法就是让每个数据库的自增主键不连续。上面两项说的是，假设需要将来可能需要10台服务器做备份，将auto-increment-increment设为10。而auto-increment-offset=1表示这台服务器的序号。从1开始，不超过auto-increment-increment。
    auto-increment-increment = 10
    auto-increment-offset = 1

**主服务器的mysql配置文件内容：**

[mysqld]
    server-id=1
    log-bin = mysql-bin
    binlog-do-db = bd-ecxel
    binlog-ignore-db = mysql,information_schema,performance_schema
    auto-increment-increment = 10
    auto-increment-offset = 1

**从服务器的mysql配置文件内容：**

[mysqld]
    server-id=2
    log-bin = mysql-bin
    binlog-do-db = bd-ecxel
    binlog-ignore-db = mysql,information_schema,performance_schema
    auto-increment-increment = 10
    auto-increment-offset = 2

**注： 这里的server-id 要确保唯一**

### 第二步： ###

首先将mysql 重启：

#停止mysql服务
    net stop mysql
    #开启mysql服务
    net start mysql
    进入mysql
    mysql -u root -p

注意： 在数据库主从配置之前要确保主从的数据库暂时是同步的，可以先把主服务器的数据库开锁，禁止对数据库做增删改操作

### 第三步： ###

首先刷新，停止线程
    mysql>stop slave;
    
    mysql>reset slave;
    刷新mysql的系统权限相关表
    mysql>flush privileges;

###  第四步： ###

**在主服务器上创建同步账号：（如果做的是主-从复制那么就只需要在主服务器上进行创建同步账号**

**如果做的是主-主复制，那么主从都需要创建同步账号**）

grant replication slave on *.* to '<userName>'@'<hostIp>' identified by '<passWord>';`
    
    # userName     用户名，默认root
    # passWord     用户密码（均为数据库账户，密码）
    # hostIp          需要同步的主机IP，可以写%，表示全部

实例：

grant replication slave on *.* to 'root'@'%' identified by '123456';

###  第五步： ###

**在主服务器上查看master状态，记录二进制文件名**  ，  **注意记住 File 和 Position 在从机上面会用到**

### 第六步： ###

**设置从库同步**，注意该处的同步点，就是主库show master status信息里的| File| Position两项

（如果是主-从那么只需要在从机上进行同步，如果是主主那么都需要执行这步）

mysql> CHANGE MASTER TO
        ->     MASTER_HOST='10.55.185.80',
        ->     MASTER_USER='root',
        ->     MASTER_PASSWORD='123456',
        ->     MASTER_LOG_FILE='mysql-bin.000003',
        ->     MASTER_LOG_POS=73;

**从机启动slave 同步线程**

mysql>stop slave;
    mysql>start slave;

**slave 状态：**

mysql> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 10.55.185.80
                      Master_User: root
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000003
              Read_Master_Log_Pos: 11662
                   Relay_Log_File: mysqld-relay-bin.000022
                    Relay_Log_Pos: 11765
            Relay_Master_Log_File: mysql-bin.000013
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
            ...

![2020102322382154.png][]

这个就是从机的两个线程 如果都为yes那么就是成功！

补充： 如果在查看状态的时候有错误，那么就可以根据错误码来检测错误的原因，

### 一般错误的解决方式有一下两种： ###

第一种：**忽略错误后，继续同步**

stop slave;
    #表示跳过一步错误，后面的数字可变
    set global sql_slave_skip_counter =1;
    start slave;
    之后再用mysql> show slave status\G 查看：
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    ok，现在主从同步状态正常了。

第二种：**在配置文件中指定跳过错误代码，继续同步**

主键冲突、表已存在等错误代码如1062,1032,1060等，可以在mysql主配置文件指定略过此类异常并继续下条sql同步，这样也可以避免很多主从同步的异常中断
    
    [mysqld]
    
    slave-skip-errors = 1062,1032,1060
    
    重新启动mysql
    
    service mysqld restart
    
    之后再用mysql> show slave status\G 查看：
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwNjMxMDYz_size_16_color_FFFFFF_t_70]: /images/20221122/25bdf8eed01a4d459452250d69641af0.png
[2020102322382154.png]: /images/20221122/23d1452090d048adb9529313a6343898.png