CENTOS7 Mysql 5.6 Multiple Master one from solution with detailed configuration _linux

Business Scenario:

Several of the company's major businesses have been isolated and placed on different database servers, but one business needs to correlate multiple business libraries for joint query statistics. At this point, you need to synchronize different business library data to a single library for statistics. Based on MySQL master-slave synchronization principle to use more from a master solution. The main library uses the InnoDB engine to open multiple instances from the library using the MyISAM engine and synchronizing data from multiple instances to the same directory, and accessing data from other instances in one instance through flush tables.

Solution Idea:

1, the main database using the InnoDB engine, and set Sql_mode to No_auto_create_user
2, from the library to open a number of instances, the main library inside the data through the master-slave replication synchronization to the same data directory. Corresponds to a master library from each instance of the library. Multiple instances use the same data directory.
3. Use the MyISAM engine from the library, turn off the default InnoDB engine from the library, and the MyISAM engine can access tables of other instances in the same data directory.
4, from each instance of the library to perform flush tables to see the data changes in other instances table, you can set up the crontab task schedule refreshes the table at the first instance every minute so that the default instance of the program connection can see real-time changes to the table.
5, set the main library and Sql_mode from the library are all No_auto_create_user, only then the main library of the InnoDB engine SQL synchronization to the library from the time to perform successfully.

Scenario Architecture diagram:

Environment Description:

Main Library -1:192.168.1.1
Main Library -2:192.168.1.2
From the library -3:192.168.1.3
From the library -3:192.168.1.4
From the library -3:192.168.1.5

Implementation steps: (MySQL installation steps are not described here)

1, the primary database configuration file, multiple master library profiles except Server-id cannot be the same.

[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 = Max_connections = max_connect_errors 100000 E = 2048 Tmp-table-size = 32M max-heap-table-size = 32M #query-cache-type = 0 query-cache-size = 0 external-locking = FAL SE Max_allowed_packet = 32M Sort_buffer_size = 2M Join_buffer_size = 2M Thread_cache_size = Wuyi 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_que Ry_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 = Sync_binlog = 1 Binlog_cache_size = 4M Max_binlog_c Ache_size = 8M Max_binlog_size = 1024M Log_slave_updates #binlog_format = Row Binlog_format = MIXED//Mixed mode replication used here 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_bu Ffer_size = 128M Myisam_max_sort_file_size = 10G myisam_repair_threads = 1 Myisam_recover transaction_isolation = REPEATAB
Le-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 = Innodb_flush_method = O_direct Innodb_file_format = Barracu Da Innodb_file_format_max = Barracuda Innodb_locK_wait_timeout = Innodb_rollback_on_timeout = 1 Innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 Innodb_locks_u Nsafe_for_binlog = 0 [mysqldump] quick Max_allowed_packet = 32M

2, from the library configuration file. Multiple from the library configuration file except Server-id can not be the same as other.

[Root@slavedb01 ~]# cat/etc/my.cnf [client] port= 3306 socket=/tmp/mysql.sock [Mysqld_multi] # Specify the path to the related command mysqld =/US R/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-fil Skip-name-resolv = 1 Open_files_limit = 65535 Back_log = E=/data/mysql/mysql.pid Max_connections = max_connect_er Rors = 100000 Table_open_cache = 2048 Tmp-table-size = 32M Max-heap-table-size = 32M query-cache-size = 0 External-lockin G = FALSE Max_allowed_packet = 32M Sort_buffer_size = 2M Join_buffer_size = 2M Thread_cache_size = Wuyi Query_cache_size = 3 2M 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# # Specify instance 1 of BI Nlog and Relaylog paths are/data/binlog directory # each fromLibraries and server_id for each instance cannot be the same. Server-id = 2 Log-bin =/data/binlog/mysql-bin log-bin-index =/data/binlog/mysql-bin.index relay_log =/data/binlog/m Ysql-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 = Sync_binlog = 1 #需要同步的库, all libraries are synchronized by default if not set. #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, the following parameters must be added. 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 Ch Aracter-set-servER = utf8mb4 #指定实例2的sock文件和pid文件 socket =/tmp/mysql3.sock Pid-file=/data/mysql/mysql3.pid skip-name-resolv = 1 open_file  S_limit = 65535 Back_log = Max_connections = The 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 = Wuyi Query_cache_size = 32M Tmp_table_size = 96M max_heap_table_size = 96M quer y_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 # # Here It's important to note that the Binlog and Relaylog of the two instances cannot be placed in the same directory, # This specifies that the Binlog log for instance 2 is/data/binlog2 directory # each from the library and each
The server_id of the instance cannot be the same. Server-id = Log-bin =/data/binlog2/mysql-bin log-bin-index =/data/binlog2/mysql-bin.index relay_log =/data/binlo
G2/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 = Sync_binlog = 1 #不需要复制的库 replicate-ignore-db = Mysql,inform Ation_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 my Isam_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引擎, the following parameters must be added. 
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, set the main library sql_mode,mysql5.6 default need to set up in the boot file inside the Sql_mode before it can take effect.

# cat/etc/init.d/mysqld
#other_args = "$*"  # uncommon, but needed when called ' an RPM upgrade action
      # EXPE CTED: "--skip-networking--skip-grant-tables"
      # They are not checked here, intentionally, as it's the resposibility
   # of the "spec" file author to give correct arguments only.
#将上面默认的 #other_args opened and changed to
other_args= "--sql-mode=no_auto_create_user"

4, open the main library and from the library

#主库
service mysqld start
#开启从库的二个实例
/usr/local/mysql/bin/mysqld_multi start 2
/usr/local/mysql/ Bin/mysqld_multi Start 3

5, in the two main libraries, respectively authorized to copy the account number

#需要授权三个从库的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, in three from the library to open the synchronization respectively.

#进入第一个实例执行
$ 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, test data synchronization

Table and insert data from the two primary databases, and from the library view you can see that two main libraries sync to the same data from the top of the library.

8, on each set of tasks from the library server scheduled to refresh the first instance of the table

# crontab-l */1 * * * * *
mysql-s/tmp/mysql.sock-e ' flush tables;

Mysql5.6 The pit of many Masters

1, Mysql5.6 default engine is InnoDB the default synchronization must be the owner and from the Sql_mode mode inside the no_engine_substitution this parameter close. If you do not close InnoDB synchronization to the SQL from the top of the library, the InnoDB engine will not be found to cause the synchronization to fail.

2, when the mysql5.6 open multiple instances of the first time in your database installation directory (/usr/local/mysql/) will generate MY.CNF configuration files, the default will be the priority to read the database installation directory inside the configuration file. Causes multiple instances to not take effect.