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 多主一從解決方案&詳細配置