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

#### 业务场景：

公司几个主要的业务已经独立，放在不同的数据库服务器上面，但是有一个业务又需要关联多个业务库进行联合查询统计。这时候就需要将不同的业务库数据同步到一台从库进行统计。根据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同步到从库的时候才能执行成功。

#### 方案架构图：

#### 环境说明：

主库-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不能一样其他都一样。

<code>[root@masterdb01 ~]</code><code>#cat /etc/my.cnf</code>

<code>[client]</code>

<code>socket= </code><code>/tmp/mysql</code><code>.sock</code>

<code>[mysqld]</code>

<code>basedir = </code><code>/usr/local/mysql</code>

<code>datadir = </code><code>/data/mysql</code>

<code>character-</code><code>set</code><code>-server = utf8mb4</code>

<code>default-storage-engine = InnoDB</code>

<code>socket = </code><code>/tmp/mysql</code><code>.sock</code>

<code>skip-name-resolv = 1</code>

<code>open_files_limit = 65535 </code>

<code>max_connections = 512</code>

<code>max_connect_errors = 100000</code>

<code>table_open_cache = 2048</code>

<code>tmp-table-size = 32M</code>

<code>max-heap-table-size = 32M</code>

<code>#query-cache-type = 0</code>

<code>query-cache-size = 0</code>

<code>external-locking = FALSE</code>

<code>max_allowed_packet = 32M</code>

<code>sort_buffer_size = 2M</code>

<code>join_buffer_size = 2M</code>

<code>thread_cache_size = 51</code>

<code>query_cache_size = 32M</code>

<code>tmp_table_size = 96M</code>

<code>max_heap_table_size = 96M</code>

<code>query_cache_type=1</code>

<code>log-error=</code><code>/data/logs/mysqld</code><code>.log</code>

<code>slow_query_log = 1</code>

<code>slow_query_log_file = </code><code>/data/logs/slow</code><code>.log</code>

<code>long_query_time = 0.1</code>

<code># BINARY LOGGING #</code>

<code>server-</code><code>id</code> <code>= 1</code>

<code>log-bin = </code><code>/data/binlog/mysql-bin</code>

<code>log-bin-index =</code><code>/data/binlog/mysql-bin</code><code>.index</code>

<code>expire-logs-days = 14</code>

<code>sync_binlog = 1</code>

<code>binlog_cache_size = 4M</code>

<code>max_binlog_cache_size = 8M</code>

<code>max_binlog_size = 1024M</code>

<code>log_slave_updates</code>

<code>#binlog_format = row </code>

<code>binlog_format = MIXED </code><code>//</code><code>这里使用的混合模式复制</code>

<code>relay_log_recovery = 1</code>

<code>#不需要同步的表</code>

<code>replicate-wild-ignore-table=mydb.sp_counter</code>

<code>#不需要同步的库</code>

<code>replicate-ignore-db = mysql,information_schema,performance_schema</code>

<code>key_buffer_size = 32M</code>

<code>read_buffer_size = 1M</code>

<code>read_rnd_buffer_size = 16M</code>

<code>bulk_insert_buffer_size = 64M</code>

<code>myisam_sort_buffer_size = 128M</code>

<code>myisam_max_sort_file_size = 10G</code>

<code>myisam_repair_threads = 1</code>

<code>myisam_recover</code>

<code>transaction_isolation = REPEATABLE-READ</code>

<code>innodb_additional_mem_pool_size = 16M</code>

<code>innodb_buffer_pool_size = 5734M</code>

<code>innodb_buffer_pool_load_at_startup = 1</code>

<code>innodb_buffer_pool_dump_at_shutdown = 1</code>

<code>innodb_data_file_path = ibdata1:1024M:autoextend</code>

<code>innodb_flush_log_at_trx_commit = 2</code>

<code>innodb_log_buffer_size = 32M</code>

<code>innodb_log_file_size = 2G</code>

<code>innodb_log_files_in_group = 2</code>

<code>innodb_io_capacity = 4000</code>

<code>innodb_io_capacity_max = 8000</code>

<code>innodb_max_dirty_pages_pct = 50</code>

<code>innodb_flush_method = O_DIRECT</code>

<code>innodb_file_format = Barracuda</code>

