美文网首页
centos7 mysql 主从复制

centos7 mysql 主从复制

作者: 就叫basi | 来源:发表于2021-03-17 17:17 被阅读0次

1. 环境

  • 操作系统
           CentOS-7
  • MySQL
          mysql-5.7
  • 三台虚拟机
          192.168.33.13      master
          192.168.33.12      relay
          192.168.33.11      slave
    复制架构
          M-S-S(多级复制架构)

2.master(192.168.33.13)节点配置

  • 配置文件开启binlog

       未配置之前检查,log_bin是OFF状态

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.01 sec)
#这是我mysql安装的位置
[root@localhost mysql]& pwd  
/usr/local/mysql

       vim /etc/my.cnf文件

//增加下面三行,我让log-bin指向我的mysql安装位置
server-id=1
binlog-format=Row //使用此模式时,Mysql实际上在binlog中逐行记录数据的变更。Row比Statement更能保证从库的一致性(复制的是记录,而非单纯操作sql),日志量也可能会增大非常多,需要考虑到磁盘空间问题
log-bin=/usr/local/mysql/binlog-files/mysql-bin                             
expire_logs_days = 10  //日志保留时间

       重启mysql

[root@localhost mysql]& service mysqld restart

       再次检查,log_bin是ON状态

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               |
| log_bin_basename                | /usr/local/mysql/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
| sql_log_bin                     | ON                               |
+---------------------------------+----------------------------------+
6 rows in set (0.00 sec)

       并且/usr/local/mysql/binlog-files目录会生成以下文件


log-bin
  • 给relay创建使用的用户
mysql> CREATE USER 'relay'@'192.168.33.12' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'relay'@'192.168.33.12';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

注:
drop user 'repl'@'192.168.33.12'; //删除user用户语句
  • 查看master状态,获取binlog名字和position。记住file名字和position,后面会用到。
    这个时候master不要发生写操作,否则position和file可能会变化。
mysql> 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      761 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

3.relay(192.168.33.12)节点配置

        vim /etc/my.cnf文件

server-id=2
binlog-format=ROW
log-bin=/usr/local/mysql/binlog-files/mysql-bin
log-slave-updates //配置从库上的更新操作是否写二进制日志,目的是作为slave的主库,搭建一个链式的复制
expire_logs_days = 10  //日志保留时间

       重启mysql

[root@localhost mysql]& service mysqld restart

       开启主从同步。file和pos是从主节点获取的

mysql> change master to master_host='192.168.33.13', 
master_user='relay', 
master_password='123456', 
master_log_file='mysql-bin.000001',
master_log_pos=761;

       查看relay节点状态。注意,主从同步成功的标志:IO线程和SQL线程都是成功运行的:
       Slave_IO_Running: Yes
       Slave_SQL_Running: Yes

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

mysql> 
mysql> 
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.33.13
                  Master_User: relay
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 761
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             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: 
1 row in set (0.00 sec)
  • 给slave创建使用的用户
mysql> CREATE USER 'slave'@'192.168.33.11' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.33.11';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

4.slave(192.168.33.11)节点配置

        vim /etc/my.cnf文件

server-id=2
binlog-format=ROW
log-bin=/usr/local/mysql/mysql-bin 
expire_logs_days = 10  //日志保留时间

       重启mysql

[root@localhost mysql]& service mysqld restart

       开启主从同步。file和pos是从主节点获取的

mysql> change master to master_host='192.168.33.12', 
master_user='slave', 
master_password='123456', 
master_log_file='mysql-bin.000001',
master_log_pos=761;

       查看从节点状态。注意,主从同步成功的标志:IO线程和SQL线程都是成功运行的:
       Slave_IO_Running: Yes
       Slave_SQL_Running: Yes

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

mysql> 
mysql> 
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.33.11
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 761
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             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: 
1 row in set (0.00 sec)

       现在可以在master节点上面做任意对于库表的修改操作,slave会自动同步。
       不要直接操作slave。

       如果IO线程not running

stop slave;                                                      
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; //跳过一个错误
start slave;                                                      
show slave status\G  

相关文章

网友评论

      本文标题:centos7 mysql 主从复制

      本文链接:https://www.haomeiwen.com/subject/mflecltx.html