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

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

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

#### 业务场景：

公司几个主要的业务已经独立，放在不同的数据库服务器上面，但是有一个业务又需要关联多个业务库进行联合查询统计。这时候就需要将不同的业务库数据同步到一台从库进行统计。根据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 版解决方案
云数据库 RDS MySQL 版配置
CentOS配置
CentOS解决方案
云数据库 RDS MySQL 版解决方案配置
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
科技小能手
目录
相关文章
库库的里昂
|
5天前
|
运维 负载均衡 关系型数据库
MySQL高可用解决方案演进：从主从复制到InnoDB Cluster架构
MySQL高可用解决方案演进：从主从复制到InnoDB Cluster架构
库库的里昂
30 9
神秘泣男子
|
6天前
|
关系型数据库 MySQL 数据库
MySQL集群 双主架构（配置命令）
MySQL集群 双主架构（配置命令）
神秘泣男子
23 1
神秘泣男子
|
7天前
|
Linux
CentOS 7 配置yum阿里源 （三步即可）
CentOS 7 配置yum阿里源 （三步即可）
神秘泣男子
90 1
你的小笔记本YY
|
11天前
|
SQL 关系型数据库 MySQL
【MySQL-3】图形化界面工具DataGrip安装＆配置＆使用
【MySQL-3】图形化界面工具DataGrip安装＆配置＆使用
你的小笔记本YY
19 0
你的小笔记本YY
|
11天前
|
关系型数据库 MySQL Linux
【MySQL-2】MySQL的下载＆安装＆启停＆配置环境变量【一条龙教程】
【MySQL-2】MySQL的下载＆安装＆启停＆配置环境变量【一条龙教程】
你的小笔记本YY
28 0
xin在这
|
13天前
|
分布式计算 DataWorks 关系型数据库
DataWorks操作报错合集之DataWorks集成实例绑定到同一个vpc下面，也添加了RDS的IP白名单报错：数据源配置有误，请检查，该怎么处理
DataWorks是阿里云提供的一站式大数据开发与治理平台，支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中，可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
xin在这
27 0
讓丄帝愛伱
|
13天前
|
Ubuntu 关系型数据库 MySQL
Ubuntu 20.04 + mysql8 安装以及配置大小写不敏感
Ubuntu 20.04 + mysql8 安装以及配置大小写不敏感
讓丄帝愛伱
29 0
xin在这
|
13天前
|
DataWorks 关系型数据库 MySQL
DataWorks产品使用合集之在DataWorks中配置RDS MySQL数据源的步骤如何解决
DataWorks作为一站式的数据开发与治理平台，提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案，帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述，涵盖数据处理的各个环节。
xin在这
27 0
景天科技苑
|
13天前
|
存储 关系型数据库 MySQL
linux安装MySQL8.0，密码修改权限配置等常规操作详解
linux安装MySQL8.0，密码修改权限配置等常规操作详解
景天科技苑
52 0
屿小夏
|
13天前
|
关系型数据库 MySQL 数据库
【MySQL】：超详细MySQL完整安装和配置教程
【MySQL】：超详细MySQL完整安装和配置教程
屿小夏
300 1

热门文章

最新文章

  • 1
    CentOS通过DNSpod实现动态域名
  • 2
    CentOS7(6.5)升级docker到较新版
  • 3
    centos 关闭selinux
  • 4
    centos 6.5 安装rsync
  • 5
    CentOS中iptables防火墙 开放80端口方法
  • 6
    CentOS7 yum lamp 虚拟主机配置 lamp各组件简单影响性能的参数调整--for 一定的环境需求
  • 7
    CentOS下MongoDB的升级
  • 8
    CentOS 6.3下安装加密的vsftpd
  • 9
    CentOS 5.5环境下安装配置Varnish
  • 10
    在虚拟机上安装centos7
  • 1
    [MySQL]SQL优化之索引的使用规则
    42
  • 2
    【mysql】—— 用户管理
    36
  • 3
    【mysql】—— 视图
    35
  • 4
    【mysql】—— 表的增删改查
    42
  • 5
    【mysql】—— 表的约束
    40
  • 6
    【MYSQL】—— MySQL 在 Centos 7环境安装
    70
  • 7
    MySQL模糊查询二三事
    28
  • 8
    MySQL的JOIN到底是怎么玩的
    109
  • 9
    MySQL怎样删除重复数据，只保留一条？
    40
  • 10
    【怒怼大厂面试官】你先说说知道哪些MySQL的高级特性
    55

    • 相关课程

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

    • 相关电子书

    更多
  • 百问百答-RDS （上）
  • CentOS Nginx PHP JAVA 多语言镜像使用手
  • CentOS Nginx PHP JAVA多语言镜像使用手册

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 基于ROS快速部署LNMP环境（CentOS 7）
  • 体验PolarDB MySQL多主集群
  • 云端MySQL的安装及应用

    • 推荐镜像

    更多
  • centos
  • centos-vault
  • centos-altarch
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）