AI 助理
备案控制台
开发者社区 数据库 文章 正文

Centos7-Mysql 5.6 多主一从 解决方案&详细配置

2017-11-12 998
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
云数据库 RDS MySQL，高可用系列 2核4GB
简介：

#### 业务场景：

公司几个主要的业务已经独立，放在不同的数据库服务器上面，但是有一个业务又需要关联多个业务库进行联合查询统计。这时候就需要将不同的业务库数据同步到一台从库进行统计。根据Mysql主从同步原理使用多从一主的方案解决。主库使用innodb引擎，从库开启多实例使用myisam引擎并将多个实例的数据同步到同一个目录，并通过flush tables 在一个实例里面访问其他实例的数据。


#### 解决思路：

1、主数据库使用Innodb引擎，并设置sql_mode为 NO_AUTO_CREATE_USER

2、从库开启多实例，将多个主库里面的数据通过主从复制同步到同一个数据目录。从库的每个实例对应一个主库。多个实例使用同一个数据目录。

3、从库使用Myisam引擎，关闭从库默认的innodb引擎,Myisam引擎可以访问同一个数据目录里面其他实例的表。

4、从库的每个实例需要执行flush tables 才能看到其他实例表的数据变化，可以设置crontab任务计划每分钟在第一个实例刷新表，以便程序连接的默认实例能看到表的实时变化。

5、设置主库和从库的sql_mode都为NO_AUTO_CREATE_USER，只有这样主库的innodb引擎的sql同步到从库的时候才能执行成功。


#### 方案架构图：

wKiom1ZxRajz4Wx8AAD1fu4_wBA031.png

#### 环境说明：

主库-1：192.168.1.1

主库-2：192.168.1.2

从库-3：192.168.1.3

从库-3：192.168.1.4

从库-3：192.168.1.5


#### 实现步骤：（Mysql安装步骤这里不在描述）

1、主数据库配置文件，多个主库配置文件除了server-id不能一样其他都一样。

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
[root@masterdb01 ~] #cat /etc/my.cnf
[client]
port= 3306
socket=  /tmp/mysql .sock
[mysqld]
port = 3306
basedir =  /usr/local/mysql
datadir =  /data/mysql
character- set -server = utf8mb4
default-storage-engine  = InnoDB
socket =  /tmp/mysql .sock
skip-name-resolv = 1
open_files_limit = 65535 
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 2048
tmp-table-size   = 32M
max-heap-table-size = 32M
#query-cache-type  = 0
query-cache-size  = 0
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 51
query_cache_size = 32M
tmp_table_size = 96M
max_heap_table_size = 96M
query_cache_type=1
log-error= /data/logs/mysqld .log
slow_query_log = 1
slow_query_log_file =  /data/logs/slow .log
long_query_time = 0.1
# BINARY LOGGING #
server- id  = 1
log-bin          =  /data/binlog/mysql-bin
log-bin-index    = /data/binlog/mysql-bin .index
expire-logs-days = 14
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1024M
log_slave_updates
#binlog_format = row 
binlog_format = MIXED    // 这里使用的混合模式复制
relay_log_recovery = 1
#不需要同步的表
replicate-wild-ignore-table=mydb.sp_counter
#不需要同步的库
replicate-ignore-db = mysql,information_schema,performance_schema
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
transaction_isolation = REPEATABLE-READ
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 5734M
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 0
[mysqldump]
quick
max_allowed_packet = 32M

