Centos7 Mysql 5.6 多主一從 解決方案與詳細(xì)配置
更新時間：2016年04月17日 11:59:29 作者：張玉坡
這篇文章主要介紹了Centos7 Mysql 5.6 多主一從 解決方案與詳細(xì)配置,需要的朋友可以參考下
業(yè)務(wù)場景：
公司幾個主要的業(yè)務(wù)已經(jīng)獨立，放在不同的數(shù)據(jù)庫服務(wù)器上面，但是有一個業(yè)務(wù)又需要關(guān)聯(lián)多個業(yè)務(wù)庫進行聯(lián)合查詢統(tǒng)計。這時候就需要將不同的業(yè)務(wù)庫數(shù)據(jù)同步到一臺從庫進行統(tǒng)計。根據(jù)Mysql主從同步原理使用多從一主的方案解決。主庫使用innodb引擎，從庫開啟多實例使用myisam引擎并將多個實例的數(shù)據(jù)同步到同一個目錄，并通過flush tables 在一個實例里面訪問其他實例的數(shù)據(jù)。
解決思路：
1、主數(shù)據(jù)庫使用Innodb引擎，并設(shè)置sql_mode為 NO_AUTO_CREATE_USER
2、從庫開啟多實例，將多個主庫里面的數(shù)據(jù)通過主從復(fù)制同步到同一個數(shù)據(jù)目錄。從庫的每個實例對應(yīng)一個主庫。多個實例使用同一個數(shù)據(jù)目錄。
3、從庫使用Myisam引擎，關(guān)閉從庫默認(rèn)的innodb引擎,Myisam引擎可以訪問同一個數(shù)據(jù)目錄里面其他實例的表。
4、從庫的每個實例需要執(zhí)行flush tables 才能看到其他實例表的數(shù)據(jù)變化，可以設(shè)置crontab任務(wù)計劃每分鐘在第一個實例刷新表，以便程序連接的默認(rèn)實例能看到表的實時變化。
5、設(shè)置主庫和從庫的sql_mode都為NO_AUTO_CREATE_USER，只有這樣主庫的innodb引擎的sql同步到從庫的時候才能執(zhí)行成功。
方案架構(gòu)圖：
環(huán)境說明：
主庫-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
實現(xiàn)步驟：（Mysql安裝步驟這里不在描述）
1、主數(shù)據(jù)庫配置文件，多個主庫配置文件除了server-id不能一樣其他都一樣。
[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 //這里使用的混合模式復(fù)制 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不能一樣其他都一樣。
[root@slavedb01 ~]# cat /etc/my.cnf [client] port= 3306 socket= /tmp/mysql.sock [mysqld_multi] # 指定相關(guān)命令的路徑 mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin ##復(fù)制主庫1的數(shù)據(jù)## [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 #指定第一個實例的錯誤日志和慢查詢?nèi)罩韭窂? 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 #需要同步的庫，如果不設(shè)置，默認(rèn)同步所有庫。 #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 #設(shè)置默認(rèn)引擎為Myisam，下面這些參數(shù)一定要加上。 default-storage-engine=MyISAM default-tmp-storage-engine=MYISAM #關(guān)閉innodb引擎 skip-innodb innodb = OFF disable-innodb #設(shè)置sql_mode模式為NO_AUTO_CREATE_USER sql_mode = NO_AUTO_CREATE_USER #關(guān)閉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 ##復(fù)制主庫2的數(shù)據(jù)## [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 #不需要復(fù)制的庫 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 #設(shè)置默認(rèn)引擎為Myisam default-storage-engine=MyISAM default-tmp-storage-engine=MYISAM #關(guān)閉innodb引擎 skip-innodb innodb = OFF disable-innodb #設(shè)置sql_mode模式為NO_AUTO_CREATE_USER sql_mode = NO_AUTO_CREATE_USER #關(guān)閉innodb引擎，下面這些參數(shù)一定要加上。 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、設(shè)置主庫sql_mode,Mysql5.6默認(rèn)需要在啟動文件文件里面設(shè)置sql_mode才可以生效。
# 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.
#將上面默認(rèn)的#other_args開啟后改為
other_args="--sql-mode=NO_AUTO_CREATE_USER"
4、開啟主庫和從庫
#主庫 service mysqld start #開啟從庫的二個實例 /usr/local/mysql/bin/mysqld_multi start 2 /usr/local/mysql/bin/mysqld_multi start 3
5、在兩臺主庫上面分別授權(quán)復(fù)制賬號
#需要授權(quán)三個從庫的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、在三個從庫分別開啟同步。
#進入第一個實例執(zhí)行 $ 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; #進入第二個實例執(zhí)行 $ 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、測試數(shù)據(jù)同步
在二個主數(shù)據(jù)庫分別建表和插入數(shù)據(jù)，到從庫查看可以看到二個主庫同步到同一個從庫上面的所有數(shù)據(jù)。
8、在每臺從庫服務(wù)器上設(shè)置任務(wù)計劃每分鐘刷新第一個實例的表
# crontab -l */1 * * * * mysql -S /tmp/mysql.sock -e 'flush tables;'
Mysql5.6多主一從的坑
1、Mysql5.6默認(rèn)的引擎是innodb默認(rèn)同步的時候一定要把主和從的sql_mode模式里面的NO_ENGINE_SUBSTITUTION這個參數(shù)關(guān)閉。如果不關(guān)閉innodb同步到從庫上面的sql將會找不到innodb引擎導(dǎo)致同步失敗。
2、在mysql5.6開啟多實例的時候第一次啟動的時候在你數(shù)據(jù)庫的安裝目錄里面（/usr/local/mysql/)會生成my.cnf配置文件，默認(rèn)會優(yōu)先讀取數(shù)據(jù)庫安裝目錄里面的配置文件。導(dǎo)致多實例不生效。
相關(guān)文章
基于Apache?Hudi在Google云構(gòu)建數(shù)據(jù)湖平臺的思路詳解
這篇文章主要介紹了基于Apache?Hudi在Google云構(gòu)建數(shù)據(jù)湖平臺,本文提供了有關(guān)如何使用上述工具構(gòu)建基本數(shù)據(jù)管道的基本介紹，需要的朋友可以參考下2022-04-04
Linux系統(tǒng)下操作Apache最基本的啟動停止和重啟命令
這篇文章主要介紹了Linux系統(tǒng)下操作Apache最基本的啟動停止和重啟命令,是網(wǎng)站搭建入門學(xué)習(xí)中的基礎(chǔ)知識,需要的朋友可以參考下2015-07-07
這篇文章主要介紹了詳解linux中nano命令，詳細(xì)的介紹了nano命令的用法，非常具有實用價值，需要的朋友可以參考下2017-09-09
本篇文章主要介紹了Linux定時任務(wù)Crontab詳解，具有一定的參考價值，感興趣的小伙伴們可以參考一下。2016-12-12
centos7 設(shè)置grub密碼及單用戶登錄實例代碼
這篇文章主要介紹了centos7 設(shè)置grub密碼及單用戶登錄實例代碼，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2018-11-11
sersync2 完全安裝配置說明（三） 插件基本配置和使用
sersync2 完全安裝配置說明（三） 插件基本配置和使用，需要的朋友可以參考下。2011-11-11
CentOS 8 正式發(fā)布 基于Red Hat Enterprise Linux 8
緊隨CentOS Linux 7.7發(fā)行版之后，CentOS Linux 8現(xiàn)已正式發(fā)布，新版本基于Red Hat Enterprise Linux 8.0源，這意味著它具有混合云時代的所有強大的新特性和增強功能2019-09-09
Linux查看分區(qū)文件系統(tǒng)類型的方法總結(jié)
這篇文章主要給大家總結(jié)介紹了關(guān)于Linux查看分區(qū)文件系統(tǒng)類型的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用linux系統(tǒng)具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-08-08
Linux里有五種IO模型：阻塞IO、非阻塞IO、多路復(fù)用IO、信號驅(qū)動式IO和異步IO，本文將詳解的給大家介紹這五種模型，感興趣的同學(xué)可以參考閱讀2023-05-05
詳解linux centos yum安裝LAMP環(huán)境
本篇文章主要介紹了linux centos yum安裝LAMP環(huán)境 ，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧。2016-12-12