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

linux  /  管理员 发布于 7年前   172

业务场景：

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

[root@masterdb01 ~]#cat /etc/my.cnf[client]port= 3306socket= /tmp/mysql.sock[mysqld]port = 3306basedir = /usr/local/mysqldatadir = /data/mysqlcharacter-set-server = utf8mb4default-storage-engine = InnoDBsocket = /tmp/mysql.sockskip-name-resolv = 1open_files_limit = 65535 back_log = 103max_connections = 512max_connect_errors = 100000table_open_cache = 2048tmp-table-size  = 32Mmax-heap-table-size = 32M#query-cache-type = 0query-cache-size = 0external-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 51query_cache_size = 32Mtmp_table_size = 96Mmax_heap_table_size = 96Mquery_cache_type=1log-error=/data/logs/mysqld.logslow_query_log = 1slow_query_log_file = /data/logs/slow.loglong_query_time = 0.1# BINARY LOGGING #server-id = 1log-bin     = /data/binlog/mysql-binlog-bin-index  =/data/binlog/mysql-bin.indexexpire-logs-days = 14sync_binlog = 1binlog_cache_size = 4Mmax_binlog_cache_size = 8Mmax_binlog_size = 1024Mlog_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_schemakey_buffer_size = 32Mread_buffer_size = 1Mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recovertransaction_isolation = REPEATABLE-READinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 5734Minnodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_data_file_path = ibdata1:1024M:autoextendinnodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 32Minnodb_log_file_size = 2Ginnodb_log_files_in_group = 2innodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_max_dirty_pages_pct = 50innodb_flush_method = O_DIRECTinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_lock_wait_timeout = 10innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodb_file_per_table = 1innodb_locks_unsafe_for_binlog = 0[mysqldump]quickmax_allowed_packet = 32M

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

[root@slavedb01 ~]# cat /etc/my.cnf[client]port= 3306socket= /tmp/mysql.sock[mysqld_multi]# 指定相关命令的路径mysqld   = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladmin##复制主库1的数据##[mysqld2]port = 3306basedir = /usr/local/mysqldatadir = /data/mysqlcharacter-set-server = utf8mb4#指定实例1的sock文件和pid文件socket = /tmp/mysql.sockpid-file=/data/mysql/mysql.pidskip-name-resolv = 1open_files_limit = 65535 back_log = 103max_connections = 512max_connect_errors = 100000table_open_cache = 2048tmp-table-size  = 32Mmax-heap-table-size = 32Mquery-cache-size = 0external-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 51query_cache_size = 32Mtmp_table_size = 96Mmax_heap_table_size = 96Mquery_cache_type=1#指定第一个实例的错误日志和慢查询日志路径log-error=/data/logs/mysqld.logslow_query_log = 1slow_query_log_file = /data/logs/slow.loglong_query_time = 0.1# BINARY LOGGING## 指定实例1的binlog和relaylog路径为/data/binlog目录# 每个从库和每个实例的server_id不能一样。server-id = 2log-bin     = /data/binlog/mysql-binlog-bin-index  =/data/binlog/mysql-bin.indexrelay_log = /data/binlog/mysql-relay-binrelay_log_index = /data/binlog/mysql-relay.indexmaster-info-file = /data/mysql/master.inforelay_log_info_file = /data/mysql/relay-log.inforead_only = 1expire-logs-days = 14sync_binlog = 1#需要同步的库，如果不设置，默认同步所有库。#replicate-do-db = xxx#不需要同步的表replicate-wild-ignore-table=mydb.sp_counter#不需要同步的库replicate-ignore-db = mysql,information_schema,performance_schemabinlog_cache_size = 4Mmax_binlog_cache_size = 8Mmax_binlog_size = 1024Mlog_slave_updates =1#binlog_format = row binlog_format = MIXEDrelay_log_recovery = 1key_buffer_size = 32Mread_buffer_size = 1Mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recover#设置默认引擎为Myisam，下面这些参数一定要加上。default-storage-engine=MyISAMdefault-tmp-storage-engine=MYISAM#关闭innodb引擎skip-innodbinnodb = OFFdisable-innodb#设置sql_mode模式为NO_AUTO_CREATE_USERsql_mode = NO_AUTO_CREATE_USER#关闭innodb引擎loose-skip-innodbloose-innodb-trx=0 loose-innodb-locks=0 loose-innodb-lock-waits=0 loose-innodb-cmp=0 loose-innodb-cmp-per-index=0loose-innodb-cmp-per-index-reset=0loose-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 = 3307basedir = /usr/local/mysqldatadir = /data/mysqlcharacter-set-server = utf8mb4#指定实例2的sock文件和pid文件socket = /tmp/mysql3.sockpid-file=/data/mysql/mysql3.pidskip-name-resolv = 1open_files_limit = 65535 back_log = 103max_connections = 512max_connect_errors = 100000table_open_cache = 2048tmp-table-size  = 32Mmax-heap-table-size = 32Mquery-cache-size = 0external-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 51query_cache_size = 32Mtmp_table_size = 96Mmax_heap_table_size = 96Mquery_cache_type=1log-error=/data/logs/mysqld3.logslow_query_log = 1slow_query_log_file = /data/logs/slow3.loglong_query_time = 0.1# BINARY LOGGING ## 这里一定要注意，不能把两个实例的binlog和relaylog放到同一个目录，# 这里指定实例2的binlog日志为/data/binlog2目录# 每个从库和每个实例的server_id不能一样。server-id = 22log-bin     = /data/binlog2/mysql-binlog-bin-index  =/data/binlog2/mysql-bin.indexrelay_log = /data/binlog2/mysql-relay-binrelay_log_index = /data/binlog2/mysql-relay.indexmaster-info-file = /data/mysql/master3.inforelay_log_info_file = /data/mysql/relay-log3.inforead_only = 1expire-logs-days = 14sync_binlog = 1#不需要复制的库replicate-ignore-db = mysql,information_schema,performance_schemabinlog_cache_size = 4Mmax_binlog_cache_size = 8Mmax_binlog_size = 1024Mlog_slave_updates =1#binlog_format = row binlog_format = MIXEDrelay_log_recovery = 1key_buffer_size = 32Mread_buffer_size = 1Mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recover#设置默认引擎为Myisamdefault-storage-engine=MyISAMdefault-tmp-storage-engine=MYISAM#关闭innodb引擎skip-innodbinnodb = OFFdisable-innodb#设置sql_mode模式为NO_AUTO_CREATE_USERsql_mode = NO_AUTO_CREATE_USER#关闭innodb引擎，下面这些参数一定要加上。loose-skip-innodbloose-innodb-trx=0 loose-innodb-locks=0 loose-innodb-lock-waits=0 loose-innodb-cmp=0 loose-innodb-cmp-per-index=0loose-innodb-cmp-per-index-reset=0loose-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]quickmax_allowed_packet = 32M```

3、设置主库sql_mode,Mysql5.6默认需要在启动文件文件里面设置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.#将上面默认的#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、在两台主库上面分别授权复制账号

#需要授权三个从库的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、在三个从库分别开启同步。

#进入第一个实例执行$ mysql -S /tmp/mysql.sockmysql> 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.sockmysql> 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、在每台从库服务器上设置任务计划每分钟刷新第一个实例的表

# 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配置文件，默认会优先读取数据库安装目录里面的配置文件。导致多实例不生效。