<code>innodb_file_format_max = Barracuda</code>

<code>innodb_lock_wait_timeout = 10</code>

<code>innodb_rollback_on_timeout = 1</code>

<code>innodb_print_all_deadlocks = 1</code>

<code>innodb_file_per_table = 1</code>

<code>innodb_locks_unsafe_for_binlog = 0</code>

<code>[mysqldump]</code>

<code>quick</code>

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

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

<code>[root@slavedb01 ~]# cat /etc/my.cnf</code>

<code>socket= /tmp/mysql.sock</code>

<code>[mysqld_multi]</code>

<code># 指定相关命令的路径</code>

<code>mysqld = /usr/local/mysql/bin/mysqld_safe</code>

<code>mysqladmin = /usr/local/mysql/bin/mysqladmin</code>

<code>[mysqld2]</code>

<code>basedir = /usr/local/mysql</code>

<code>datadir = /data/mysql</code>

<code>character-set-server = utf8mb4</code>

<code>#指定实例1的sock文件和pid文件</code>

<code>socket = /tmp/mysql.sock</code>

<code>pid-file=/data/mysql/mysql.pid</code>

<code>#指定第一个实例的错误日志和慢查询日志路径</code>

<code>log-error=/data/logs/mysqld.log</code>

<code>slow_query_log_file = /data/logs/slow.log</code>

<code># BINARY LOGGING#</code>

<code># 指定实例1的binlog和relaylog路径为/data/binlog目录</code>

<code># 每个从库和每个实例的server_id不能一样。</code>

<code>server-id = 2</code>

<code>log-bin = /data/binlog/mysql-bin</code>

<code>log-bin-index =/data/binlog/mysql-bin.index</code>

<code>relay_log = /data/binlog/mysql-relay-bin</code>

<code>relay_log_index = /data/binlog/mysql-relay.index</code>

<code>master-info-file = /data/mysql/master.info</code>

<code>relay_log_info_file = /data/mysql/relay-log.info</code>

<code>#需要同步的库，如果不设置，默认同步所有库。</code>

<code>#replicate-</code><code>do</code><code>-db = xxx</code>

<code>log_slave_updates =1</code>

<code>binlog_format = MIXED</code>

<code>#设置默认引擎为Myisam，下面这些参数一定要加上。</code>

<code>default</code><code>-storage-engine=MyISAM</code>

<code>default</code><code>-tmp-storage-engine=MYISAM</code>

<code>#关闭innodb引擎</code>

<code>skip-innodb</code>

<code>innodb = OFF</code>

<code>disable-innodb</code>

<code>#设置sql_mode模式为NO_AUTO_CREATE_USER</code>

<code>sql_mode = NO_AUTO_CREATE_USER</code>

<code>loose-skip-innodb</code>

<code>loose-innodb-trx=0 </code>

<code>loose-innodb-locks=0 </code>

<code>loose-innodb-lock-waits=0 </code>

<code>loose-innodb-cmp=0 </code>

<code>loose-innodb-cmp-per-index=0</code>

<code>loose-innodb-cmp-per-index-reset=0</code>

<code>loose-innodb-cmp-reset=0 </code>

<code>loose-innodb-cmpmem=0 </code>

<code>loose-innodb-cmpmem-reset=0 </code>

<code>loose-innodb-buffer-page=0 </code>

<code>loose-innodb-buffer-page-lru=0 </code>

<code>loose-innodb-buffer-pool-stats=0 </code>

<code>loose-innodb-metrics=0 </code>

<code>loose-innodb-ft-</code><code>default</code><code>-stopword=0 </code>

<code>loose-innodb-ft-inserted=0 </code>

<code>loose-innodb-ft-deleted=0 </code>

<code>loose-innodb-ft-being-deleted=0 </code>

<code>loose-innodb-ft-config=0 </code>

<code>loose-innodb-ft-index-cache=0 </code>

<code>loose-innodb-ft-index-table=0 </code>

<code>loose-innodb-sys-tables=0 </code>

<code>loose-innodb-sys-tablestats=0 </code>

<code>loose-innodb-sys-indexes=0 </code>

<code>loose-innodb-sys-columns=0 </code>

