Mysql 5.7 Dual master replication

Software:

Host a ip:192.168.1.201

Host B ip:192.168.1.202

Operating system: Centos6.5-x86

MySQL version: mysql-5.7.12-linux-glibc2.5-x86_64.tar.gz

Install dependency package: Yum Install libaio-devel glibc perl–y

Close SELinux and iptables:

/etc/init.d/iptables stop && setenforce 0 && sed–i ' s/selinux=enforcing/selinux=disabled/'/etc/ Selinux/config

One, MySQL installation configuration

■ 1.Mysql Installation

_________________________________________________________

Groupadd mysqluseradd-g mysql-s/sbin/nologin-m Mysqltar XF mysql-5.7.12-linux-glibc2.5-x86_64.tar.gzmv mysql-5.7.12- Linux-glibc2.5-x86_64/usr/local/mysqlmkdir/usr/local/mysql/datachown-r root:mysql/usr/local/mysql/chown-r MySQL: Mysql/usr/local/mysql/data/>/etc/my.cnfcp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld

■ 2. Configure MySQL

_________________________________________________________

Vim/etc/init.d/mysqldbasedir=/usr/local/mysqldatadir=/usr/local/mysql/data

================================

Vim/etc/my.cnf

[mysqld]####### #basic  settings####### #server-id = 1     #另一台ID不同port  =  3306user = mysqlbind_address = 192.168.1.201    # Another IP is not autocommit = 0character_set_server=utf8mb4skip_name_resolve = 1max_connections  = 800max_connect_errors = 1000datadir = /usr/local/mysql/datatransaction_ isolation = read-committedexplicit_defaults_for_timestamp = 1join_buffer_size =  134217728tmp_table_size = 67108864tmpdir = /tmpmax_allowed_packet = 16777216sql_ mode =  "Strict_trans_tables,no_engine_substitution,no_zero_date,no_zero_in_date,error_for_division_ By_zero,no_auto_create_user "Interactive_timeout = 1800wait_timeout = 1800read_buffer_size  = 16777216read_rnd_buffer_size = 33554432sort_buffer_size = 33554432####### #log  settings####### #log_error  =&Nbsp;error.logslow_query_log = 1slow_query_log_file = slow.loglog_queries_not_using_ Indexes = 1log_slow_admin_statements = 1log_slow_slave_statements = 1log_ throttle_queries_not_using_indexes = 10expire_logs_days = 90long_query_time =  2min_examined_row_limit = 100####### #replication  settings####### #master_info_repository  =  tablerelay_log_info_repository = tablelog_bin = bin.logsync_binlog = 1gtid _mode = onenforce_gtid_consistency = 1log_slave_updatesbinlog_format = row  Relay_log = relay.logrelay_log_recovery = 1binlog_gtid_simple_recovery = 1slave_ Skip_errors = all# slaveslave-parallel-type = logical_clockslave-parallel-workers  = 0#slave-parallel-workers = 16####### #innodb  settings####### #innodb_buffer_pool_ size = 750m //7 of memory5% more suitable Innodb_buffer_pool_instances = 16innodb_buffer_pool_load_at_startup = 1innodb_buffer _pool_dump_at_shutdown = 1innodb_lru_scan_depth = 2000innodb_lock_wait_timeout =  10000innodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_flush_method  = o_directinnodb_flush_neighbors = 1innodb_log_file_size = 4ginnodb_log_buffer_size  = 16777216innodb_purge_threads = 4innodb_large_prefix = 1innodb_thread_ Concurrency = 64innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_sort_ buffer_size = 67108864 innodb_flush_log_at_trx_commit = 2innodb_read_io_threads  = 16innodb_write_io_threads = 16 ####### #semi  sync replication settings#### # # # #plugin_dir =/usr/local/mysql/lib/pluginplugin_load =  "RPL_SEMI_SYNC_MASTER=SEMISYNC_MASTER.SO;RPL _semi_sync_slave=semisync_slave.so "Loose_rpl_semi_sync_master_enabled = 1loose_rpl_semi_sync_slave_enabled = 1loose _rpl_semi_sync_master_timeout = 5000[mysqld-5.7]innodb_buffer_pool_dump_pct = 40innodb_ Page_cleaners = 4innodb_undo_log_truncate = 1innodb_max_undo_log_size = 2ginnodb _purge_rseg_truncate_frequency = 128binlog_gtid_simple_recovery=1log_timestamps=systemtransaction_ Write_set_extraction=murmur32show_compatibility_56=on

3. Initializing the Directory

5.7 has not been used The mysql_install_db has been initialized and has been changed to the following command.

_________________________________________________________

/usr/local/mysql/bin/mysqld--user=mysql--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data--initialize/ Etc/init.d/mysqld start

4. Set the password

5.7 More annoying, the initial password is not empty, it can be said that the security is higher.

Initialize the password in the initialization log

_________________________________________________________

grep "Temporary password"/usr/local/mysql/data/error.log2016-07-04t09:50:17.455227+08:00 1 [Note] A Temporary Password is generated for [email protected]: wlem;g+?&1ef/usr/local/mysql/bin/mysql-uroot-p #输入日志里的密码mysql > Alt Er user ' root ' @ ' localhost ' identified by ' 123456 ';mysql> flush privileges;

#修改密码

5. Set up data synchronization

#两台Mysql同时添加同步用户

mysql> create user ' repl ' @ ' 192.168.1.% ' identified by ' 123456 ';mysql> GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192. 168.1.% ';mysql> flush privileges;

Lock the table, flush the two machine data, and unlock it.

first see if Gtid is open

mysql> show global variables like  '%gtid% '; +--------------------------+-------+|  variable_name            | value | +--------------------------+-------+| enforce_gtid_consistency | on    | |  gtid_executed            |        | |  gtid_mode                 | on    | |  gtid_owned               |        | |  gtid_purged              |        |+--------------------------+-------+5 rows in set  ( 0.10 SEC)

#说明gtid功能已启动.

Gtid synchronization data no longer log the other side of the log file and location, with Master_auto_position=1 on the line, but you use the old method to view master's logfile and Logpos, synchronization is also possible.

#在两台Mysql上都运行, IP has to refer to each other

mysql> Change Master to master_host= ' 192.168.1.201 ', master_user= ' repl ', master_password= ' 123456 ', master_auto_position=1;

mysql> start slave;

show slave status\g;

#表示同步的文件和位置

Master_log_file:

Read_master_log_pos:4

#显示下面表示工作正常

Slave_io_running:yes

Slave_sql_running:yes

#表示当前同步的数据库

Replicate_do_db:nzabbix,szabbix

Error One:

mysql> start slave;

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

Workaround:

mysql> Reset Slave;

Error Two:

Last_io_error:fatal error:the slave I/O thread stops because master and slave have equal MySQL server IDs; These IDs must be different for replication to work (or the--replicate-same-server-id option must is used on slave but th is does do sense; Please check the manual before using it).

Workaround:

Modify the MY.CNF configuration file, slave_skip_errors = All

This article is from the "Fuqin Wine" blog, please make sure to keep this source http://szk5043.blog.51cto.com/8456440/1812480

Mysql 5.7 Dual master replication