基于docker实现mysql主从复制、读写分离

发表于 | 分类于 | | 本文总阅读数 次

SQL查询慢怎么办？

  1. 从数据库设计方面
    • 加索引
    • 尽量多考虑使用数值型
    • 尽量多考虑使用定长而非变长
  2. 从SQL优化入手
    • exist和in
    • in和not in
    • like的使用从数据库设计方面加索引尽量多考虑使用数值型尽量多考虑使用定长而非变长从SQL优化入手exist和inin和not inlike的使用

随着数据增长将会遇到什么问题？

  • 一台服务器的资源是有限的
  • 数据库中的数据量不可控
  • 库中的表会越来越多
  • 表中的数据量也会越来越大
  • 增删改查的开销会越来越大

用架构进行优化！

为什么MySQL要做主从复制（读写分离）？

主从复制（读写分离）的目的：分散压力

原因：如果对数据库的读和写都在同一个数据库服务中操作，业务系统性能会降低

配置主从数据库

  1. 在宿主机中创建一个conf文件夹，存放主从数据库的配置文件
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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
[root@VM-24-12-centos /]# mkdir /conf
[root@VM-24-12-centos /]# cd /conf/

#######################################################################################################
# 主数据库配置文件
[root@VM-24-12-centos conf]# vim master.cnf
[root@VM-24-12-centos conf]# cat master.cnf 
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8 
#skip-networking 
innodb_print_all_deadlocks = 1
max_connections = 2000 
max_connect_errors = 6000 
open_files_limit = 65535 
table_open_cache = 128 
max_allowed_packet = 4M 
binlog_cache_size = 1M 
max_heap_table_size = 8M 
tmp_table_size = 16M 
read_buffer_size = 2M 
read_rnd_buffer_size = 8M 
sort_buffer_size = 8M 
join_buffer_size = 28M 
key_buffer_size = 4M 
thread_cache_size = 8 
query_cache_type = 1 
query_cache_size = 8M 
query_cache_limit = 2M 
ft_min_word_len = 4 
log-bin = mysql-bin
server-id = 1
binlog_format = mixed 
performance_schema = 0 
explicit_defaults_for_timestamp 
#lower_case_table_names = 1 
interactive_timeout = 28800 
wait_timeout = 28800 
# Recommended in standard MySQL setup 
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES 
[mysqldump] 
quick 
max_allowed_packet = 16M 
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

############################################################################################
# 从数据库1配置文件
[root@VM-24-12-centos conf]# vim slave.cnf
[root@VM-24-12-centos conf]# cat slave.cnf 
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8 
#skip-networking 
innodb_print_all_deadlocks = 1
max_connections = 2000 
max_connect_errors = 6000 
open_files_limit = 65535 
table_open_cache = 128 
max_allowed_packet = 4M 
binlog_cache_size = 1M 
max_heap_table_size = 8M 
tmp_table_size = 16M 
read_buffer_size = 2M 
read_rnd_buffer_size = 8M 
sort_buffer_size = 8M 
join_buffer_size = 28M 
key_buffer_size = 4M 
thread_cache_size = 8 
query_cache_type = 1 
query_cache_size = 8M 
query_cache_limit = 2M 
ft_min_word_len = 4 
log-bin = mysql-bin
server-id = 2
binlog_format = mixed 
performance_schema = 0 
explicit_defaults_for_timestamp 
#lower_case_table_names = 1 
interactive_timeout = 28800 
wait_timeout = 28800 
# Recommended in standard MySQL setup 
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES 
[mysqldump] 
quick 
max_allowed_packet = 16M 
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

