Centos7 Mysql 5.6 多主一从 解决方案与详细配置
时间：2016-09-25业务场景：
公司几个主要的业务已经独立，放在不同的数据库服务器上面，但是有一个业务又需要关联多个业务库进行联合查询统计。这时候就需要将不同的业务库数据同步到一台从库进行统计。根据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= 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不能一样其他都一样。
[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才可以生效。
# 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.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、在每台从库服务器上设置任务计划每分钟刷新第一个实例的表
# 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配置文件，默认会优先读取数据库安装目录里面的配置文件。导致多实例不生效。
相关文章
CentOS7下编译安装tesseract-ocr流程什么是tesseract-ocr OCR(Optical Character Recognition):光学字符识别,是指对图片文件中的文字进行分析识别，获取的过程。 Tesseract：开源的OCR识别引擎，初- windows下WNMP(windows+nginx+mysql+php)配置1.下载 NGINX-1.8.0官网下载：http://nginx.org/en/download.html PHP5.6.15版本下载地址：http://windows.php.net/download/ Mysql5.5.19版本下载地址：http://
- configure: WARNING: unrecognized options: --with-mysql编译php后发生warning：configure:WARNING:unrecognizedoptions:--with-mysql 只是php不支持mysql模块 ./configure --help 查看configure的正确语法，重新编译ph
win2003 MySQL5安装图文教程与设置方法本教程共分八篇：系统安装与设置篇、软件安装与设置篇、文件及文件夹权限篇、系统服务篇、安全策略篇、系统组件篇、注册表篇、软件安全篇。本篇讨论的是第二篇软
Windows下Apache+MySQL+PHP运行环境的安装图文方法Windows下Apache+MySQL+PHP的安装方法，刚开始学习php的朋友最好能自己掌握下，如果已经会了不想花时间用在建设平台上面，可以直接用一些php运行环境包，例如php- Windows上安装Apache2、PHP5、MySQL5及与Resin配合实现多系统之笔者之前一直使用Resin作为WEB应用服务器，近来有项目需要在已有的JSP系统中整合phpwind系统，因此首次尝试在电脑中配置PHP的运行环境。
网站无法加载woff字体文件的解决办法？有客户反馈在安装网站后，woff、woff2字体无法加载，导致无法显示图标文件，这种情况要怎么解决呢？ 这是因为服务器IIS默认是没有SVG,WOFF,WOFF2这几个文件类型的扩展的，
网站服务器不支持.7z文件下载的解决方法7-Zip是一款号称有着现今最高压缩比的压缩软件，它不仅支持独有的7z文件格式，而且还支持各种其它压缩文件格式，其中包括ZIP, RAR, CAB, GZIP, BZIP2和TAR。此软件压缩的压