一、简介
MySQL数据库支持单向、双向、链式级联、环状等不同业务场景的复制。在复制过程中,一台服务器充当主服务器(Master),接收来自用户的内容更新,而一个或多个其他的服务器充当从服务器(Slave),接收来自主服务器 binlog文件的日志内容,解析出SQL,重新更新到从服务器,使得主从服务器数据达到一致。
如果设置了链式级联复制,那么,从服务器( Slave)本身除了充当从服务器外,也会同时充当其下面从服务器的主服务器。链式级联复制类似A→B→C的复制形式。
二、MySQL数据库主从复制的企业应用场景
MySQL主从复制集群功能使得 MySQL数据库支持大规模高并发读写成为可能,同时有效地保护了物理服务器宕机场景的数据备份。
应用场景1:从服务器作为主服务器的实时数据备份
主从服务器架构的设置可以大大加强 MySQL数据库架构的健壮性。例如:当主服务器出现问题时,我们可以人工或设置自动切换到从服务器继续提供服务,此时从服务器的数据与宕机时的主数据库几乎是一致的。
这类似NFS存储数据通过 inotify+rsync同步到备份的NFS服务器,只不过 MySQL的复制方案是其自带的工具。
利用 MySQL的复制功能进行数据备份时,在硬件故障、软件故障的场景下,该数
据备份是有效的,但对于人为地执行dop、 delete等语句删除数据的情况,从库的备份功能就没用了,因为从服务器也会执行删除的语句。
应用场景2:主从服务器实现读写分离,从服务器实现负载均衡
主从服务器架构可通过程序(PHP、Java等)或代理软件( mysql- proxy、 Amoeba)实现对用户(客户端)的请求读写分离,即让从服务器仅仅处理用户的 select查询请求降低用户查询响应时间,以及同时读写在主服务器上带来的访问压力。对于更新的数据(例如 update、 Insert、 delete语句),则仍然交给主服务器处理,确保主服务器和从服务器保持实时同步。
应用场景3:把多个从服务器根据业务重要性进行拆分访问
可以把几个不同的从服务器,根据公司的业务进行拆分。例如:有为外部用户提供查询服务的从服务器,有内部DBA用来数据备份的从服务器,还有为公司内部人员提供访问的后台、脚本、日志分析及供开发人员查询使用的从服务器。这样的拆分除了减
轻主服务器的压力外,还可以使数据库对外部用户浏览、内部用户业务处理及DBA人员的备份等互不影响。
三、实现MySQL主从读写分离的方案
(1)通过程序实现读写分离(性能和效率最佳,推荐)
PHP和Java程序都可以通过设置多个连接文件轻松地实现对数据库的读写分离,即当语句关键字为 select时,就去连接读库的连接文件,若为 update、 Insert、delete时,则连接写库的连接文件。
通过程序实现读写分离的缺点就是需要开发人员对程序进行改造,使其对下层不透明,但这种方式更容易开发和实现,适合互联网业务场景。
(2)通过开源的软件实现读写分离
MySQL- proxy、 Amoeba等代理软件也可以实现读写分离功能,这些软件的稳定性和功能一般,不建议生产使用。绝大多数公司常用的还是在应用端发程序实现读写分离。
(3)大型门户独立开发DAL层综合软件
百度、阿里等大型门户都有开发牛人,会花大力气开发适合自己业务的读写分离、负载均衡、监控报警、自动扩容、自动收缩等一系列功能的DAL层软件。
四、MySQL主从复制原理介绍
MySQL的主从复制是一个异步的复制过程(虽然一般情况下感觉是实时的,数据将从一个 MySQL数据库(我们称之为 Master)复制到另一个 MySQL数据库(我们称之为 Slave),在 Master与 Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程(SQL线程和IO线程)在 Slave端,另外一个线程(IO线程)在Master端要实现 MySQL的主从复制,首先必须打开 Master端的 binlog记录功能,否则就无法实现。因为整个复制过程实际上就是Save从 Master端获取 binlog日志,然后再在Slave上以相同顺序执行获取的 binlog日志中所记录的各种SQL操作。要打开 MySQL的 binlog记录功能,可通过在 MySQL的配置文件 my.cnf中的mysqld模块( [mysid]标识后的参数部分)增加"log-bin"参数选项来实现,具体信息如下：
[mysqld] log_bin = /data/mysql-bin
五、MySQL主从复制原理过程详细描述下面简单描述 MySQL Replication的复制原理过程。
1)在 Slave服务器上执行 start slave命令开启主从复制开关,开始进行主从复制。
2)此时, Slave服务器的MO线程会通过在 Master上已经授权的复制用户权限请求连接 Master服务器,并请求从指定 binlog日志文件的指定位置(日志文件名和位置就是在配置主从复制服务时执行 change master命令指定的)之后开始发送 binlog日志内容。
3) Master服务器接收到来自 Slave服务器的IO线程的请求后,其上负责复制的IO线程会根据 Slave服务器的Io线程请求的信息分批读取指定 binlog日志文件指定位置之后的 binlog日志信息,然后返回给 Slave端的IO线程。返回的信息中除了 binlog日志内容外,还有在 Master服务器端记录的新的 binlog文件名称,以及在新的 binlog中的下一个指定更新位置。
4)当 Slave服务器的IO线程获取到 Master服务器上IO线程发送的日志内容、日志文件及位置点后,会将 binlog日志内容依次写到 Slave端自身的 Relay log(即中继日志)文件( MySQL-relay-bin.xxxx)的最末端,并将新的binlog文件名和位置记录到master-info文件中,以便下一次读取 Master端新 binlog日志时能够告诉 Master服务器从新 binlog日志的指定文件及位置开始请求新的 binlog日志内容。
5) Slave服务器端的SQL线程会实时检测本地 Relay Log中IO线程新增加的日志内容,然后及时地把 Relay log文件中的内容解析成SQL语句,并在自身 Slave服务器上按解析SQL语句的位置顺序执行应用这些SQL语句,并在 relay- -log. info中记录当前应用中继日志的文件名及位置点。
经过了上面的过程,就可以确保在 Master端和 Slave端执行了同样的SQL语句当复制状态正常时, Master端和 Slave端的数据是完全一样的。当然, MySQL的复制机制也有一些特殊情况,具体请参考官方的说明,大多数情况下,大家不用担心。
下面针对 MySQL主从复制原理的重点进行小结:
主从复制是异步的逻辑的SQL语句级的复制。
复制时,主库有一个I/O线程,从库有两个线程,即I/O和SQL线程。
实现主从复制的必要条件是主库要开启记录 binlog功能。
作为复制的所有 MySQL节点的 server-id都不能相同。
binlog文件只记录对数据库有更改的SQL语句(来自主数据库内容的变更),不记录任何查询(如 select、show)语句。
六、MySQL主从复制配置
1）主从复制环境准备如下：
[root@master ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
[root@test ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper
IP：192.168.200.103
[root@slave ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
[root@test ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper
IP：192.168.200.104
2)、在主库master上执行操作配置
设置server-id值并开启binlog功能参数
设置server-id值并开启binlog功能参数 [root@master mysql]# egrep "server-id|log-bin" /etc/my.cnf log-bin=mysql-bin server-id = 1 说明：server-id 服务器唯一标识，log_bin 启动MySQL二进制日志，上面的2行是添加在[mysqld]下面。 也可以作如下设置： #不同步的数据库，可设置多个 binlog-ignore-db=information_schema binlog-ignore-db=cluster binlog-ignore-db=mysql #指定需要同步的数据库（和slave是相互匹配的），可以设置多个 binlog-do-db=test 添加日志存储方式和规则（选填） #设置存储模式不设置默认 binlog_format=MIXED #日志清理时间 expire_logs_days=7 #日志大小 max_binlog_size=100m #缓存大小 binlog_cache_size=4m #最大缓存大小 max_binlog_cache_size=521m 登录数据库检查参数更改情况 [root@master mysql]# mysql -pdm123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.22-log Source distribution Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | #server-id为1 +---------------+-------+ 1 row in set (0.01 sec) mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | #binlog功能已开启 +---------------+-------+ 1 row in set (0.01 sec)
在主库上建立用于主从复制的账号
mysql> grant replication slave on *.* to 'masterbackup' @'192.168.200.%' identified by 'masterbackup'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) replication slave为MySQL同步的必须权限，此处不要授权all权限 *.* 表示所有库所有表，masterbackup为同步账号，192.168.200.%为授权主机网段，使用了%号表示允许整个192.168.200.0网段以masterbackup用户访问 identified by 'masterbackup'; masterbackup为密码 检查主库创建的masterbackup复制账号命令及结果 mysql> select user,host from mysql.user; +---------------+---------------+ | user | host | +---------------+---------------+ | masterbackup | 192.168.200.% | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+---------------+ 4 rows in set (0.00 sec) mysql> select user,host from mysql.user where user='masterbackup'; +--------------+---------------+ | user | host | +--------------+---------------+ | masterbackup | 192.168.200.% | +--------------+---------------+ 1 row in set (0.01 sec) mysql> show grants for masterbackup@'192.168.200.%'; +------------------------------------------------------------------+ | Grants for masterbackup@192.168.200.% | +------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'masterbackup'@'192.168.200.%' | +------------------------------------------------------------------+ 1 row in set (0.00 sec)
实现对主数据库锁表只读
对主数据库锁表只读（当前窗口不要关掉）的命令如下： mysql> flush table with read lock; Query OK, 0 rows affected (0.00 sec) 说明：在引擎不同的情况下，这个锁表命令的时间会受下面参数的控制。锁表时，如果超过设置时间不操作会自动解锁。 默认情况下自动解锁的时长参数值如下： mysql> show variables like '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 60 | | wait_timeout | 28800 | +-----------------------------+----------+ 13 rows in set (0.00 sec) 锁表后查看主库状态。可通过当前binlog日志文件名和二进制binlog日志偏移量来查看，结果如下。 注意，show master status;命令显示的信息要记录在案，后面的从库导入全备后，继续和主从复制是就是要从这个位置开始。 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000012 | 609 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 或者新开个窗口用如下命令查看锁标后的主库binlog位置点信息： [root@master ~]# mysql -u root -pdm123 -e "show master status" mysql: [Warning] Using a password on the command line interface can be insecure. +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000012 | 609 | | | | +------------------+----------+--------------+------------------+-------------------+ 锁表后，一定要单开一个新的ssh窗口，导数数据库的所有数据，如果数据量很大（50G以上），并且允许停机，可以停库直接打包数据文件进行迁移，那样更快。 [root@master ~]# mkdir /server/backup/ -p [root@master ~]# mysqldump -uroot -pdm123 --events -A -B |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@master ~]# ls -l /server/backup/ 总用量 212 -rw-r--r-- 1 root root 215654 9月 9 22:13 mysql_bak.2018-09-09.sql.gz 为了确保导出数据期间，数据库没有数据插入，导库完毕可以再次价差主库状态信息，结果如下： [root@master ~]# mysql -u root -pdm123 -e "show master status" mysql: [Warning] Using a password on the command line interface can be insecure. +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000012 | 609 | | | | +------------------+----------+--------------+------------------+-------------------+ 导出数据库后，解锁主库，恢复可写，命令如下： mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) 把导出的数据库迁移到从库，可以使用scp、rsync等将备份的数据拷贝到从库。 [root@master ~]# scp -r /server/backup/mysql_bak.2018-09-09.sql.gz root@192.168.200.104:/server/backup/ The authenticity of host '192.168.200.104 (192.168.200.104)' can't be established. ECDSA key fingerprint is SHA256:dSlBmttAK+8cWnh7B/h7ywKYmicJrX0oba5StAR8F/U. ECDSA key fingerprint is MD5:2d:81:51:5b:1e:5f:d3:13:dd:f9:88:c9:3e:06:61:5c. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.200.104' (ECDSA) to the list of known hosts. root@192.168.200.104's password: mysql_bak.2018-09-09.sql.gz 100% 211KB 58.4MB/s 00:00
3）、在MySQL从库上执行如下操作
设置server-id值并关闭binlog功能参数
在my.cnf配置文件中的[mysqld]下面添加 vim /etc/my.cnf server-id = 5
登录数据库检查参数的改变情况
[root@slave ~]# mysql -uroot -pdm123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.7.22 Source distribution Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 5 | +---------------+-------+ 1 row in set (0.01 sec) mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | OFF | +---------------+-------+ 1 row in set (0.00 sec)
把从主库mysqldump导出的数据恢复到从库
[root@slave ~]# cd /server/backup/ [root@slave backup]# ls mysql_bak.2018-09-09.sql.gz [root@slave backup]# gzip -d mysql_bak.2018-09-09.sql.gz #解压目标数据库备份，源文件会被删除 [root@slave backup]# ll 总用量 784 -rw-r--r-- 1 root root 801238 9月 9 22:31 mysql_bak.2018-09-09.sql [root@slave backup]# mysql -uroot -pdm123 <mysql_bak.2018-09-09.sql #把数据还原到从库的命令 mysql: [Warning] Using a password on the command line interface can be insecure.
登录从库配置复制参数
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.200.103', #主库的IP
-> MASTER_PORT=3306, #主库的端口
-> MASTER_USER='masterbackup', #主库上建立的用于复制的用户masterbackup
-> MASTER_PASSWORD='masterbackup', #这里是masterbackup用户的密码
-> MASTER_LOG_FILE='mysql-bin.000012', #这里是show master status时查看到的二进制日志文件名称
-> MASTER_LOG_POS=609; #这里是show master status时查看到的二进制日志偏移量
Query OK, 0 rows affected, 2 warnings (0.00 sec)
也可以不登录数据库内部命令行，在Linux命令行快熟执行CHANGE MASTER的语句实现相应的功能，如下：
[root@slave backup]# mysql -uroot -pdm123 << EOF
> CHANGE MASTER TO
> MASTER_HOST='192.168.200.103',
> MASTER_PORT=3306,
> MASTER_USER='masterbackup',
> MASTER_PASSWORD='masterbackup',
> MASTER_LOG_FILE='mysql-bin.000012',
> MASTER_LOG_POS=609;
> EOF
上述操作原理实际上是吧用户密码等信息写入到从库新的额master.info文件中。
[root@slave backup]# cat /usr/local/mysql/
bin/ COPYING-test include/ man/ README share/ var/
COPYING docs/ lib/ mysql-test/ README-test support-files/
[root@slave backup]# cat /usr/local/mysql/var/
auto.cnf ibtmp1 mysql-bin.000003 mysql-bin.000008 slave.err
ib_buffer_pool master.info mysql-bin.000004 mysql-bin.000009 slave.pid
ibdata1 mysql/ mysql-bin.000005 mysql-bin.index slave-relay-bin.000001
ib_logfile0 mysql-bin.000001 mysql-bin.000006 performance_schema/ slave-relay-bin.index
ib_logfile1 mysql-bin.000002 mysql-bin.000007 relay-log.info sys/
[root@slave backup]# cat /usr/local/mysql/var/master.info
25
mysql-bin.000012 #这里是show master status时查看到的二进制日志文件名称
609 #这里是show master status时查看到的二进制日志偏移量
192.168.200.103 #主库的IP
masterbackup #主库上建立的用于复制的用户masterbackup
masterbackup #这里是masterbackup用户的密码
3306 #这是主库的端口
60
0
0
30.000
0
86400
0
启动从库同步开关，测试主从复制配置情况
启动从库主从复制开关，并查看复制状态
[root@slave backup]# mysql -uroot -pdm123 -e "start slave;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@slave backup]# mysql -uroot -pdm123 -e "show slave statusG;"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.103
Master_User: masterbackup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 609
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000012
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: 609
Relay_Log_Space: 527
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: 6fc3e9ed-b395-11e8-b3a4-000c29f2a33a
Master_Info_File: /usr/local/mysql/var/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:
主从复制是否成功，最关键的为下面的3项状态参数：
[root@slave backup]# mysql -uroot -pdm123 -e "show slave statusG;"|egrep "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master"
mysql: [Warning] Using a password on the command line interface can be insecure.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
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,这个是复制过程中从库比主库延迟的秒数,这个参数很重要,但企业里更准确地判断主从延迟的方法为:在主库写时间戳,然后从库读取时间戳,和当前数据库时间进行比较,从而认定是否延迟。
测试主从复制结果
在主库上写入数据然后观察从库的数据情况 [root@master ~]# mysql -u root -pdm123 -e "create database test;" [root@master ~]# mysql -u root -pdm123 -e "show databases like 'test';" mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+ | Database (test) | +-----------------+ | test | +-----------------+ 在从库查看主库创建的数据库是否同步过来，如果同步过来即说明同步成功。 [root@slave backup]# mysql -uroot -pdm123 -e "show databases like 'test';" mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------+ | Database (test) | +-----------------+ | test | +-----------------+
七、 mysql主从复制配置步骤总结
1)、准备2台数据库环境和单台多实例环境,确保能正常启动和登录
2)、配置my.cnf文件:主库配置log-bin和server-id参数;从库配置server-id,该值不能和主库及其其他从库一样,一般不开启从库log-bin功能.注意配置参数后要重启才能生效.
3)、登录主库,增加从库连接主库同步的账户,例如:masterbackup,并授权 replication slave同步的权限。
4)、登录主库,整库锁表 flush table with read lock(窗口关闭后即失效,超时参数设置的时间到了,锁表也失效),然后 show master status查看 binlog的位置状态。
5)、新开窗口,在 Linux命令行备份导出原有的数据库数据,并拷贝到从库所在的服务器目录。如果数据库数据量很大,并且允许停机,可以停机打包,而不用mysqldump。
6)、导出主库数据后,执行 unlock tables解锁主库。
7)、把主库导出的数据恢复到从库。
8)、根据主库的 show master status查看到的 binlog的位置状态,在从库执行 change master to..语句。
9)、从库开启复制开关,即执行 start slave;
10)、从库 show slave statusG,检查同步状态,并在主库进行更新测试。
八、生产环境下轻松部署MySQL主从复制
步骤如下：
1)、安装好要配置从库的数据库,配置好log-bin和 server-id参数。
2)、无需配置主库 my.cnf文件,主库的log-bin和 server-id参数默认就是配置好的。
3)、登录主库,增加从库连接主库同步的账户,例如:masterbackup,并授权 replication slave同步的权限。
4)、使用曾经在半夜通过 mysqldump带-x和-- master-data=1的命令及参数定时备份的全备数据文件,把它恢复到从库。
5)、在从库执行 change master to...语句,无需 binlog文件及对应位置点。
6)、从库开启同步开关, start slave。
7)、从库 show slave status,检查同步状态,并在主库进行更新测试。
实施过程如下：
备份导出主库数据 mysqldump -uroot -pdm123 -A --events -B -x --master-data=1|gzip >/opt/$(date +%F).sql.gz --master-data=1参数会在备份数据里增加如下语句： -- Position to start replication or point-in-time recovery from CHANGE MASTER TO MASTER_LOG_FILE='musql-bin.000005',MASTER_LOG_POS=107; 在需要做复制的从库上导入全备做从库,命令如下: gzip -d /opt/2018-09-10.sql.gz mysql -uroot -pdm123 <<EOF CHANGE MASTER TO MASTER_HOST='192.168.20.104', MASTER_PORT=3306 MASTER_USER='masterbackup', MASTER_PASSWORD='masterbackup' EOF 这里的CHANGE MASTER后面无需指定binlog文件名及具体位置,因为这部分已经在还原数据时提前应用到数据库里了(备份时 --master-data=1) start slave; #开启主从复制开关 show slave statusG #查看主从复制状态
九、MySQL主从复制线程状态说明及用途
MySQL主从复制主库I/O线程状态说明登录主数据库查看MySQL线程的同步状态:
mysql> show processlistG;
*************************** 1. row ***************************
Id: 2
User: masterbackup
Host: 192.168.200.104:29639
db: NULL
Command: Binlog Dump
Time: 5985
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 2. row ***************************
Id: 3
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
2 rows in set (0.01 sec)
ERROR:
No query specified
上面状态的意思是线程已经从binlog日志读取所有更新,并已经发送到了从数据库服务器.线程目前为空闲状态,等待有服务器上二进制日志中的新事件更新.
主服务器的binlog Dump线程中State列的常见状态如下:
主库I/O线程工作状态及其解释说明 Sending binlog event to slave #线程已经从二进制binlog日志读取了一个事件并且正将它发送到服务器 Finished reading one binlog;switching to next binlog #线程已经读完二进制binlog日志文件,并且正在打开下一个要发送到从服务器的binlog日志文件 Has sent all binlog to slave;waiting for binlog to be updated #线程已经从binlog日志读取所有更新并已经发送到了从数据库服务器.线程目前为空闲状态,等待由服务器上二进制binlog日志中的新事件更新 Waiting to finalize termination #线程停止时发生的一个很简单的状态
登录从数据库查看MySQL线程工作状态
从库有有2个线程,即I/O和SQL线程.从库I/O线程的状态如下:
mysql> show processlistG;
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 6093
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 6093
State: Slave has read all relay log; waiting for more updates
Info: NULL
*************************** 3. row ***************************
Id: 6
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
3 rows in set (0.00 sec)
ERROR:
No query specified
从服务器的I/O线程的state列的常见状态如下:
Connecting to master #线程正试图连接主服务器 Checking master version #同主服务器之间建立连接后临时出现的状态 Requesting binlog dump #建立同主服务器之间的连接后临时出现的状态.线程向主服务器发送一个请求,索取从请求的二进制binlog日志文件名和位置开始的二进制binlog日志的内容 Waiting to reconnect after a failed binlog dump request #如果二进制binlog日志转储请求失败,线程进入睡眠状态,然后定期尝试重新连接.可以使用--master-connect-retry选项指定重试之间的间隔 Reconnecting after a failed binlog dump request #线程正尝试重新连接主服务器 Waiting for master to send event #线程已经连接上主服务器,正等待二进制binlog日志事件达到 Queueing master event to the relay log #线程已经读取一个事件,正将它复制到中继日志供SQL线程来处理 Waiting to reconnect after a failed master event read #读取时(由于没有连接)出现错误.线程企图重新连接前将睡眠master-connect-retry秒 Reconnecting after a failed master event read #线程正尝试重新连接主服务器.当连接重新建立后,状态变为Waiting for master to send event
从库SQL线程的状态如下:
从服务器的SQL线程的state列的常见状态: Reading event from the relay log #线程已经从中继日志读取一个事件,可以对事件进行处理了 Has read all relay log;Waiting for the slave I/O thread to update it #线程已经处理了中继日志文件中的所有事件,现在正等待I/O线程将新事件写入中继日志 Waiting for slave mutex on exit #线程停止时发生的一个很简单的状态
查看MySQL线程同步状态的用途
通过MySQL线程同步状态可以看到同步是否正常进行,故障的位置是什么,另外还可以查看数据库是否同步完成,可用于主机宕机切换数据库或人工数据库主从切换迁移等.
例如:主库宕机,要选择最快的从库将其提升为主库,就需要查看主从库的线程状态,如果主从库复制在正常情况下进行角色切换,也需要查看主从库的线程状态,根据复制状态确定更新是否完成.
十、MySQL主从复制更多应用技巧
1）、让MySQL冲库记录binlog日志的方法
当前的从库还需要作为其他从库的主库，例如级联复制或双主互为主从场景的情况下，需要从库记录binlog日志。 在从库的my.cnf中加入如下参数，然后重启服务生效即可。 log-slave-updates logbin = mysql-bin expire_logs_days = 7 #相当于find /usr/local/mysql/var/ -type f -name " mysql-bin.000*" -mtime +7 | xargs rm -f
2)、MySQL主从复制集群架构的数据备份策略
如果主库有语句级误操作（例如：drop database dm;），从库也会执行drop database dm;，这样MySQL主从库就都删除了该数据。
高并发业务场景备份时需要在从库开启binlog功能，其逻辑图如下：
步骤如下：
1）、选择一个不对外提供服务的从库，这样可以确保和主库更新更接近，专门用于做数据备份。
2）、开启从库的binlog功能。
备份时可以选择只停止sql线程，停止应用SQL语句到数据库，I/O线程保留工作状态，执行命令为 stop slave sql_thread;,备份方式可以采取 mysqldump逻辑备份或直接物理备份,例如:使用cp、tar(针对mysql目录)工具或 xtrabackup(第三方的物理备份软件)进行备份,则逻辑备份和物理备份的选择,一般是根据总的备份数据量的多少进行选择的,数据量低于30G,建议选择mysqldump逻辑备份方法,安全稳定,最后把全备和 binlog数据发送到备份服务器上留存。
十一、MySQL主从复制延迟问题的原因及解决方案
问题一:主库的从库太多,导致复制延迟。
从库数量以3~5个为宜,要复制的从节点数量过多,会导致复制延迟。
问题二:从库硬件比主库差,导致复制延迟
查看 Master和 Slave的系统配置,可能会因为机器配置不当,包括磁盘I/O、CPU内存等各方面因素造成复制的延迟。这一般发生在高并发大数据量写入场景中。
问题三:慢SQL语句过多。
假如一条SL语句执行时间是20秒,那么从执行完毕到从库上能查到数据至少需要20秒,这样就延迟20秒了.
一般要把SQL语句的优化作为常规工作,不断地进行监控和优化,如果单个SQL的写入时间长,可以修改后分多次写入。通过查看慢查询日志或 show full processlist命令,找出执行时间长的查询语句或大的事务
问题四:主从复制的设计问题。
例如,主从复制单线程,如果主库写并发太大,来不及传送到从库,就会导致延迟。
更高版本的 MySQL可以支持多线程复制,门户网站则会自己开发多线程同步功能。
问题五:主从库之间的网络延迟。
主从库的网卡、网线、连接的交换机等网络设备都可能成为复制的瓶颈,导致复制延迟,另外,跨公网主从复制很容易导致主从复制延迟。
问题六:主库读写压力大,导致复制延迟。
主库硬件要搞好一点,架构的前端要加 buffer及缓存层。
十二、通过read-only参数让从库只读访问
read-only参数选项可以让从服务器只允许来自从服务器线程或具有 SUPER权限的数据库用户进行更新,确保从服务器不接受来自用户端的非法用户更新。
read-only参数允许数据库更新的条件为:
具有 SUPER权限的用户可以更新,不受read-only参数影响,例如:管理员 root
来自从服务器线程可以更新,不受read-only参数影响,例如:前面的masterbackup用户。
在生产环境中,可以在从库 Slave中使用read-only参数,确保从库数据不被非法更新。
read-only参数的配置方法如下。
在my.cnf里[mysqld]模块下加read-only参数重启数据库，配置如下：
[mysqld] read-only
十二、Web用户专业设置方案: MySQL主从复制读写分离集群
专业的运维人员提供给开发人员读写分离的账户设置方法如下:
1)访问主库和从库时使用一套用户密码,例如,用户为web,密码为dm123
2)即使访问IP不同,端口也尽量相同(3306)。例如:写库VIP为192.168.200.103,读库VIP为192.168.200.104。
除了IP没办法修改之外,要尽量为开发人员提供方便,如果数据库前端有DAL层( DBProxy),还可以只给开发人员一套用户、密码、IP、端口,这样就更专业了,剩下
的都由运维人员搞定。
下面是授权Web连接用户访问的方案: MySQL主从复制读写分离集群。
方法一:主库和从库使用不同的用户,授予不同的权限。
主库上对webw用户授权如下：
用户：web_w 密码：dm123 端口3306 主库VIP：192.168.200.103 权限：SELECT，INSERT，UPDATE，DELETE 命令：GRANT SELECT, INSERT, UPDATE, DELETE ON `web`.* TO 'web_w'@'192.168.200.%' identified by 'dm123';
从库上对web_r用户授权如下：
用户：web_w 密码：dm123 端口3306 主库VIP：192.168.200.104 权限：SELECT 命令：GRANT SELECT ON `web`.* TO 'web_w'@'192.168.200.%' identified by 'dm123';
方法二：主库和从库使用相同的用户，但授予不同的权限。
主库上对web用户授权如下：
用户：web_w 密码：dm123 端口3306 主库VIP：192.168.200.103 权限：SELECT，INSERT，UPDATE，DELETE 命令：GRANT SELECT, INSERT, UPDATE, DELETE ON `web`.* TO 'web_w'@'192.168.200.%' identified by 'dm123';
主库上对web用户授权如下：
用户：web_w 密码：dm123 端口3306 主库VIP：192.168.200.104 权限：SELECT 提示：用于从库和主库是同步复制的，所以从库上的web用户会自动和主库保持一致，即无法实现制度SELECT的授权
要实现方法二中的授权有如下2个方法：
一是在主库上创建用户和权限后，从库上revoke收回对应更新权限（insert、update、delete）。命令为：
REVOKE INSERT,UPDATE,DELETE on web.* FROM 'web'@192.168.200.%;
而是授权库MySQL同步，主库的配置参数如下：
binlog-ignore-db = mysql replicate-ignore-db = mysql
方法三：在从库上设置read-only参数，让从库只读。
主库从库：主库和从库使用相同的用户，授予相同的权限（非ALL权限）。
用户：web 密码：dm123 端口：3306 主库VIP：192.168.200.103，从库VIP：192.168.200.104
权限：SELECT, INSERT, UPDATE, DELETE
命令：GRANT SELECT, INSERT, UPDATE, DELETE ON web.* TO 'web_w'@'192.168.200.%' identified by 'dm123';
由于从库设置了read-only，非super权限是无法写入的，因此，通过read-only参数就可以很好的控制用户，使其不能非法将数据写入从库。
生产工作场景的设置方案如下：
1)忽略授权库MySQL同步，主库配置参数如下：
binlog-ignore-db = mysql replicate-ignore-db = mysql
2)主库和从库使用相同的用户，但授予不同的权限
主库上对web用户授权如下：
用户：web 密码：dm123 端口：3306 主库VIP：192.168.200.103 权限：SELECT, INSERT, UPDATE, DELETE 命令：GRANT SELECT, INSERT, UPDATE, DELETE ON web.* TO 'web_w'@'192.168.200.%' identified by 'dm123';
从库上对web用户授权如下：
用户：web 密码：dm123 端口：3306 从库VIP：192.168.200.104 权限：SELECT