2、从库配置文件。多个从库配置文件除了server-id不能一样其他都一样。

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
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
[root@slavedb01 ~]# cat /etc/my.cnf
[client]
port= 3306
socket= /tmp/mysql.sock
[mysqld_multi]
# 指定相关命令的路径
mysqld     =  /usr/local/mysql/bin/mysqld_safe
mysqladmin =  /usr/local/mysql/bin/mysqladmin
##复制主库1的数据##
[mysqld2]
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
character-set-server = utf8mb4
#指定实例1的sock文件和pid文件
socket = /tmp/mysql.sock
pid-file=/data/mysql/mysql.pid
skip-name-resolv = 1
open_files_limit = 65535 
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 2048
tmp-table-size   = 32M
max-heap-table-size = 32M
query-cache-size  = 0
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 51
query_cache_size = 32M
tmp_table_size = 96M
max_heap_table_size = 96M
query_cache_type=1
#指定第一个实例的错误日志和慢查询日志路径
log-error=/data/logs/mysqld.log
slow_query_log = 1
slow_query_log_file = /data/logs/slow.log
long_query_time = 0.1
# BINARY LOGGING#
# 指定实例1的binlog和relaylog路径为/data/binlog目录
# 每个从库和每个实例的server_id不能一样。
server-id = 2
log-bin          = /data/binlog/mysql-bin
log-bin-index    =/data/binlog/mysql-bin.index
relay_log = /data/binlog/mysql-relay-bin
relay_log_index = /data/binlog/mysql-relay.index
master-info-file = /data/mysql/master.info
relay_log_info_file = /data/mysql/relay-log.info
read_only = 1
expire-logs-days = 14
sync_binlog = 1
#需要同步的库，如果不设置，默认同步所有库。
#replicate- do -db = xxx
#不需要同步的表
replicate-wild-ignore-table=mydb.sp_counter
#不需要同步的库
replicate-ignore-db = mysql,information_schema,performance_schema
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1024M
log_slave_updates =1
#binlog_format = row 
binlog_format = MIXED
relay_log_recovery = 1
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
#设置默认引擎为Myisam，下面这些参数一定要加上。
default -storage-engine=MyISAM
default -tmp-storage-engine=MYISAM
#关闭innodb引擎
skip-innodb
innodb = OFF
disable-innodb
#设置sql_mode模式为NO_AUTO_CREATE_USER
sql_mode = NO_AUTO_CREATE_USER
#关闭innodb引擎
loose-skip-innodb
loose-innodb-trx=0 
loose-innodb-locks=0 
loose-innodb-lock-waits=0 
loose-innodb-cmp=0 
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0 
loose-innodb-cmpmem=0 
loose-innodb-cmpmem-reset=0 
loose-innodb-buffer-page=0 
loose-innodb-buffer-page-lru=0 
loose-innodb-buffer-pool-stats=0 
loose-innodb-metrics=0 
loose-innodb-ft- default -stopword=0 
loose-innodb-ft-inserted=0 
loose-innodb-ft-deleted=0 
loose-innodb-ft-being-deleted=0 
loose-innodb-ft-config=0 
loose-innodb-ft-index-cache=0 
loose-innodb-ft-index-table=0 
loose-innodb-sys-tables=0 
loose-innodb-sys-tablestats=0 
loose-innodb-sys-indexes=0 
loose-innodb-sys-columns=0 
loose-innodb-sys-fields=0 
loose-innodb-sys-foreign=0 
loose-innodb-sys-foreign-cols=0
 
 
##复制主库2的数据##
[mysqld3]
port = 3307
basedir = /usr/local/mysql
datadir = /data/mysql
character-set-server = utf8mb4
#指定实例2的sock文件和pid文件
socket = /tmp/mysql3.sock
pid-file=/data/mysql/mysql3.pid
skip-name-resolv = 1
open_files_limit = 65535 
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 2048
tmp-table-size   = 32M
max-heap-table-size = 32M
query-cache-size  = 0
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 51
query_cache_size = 32M
tmp_table_size = 96M
max_heap_table_size = 96M
query_cache_type=1
log-error=/data/logs/mysqld3.log
slow_query_log = 1
slow_query_log_file = /data/logs/slow3.log
long_query_time = 0.1
# BINARY LOGGING #
# 这里一定要注意，不能把两个实例的binlog和relaylog放到同一个目录，
# 这里指定实例2的binlog日志为/data/binlog2目录
# 每个从库和每个实例的server_id不能一样。
server-id = 22
log-bin          = /data/binlog2/mysql-bin
log-bin-index    =/data/binlog2/mysql-bin.index
relay_log = /data/binlog2/mysql-relay-bin
relay_log_index = /data/binlog2/mysql-relay.index
master-info-file = /data/mysql/master3.info
relay_log_info_file = /data/mysql/relay-log3.info
read_only = 1
expire-logs-days = 14
sync_binlog = 1
#不需要复制的库
replicate-ignore-db = mysql,information_schema,performance_schema
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1024M
log_slave_updates =1
#binlog_format = row 
binlog_format = MIXED
relay_log_recovery = 1
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
#设置默认引擎为Myisam
default -storage-engine=MyISAM
default -tmp-storage-engine=MYISAM
#关闭innodb引擎
skip-innodb
innodb = OFF
disable-innodb
#设置sql_mode模式为NO_AUTO_CREATE_USER
sql_mode = NO_AUTO_CREATE_USER
#关闭innodb引擎，下面这些参数一定要加上。
loose-skip-innodb
loose-innodb-trx=0 
loose-innodb-locks=0 
loose-innodb-lock-waits=0 
loose-innodb-cmp=0 
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0 
loose-innodb-cmpmem=0 
loose-innodb-cmpmem-reset=0 
loose-innodb-buffer-page=0 
loose-innodb-buffer-page-lru=0 
loose-innodb-buffer-pool-stats=0 
loose-innodb-metrics=0 
loose-innodb-ft- default -stopword=0 
loose-innodb-ft-inserted=0 
loose-innodb-ft-deleted=0 
loose-innodb-ft-being-deleted=0 
loose-innodb-ft-config=0 
loose-innodb-ft-index-cache=0 
loose-innodb-ft-index-table=0 
loose-innodb-sys-tables=0 
loose-innodb-sys-tablestats=0 
loose-innodb-sys-indexes=0 
loose-innodb-sys-columns=0 
loose-innodb-sys-fields=0 
loose-innodb-sys-foreign=0 
loose-innodb-sys-foreign-cols=0
[mysqldump]
quick
max_allowed_packet = 32M
```

3、设置主库sql_mode,Mysql5.6默认需要在启动文件文件里面设置sql_mode才可以生效。

1
2
3
4
5
6
7
# cat /etc/init.d/mysqld
#other_args="$*"   # uncommon, but needed when called from an RPM upgrade action
            # Expected: "--skip-networking --skip-grant-tables"
            # They are not checked here, intentionally, as it is the resposibility
            # of the "spec" file author to give correct arguments only.
#将上面默认的#other_args开启后改为
other_args= "--sql-mode=NO_AUTO_CREATE_USER"

4、开启主库和从库

1
2
3
4
5
#主库
service mysqld start
#开启从库的二个实例
/usr/local/mysql/bin/mysqld_multi  start 2
/usr/local/mysql/bin/mysqld_multi  start 3

5、在两台主库上面分别授权复制账号

1
2
3
4
5
#需要授权三个从库的ip可以同步
mysql> GRANT REPLICATION SLAVE ON *.* TO rep@ '192.168.1.3'  IDENTIFIED BY  'rep123' ;
mysql> GRANT REPLICATION SLAVE ON *.* TO rep@ '192.168.1.4'  IDENTIFIED BY  'rep123' ;
mysql> GRANT REPLICATION SLAVE ON *.* TO rep@ '192.168.1.5'  IDENTIFIED BY  'rep123' ;
mysql> flush privileges;

6、在三个从库分别开启同步。

1
2
3
4
5
6
7
#进入第一个实例执行
$ mysql -S   /tmp/mysql .sock
mysql> CHANGE MASTER TO MASTER_HOST= '192.168.1.1' ,MASTER_USER= 'rep' ,MASTER_PASSWORD= 'rep123' ,MASTER_LOG_FILE= 'mysql-bin.000001' ,MASTER_LOG_POS=112;
 
#进入第二个实例执行
$ mysql -S   /tmp/mysql3 .sock
mysql> CHANGE MASTER TO MASTER_HOST= '192.168.1.2' ,MASTER_USER= 'rep' ,MASTER_PASSWORD= 'rep123' ,MASTER_LOG_FILE= 'mysql-bin.000001' ,MASTER_LOG_POS=112;

7、测试数据同步

    在二个主数据库分别建表和插入数据，到从库查看可以看到二个主库同步到同一个从库上面的所有数据。


8、在每台从库服务器上设置任务计划每分钟刷新第一个实例的表

1
2
# crontab -l
* /1  * * * * mysql -S  /tmp/mysql .sock -e  'flush tables;'



Mysql5.6多主一从的坑

1、Mysql5.6默认的引擎是innodb默认同步的时候一定要把主和从的sql_mode模式里面的NO_ENGINE_SUBSTITUTION这个参数关闭。如果不关闭innodb同步到从库上面的sql将会找不到innodb引擎导致同步失败。


2、在mysql5.6开启多实例的时候第一次启动的时候在你数据库的安装目录里面（/usr/local/mysql/)会生成my.cnf配置文件，默认会优先读取数据库安装目录里面的配置文件。导致多实例不生效。


参考链接：http://kms.lenovots.com/kb/article.php?id=9436




本文转自 张玉坡 51CTO博客，原文链接:http://blog.51cto.com/fighter/1725378

文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
数据库
关键词：
云数据库 RDS MySQL 版配置
CentOS配置
云数据库 RDS MySQL 版解决方案
CentOS解决方案
云数据库 RDS MySQL 版解决方案配置
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
科技小能手
目录
相关文章
kijlee
|
5月前
|
应用服务中间件 Linux 网络安全
Centos 8.0中Nginx配置文件和https正书添加配置
这是一份Nginx配置文件，包含HTTP与HTTPS服务设置。主要功能如下：1) 将HTTP（80端口）请求重定向至HTTPS（443端口），增强安全性；2) 配置SSL证书，支持TLSv1.1至TLSv1.3协议；3) 使用uWSGI与后端应用通信（如Django）；4) 静态文件托管路径设为`/root/code/static/`；5) 定制错误页面（404、50x）。适用于Web应用部署场景。
kijlee
674 87
蓝易云
|
1月前
|
Ubuntu 安全 关系型数据库
安装与配置MySQL 8 on Ubuntu，包括权限授予、数据库备份及远程连接指南
以上步骤提供了在Ubuntu上从头开始设置、配置、授权、备份及恢复一个基础但完整的MySQL环境所需知识点。
蓝易云
309 7
蓝易云
|
1月前
|
SQL 监控 关系型数据库
查寻MySQL或SQL Server的连接数，并配置超时时间和最大连接量
以上步骤提供了直观、实用且易于理解且执行的指导方针来监管和优化数据库服务器配置。务必记得，在做任何重要变更前备份相关配置文件，并确保理解每个参数对系统性能可能产生影响后再做出调节。
蓝易云
269 11
蓝易云
|
3月前
|
存储 Linux Apache
在CentOS上配置SVN至Web目录的自动同步
通过上述配置，每次当SVN仓库中提交新的更改时，`post-commit`钩子将被触发，SVN仓库的内容会自动同步到指定的Web目录，从而实现代码的连续部署。
蓝易云
168 16
蓝易云
|
3月前
|
NoSQL 安全 Linux
设置Redis在CentOS7上的自启动配置
这些步骤总结了在CentOS 7系统上设置Redis服务自启动的过程。这些命令提供了一个直接且明了的方式，确保Redis作为关键组件在系统启动时能自动运行，保障了依赖于Redis服务的应用的稳定性和可用性。
蓝易云
418 9
小lin
|
5月前
|
Linux
Centos6配置阿里云yum源报错
在CentOS 6配置阿里云Yum源时，可能出现EPEL仓库访问报错（404 Not Found）。解决方法：编辑`/etc/yum.repos.d/epel.repo`文件，将`enabled`和`gpgcheck`参数设为0 ``` 此设置可解决仓库无法访问的问题。
小lin
1420 29
蓝易云
|
5月前
|
Ubuntu 安全 Linux
CentOS与Ubuntu中防火墙配置命令集汇
有了这些，你就能遨游在 CentOS 和 Ubuntu 的海洋中，频繁地改变你的防火墙设置，快速地应对各种安全威胁，同时也能保证你的系统可以正常工作。出发吧，勇敢的编程者，随着这些命令集的涌动，扬帆起航，走向安全的网络世界！
蓝易云
179 5
蓝易云
|
5月前
|
关系型数据库 MySQL Java
安装和配置JDK、Tomcat、MySQL环境，以及如何在Linux下更改后端端口。
遵循这些步骤，你可以顺利完成JDK、Tomcat、MySQL环境的安装和配置，并在Linux下更改后端端口。祝你顺利！
蓝易云
402 11
蓝易云
|
6月前
|
Ubuntu 关系型数据库 MySQL
在Ubuntu 22.04上配置和安装MySQL
以上就是在Ubuntu 22.04上配置和安装MySQL的步骤。这个过程可能看起来有点复杂，但只要按照步骤一步步来，你会发现其实并不难。记住，任何时候都不要急于求成，耐心是解决问题的关键。
蓝易云
701 30

