前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >mysql 复制

mysql 复制

作者头像
魔王卷子
发布2019-06-02 13:09:45
2.4K0
发布2019-06-02 13:09:45
举报
文章被收录于专栏:魔王卷子的专栏

概述

MySQL的复制功能是构建基于MySQL的大规模、高性能应用的基础。复制功能不仅有利于构建高性能的应用,同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。

本文主要讲述复制如何工作,基本的复制如何搭建,复制的相关配置和优化复制服务器。

复制的工作原理:

简单来说,复制分为下面三个步骤:

  1. 在主库上把数据更改记录到二进制日志中(这些记录被称为二进制日志事件)。
  2. 备库将主库上的日志复制到自己的中继日志(relay log)中。
  3. 备库读取中继日志中的事件,将其重放到备库数据之上。

配置复制

为MySQL服务器配置复制非常简单。最基本的场景是新安装的主库和备库,总的来说分为以下几步:

  1. 在每台服务器上创建复制账号。
  2. 配置主库和备库。
  3. 通知备库连接到主库并从主库复制数据。

创建复制账号

MySQL 会赋予一个特殊的权限给复制线程。在备库运行的I/O线程会建立一个到主库的TCP/IP连接。这意味着必须在主库创建一个用户,并赋予一个合适的权限。

通过如下语句创建用户账号:

代码语言:javascript
复制
mysql> grant replication slave, replication client on *.* to repl@'10.55.160.%' identified by 'Pw12345.';
Query OK, 0 rows affected, 1 warning (0.00 sec)

请注意我们将这个账户限制在本地网络。因为这是一个特权账号。

复制账户只需要有主库上的 replication slave 权限,并不需要每一端都赋予 replication client 权限,为啥两端都要赋予呢:

  • 用来监控和管理复制的账号需要 replication client 权限,并且针对两个目的使用同一个账号更加容易。
  • 如果在主库上建立了账号,然后从主库将数据克隆到备库上时,备库也就设置了 ———— 变成主库所需要的角色,方便后续有需要进行转换角色。

配置文件

在主库的 my.cnf 文件中增加或者修改如下内容:

代码语言:javascript
复制
log_bin=mysql-bin
server_id=10

必须明确的指定一个唯一的服务器ID,默认服务器ID为1,使用默认值可能会导致和其它服务器的ID产生冲突,所以我们选择10来作为ID,一般的做法时使用服务器的末8位,但要保证它是不变且唯一的。最好选择一些有意义的约定并遵循。

如果之前没有在MySQL的配置中指定log_bin选项,需要重新启动MySQL。为了确认二进制日志文件是否已经创建,我们使用以下命令进行查看:

代码语言:javascript
复制
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

需要在备库上的my.cnf文件中增加类似的配置,并且同样需要重启服务器:

代码语言:javascript
复制
log_bin=mysql-bin
server_id=11
relay_log=/var/lib/mysql/mysql-relay-bin
log_slave_updates=1
read_only=1

从技术上来说,这些选项都不是必须的。其中一些选项只是显式的列出了默认值。事实上只有server_id时必需的。

log_bin:默认情况下,它是根据机器名来命名的,但是如果机器名变化了可能会出现问题,为了简便起见,我们在主库和备库设置了同样的值。 relay_log:指定中继日志的位置和命名。 slave_updates:允许备库将其重放的事件也记录到自身的二进制日志中。 read_only:该选项会阻止没有任何特权权限的线程修改数据。所以最好不要给用户超出需要的权限。但是并不是很实用,特别是那些需要在备库建表的应用。

启动复制

下面我们告诉备库如何连接到主库并重放其二进制日志。这一步不要通过my.cnf来配置,而是使用 change master to 语句。因为该语句完全替代了my.cnf中相应的配置而且允许以后指向别的主库的时候无需重启备库。

代码语言:javascript
复制
mysql> change master to master_host='10.55.160.91', master_user='repl', master_password='Pw12345.', master_log_file='mysql-bin.000001', master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

master_log_pos参数倍设置成0,因为要从日志的开头读起。我们可以通过show slave status语句来检查复制是否正确执行了。

代码语言:javascript
复制
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.55.160.91
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             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: 4
              Relay_Log_Space: 154
              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: 0
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Slave_IO_StateSlave_IO_RunningSlave_SQL_Running这三列显式当前备库尚未运行。

运行下面的命令进行复制:

代码语言:javascript
复制
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

执行该命令没有显示错误,我们再用show slave status命令检查一下:

代码语言:javascript
复制
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.55.160.91
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            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: 4
              Relay_Log_Space: 154
              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: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/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: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 161114 15:28:07
     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)

看到这里报出一个错误:Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.,其实这是因为我是用的虚拟机,然后直接进行复制的,倒是UUID相同,解决办法就是删除auto.cnf就好了:

代码语言:javascript
复制
[root@localhost ~]# service mysqld stop
Redirecting to /bin/systemctl stop  mysqld.service
[root@localhost ~]# \rm /var/lib/mysql/auto.cnf 
[root@localhost ~]# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service

然后我们继续上面的操作:

代码语言:javascript
复制
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.55.160.91
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 472
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 685
        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: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 472
              Relay_Log_Space: 892
              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: 10
                  Master_UUID: a901199b-aa23-11e6-862e-000c2943e6a3
             Master_Info_File: /var/lib/mysql/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: 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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

这样就正常了。Slave_IO_State 显示正在等待从主库传递过来的事件,这意味着I/O线程已经读取了主库所有的事件。

到此,一个全新的复制就搞定了。

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2017-11-172,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 概述
  • 复制的工作原理:
  • 配置复制
    • 创建复制账号
      • 配置文件
        • 启动复制
        相关产品与服务
        云数据库 SQL Server
        腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
        领券
        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档