MySQL主从复制：实践

最新推荐文章于 2024-02-19 16:47:31 发布

勤天

最新推荐文章于 2024-02-19 16:47:31 发布

阅读量923

点赞数

分类专栏： MySQL 文章标签： mysql 主从复制

本文链接：https://blog.csdn.net/demored/article/details/123329560

版权

MySQL 专栏收录该内容

41 篇文章 6 订阅

订阅专栏

五、查看master和slave的线程状态

4、通过relay_log_purge 选项来控制

5、通过relay_log_space_limit选项设置

6、主服务器的日志格式(binlog-format) 用哪种好?

七、复制总结

MySQL开始复制是很简单的过程，不过，根据特定的应用场景，都会在基本的步骤上有一些变化。最简单的场景就是一个新安装的master和slave，从高层来看，整个过程如下：

(1) 在主服务器上创建一个复制帐号；

(2) 配置master和slave；

(3) Slave连接master开始复制。

一、在master上创建复制帐号

在主库上操作，需要将数据存储到二进制日志中的服务器，然后每个slave都需要master开设的账号连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。

用户名的密码都会存储在文本文件master.info中。假如，你想创建repl用户，如下：

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY '123456';
flush privileges;

复制事实上只需要有主库上的replication slave 权限，slave并不一定需要都有replication client 权限，那为什我们要把这两种权限给主/备库都赋予呢？

1）用来监控和管理复制的账号需要replication client 权限，并且针对这两种目的使用同一个账号更加工容易

2）如果在主库上建立了账号，然后从主库将数据克隆到备库上时，备库也就设置好了变成主库所需要的配置，这样后续有需要可以方便地交换备库的角色

二、配置master，开启binlog

在my.cnf 中配置，下面的配置slave也是一样

（1）配置二进制日志

（2）配置server-id 通常默认的服务器ID 为 1，但是会导致和其他服务器ID冲突，一种通用的做法是使用服务器IP地址的末8位，但要保证它是不变而且唯一

[mysqld]
log-bin=mysql-bin
server-id=10

# 需要同步的日志的数据库
binlog-do-db=ty

#不同步的二进制数据库，如果不设置可以将其注释掉
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=personalsite
binlog-ignore-db=test

（3）重启master，运行SHOW MASTER STATUS，输出如下：

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

三、配置slave

Slave的配置与master类似，你同样需要重启slave的MySQL。如下：

[mysqld]
server_id         = 2
log-bin           = mysql-bin #同时可以指定位置
relay_log         = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only         = 1

备注：relay_log的目录可以自定义，要保证有权限访问，否则会出现如下错误：

 File '/var/lib/mysql/mysql-relay-bin.index' not found (Errcode: 13 - Permission denied)
 chmod -R 777 /var/lib/mysql/

说明：

（1）server_id是必须的，而且唯一。slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置bin_log，这里我们开启了二进制日志。

（2）relay_log 指定中继日志的位置和命名（默认名称为hostname，但是，如果hostname改变则会出现问题）

（3）log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)。

这样会给备库增加额外的工作，这样也有必要，有时候有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。所以，尽量使用

（4）read_only，它防止改变数据(除了特殊的线程)。但是，read_only并不是很实用，特别是那些需要在slave上创建表的应用。

备注：不要在配置文件my.cnf 中设置 master_port 和master_host 这些选项，这是老的配置方式，已经被废除，它只会导致问题，不会有任何好处

四、启动slave

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

1、在slave服务器上操作

CHANGE MASTER TO MASTER_HOST='server1',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;

1）master_log_pos 参数被设置为0，因为要才从日志的开头读起。这是对于master之前都是全新的，没有进行二进制文件存储操作

2）如果master之前已经配置过二进制文件，并且之前该slave也change过，后面reset slave ，此时要在master上show master status查看log position

3）如果之前master有配置过二进制文件，此时一个全新的slave配置到change到该master上，可以配置mysql-bin.000001 以及master_log_pos = 0,这时指的是将该master全部的数据都同步过来

4）配置好之后使用show slave status查看配置是否正确

2、开启slave

start slave ;

