MySQL-5.6主从复制搭建

原创于 2021-05-26 10:53:14 发布 · 434 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

MySQL 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了如何搭建MySQL 5.6的主从复制，包括主库的配置、授权、查看binlog信息，以及从库的配置、主从复制设置、开启线程和检查主从状态等步骤。

1.主库操作

1）配置主库

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin

2）授权一个主从用户

mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123';

#grant replication slave 是一个全局授权，不能指定单个库授权，如果想同步单个库使用过滤复制

3）查看主库的binlog信息

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      326 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.从库操作

1）配置从库

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2

2）配置主从复制

change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=326;

3）开启IO线程和sql线程

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

4）查看主从状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 326
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes