首页 > 代码库 > 第五章:MySQL主从复制

第五章:MySQL主从复制


目录

1. 主从MySQL主从复制介绍... 1

2. MySQL主从复制的企业应用场景... 3

3. 实现MySQL主从读写分离的方案... 5

4. MySQL主从复制原理... 7

5. 主从复制实战... 8

5.1 MySQL主从配置... 8

5.2 MySQL数据库级联配置... 12

5.3 MySQL主从复制配置步骤小结... 15

5.4 主从配置表示成功后的关键参数说明... 15

5.5 MySQL主从复制配置问题汇总... 16

6. MySQL主从复制更多应用技巧... 17

 

1. 主从MySQL主从复制介绍

    MySQL的主从复制方案,其实就和文件及文件系统级别同步是类似的,都是数据的传输,只不过MySQL无需借助第三方工具,而是自带的同步复制功能,另外一点,MySQL的主从复制并不是磁盘上文件直接同步,而是逻辑的binlog日志同步到本地在应用执行的过程。

MySQL数据库支持单向、双向、链式级联等不同场景的复制,在复制过程中,一台服务器充当主服务器(Master),而一个或多个其它的服务器充当从服务器(Slave)。

复制可以单向:M==>S;可以双向:M<==>S;当然也可以多M环装同步等。

如果设置了链式级联复制,那么,从(slave)服务器本身除了充当从服务器外,也会同时充当其下面从服务器的主服务器,链式级联复制类似A==>B==>C==>D的复制形式。

下面是MySQL各种同步结构的逻辑图:

1. 单向主从复制逻辑图,此架构只能在Master端进行数据写入


技术分享

技术分享

 

2. 双向主主同步逻辑图,此架构可以在Master1端或Master2端进行数据写入

 

技术分享

3. 线性级联单向双主同步逻辑图,此架构只能在Master端进行数据写入

技术分享

4. 环装级联单向多主同步逻辑图,任意一个点都可以写入数据

技术分享

5. 环装级联单向多主多从同步逻辑图,次架构只能在任意一个master端进行数据写入

技术分享

6. MySQL官方同步架构图

技术分享

    在当前的生产环境中,MySQL主从复制都是异步的复制方式,既不是严格实时的数据同步。


2. MySQL主从复制的企业应用场景

MySQL主从复制集群功能使得MySQL数据库支持大规模高并发读写称为可能,同时有效的保护了物理服务器宕机场景的数据备份。


应用场景1:从服务器作为主服务器的实时数据备份

主从服务器架构的设置可以大大加强MySQL数据库架构的健壮性,例如:当主服务器出现问题时,我们可以人工或设置自动切换到从服务器继续提供服务,此时从服务器的数据与宕机时的数据库几乎是一致的。

这类似NFS存储数据通过inotify+rsync同步到备份的NFS服务器,只不过MySQL的复制方案是其自带的工具。

利用MySQL的复制功能进行数据备份时,在硬件故障、软件故障的场景下,该数据备份是有效的,但对于人为执行drop、delete等语句删除数据的情况,从库的备份功能就没用了,因为从服务器也会执行删除的语句。


应用场景2:主从服务器实现读写分离,从服务器实现负载均衡

主从服务器架构可通过(PHP、java等)或代理软件(mysql-proxy、Amoeba)实现对用户(客户端)的请求读写分离,揖让从服务器金聪处理用户的select查询请求,降低用户查询响应时间,以及同时读写在主服务器上带来的访问压力,对于更新的数据(例如:update、insert、delete语句),则仍然交给主服务器处理,确保主服务器和从服务器保持实时同步。

技术分享


应用场景3:把多个从服务器根据业务重要性进行拆分访问

可以把几个不同的从服务器,根号月公司的业务进行拆分,例如:在为外部用户提供查询服务的从服务器,在内部DBA用来数据备份的从服务器,还有为公司内部人员提供访问的后台、脚本、日志分析及供开发人员查询使用的从服务器,这样拆分除了减轻主服务器的压力外,还可以是数据库对外不用户浏览,内部用户业务处理及DBA人员的备份等互不影响。


3. 实现MySQL主从读写分离的方案

(1)通过程序实现读写分离(性能和效率最佳,推荐)

PHP和java程序都可以通过设置多个连接文件轻松地实现对数据库的读写分离,即当语句关键字为select时,就去连接读库的连接文件,若为update、insert、delete时,则连接写库的文件。

通过程序实现读写分离的缺点就是需要开发人员对程序进行改造,使其对下层不透明,但这种方式更容易开发和实现,适合互联网业务场景。

技术分享


2)通过开源的软件实现读写分离

MySQL-Proxy、Amoeba等代理软件也可以是实现读写分离功能,这些软件的稳定性和功能一般,不建议生产使用,绝大多数公司常用的还是在应用端发程序实现读写分离。


(3)大型门户独立开发DAL层综合软件

       百度、阿里等大型门户都有开发牛人,会花大力气开发适合自己业务的读写分离,负载均衡,监控报警,自动扩容,自动收缩等一系列功能的DAL层软件。

技术分享

4. MySQL主从复制原理

技术分享

简单描述MySQL Replication的复制原理过程

1. 在Slave服务器上执行start slave命令开启主从复制开关,开始进行主从复制。

2. 此时,slave服务器的I/O线程会通过在Master上已经授权的复制用户权限请求选择Master服务器,并   请求从指定binlog日志文件的指定位置(日志文件名和位置就是在哦诶之主从渎职服务时执行change   master命令指定的)之后,之后开始发送binlog日志内容。

3. Master服务器接收来自slave服务器的IO线程的请求后,其上负责复制的I/O线程会根据slave服务器   的io线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息,然后返回给     slave端的I/O线程,返回的信息中除了binlog日志内容外,还有在master服务器端记录的新的binlog   文件名称,以及在新的binlog中的下一个指定的跟新位置。

4. 当slave服务器的io线程获取带master服务器上io线程发送的日志内容,日志文件及位置点后,会将   binlog日志内容写到slave端自身的relay loy(即中继日志)的最末端,并将新的binlog日志文件名   和位置点记录到master-info文件中,以便下一次读取master端新的binlog日志是能够告诉master服   务端从新的binlog日志指定文件及位置开始请求新的binlog日志内容。

5.slave服务器端的SQL线程会实时监测本地relay log中的io线程新增加的日志内容。然后及时的把  relay.log文件中的内容解析成SQL语句,并且自身slave服务器上按解析sql语句的位置顺序执行应用  这些SQL语句,并在relay-log.info中记录当前应用中继日志文件名及位置点。


MySQL主从复制原理的重点小结:

  • 主从复制是异步的逻辑的SQL语句级的复制

  • 复制时,主库有一个线程,从库有两个线程,即I/O和SQL线程

  • 实现主从复制的前提条件时主库开启记录binlog功能

  • 作为复制的所有MySQL节点的sever id都不能相同

binlog文件只记录对数据有更改的SQL语句(来自主数据库内容的变更),不记录任何查询(如:select、show)语句。

5. 主从复制实战

实现主从复制条件

  • 开启binlog功能。

  • 主库建立同步账号。

  • 从库配置master.info(change master..)。

startslave复制开关。

5.1 主从配置

如图:

技术分享

技术分享

 

配置主从复制

测试环境:使用一台服务器,多实例,一个实例做主,另外的实例做从(多台服务器也一样)


1)主库操作


1. 先确认主库binlog功能是否开启和各个实例server-id实例的不同

[root@db01 3308]# egrep "log-bin|server-id" /data/{3306,3307}/my.cnf
/data/3306/my.cnf:log-bin= /data/3306/mysql-bin
/data/3306/my.cnf:server-id= 1
/data/3307/my.cnf:#log-bin= /data/3307/mysql-bin
/data/3307/my.cnf:server-id= 3


2. 主库授权同步的用户

[root@db01 /]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock  #<== 登录数据库
mysql> grant replication slave on *.* torep@‘172.16.1.%‘ identified by ‘oldboy123‘;
Query OK, 0 rowsaffected (0.00 sec)                 #<== 创建rep用户并授权
mysql> flush privileges;                            #<== 刷新数据库
Query OK, 0 rowsaffected (0.00 sec)
 
mysql> select user,host from mysql.user;            #<== 创建用户后查看确认
mysql> show grants for rep@‘172.16.1.%‘;            #<== 查看rep用户权限


3. 备份前对主数据库锁表(只读)

mysql> flush table with read lock;
Query OK, 0 rowsaffected (0.00 sec)


测试:单开一个窗口,创建一个数据库(这时会发现创建不了,因为表锁了)

mysql> create database oldgirl;


4. 查看全备与增量之间的临界点

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


5. 开始备份

创建备份目录

[root@db01 ~]# mkdir -p /server/backup/

全备

[root@db01~]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B -A --events|gzip >/server/backup/rep_bak_$(date+%F).sql.gz
[root@db01 ~]# ll -htr /server/backup/|tail -1
-rw-r--r-- 1 root root 149K Aug 31 23:57rep_bak_2016-08-31.sql.gz

6. 因为数据已经备份完,所以可以开放用户写入功能,此时会发现测试窗口的数据会写入

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)


(2)从库操作

1. 确保所有用户server-id不同(主库已经确认过)

[root@db01 3308]# egrep "log-bin|server-id" /data/{3306,3307}/my.cnf
/data/3306/my.cnf:log-bin= /data/3306/mysql-bin
/data/3306/my.cnf:server-id= 1
/data/3307/my.cnf:#log-bin= /data/3307/mysql-bin
/data/3307/my.cnf:server-id= 3


2. 把主库的全备导入到从库

[root@db01 ~]# gzip -d/server/backup/rep_bak_2016-08-31.sql.gz
[root@db01 ~]# ll /server/backup/rep_bak_2016-08-31.sql
-rw-r--r-- 1 root root 552788 Aug 31 23:57//server/backup/rep_bak_2016-08-31.sql
[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3307/mysql.sock </server/backup/rep_bak_2016-08-31.sql


3. 找位置点,开始配置masterinfo


[root@db01 3307]# mysql -uroot -poldboy123 -S/data/3307/mysql.sock
CHANGE MASTER TO                                           
MASTER_HOST=‘172.16.1.51‘,           #<== 主库IP
MASTER_PORT=3306,                    #<== 主库端口(如果配置一主多从可不写,默认)
MASTER_USER=‘rep‘,                   #<== 这里是主库上建立的用于复制的用户rep
MASTER_PASSWORD=‘oldboy123‘,         #<== rep用户密码
MASTER_LOG_FILE=‘mysql-bin.000016‘,  #<== 这里是showmaster status; 时看到的临界点binlog文件,注意,这里不能多空格
MASTER_LOG_POS=107;                  #<== 这是showmaster status; 查看二进制文件偏移量,注意,不能多空格

[root@db01 ~]# ll /data/3307/     
-rw-rw---- 1 mysql mysql   51 Sep 1 03:40 relay-log.info

参数如下:

CHANGE MASTER TO
MASTER_HOST=‘172.16.1.51‘,
MASTER_PORT=3306,
MASTER_USER=‘rep‘,
MASTER_PASSWORD=‘oldboy123‘,
MASTER_LOG_FILE=‘mysql-bin.000016‘,
MASTER_LOG_POS=107;


4. 开启同步开关

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


5. 查看同步状态,看到以下黄色部分是以下状态就算成功

mysql> show slave status\G
*************************** 1. row***************************
-----------------------省略部分内容------------         
       Relay_Master_Log_File: mysql-bin.000017
             Slave_IO_Running: Yes                #<== 看这IO线程yes和SQLyes就成功了
            Slave_SQL_Running: Yes
             Replicate_Do_DB:
-------------------------------------省略-----------
        Seconds_Behind_Master: 0                  #<== 主到从复制的延迟
1 row in set (0.00 sec)

或者使用命令查看:

mysql -S /data/3307/mysql.sock -e"show slave status\G"|egrep -i "_running|Behind "
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
Seconds_Behind_Master: 0


(3)简单测试是否成功

1. 在主库创建数据库,查看从库是否同步

[root@db01 3307]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock
mysql> show databases;
+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| mysql                 |
| performance_schema    |
+-----------------------+
8 rows in set (0.00 sec)

mysql> create database testdatabase;
Query OK, 1 row affected (0.00 sec)
 
mysql> show databases;             
+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| mysql                 |
| performance_schema    |
| testdatabase          |
+-----------------------+
9 rows in set (0.00 sec)

登录从库查看

[root@db01 3307]# mysql -uroot -poldboy123 -S/data/3307/mysql.sock
mysql> show databases;
+-------------------------------+
| Database              |
+-------------------------------+
| information_schema   |
| mysql                |
| performance_schema  |
| testdatabase         |
+-------------------------------+
9 rows in set (0.00 sec)


------------------成功!!!-------------------


5.2 MySQL数据库级联配置

技术分享

说明:在配置了MySQL一主一从或MySQL一主多从的情况下,在该基础上的从库下面在配置一台从库,构成线性级联单向双主同步。


环境:在以上5.1的配置上继续配置级联


以下在以上配置的3307从库上操作


1. 开启从库3309记录binlog日志功能,配置如下:

[root@db01 3307]# vim /data/3307/my.cnf
[mysqld]
log-slave-updates                      #<==在从库上配置binloa文件时,此行也是必须加的
log-bin = /data/3309/mysql-bin

重启生效

[root@db01 3307]# /data/3307/mysql restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL..


2. 全备3307从库实例,导入到3309

[root@db01 3307]# mysqldump -uroot -poldboy123 -S/data/3307/mysql.sock -B -A --master-data=http://www.mamicode.com/1 --events>/server/backup/3307.sql>


以下步骤在3309上面操作


3. 在3309上面配置master info文件

[root@db01 3309]# mysql -uroot -poldboy123 -S/data/3309/mysql.sock 
mysql> CHANGE MASTER TO
    ->MASTER_HOST=‘172.16.1.51‘,                     
    ->MASTER_PORT=3307,                                     
    ->MASTER_USER=‘rep‘,                             
    ->MASTER_PASSWORD=‘oldboy123‘;              
Query OK, 0 rows affected (0.01 sec)
说明:这里没有进行查看并指定binlog文件的位置点是因为备份时已经使用参数-x(锁表),和--master-data=http://www.mamicode.com/1>

参数如下:

CHANGEMASTER TO
MASTER_HOST=‘172.16.1.51‘,
MASTER_PORT=3307,
MASTER_USER=‘rep‘,
MASTER_PASSWORD=‘oldboy123‘;


4. 在3309上面开启slave开关

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

5. 查看状态

mysql> show slave status\G 
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
       Seconds_Behind_Master: 0
说明:以上输出部分内容省略,只要以上三行是该状态就表示正确(此三行是配置成功标志最关键的参数)

# 还可以在命令行外执行命令查看,如下:

[root@db01 3309]# mysql -uroot -poldboy123 -S/data/3309/mysql.sock  -e "showslave status\G"|egrep -i "_running|Seconds"
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
       Seconds_Behind_Master: 0


测试:

在主库3306里面写入数据,这时在3307库里面和3309库里面就会同时有啦,如下:

# 3306查看并创建数据库

[root@db01 3308]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock
mysql> show databases;    
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| mysql               |
| oldboy              |
| performance_schema  |
+---------------------+
4 rows in set (0.00 sec)
mysql> create database zyl;                         #<== 创建名为zyl的库
mysql> show databases;                              #<==在3306上面查看
+-------------------------------+
| Database            |
+-------------------------------+
| information_schema   |      
| mysql                    |
| oldboy              |
| performance_schema  |
| zyl                |
+-------------------------------+
5 rows in set (0.00 sec)[
[root@db01 3307]# mysql -uroot -poldboy123 -S/data/3307/mysql.sock
mysql> show databases;                               #<==在3307上面查看
+-------------------------------+
| Database            |
+-------------------------------+
| information_schema   |      
| mysql                     |
| oldboy              |
| performance_schema  |
| zyl                |
+-------------------------------+
5 rows in set (0.00 sec)
[root@db01 3307]# mysql -uroot -poldboy123 -S/data/3309/mysql.sock
mysql> show databases;                              #<==在3309上面查看
+---------------------+
| Database            |
+---------------------+
| information_schema  |      
| mysql               |
| oldboy              |
| performance_schema  |
| zyl                 |
+---------------------+
5 rows in set (0.00 sec)


报错原因:  1. 防火墙原因;

        2. 复制账号和密码不对。


5.3 MySQL主从复制配置步骤小结

 1. 一主一从环境为一台服务器(多实例),3306为主,3307为从,3309为级联。

2. 配置my.cnf文件,主库配置bin-logserver-id出本书,从库配置sever-id,该值个库不能一样,一般不开启从库的binlog功能,但是在5.2因为在3307从库下还有配置级联从库,所以需要开启。配置这些参数要重启才能生效。

3. 登录主库,增加从库连接主库同步的账户,例如:rep、并授权replication slave复制同步的权限。

4. 登录主库,整库锁表flus tablewith read lock(窗口关闭后即失效,超时参数设定的时间到了,锁表也会失效),然后show master status查看binlog的位置状态。

5 . 离开窗口,在linux命令行备份导出原有的数据库数据,并拷贝到从库所在的服务器目录,如果数据量庞大,并且允许停机,可以停机打包,而不用mysqldump

6. 导出主库数据后,执行unlocktables解锁主库。

7. 把主库导出的数据恢复到从库。

8. 根据主库的show masterstatus查看到的binlog的位置状态,在从库执行change master to...语句。

9. 从库开启复制开关,即执行Lstartslave;。

5.4 主从配置表示成功后的关键参数说明

主从复制是否配置成功,最关键的是下面的3项状态参数

 

Slave_IO_Running:Yes

这个是I/O线程状态,I/O线程复制从主库到从库读取binlog日志,并写入从库的中继日志,状态为yes表示I/O线程工作正常。


Slave_SQL_Running:Yes

这个是SQL线程状态,SQL线程负责读取中继日志(relay-log)中的数据并装换为SQL语句应用到数据库中,状态为YES表示I/O线程工作正常。


Seconds_Behind_Master:0

这个是复制过程中从库比主库延迟的描述,这个参数很重要,但企业更准确地判断主从延迟的方法为:在主库写时间戳,然后从库读取时间戳,和当前数据库时间进行比较,从而认定是否延迟。

5.5 MySQL主从复制配置问题汇总

1. 主库show master status没返回状态结果,如下

mysql> show master status;
0 row in set (0.00 sec)


原因:binlog功能开关没开或没生效导致。正确开启方式如下:

[root@db01 3307]# grep "log-bin"/data/3306/my.cnf
log-bin = /data/3306/mysql-bin               #<== 在配置文件中将此行注释打开
[root@db01 3307]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock-e "show variables like ‘log_bin‘;"             #<== 修改后查看结果
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+


2. 出现错误信息:“Last_IO_Error:GOTdatal errow 1236 drom master when when reading data from binary log; ‘ Couldnot find first log file name in binary log index file’ ”

原因:以上故障原因是执行CHANGE MASTER 命令时,某一个参数的值多个空格导致,如下:

CHANGE MASTER TO
MASTER_HOST=‘172.16.1.51‘,
MASTER_PORT=3307,
MASTER_USER=‘rep‘,
MASTER_PASSWORD=‘oldboy123‘;
说明;以上参数均不能有空格

3. MySQL服务无法启动

[root@db01 /]# /data/3306/mysql start
MySQL is running...                                  #<==正在运行

[root@db01 /]# ps -ef|grep mysql                     #<== 并无mysql进程
root     30345  26941  0 22:16 pts/4    00:00:00 grep mysql

原因:其原因是启动脚本里对mysql.sock是否存在做了判断,如果存在mysql.sock,就认为服务运行,这是个bug,解决如下:

[root@db01 /]# rm -f /data/3306/mysql.sock
# 说明:删除后重新启动查看即可


4. 当从起或关闭MySQL服务从起或关闭不了,报错如下:

[root@db01 3307]# /data/3307/mysql restart
Restarting MySQL...
Stoping MySQL...
/application/mysql/bin/mysqladmin: connect toserver at ‘localhost‘ failed
error: ‘Access denied for user ‘root‘@‘localhost‘(using password: YES)‘
MySQL is running...

原因:是启动文件/data/3306/mysql里面没有修改数据库密码,当我们设置完密码后,在mysql启动文件mysql中也是有密码的,如果不改,就无法停止服务。

[root@db01 /]# vim /data/3306/mysql
mysql_pwd="oldboy123"


6. MySQL主从复制更多应用技巧


1.工作中MySQL从库停止复制故障案例

模拟重现故障的能力是运维人员最重要的能力。下面就来次模拟操作。先在从库创建一个库,然后去主库创建同名的库来模拟数据冲突,命令如下:

show slave status;报错:且 show slavestatus\G:
Slave_IO_Running:Yes
Slave_SQL_Running:NO
Seconds_Behind_Master:NULL
Last_Error:Error ‘Can‘t create database ‘xiaoliu‘;database exists‘ onquery. Default database:‘xiaoliu‘. Query:‘create database xiaoliu‘

对于该冲突,解决方法1如下:

top slave;                               #<== 临时停止同步开关
set global sql_slave_skip_counter =1;    #<== 将同步指针向下移动一个,如果多次不同步,可以重复操作
start slave;                             #<== 冲洗你开启
show slave status\G:                     #<== 再次进行查看确认

如果修改后还会报错,那可以适当的将set global sql_slave_skip_counter值增大即可。

 

提示:setglobal sql_slave_skip_counter=n;#n取值>0,忽略执行N个更新。

 

解决方法2:根据可以忽略的错误号事先在配置文件中配置,跳过指定的不影响业务数据的错误,例如:

root@MySQL ~]# grep slave-skip /data/3306/my.cnf
slave-skip-errors = 1032,1062,1007


提示:类似由于入库重复导致的失败可以忽略,其他情况是不是可以忽略需要根据不同公司的具体业务来评估。可以查看博客的代码含义


其他可能引起复制故障的原因:


  • MySQL自身的原因及人为重复插入数据。

  • 不同的数据库版本会引起不同步,低版本到高版本可以,但是高版本不能往低版本同步。

  • ·MySQL的运行错误或程序bug。

  • ·binlog记录模式,例如:row level模式就比默认的语句模式要好。


2. 让MySQL从库记录binlog日志的方法

从库需要记录binlog的应用场景:当前的从库还要作为其他从库的主库,例如级联复制或双主互为主从场景的情况下。下面介绍从库记录binlog日志的方法。

在从库的my.cnf中加入如下参数,然后重启服务生效即可。

log-slave-updates                       #<== 必须要有这个参数
log-bin = /data/3307/mysql-bin          #<==开启从库binlog功能
expire_logs_days = 7  #<==相当于find/data/3307/ -type f -name " mysql-bin.000*" -mtime +7 |xargs rm -f


3. MySQL主从复制集群架构的数据备份策略

有主从复制了,还需要做定时全量加增量备份么?答案是肯定的!

因为,如果主库有语句级误操作(例如:drop database oldboy;),从库也会执行drop databaseoldboy;,这样MySQL主从库就都删除了该数据。

把从库作为数据库备份服务器时,备份策略如下:

高并发业务场景备份时,可以选择在一台从库上备份(Slave5),把从库作为数据库备份服务器时需要在从库开启binlog功能,其逻辑如图所示。

技术分享


步骤如下:

1.选择一个不对外提供服务的从库,这样可以确保和主库更新最接近,专门用于做数据备份。

2.开启从库的binlog功能。

  备份时可以选择只停止SQL线程,停止应用SQL语句到数据库,I/O线程保留工作状态,执行命令为stop slave sql_thread;,备份方式可以采取mysqldump逻辑备份或直接物理备份,例如:使用cp、tar(针对/data/目录)工具或xtrabackup(第三方的物理备份软件)进行备份,则逻辑备份和物理备份的选择,一般是根据总的备份数据量的多少进行选择的,数据量低于30G,建议选择mysqldump逻辑备份方法,安全稳定,最后把全备和binlog数据发送到备份服务器上留存。


4. MySQL主从复制延迟问题的原因及解决方案


问题一:主库的从库太多,导致复制延迟。

从库数量以3~5个为宜,要复制的从节点数量过多,会导致复制延迟。


问题二:从库硬件比主库差,导致复制延迟。

查看Master和Slave的系统配置,可能会因为机器配置不当,包括磁盘I/O、CPU、内存等各方面因素造成复制的延迟。这一般发生在高并发大数据量写入场景U、内存等各方面因素造成复制的延迟。这一般发生在高并发大数据量写入场景中。


问题三:慢SQL语句过多

假如一条SQL语句执行时间是20秒,那么从执行完毕到从库上能查到数据至少需要20秒,这样就延迟20秒了。

一般要把SQL语句的优化作为常规工作,不断地进行监控和优化,如果单个SQL的写入时间长,可以修改后分多次写入。通过查看慢查询日志或show fullprocesslist命令,找出执行时间长的查询语句或大的事务。


问题四:主从复制的设计问题。

例如,主从复制单线程,如果主库写并发太大,来不及传送到从库,就会导致延迟。

更高版本的MySQL可以支持多线程复制,门户网站则会自己开发多线程同步功能。


问题五:主从库之间的网络延迟。

主从库的网卡、网线、连接的交换机等网络设备都可能成为复制的瓶颈,导致复制延迟,另外,跨公网主从复制很容易导致主从复制延迟。

问题六:主库读写压力大,导致复制延迟。

主库硬件要搞好一点,架构的前端要加buffer及缓存层。


5. 通过read-only参数让从库只读访问

read-only参数选项可以让从服务器只允许来自从服务器线程或具有SUPER权限的数据库用户进行更新,确保从服务器不接受来自用户端的非法用户更新。

read-only参数允许数据库更新的条件为:

  • 具有SUPER权限的用户可以更新,不受read-only参数影响,例如:管理员root。

  • 来自从服务器线程可以更新,不受read-only参数影响,例如:前文的rep用户。

 

在生产环境中,可以在从库Slave中使用read-only参数,确保从库数据不被非法更新。

read-only参数的配置方法如下。

方法一:直接带--read-only参数启动或重启数据库,使用

killall mysqld

mysqladmin -uroot -poldboy123 -S/data/3307/mysql.sock shutdown
mysqld_safe --defaults-file=/data/3307/my.cnf--read-only &

方法二:在my.cnf里[mysqld]模块下加read-only参数重启数据库,配置如下:

[mysqld]
read-only


6.Web用户专业设置方案:MySQL主从复制读写分离集群


专业的运维人员提供给开发人员读写分离的账户设置方法如下:

1)访问主库和从库时使用一套用户密码,例如,用户为web,密码为oldboy123。