# 从数据库2配置文件
[root@VM-24-12-centos conf]# vim slave.cnf
[root@VM-24-12-centos conf]# cat slave.cnf 
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8 
#skip-networking 
innodb_print_all_deadlocks = 1
max_connections = 2000 
max_connect_errors = 6000 
open_files_limit = 65535 
table_open_cache = 128 
max_allowed_packet = 4M 
binlog_cache_size = 1M 
max_heap_table_size = 8M 
tmp_table_size = 16M 
read_buffer_size = 2M 
read_rnd_buffer_size = 8M 
sort_buffer_size = 8M 
join_buffer_size = 28M 
key_buffer_size = 4M 
thread_cache_size = 8 
query_cache_type = 1 
query_cache_size = 8M 
query_cache_limit = 2M 
ft_min_word_len = 4 
log-bin = mysql-bin
server-id = 3
binlog_format = mixed 
performance_schema = 0 
explicit_defaults_for_timestamp 
#lower_case_table_names = 1 
interactive_timeout = 28800 
wait_timeout = 28800 
# Recommended in standard MySQL setup 
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES 
[mysqldump] 
quick 
max_allowed_packet = 16M 
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
  1. 启动主从数据库
1
2
3
4
5
6
[root@VM-24-12-centos conf]# docker run --privileged=true -p 3311:3306 --name mysql-master -v /conf/master.cnf:/etc/mysql/conf.d/master.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
2eed8da7114dba469b85f6f0a5bb087de7fa7ad49fd5c5e56ff2b4ffc91c9e3b
[root@VM-24-12-centos conf]# docker run --privileged=true -p 3312:3306 --name mysql-slave01 -v /conf/slave.cnf:/etc/mysql/conf.d/slave.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
cfa3e8c1062c69ad14a9c5c6eabf415a44c6398efb32a6acd99b087ba6ba441c
[root@VM-24-12-centos conf]# docker run --privileged=true -p 3313:3306 --name mysql-slave02 -v /conf/slave02.cnf:/etc/mysql/conf.d/slave.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
370af2a651d5d983b99eb6d916a0885fb835c04ba3c225bc6b8dc05f20004fe6
  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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
[root@VM-24-12-centos conf]# docker exec -it mysql-master /bin/bash
root@20a63bf1a35a:/# mysql -uroot -p
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 创建用户
mysql> create user codechen;
Query OK, 0 rows affected (0.00 sec)

# 授予权限
mysql> GRANT ALL PRIVILEGES ON *.* TO codechen@'%' IDENTIFIED BY '123456'; FLUSH PRIVILEGES;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

# 主数据库原始数据
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| song               |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

# 查看二进制文件，配置slave数据库时需要
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |   5889540 |
| mysql-bin.000003 |      2123 |
+------------------+-----------+


#############################################################################################
# 开启从数据库（两个从数据库相同）
[root@VM-24-12-centos conf]# docker exec -it mysql-slave01 /bin/bash
root@07abacc7cfa4:/# mysql -uroot -p

# 原始的数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)


# 在slave数据库中配置主从关联'152.136.154.107'
# master_host='172.17.0.2',
# master_port=3311,
mysql> change master to 
master_host='172.17.0.2',
master_port=3306,
master_user='codechen',
master_password='123456',
master_log_file='mysql-bin.000003', #Master服务器产生的日志要和Master服务器所配置的相互对应如下如所示的file对应
master_log_pos=0; #要和Master服务器所配置的相互对应如下如所示的Position对应（经过测试发现如果是一样的只能同步一次，所以这里修正成0，0是可以每次都同步成功的）
Query OK, 0 rows affected, 2 warnings (0.04 sec)

#开启服务并查看启动状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 152.136.154.107
                  Master_User: codechen
                  Master_Port: 3311
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 100440
               Relay_Log_File: 07abacc7cfa4-relay-bin.000003
                Relay_Log_Pos: 100653
        Relay_Master_Log_File: mysql-bin.000003
             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: 100440
              Relay_Log_Space: 5990443
              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: a269da86-b350-11ec-b5cc-0242ac110002
             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)

# 配置成功后数据库就可以同步数据库了
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| song               |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
1
2
3
4
# 内网ip地址
172.17.0.2   # master
172.17.0.3   # slave01
172.17.0.4   # slave02
分享到： 收藏夹 复制网址 邮件 微信 QQ空间 腾讯微博 豆瓣 一键分享 更多