3、查看slave状态

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               #查看复制状态
               Slave_IO_State: Waiting for master to send event
               
                  Master_Host: 192.168.80.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000005
                Relay_Log_Pos: 266
        Relay_Master_Log_File: mysql-bin.000002
            
             #查看复制状态
             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
         ...................................
             Master_Server_Id: 131
                  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 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表示复制的状态

五、查看master和slave的线程状态

1、查看master线程的状态

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 1
   User: repl
   Host: 192.168.80.133:41636
     db: NULL
Command: Binlog Dump
   Time: 6021
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

2、查看slave线程状态

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 6042
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 6043
  State: Waiting for master to send event
   Info: NULL

六、推荐的复制参数配置

1、sync_binlog

在主库上关于二进制日志设置最重要的选项就是sync_binlog

sync_binlog = 1

如果开启该选项，mysql 每次在提交事务前会将二进制日志同步到磁盘上，保证在服务器奔溃时不会丢失事件。如果禁止该选项，服务器会少做一些工作，但二进制日志文件可能在服务器崩溃时损坏或丢失。在一个不需要作为主库的备库上，该选项带来了不必要的开销。

它只适用于二进制日志。而非中继日志（所以最好是在主库上设置该配置）

2、如果使用innodb 的推荐设置

innodb_flush_logs_at_trx_commit    #Flush every log write
innodb_support_xa = 1
innodb_safe_binlog

3、在备库上推荐如下设置

log_bin = /var/lib/mysql/mysql-bin  #从库的二进制日志，选择一个路径和名称
relay_log = /path/to/logs/relay-bin   #为中继日志指定绝对路径
skip_slave_start = 1
read_only = 1

具体解释：

（1）read_only可以阻止大部分用户更改非临时表，除了复制sql线程和其他拥有超级权限的用户之外，这也是要尽量避免给正常账号授予超级权限的原因之一

（2）通过设置relay_log 可以避免中继日志文件基于机器名来命名，防止之前提到的可能在主库发生的问题。指定绝对路径可以避免多个mysql版本中存在的bug ，这些bug可能会导致中继日志，在一个意料的位置创建.

（3）skip_salve_start 选项能都阻止备库在崩溃后自动启动复制，这可以给你一些机会来修复可能发生的问题。如果备库在崩溃后自动启动并且处于不一致的状态，这可能就会导致更多的损坏。

4、通过relay_log_purge 选项来控制

如果备库与主库的延迟很大，备库的I/O 线程可能会写很多中继日志文件，SQL线程在重放完一个中继日志的事件后会尽快将其删除（通过relay_log_purge 选项来控制）

5、通过relay_log_space_limit选项设置

如果延迟非常严重，I/O 线程可能会把整个磁盘撑满，解决办法是配置relay_log_space_limit 变量，如果所有中继日志的大小之和超过这个值，I/O 线程会停止，等待SQL线程释放磁盘空间

6、主服务器的日志格式(binlog-format) 用哪种好?

有 statement,row, mixed3种,其中mixed是指前2种的混合.

binlog-format=statement/row/mixed

具体事例：

insert into xxtable values (x,y,z)

影响1行,且为新增1行, 对于其他行没有影响，这个情况,用row格式,直接复制磁盘上1行的新增变化.

update xxtable set age=21 where name="sss";

这个情况,一般也只是影响1行. 用row也比较合适.

update xxtable set salary=salary+100;

这个语句带来的影响,是针对每一行的, 因此磁盘上很多row都发生了变化,这种就应该适合statment格式的日志.

2种日志,各有各的高效的地方,mysql提供了mixed类型. 可以根据语句的不同,而自动选择适合的日志格式

七、复制总结

1、一台主服务器和一台从服务器，当从服务器发生写之后，这时便发生了错误，主从关系便丢失，主服务器写/更新/删除数据，都不会影响从服务器。

2、当主从服务器，关系在监听时；主服务器上（增、删、改），都会影响从服务器的数据；

勤天

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL主从复制：实践

目录一、在master上创建复制帐号二、配置master，开启binlog三、配置slave四、启动slave1、在slave服务器上操作2、开启slave3、查看slave状态五、查看master和slave的线程状态1、查看master线程的状态2、查看slave线程状态六、推荐的复制参数配置1、sync_binlog2、如果使用innodb 的推荐设置3、在备库上推荐如下设置4、通过relay_log_purge 选项来控制5、通过re
复制链接

扫一扫