2)即使访问IP不同,端口也尽量相同(3306)。例如:写库VIP为10.0.0.7,读库VIP为10.0.0.8。


除了IP没办法修改之外,要尽量为开发人员提供方便,如果数据库前端有DAL层(DBProxy),还可以只给开发人员一套用户、密码、IP、端口,这样就更专业了,剩下的都由运维人员搞定。


下面是授权Web连接用户访问的方案:MySQL主从复制读写分离集群。


方法1:主库和从库使用不同的用户,授予不同的权限。


主库上对web_w用户授权如下:

用户:web_w   密码:oldboy123   端口:3306   主库VIP10.0.0.7

权限:SELECTINSERTUPDATEDELETE

命令:
GRANT SELECT,INSERT,UPDATE,DELETE ON `web`.* TO ‘web_w‘@‘10.0.0.%‘ identified by ‘oldboy123‘;

从库上对web_r用户授权如下:

用户:web_r   密码:oldboy123    端口:3306  从库VIP10.0.0.8

权限:SELECT

命令:
GRANT SELECT ON `web`.* TO ‘web_r‘@‘10.0.0.%‘ identified by‘oldboy123‘

 

提示:此法显得不够专业,但是可以满足开发需求。

 

方法2:主库和从库使用相同的用户,但授予不同的权限。


