实践MySQL主从复制(Master-Slave)的操作流程！

2018-07-16

mysql, lvs

对于发展很小的公司来说，单台服务器支撑整个公司的运转是常见的事情。但是随着用户量的增加，随之而来的数据量也会跟着增加，这就导致更多地用户会使用查询select我们的数据库，从而导致性能急剧的下降。例如，查询缓慢，用户等待时间长等等，高并发，大数据，负载均衡甚至是集群，这些方案都是小公司不会采用的，一方面成本极高，需要运维维护，这已经抵n个程序猿的工资了，所以一般不会采用这种模式。

仔细的分析一下，这种情况，其实更多地是来源于数据库mysql的承受能力变大，一方面需要读操作，另一方面有需要增删改操作，那么多人同时请求肯定受不了啦！
有人说使用缓存，但是再想想mysql的缓存是有限制的，并不是一味的去缓存就可以，又有人说使用redis缓存，存储在内存中，然而事实上运转的时候redis缓存并不能全部给你缓存，有些数据需要实时的查看，而且redis缓存过多，导致cpu直接上涨，严重的话，机器直接运转停止。

mysql主从复制的解析描述

主从复制是指一台服务器充当主数据库服务器（master），另一台或多台服务器充当从数据库服务器（slave），主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。
请注意：当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

首先准备环境：
1 两台服务器（这里还是以虚拟机做）

  • 主服务器IP：192.168.200.139
  • 从服务器IP：192.168.153.140
    安装的mysql（安装过程不在叙述）：版本号尽可能的保持一致性 本机安装版本是：mysql5.7.21

一、修改主服务器的mysql配置文件

  1. 本机的主服务器mysql配置文件地址在：etc/my.cnf
1
2
3
4
5
6
7
8
[mysqld]
# 开启二进制日志
log-bin=master-bin
log-bin-index=master-bin.index
# 设置server-id
server-id=1
# 关闭名称解析
skip-name-resolve
  1. 创建从服务器登录主服务器的账号
1
2
3
4
5
6
7
8
9
10
systemctl restart mysqld

mysql -uroot -p

# 创建用户
mysql> create user 'galudisu'@'192.168.200.%' identified by 'password';
# 分配权限
mysql> grant replication slave on *.* to 'galudisu'@'192.168.200.%';
# 刷新权限
mysql> flush privileges;
  1. 查看主服务器(master)二进制日志状态
1
2
3
4
5
6
7
8
9
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      567 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

解析下参数：
File->mysql-bin.000001 表示记录二进制文件名称
Position->948 所在位置
Binlog_Do_DB 指定mysql的binlog日志记录哪个db
Binlog_Ignore_DB 告诉master，如果当前的数据库是db_name（就是Binlog_Ignore_DB这个对应的值），不应将更新保存到二进制日志中
Executed_Gtid_Set mysql5.7以上新增参数 具体用法 自行百度下

1
2
3
4
5
6
7
# 不同步哪些数据库
binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=information_schema

# 只同步哪些数据库，除此之外，其它不同步
binlog-do-db=shop
  1. 查看二进制日志状态
1
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
mysql> show binlog events \G
*************************** 1. row ***************************
   Log_name: master-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.22-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: master-bin.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: master-bin.000001
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: master-bin.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 415
       Info: CREATE USER 'galudisu'@'192.168.200.%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
*************************** 5. row ***************************
   Log_name: master-bin.000001
        Pos: 415
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 480
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 6. row ***************************
   Log_name: master-bin.000001
        Pos: 480
 Event_type: Query
  Server_id: 1
End_log_pos: 567
       Info: flush privileges
6 rows in set (0.00 sec)

mysql>

二、配置从服务器（slave）

  1. 同样的配置文件路径 etc/my.cnf
1
2
3
4
5
6
7
8
9
10
11
# 配置server-id，让从服务器又唯一ID号
server-id=5
# 打开MySQL日志，日志格式为二进制
relay_log=relay-bin
relay_log_index=relay-bin.index
# 设置制度权限
read_only=1
# 开启从服务器二进制日志
log_bin=slave-bin
# 使得更新的数据写入二进制日志中
log_slave_updates=1

PS：上面未打开选项是配置文件中可能需要用到的参数，目前只做演示主从 所以是需要使用server-id=5 这个参数就行了。

  1. 启动从服务器复制线程机制
    进入从服务器的数据库中：
1
2
3
4
5
6
7
8
9
10
msyql -uroot -p**

mysql> change master to
	-> master_host='192.168.200.139', # 主服务器ip地址
	-> master_user='galudisu', # 主服务器创建的授权用户(切记这个是给从服务器登录的账号)
	-> master_password='123456', # 主服务器创建用户的密码
	-> master_log_file='mysql-bin.000001', # 主服务器的二进制文件名称
	-> master_log_pos=567; # 开始记录的位置
回车出现：
Query OK, 0 rows affected, 2 warnings(0.03 sec)

PS：上面的sql语句是从头开始复制第一个binlog，如果想从某个位置开始复制binlog，就需要在change master to时指定要开始的binlog文件名和语句在文件中的起点位置，参数为：master_log_file和master_log_pos这两个参数哦。

  1. 启动slave同步进程：
1
mysql> start slave;
  1. 查看状态是否已启动复制功能
1
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
mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.139
                  Master_User: galudisu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 931
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-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: 931
              Relay_Log_Space: 522
              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: 14739182-88be-11e8-ba6a-000c292e5d5b
             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)

ERROR: 
No query specified

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

这两项必须为Yes才是成功,截图出现问题 没有截图（抱歉）。

如果使用了复制的虚拟机, mysql服务的uuid可能会一样Slave_IO_Runing=No, 需要把从服务器mysql目录下的auto.cnf删掉然后重启mysql服务。
例如我从服务器的mysql地址是：/var/lib/mysql 切换到下面删除auto.cnf即可：

1
rm -rf auto.cnf

另外，如果有防火墙，需要添加3306端口到iptables。

三、测试一下喽

在主服务器(master)上，进入数据库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.11 sec)

mysql> create database shop;
Query OK, 1 row affected (0.00 sec)

mysql>

在从服务器(slave)上，看看是否有相应的数据库：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shop               |
| sys                |
+--------------------+
5 rows in set (0.12 sec)

mysql>

这个时候从库就出现这条数据了。

好了到这里基本上已经OK了，如果有细节未处理，大家就可以自行补充。