<code>loose-innodb-sys-fields=0 </code>

<code>loose-innodb-sys-foreign=0 </code>

<code>loose-innodb-sys-foreign-cols=0</code>

<code>[mysqld3]</code>

<code>#指定实例2的sock文件和pid文件</code>

<code>socket = /tmp/mysql3.sock</code>

<code>pid-file=/data/mysql/mysql3.pid</code>

<code>log-error=/data/logs/mysqld3.log</code>

<code>slow_query_log_file = /data/logs/slow3.log</code>

<code># 这里一定要注意，不能把两个实例的binlog和relaylog放到同一个目录，</code>

<code># 这里指定实例2的binlog日志为/data/binlog2目录</code>

<code>server-id = 22</code>

<code>log-bin = /data/binlog2/mysql-bin</code>

<code>log-bin-index =/data/binlog2/mysql-bin.index</code>

<code>relay_log = /data/binlog2/mysql-relay-bin</code>

<code>relay_log_index = /data/binlog2/mysql-relay.index</code>

<code>master-info-file = /data/mysql/master3.info</code>

<code>relay_log_info_file = /data/mysql/relay-log3.info</code>

<code>#不需要复制的库</code>

<code>#设置默认引擎为Myisam</code>

<code>#关闭innodb引擎，下面这些参数一定要加上。</code>

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

<code># cat /etc/init.d/mysqld</code>

<code>#other_args="$*" # uncommon, but needed when called from an RPM upgrade action</code>

<code> </code><code># Expected: "--skip-networking --skip-grant-tables"</code>

<code> </code><code># They are not checked here, intentionally, as it is the resposibility</code>

<code> </code><code># of the "spec" file author to give correct arguments only.</code>

<code>#将上面默认的#other_args开启后改为</code>

<code>other_args=</code><code>"--sql-mode=NO_AUTO_CREATE_USER"</code>

4、开启主库和从库

<code>service mysqld start</code>

<code>#开启从库的二个实例</code>

<code>/usr/local/mysql/bin/mysqld_multi</code> <code>start 2</code>

<code>/usr/local/mysql/bin/mysqld_multi</code> <code>start 3</code>

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

<code>#需要授权三个从库的ip可以同步</code>

<code>mysql> GRANT REPLICATION SLAVE ON *.* TO rep@</code><code>'192.168.1.3'</code> <code>IDENTIFIED BY </code><code>'rep123'</code><code>;</code>

<code>mysql> GRANT REPLICATION SLAVE ON *.* TO rep@</code><code>'192.168.1.4'</code> <code>IDENTIFIED BY </code><code>'rep123'</code><code>;</code>

<code>mysql> GRANT REPLICATION SLAVE ON *.* TO rep@</code><code>'192.168.1.5'</code> <code>IDENTIFIED BY </code><code>'rep123'</code><code>;</code>

<code>mysql> flush privileges;</code>

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

<code>#进入第一个实例执行</code>

<code>$ mysql -S </code><code>/tmp/mysql</code><code>.sock</code>

<code>mysql> CHANGE MASTER TO MASTER_HOST=</code><code>'192.168.1.1'</code><code>,MASTER_USER=</code><code>'rep'</code><code>,MASTER_PASSWORD=</code><code>'rep123'</code><code>,MASTER_LOG_FILE=</code><code>'mysql-bin.000001'</code><code>,MASTER_LOG_POS=112;</code>

<code>#进入第二个实例执行</code>

<code>$ mysql -S </code><code>/tmp/mysql3</code><code>.sock</code>

<code>mysql> CHANGE MASTER TO MASTER_HOST=</code><code>'192.168.1.2'</code><code>,MASTER_USER=</code><code>'rep'</code><code>,MASTER_PASSWORD=</code><code>'rep123'</code><code>,MASTER_LOG_FILE=</code><code>'mysql-bin.000001'</code><code>,MASTER_LOG_POS=112;</code>

7、测试数据同步

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

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

<code># crontab -l</code>

<code>*</code><code>/1</code> <code>* * * * mysql -S </code><code>/tmp/mysql</code><code>.sock -e </code><code>'flush tables;'</code>

Mysql5.6多主一从的坑

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

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

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

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

继续阅读

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述

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

继续阅读

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