mysql5.6主从复制搭建

最新推荐文章于 2021-02-18 09:52:49 发布

YaoSirComeOn

最新推荐文章于 2021-02-18 09:52:49 发布

阅读量218

点赞数

分类专栏：服务器数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/xxx11144/article/details/78430741

版权

服务器数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1、环境说明：
主服务器系统：centos
从服务器系统：ubuntu
数据库：mysql5.6.35
2、搭建流程：
1）创建连接master服务器的账号

mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* 
TO username@’slaveServerIP’ 
IDENTIFIED BY ‘password’;

2）同步主服务器和从服务器数据
这里使用的master服务器和slave服务器都是刚初始化的，所有都是默认数据，这里就不做同步操作了，主从分布前提要保证主从服务器数据的一致性。

3）修改master服务器配置文件

[client]  
port            = 3307  
socket          = /tmp/mysql3307.sock  

[mysql]  
no-auto-rehash  

[mysqld]  
user    = mysql  
port    = 3307
socket  = /tmp/mysql3307.sock
basedir = /data/application/mysql
datadir = /data/application/mysql-multi-instance/mysql3307
server-id = 1   #主服务器A的ID值
log-bin = mysql-bin     #二进制变更日志

[mysqldump]  
quick  
max_allowed_packet = 2M  

[mysqld_safe]  
log-error=/data/application/mysql-multi-instance/mysql3307/mysql_3307.err  
pid-file=/data/application/mysql-multi-instance/mysql3307/mysqld.pid

4）修改slave服务器配置文件

[client]  
port            = 3306  
socket          = /tmp/mysql3306.sock  

[mysql]  
no-auto-rehash  

[mysqld]  
user    = mysql  
port    = 3306
socket  = /tmp/mysql3306.sock
basedir = /data/application/mysql
datadir = /data/application/mysqlData

##################配置从服务器##################
log_bin = mysql-bin         #二进制变更日志
server_id = 2               #mysql服务器的唯一标识
relay_log = mysql-relay-bin     #配置中继日志
log_slave_updates = 1           #用于查看slave的数据是否改变
read_only = 1           #防止改变数据
##################配置从服务器##################

[mysqldump]  
quick  
max_allowed_packet = 2M  

[mysqld_safe]  
log-error=/data/application/mysqlData/mysql_3306.err
pid-file=/data/application/mysqlData/mysqld.pid

5）重启master服务器，连接客户端，执行SHOW MASTER STATUS

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      382 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

6）启动slave服务器
让slave连接master，并开始重做master二进制日志中的事件。你不应该用配置文件进行该操作，而应该使用CHANGE MASTER TO语句，该语句可以完全取代对配置文件的修改，而且它可以为slave指定不同的master，而不需要停止服务器。
执行下面sql语句

mysql> CHANGE MASTER TO MASTER_HOST='masterServerIP',
    -> MASTER_USER='username',
    -> MASTER_PORT = 3306,
    -> MASTER_PASSWORD='password',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=0;

再执行下面sql语句

mysql> SHOW SLAVE STATUS\G;

出现一下内容

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 139.224.194.210
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 382
               Relay_Log_File: mysql-relay-bin.000008
                Relay_Log_Pos: 545
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: no
            Slave_SQL_Running: no
              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: 382
              Relay_Log_Space: 881
              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: NULL
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: c1b48803-bc9c-11e7-a619-00163e0e1f85
             Master_Info_File: /data/application/mysqlData/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.00 sec)

Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No可以看出还没有开启复制，执行以下sql语句

mysql> start slave;

再次执行SHOW SLAVE STATUS\G出现以下内容

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 139.224.194.210
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 382
               Relay_Log_File: mysql-relay-bin.000008
                Relay_Log_Pos: 545
        Relay_Master_Log_File: mysql-bin.000003
             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: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 382
              Relay_Log_Space: 881
              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: c1b48803-bc9c-11e7-a619-00163e0e1f85
             Master_Info_File: /data/application/mysqlData/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.00 sec)

可以看出Slave_IO_Running: Yes，Slave_SQL_Running: Yes并且Seconds_Behind_Master不再为空，主从复制开启。

7）测试主从复制
可以在master服务器上创建数据库和表并且插入数据，可以在slave服务器也看到相同的记录。

至此记录完毕。

YaoSirComeOn

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql5.6主从复制搭建

1、环境说明：主服务器系统：centos 从服务器系统：ubuntu 数据库：mysql5.6.35 2、搭建流程： 1）创建连接master服务器的账号mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO username@’slaveServerIP’ IDENTIFIED BY ‘password’;2）同步主服务器和从服
复制链接

扫一扫