主库上对web用户授权如下

用户:web   密码:oldboy123   端口:3306    主库VIP10.0.0.7 

权限:SELECTINSERTUPDATEDELETE

命令:
GRANT SELECT,INSERT,UPDATE,DELETE ON `web`.* TO ‘web‘@‘10.0.0.%‘identified by ‘oldboy123‘
;

从库上对web用户授权如下:

用户:web  密码:oldboy123    端口:3306    主库VIP10.0.0.8

权限:SELECT

提示:由于主库和从库是同步复制的,所以从库上的web用户会自动和主库保持一致,即无法实现只读select的授权

要实现方法2中的授权方案,有如下两个方法。

一是在主库上创建用户和权限后,从库上revoke收回对应更新权限(insert、update、delete)。

命令为:
REVOKE INSERT,UPDATE,DELETE ONweb.* FROM ‘web‘@‘10.0.0.%‘;

二是忽略授权库MySQL同步,主库的配置参数如下:

binlog-ignore-db = mysql
replicate-ignore-db = mysql

提示:上面参数等号两边必须有空格。

 

方法3:在从库上设置read-only参数,让从库只读。

主库从库:主库和从库使用相同的用户,授予相同的权限(非ALL权限)。

用户:web   密码:oldboy123   端口:3306  主库VIP10.0.0.7,从库VIP10.0.0.8

权限:SELECTINSERTUPDATEDELETE

命令:
GRANT SELECT,INSERT,UPDATE,DELETE ON web.* TO ‘web_w‘@‘10.0.0.%‘ identified by ‘oldboy123‘;

由于从库设置了read-only,非super权限是无法写入的,因此,通过read-only参数就可以很好地控制用户,使其不能非法将数据写入从库


老男孩生产工作场景的设置方案如下:


1)忽略授权库MySQL同步,主库配置参数如下:

binlog-ignore-db = mysql
replicate-ignore-db = mysql

提示:上面参数等号两边必须有空格。

2)主库和从库使用相同的用户,但授予不同的权限。

主库上对web用户授权如下:

用户:web  密码:oldboy123  端口:3306   主库VIP10.0.0.7 


权限:SELECTINSERTUPDATEDELETE

命令:
GRANT SELECT,INSERT,UPDATE,DELETE ON web.* TO ‘web‘@‘10.0.0.%‘ identified by ‘oldboy123‘;

从库上对web用户授权如下:

用户:web  密码:oldboy123  端口:3306  主库VIP10.0.0.8

权限:SELECT


3)从库设置read-only,增加双保险。

 

本文出自 “土豆” 博客,谢绝转载!

第五章:MySQL主从复制