热门文章

最新文章

  • 1
    安装篇--CentOS 7 虚拟机安装
  • 2
    CentOS 7 yum 安装 MySQL教程
  • 3
    MySQL包安装 -- RHEL系列（离线RPM包安装MySQL）
  • 4
    MySQL介绍和MySQL包安装 -- RHEL系列（Yum资源库安装MySQL）
  • 5
    MySQL包安装 -- Debian系列（Apt资源库安装MySQL）
  • 6
    MySQL包安装 -- SUSE系列（离线RPM包安装MySQL）
  • 7
    当MySQL遇见AI：使用Vector扩展实现智能语义搜索
  • 8
    MySQL包安装 -- Debian系列（离线DEB包安装MySQL）
  • 9
    MySQL Docker 容器化部署全指南
  • 10
    【Docker】（3）学习Docker中 镜像与容器数据卷、映射关系！手把手带你安装 MySql主从同步 和 Redis三主三从集群！并且进行主从切换与扩容操作，还有分析 哈希分区 等知识点！
  • 1
    MySQL函数
    91
  • 2
    AnalyticDB MySQL新购页面融合升级，提供企业版购买选项
    486
  • 3
    Mysql 数据库基本操作
    112
  • 4
    POLARDB：新零售用户MySQL上云最佳选择
    503
  • 5
    MySQL (索引 & 事务)
    113
  • 6
    MySQL索引
    51
  • 7
    MySQL事务
    74
  • 8
    MySQL表的进阶知识（下）
    81
  • 9
    MySQL表的进阶知识（上）
    100
  • 10
    MySQL数据表的基础知识
    114

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 阿里云MySQL云数据库产品体系介绍
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL

    • 推荐镜像

    更多
  • centos
  • centos-vault
  • centos-altarch
    • 下一篇
    开箱即用-OSS无代理备份