一、简介
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"参数选项来实现,具体信息如下：
2
3
2log_bin = /data/mysql-bin
3
五、MySQL主从复制原理过程详细描述下面简单描述 MySQL Replication的复制原理过程。
1)在 Slave服务器上执行 start slave命令开启主从复制开关,开始进行主从复制。
2)此时, Slave服务器的MO线程会通过在 Master上已经授权的复制用户权限请求连接 Master服务器,并请求从指定 binlog日志文件的指定位置(日志文件名和位置就是在配置主从复制服务时执行 change master命令指定的)之后开始发送 binlog日志内容。
- 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日志内容。
- 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功能参数
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
2[root@master mysql]# egrep "server-id|log-bin" /etc/my.cnf
3log-bin=mysql-bin
4server-id = 1
5说明：server-id 服务器唯一标识，log_bin 启动MySQL二进制日志，上面的2行是添加在[mysqld]下面。
6
7也可以作如下设置：
8#不同步的数据库，可设置多个
9binlog-ignore-db=information_schema
10binlog-ignore-db=cluster
11binlog-ignore-db=mysql
12#指定需要同步的数据库（和slave是相互匹配的），可以设置多个
13binlog-do-db=test
14
15添加日志存储方式和规则（选填）
16#设置存储模式不设置默认
17binlog_format=MIXED
18#日志清理时间
19expire_logs_days=7
20#日志大小
21max_binlog_size=100m
22#缓存大小
23binlog_cache_size=4m
24#最大缓存大小
25max_binlog_cache_size=521m
26
27登录数据库检查参数更改情况
28[root@master mysql]# mysql -pdm123
29mysql: [Warning] Using a password on the command line interface can be insecure.
30Welcome to the MySQL monitor. Commands end with ; or \g.
31Your MySQL connection id is 4
32Server version: 5.7.22-log Source distribution
33
34Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
35
36Oracle is a registered trademark of Oracle Corporation and/or its
37affiliates. Other names may be trademarks of their respective
38owners.
39
40Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
41
42mysql> show variables like 'server_id';
43+---------------+-------+
44| Variable_name | Value |
45+---------------+-------+
46| server_id | 1 | #server-id为1
47+---------------+-------+
481 row in set (0.01 sec)
49
50mysql> show variables like 'log_bin';
51+---------------+-------+
52| Variable_name | Value |
53+---------------+-------+
54| log_bin | ON | #binlog功能已开启
55+---------------+-------+
561 row in set (0.01 sec)
57
在主库上建立用于主从复制的账号
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
2Query OK, 0 rows affected, 1 warning (0.01 sec)
3
4mysql> flush privileges;
5Query OK, 0 rows affected (0.01 sec)
6
7replication slave为MySQL同步的必须权限，此处不要授权all权限
8
9*.* 表示所有库所有表，masterbackup为同步账号，192.168.200.%为授权主机网段，使用了%号表示允许整个192.168.200.0网段以masterbackup用户访问
10
11identified by 'masterbackup'; masterbackup为密码
12
13检查主库创建的masterbackup复制账号命令及结果
14mysql> select user,host from mysql.user;
15+---------------+---------------+
16| user | host |
17+---------------+---------------+
18| masterbackup | 192.168.200.% |
19| mysql.session | localhost |
20| mysql.sys | localhost |
21| root | localhost |
22+---------------+---------------+
234 rows in set (0.00 sec)
24
25mysql> select user,host from mysql.user where user='masterbackup';
26+--------------+---------------+
27| user | host |
28+--------------+---------------+
29| masterbackup | 192.168.200.% |
30+--------------+---------------+
311 row in set (0.01 sec)
32
33mysql> show grants for masterbackup@'192.168.200.%';
34+------------------------------------------------------------------+
35| Grants for masterbackup@192.168.200.% |
36+------------------------------------------------------------------+
37| GRANT REPLICATION SLAVE ON *.* TO 'masterbackup'@'192.168.200.%' |
38+------------------------------------------------------------------+
391 row in set (0.00 sec)
40
实现对主数据库锁表只读
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
2mysql> flush table with read lock;
3Query OK, 0 rows affected (0.00 sec)
4
5说明：在引擎不同的情况下，这个锁表命令的时间会受下面参数的控制。锁表时，如果超过设置时间不操作会自动解锁。
6默认情况下自动解锁的时长参数值如下：
7mysql> show variables like '%timeout%';
8+-----------------------------+----------+
9| Variable_name | Value |
10+-----------------------------+----------+
11| connect_timeout | 10 |
12| delayed_insert_timeout | 300 |
13| have_statement_timeout | YES |
14| innodb_flush_log_at_timeout | 1 |
15| innodb_lock_wait_timeout | 50 |
16| innodb_rollback_on_timeout | OFF |
17| interactive_timeout | 28800 |
18| lock_wait_timeout | 31536000 |
19| net_read_timeout | 30 |
20| net_write_timeout | 60 |
21| rpl_stop_slave_timeout | 31536000 |
22| slave_net_timeout | 60 |
23| wait_timeout | 28800 |
24+-----------------------------+----------+
2513 rows in set (0.00 sec)
26
27锁表后查看主库状态。可通过当前binlog日志文件名和二进制binlog日志偏移量来查看，结果如下。
28注意，show master status;命令显示的信息要记录在案，后面的从库导入全备后，继续和主从复制是就是要从这个位置开始。
29mysql> show master status;
30+------------------+----------+--------------+------------------+-------------------+
31| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
32+------------------+----------+--------------+------------------+-------------------+
33| mysql-bin.000012 | 609 | | | |
34+------------------+----------+--------------+------------------+-------------------+
351 row in set (0.00 sec)
36
37或者新开个窗口用如下命令查看锁标后的主库binlog位置点信息：
38[root@master ~]# mysql -u root -pdm123 -e "show master status"
39mysql: [Warning] Using a password on the command line interface can be insecure.
40+------------------+----------+--------------+------------------+-------------------+
41| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
42+------------------+----------+--------------+------------------+-------------------+
43| mysql-bin.000012 | 609 | | | |
44+------------------+----------+--------------+------------------+-------------------+
45
46锁表后，一定要单开一个新的ssh窗口，导数数据库的所有数据，如果数据量很大（50G以上），并且允许停机，可以停库直接打包数据文件进行迁移，那样更快。
47[root@master ~]# mkdir /server/backup/ -p
48[root@master ~]# mysqldump -uroot -pdm123 --events -A -B |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
49mysqldump: [Warning] Using a password on the command line interface can be insecure.
50[root@master ~]# ls -l /server/backup/
51总用量 212
52-rw-r--r-- 1 root root 215654 9月 9 22:13 mysql_bak.2018-09-09.sql.gz
53
54为了确保导出数据期间，数据库没有数据插入，导库完毕可以再次价差主库状态信息，结果如下：
55[root@master ~]# mysql -u root -pdm123 -e "show master status"
56mysql: [Warning] Using a password on the command line interface can be insecure.
57+------------------+----------+--------------+------------------+-------------------+
58| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
59+------------------+----------+--------------+------------------+-------------------+
60| mysql-bin.000012 | 609 | | | |
61+------------------+----------+--------------+------------------+-------------------+
62
63导出数据库后，解锁主库，恢复可写，命令如下：
64mysql> unlock tables;
65Query OK, 0 rows affected (0.00 sec)
66
67把导出的数据库迁移到从库，可以使用scp、rsync等将备份的数据拷贝到从库。
68[root@master ~]# scp -r /server/backup/mysql_bak.2018-09-09.sql.gz root@192.168.200.104:/server/backup/
69The authenticity of host '192.168.200.104 (192.168.200.104)' can't be established.
70ECDSA key fingerprint is SHA256:dSlBmttAK+8cWnh7B/h7ywKYmicJrX0oba5StAR8F/U.
71ECDSA key fingerprint is MD5:2d:81:51:5b:1e:5f:d3:13:dd:f9:88:c9:3e:06:61:5c.
72Are you sure you want to continue connecting (yes/no)? yes
73Warning: Permanently added '192.168.200.104' (ECDSA) to the list of known hosts.
74root@192.168.200.104's password:
75mysql_bak.2018-09-09.sql.gz 100% 211KB 58.4MB/s 00:00
76
3）、在MySQL从库上执行如下操作
设置server-id值并关闭binlog功能参数
2
3
4
2vim /etc/my.cnf
3server-id = 5
4
登录数据库检查参数的改变情况
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
2mysql: [Warning] Using a password on the command line interface can be insecure.
3Welcome to the MySQL monitor. Commands end with ; or \g.
4Your MySQL connection id is 2
5Server version: 5.7.22 Source distribution
6
7Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
8
9Oracle is a registered trademark of Oracle Corporation and/or its
10affiliates. Other names may be trademarks of their respective
11owners.
12
13Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14
15mysql> show variables like 'server_id';
16+---------------+-------+
17| Variable_name | Value |
18+---------------+-------+
19| server_id | 5 |
20+---------------+-------+
211 row in set (0.01 sec)
22
23mysql> show variables like 'log_bin';
24+---------------+-------+
25| Variable_name | Value |
26+---------------+-------+
27| log_bin | OFF |
28+---------------+-------+
291 row in set (0.00 sec)
30
把从主库mysqldump导出的数据恢复到从库
2
3
4
5
6
7
8
9
10
2[root@slave backup]# ls
3mysql_bak.2018-09-09.sql.gz
4[root@slave backup]# gzip -d mysql_bak.2018-09-09.sql.gz #解压目标数据库备份，源文件会被删除
5[root@slave backup]# ll
6总用量 784
7-rw-r--r-- 1 root root 801238 9月 9 22:31 mysql_bak.2018-09-09.sql
8[root@slave backup]# mysql -uroot -pdm123 <mysql_bak.2018-09-09.sql #把数据还原到从库的命令
9mysql: [Warning] Using a password on the command line interface can be insecure.
10
登录从库配置复制参数
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
2 -> MASTER_HOST='192.168.200.103', #主库的IP
3 -> MASTER_PORT=3306, #主库的端口
4 -> MASTER_USER='masterbackup', #主库上建立的用于复制的用户masterbackup
5 -> MASTER_PASSWORD='masterbackup', #这里是masterbackup用户的密码
6 -> MASTER_LOG_FILE='mysql-bin.000012', #这里是show master status时查看到的二进制日志文件名称
7 -> MASTER_LOG_POS=609; #这里是show master status时查看到的二进制日志偏移量
8Query OK, 0 rows affected, 2 warnings (0.00 sec)
9
10也可以不登录数据库内部命令行，在Linux命令行快熟执行CHANGE MASTER的语句实现相应的功能，如下：
11[root@slave backup]# mysql -uroot -pdm123 << EOF
12> CHANGE MASTER TO
13> MASTER_HOST='192.168.200.103',
14> MASTER_PORT=3306,
15> MASTER_USER='masterbackup',
16> MASTER_PASSWORD='masterbackup',
17> MASTER_LOG_FILE='mysql-bin.000012',
18> MASTER_LOG_POS=609;
19> EOF
20
21上述操作原理实际上是吧用户密码等信息写入到从库新的额master.info文件中。
22[root@slave backup]# cat /usr/local/mysql/
23bin/ COPYING-test include/ man/ README share/ var/
24COPYING docs/ lib/ mysql-test/ README-test support-files/
25[root@slave backup]# cat /usr/local/mysql/var/
26auto.cnf ibtmp1 mysql-bin.000003 mysql-bin.000008 slave.err
27ib_buffer_pool master.info mysql-bin.000004 mysql-bin.000009 slave.pid
28ibdata1 mysql/ mysql-bin.000005 mysql-bin.index slave-relay-bin.000001
29ib_logfile0 mysql-bin.000001 mysql-bin.000006 performance_schema/ slave-relay-bin.index
30ib_logfile1 mysql-bin.000002 mysql-bin.000007 relay-log.info sys/
31[root@slave backup]# cat /usr/local/mysql/var/master.info
3225
33mysql-bin.000012 #这里是show master status时查看到的二进制日志文件名称
34609 #这里是show master status时查看到的二进制日志偏移量
35192.168.200.103 #主库的IP
36masterbackup #主库上建立的用于复制的用户masterbackup
37masterbackup #这里是masterbackup用户的密码
383306 #这是主库的端口
3960
400
41
42
43
44
45
460
4730.000
48
490
50
5186400
52
53
540
55
启动从库同步开关，测试主从复制配置情况
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
2[root@slave backup]# mysql -uroot -pdm123 -e "start slave;"
3mysql: [Warning] Using a password on the command line interface can be insecure.
4[root@slave backup]# mysql -uroot -pdm123 -e "show slave status\G;"
5mysql: [Warning] Using a password on the command line interface can be insecure.
6*************************** 1. row ***************************
7 Slave_IO_State: Waiting for master to send event
8 Master_Host: 192.168.200.103
9 Master_User: masterbackup
10 Master_Port: 3306
11 Connect_Retry: 60
12 Master_Log_File: mysql-bin.000012
13 Read_Master_Log_Pos: 609
14 Relay_Log_File: slave-relay-bin.000002
15 Relay_Log_Pos: 320
16 Relay_Master_Log_File: mysql-bin.000012
17 Slave_IO_Running: Yes
18 Slave_SQL_Running: Yes
19 Replicate_Do_DB:
20 Replicate_Ignore_DB:
21 Replicate_Do_Table:
22 Replicate_Ignore_Table:
23 Replicate_Wild_Do_Table:
24 Replicate_Wild_Ignore_Table:
25 Last_Errno: 0
26 Last_Error:
27 Skip_Counter: 0
28 Exec_Master_Log_Pos: 609
29 Relay_Log_Space: 527
30 Until_Condition: None
31 Until_Log_File:
32 Until_Log_Pos: 0
33 Master_SSL_Allowed: No
34 Master_SSL_CA_File:
35 Master_SSL_CA_Path:
36 Master_SSL_Cert:
37 Master_SSL_Cipher:
38 Master_SSL_Key:
39 Seconds_Behind_Master: 0
40Master_SSL_Verify_Server_Cert: No
41 Last_IO_Errno: 0
42 Last_IO_Error:
43 Last_SQL_Errno: 0
44 Last_SQL_Error:
45 Replicate_Ignore_Server_Ids:
46 Master_Server_Id: 1
47 Master_UUID: 6fc3e9ed-b395-11e8-b3a4-000c29f2a33a
48 Master_Info_File: /usr/local/mysql/var/master.info
49 SQL_Delay: 0
50 SQL_Remaining_Delay: NULL
51 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
52 Master_Retry_Count: 86400
53 Master_Bind:
54 Last_IO_Error_Timestamp:
55 Last_SQL_Error_Timestamp:
56 Master_SSL_Crl:
57 Master_SSL_Crlpath:
58 Retrieved_Gtid_Set:
59 Executed_Gtid_Set:
60 Auto_Position: 0
61 Replicate_Rewrite_DB:
62 Channel_Name:
63 Master_TLS_Version:
64
65主从复制是否成功，最关键的为下面的3项状态参数：
66[root@slave backup]# mysql -uroot -pdm123 -e "show slave status\G;"|egrep "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master"
67mysql: [Warning] Using a password on the command line interface can be insecure.
68 Slave_IO_Running: Yes
69 Slave_SQL_Running: Yes
70 Seconds_Behind_Master: 0
71
72Slave_IO_Running:Yes,这个是I/O线程状态,I/O线程负责从从库到主库读取binlog日志,并写入从库的中继日志,状态为Yes表示I/O线程工作正常。
73
74Slave_SQL_Running:Yes,这个是SQL线程状态,SQL线程负责读取中继日志(relay-log)中的数据并转换为SQL语句应用到从数据库中,状态为Yes表示I/O线程工作正常。
75
76Seconds_Behind_Master:0,这个是复制过程中从库比主库延迟的秒数,这个参数很重要,但企业里更准确地判断主从延迟的方法为:在主库写时间戳,然后从库读取时间戳,和当前数据库时间进行比较,从而认定是否延迟。
77
测试主从复制结果
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2[root@master ~]# mysql -u root -pdm123 -e "create database test;"
3
4[root@master ~]# mysql -u root -pdm123 -e "show databases like 'test';"
5mysql: [Warning] Using a password on the command line interface can be insecure.
6+-----------------+
7| Database (test) |
8+-----------------+
9| test |
10+-----------------+
11
12在从库查看主库创建的数据库是否同步过来，如果同步过来即说明同步成功。
13[root@slave backup]# mysql -uroot -pdm123 -e "show databases like 'test';"
14mysql: [Warning] Using a password on the command line interface can be insecure.
15+-----------------+
16| Database (test) |
17+-----------------+
18| test |
19+-----------------+
20
七、 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 status\G,检查同步状态,并在主库进行更新测试。
八、生产环境下轻松部署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,检查同步状态,并在主库进行更新测试。
实施过程如下：
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2mysqldump -uroot -pdm123 -A --events -B -x --master-data=1|gzip >/opt/$(date +%F).sql.gz
3
4--master-data=1参数会在备份数据里增加如下语句：
5-- Position to start replication or point-in-time recovery from CHANGE MASTER TO MASTER_LOG_FILE='musql-bin.000005',MASTER_LOG_POS=107;
6
7在需要做复制的从库上导入全备做从库,命令如下:
8gzip -d /opt/2018-09-10.sql.gz
9mysql -uroot -pdm123 <<EOF
10CHANGE MASTER TO
11MASTER_HOST='192.168.20.104',
12MASTER_PORT=3306
13MASTER_USER='masterbackup',
14MASTER_PASSWORD='masterbackup'
15EOF
16
17这里的CHANGE MASTER后面无需指定binlog文件名及具体位置,因为这部分已经在还原数据时提前应用到数据库里了(备份时 --master-data=1)
18
19start slave; #开启主从复制开关
20show slave status\G #查看主从复制状态
21
九、MySQL主从复制线程状态说明及用途
MySQL主从复制主库I/O线程状态说明登录主数据库查看MySQL线程的同步状态:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2*************************** 1. row ***************************
3 Id: 2
4 User: masterbackup
5 Host: 192.168.200.104:29639
6 db: NULL
7Command: Binlog Dump
8 Time: 5985
9 State: Master has sent all binlog to slave; waiting for more updates
10 Info: NULL
11*************************** 2. row ***************************
12 Id: 3
13 User: root
14 Host: localhost
15 db: NULL
16Command: Query
17 Time: 0
18 State: starting
19 Info: show processlist
202 rows in set (0.01 sec)
21
22ERROR:
23No query specified
24
上面状态的意思是线程已经从binlog日志读取所有更新,并已经发送到了从数据库服务器.线程目前为空闲状态,等待有服务器上二进制日志中的新事件更新.
主服务器的binlog Dump线程中State列的常见状态如下:
2
3
4
5
6
7
8
9
10
11
12
2Sending binlog event to slave #线程已经从二进制binlog日志读取了一个事件并且正将它发送到服务器
3
4Finished reading one binlog;switching to next binlog
5#线程已经读完二进制binlog日志文件,并且正在打开下一个要发送到从服务器的binlog日志文件
6
7Has sent all binlog to slave;waiting for binlog to be updated
8#线程已经从binlog日志读取所有更新并已经发送到了从数据库服务器.线程目前为空闲状态,等待由服务器上二进制binlog日志中的新事件更新
9
10Waiting to finalize termination
11#线程停止时发生的一个很简单的状态
12
登录从数据库查看MySQL线程工作状态
从库有有2个线程,即I/O和SQL线程.从库I/O线程的状态如下:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
2*************************** 1. row ***************************
3 Id: 1
4 User: system user
5 Host:
6 db: NULL
7Command: Connect
8 Time: 6093
9 State: Waiting for master to send event
10 Info: NULL
11*************************** 2. row ***************************
12 Id: 2
13 User: system user
14 Host:
15 db: NULL
16Command: Connect
17 Time: 6093
18 State: Slave has read all relay log; waiting for more updates
19 Info: NULL
20*************************** 3. row ***************************
21 Id: 6
22 User: root
23 Host: localhost
24 db: NULL
25Command: Query
26 Time: 0
27 State: starting
28 Info: show processlist
293 rows in set (0.00 sec)
30
31ERROR:
32No query specified
33
从服务器的I/O线程的state列的常见状态如下:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
2#线程正试图连接主服务器
3
4Checking master version
5#同主服务器之间建立连接后临时出现的状态
6
7Requesting binlog dump
8#建立同主服务器之间的连接后临时出现的状态.线程向主服务器发送一个请求,索取从请求的二进制binlog日志文件名和位置开始的二进制binlog日志的内容
9
10Waiting to reconnect after a failed binlog dump request
11#如果二进制binlog日志转储请求失败,线程进入睡眠状态,然后定期尝试重新连接.可以使用--master-connect-retry选项指定重试之间的间隔
12
13Reconnecting after a failed binlog dump request
14#线程正尝试重新连接主服务器
15
16Waiting for master to send event
17#线程已经连接上主服务器,正等待二进制binlog日志事件达到
18
19Queueing master event to the relay log
20#线程已经读取一个事件,正将它复制到中继日志供SQL线程来处理
21
22Waiting to reconnect after a failed master event read
23#读取时(由于没有连接)出现错误.线程企图重新连接前将睡眠master-connect-retry秒
24
25Reconnecting after a failed master event read
26#线程正尝试重新连接主服务器.当连接重新建立后,状态变为Waiting for master to send event
27
从库SQL线程的状态如下:
2
3
4
5
6
7
8
9
10
2Reading event from the relay log
3#线程已经从中继日志读取一个事件,可以对事件进行处理了
4
5Has read all relay log;Waiting for the slave I/O thread to update it
6#线程已经处理了中继日志文件中的所有事件,现在正等待I/O线程将新事件写入中继日志
7
8Waiting for slave mutex on exit
9#线程停止时发生的一个很简单的状态
10
查看MySQL线程同步状态的用途
通过MySQL线程同步状态可以看到同步是否正常进行,故障的位置是什么,另外还可以查看数据库是否同步完成,可用于主机宕机切换数据库或人工数据库主从切换迁移等.
例如:主库宕机,要选择最快的从库将其提升为主库,就需要查看主从库的线程状态,如果主从库复制在正常情况下进行角色切换,也需要查看主从库的线程状态,根据复制状态确定更新是否完成.
十、MySQL主从复制更多应用技巧
1）、让MySQL冲库记录binlog日志的方法
2
3
4
5
6
7
2
3在从库的my.cnf中加入如下参数，然后重启服务生效即可。
4log-slave-updates
5logbin = mysql-bin
6expire_logs_days = 7 #相当于find /usr/local/mysql/var/ -type f -name " mysql-bin.000*" -mtime +7 | xargs rm -f
7
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参数重启数据库，配置如下：
2
3
4
2
3read-only
4
十二、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用户授权如下：
2
3
4
2权限：SELECT，INSERT，UPDATE，DELETE
3命令：GRANT SELECT, INSERT, UPDATE, DELETE ON `web`.* TO 'web_w'@'192.168.200.%' identified by 'dm123';
4
从库上对web_r用户授权如下：
2
3
4
2权限：SELECT
3命令：GRANT SELECT ON `web`.* TO 'web_w'@'192.168.200.%' identified by 'dm123';
4
方法二：主库和从库使用相同的用户，但授予不同的权限。
主库上对web用户授权如下：
2
3
4
2权限：SELECT，INSERT，UPDATE，DELETE
3命令：GRANT SELECT, INSERT, UPDATE, DELETE ON `web`.* TO 'web_w'@'192.168.200.%' identified by 'dm123';
4
主库上对web用户授权如下：
2
3
4
2权限：SELECT
3提示：用于从库和主库是同步复制的，所以从库上的web用户会自动和主库保持一致，即无法实现制度SELECT的授权
4
要实现方法二中的授权有如下2个方法：
一是在主库上创建用户和权限后，从库上revoke收回对应更新权限（insert、update、delete）。命令为：
2
2
而是授权库MySQL同步，主库的配置参数如下：
2
3
2replicate-ignore-db = mysql
3
方法三：在从库上设置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同步，主库配置参数如下：
2
3
2replicate-ignore-db = mysql
3
2)主库和从库使用相同的用户，但授予不同的权限
主库上对web用户授权如下：
2
3
4
2权限：SELECT, INSERT, UPDATE, DELETE
3命令：GRANT SELECT, INSERT, UPDATE, DELETE ON web.* TO 'web_w'@'192.168.200.%' identified by 'dm123';
4
从库上对web用户授权如下：
2
3
2权限：SELECT
3
