MySQL主主复制

432edw

   MySQL主主复制结构区别于主从复制结构。在主主复制结构中，两台服务器的任何一台上面的数据库存发生了改变都会同步到另一台服务器上，这样两台服务器互为主从，并且都能向外提供服务。

一、搭建环境描述：
主机A IP：192.168.1.201
主机B IP：192.168.1.202
操作系统：centos6.5-x86
Mysql版本：mysql-5.7.12-linux-glibc2.5-x86_64.tar.gz
安装所需依赖包：yum install  libaio-devel   –y

关闭SElinux及IPtables。

二、配置主主复制：
1、修改配置文件
vim /etc/my.cnf

[size=1em][mysqld] [size=1em]########basic settings######## [size=1em]server-id = 1    #另一台ID不同 [size=1em]port = 3306 [size=1em]user = mysql [size=1em]bind_address = 192.168.1.201    #另一台IP不用 [size=1em]autocommit = 0 [size=1em]character_set_server=utf8mb4 [size=1em]skip_name_resolve = 1 [size=1em]max_connections = 800 [size=1em]max_connect_errors = 1000 [size=1em]datadir = /usr/local/mysql/data [size=1em]transaction_isolation = READ-COMMITTED [size=1em]explicit_defaults_for_timestamp = 1 [size=1em]join_buffer_size = 134217728 [size=1em]tmp_table_size = 67108864 [size=1em]tmpdir = /tmp [size=1em]max_allowed_packet = 16777216 [size=1em]sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER" [size=1em]interactive_timeout = 1800 [size=1em]wait_timeout = 1800 [size=1em]read_buffer_size = 16777216 [size=1em]read_rnd_buffer_size = 33554432 [size=1em]sort_buffer_size = 33554432 [size=1em]########log settings######## [size=1em]log_error = error.log [size=1em]slow_query_log = 1 [size=1em]slow_query_log_file = slow.log [size=1em]log_queries_not_using_indexes = 1 [size=1em]log_slow_admin_statements = 1 [size=1em]log_slow_slave_statements = 1 [size=1em]log_throttle_queries_not_using_indexes = 10 [size=1em]expire_logs_days = 90 [size=1em]long_query_time = 2 [size=1em]min_examined_row_limit = 100 [size=1em]########replication settings######## [size=1em]master_info_repository = TABLE [size=1em]relay_log_info_repository = TABLE [size=1em]log_bin = bin.log [size=1em]sync_binlog = 1 [size=1em]gtid_mode = on [size=1em]enforce_gtid_consistency = 1 [size=1em]log_slave_updates [size=1em]binlog_format = row [size=1em]relay_log = relay.log [size=1em]relay_log_recovery = 1 [size=1em]binlog_gtid_simple_recovery = 1 [size=1em]slave_skip_errors = all [size=1em]# slave [size=1em]slave-parallel-type = LOGICAL_CLOCK [size=1em]slave-parallel-workers = 0 [size=1em]#slave-parallel-workers = 16 [size=1em]########innodb settings######## [size=1em]innodb_buffer_pool_size = 750M //内存的75%比较合适 [size=1em]innodb_buffer_pool_instances = 16 [size=1em]innodb_buffer_pool_load_at_startup = 1 [size=1em]innodb_buffer_pool_dump_at_shutdown = 1 [size=1em]innodb_lru_scan_depth = 2000 [size=1em]innodb_lock_wait_timeout = 10000 [size=1em]innodb_io_capacity = 4000 [size=1em]innodb_io_capacity_max = 8000 [size=1em]innodb_flush_method = O_DIRECT [size=1em]innodb_flush_neighbors = 1 [size=1em]innodb_log_file_size = 4G [size=1em]innodb_log_buffer_size = 16777216 [size=1em]innodb_purge_threads = 4 [size=1em]innodb_large_prefix = 1 [size=1em]innodb_thread_concurrency = 64 [size=1em]innodb_print_all_deadlocks = 1 [size=1em]innodb_strict_mode = 1 [size=1em]innodb_sort_buffer_size = 67108864 [size=1em]innodb_flush_log_at_trx_commit = 2 [size=1em]innodb_read_io_threads = 16 [size=1em]innodb_write_io_threads = 16 [size=1em]########semi sync replication settings######## [size=1em]plugin_dir=/usr/local/mysql/lib/plugin [size=1em]plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" [size=1em]loose_rpl_semi_sync_master_enabled = 1 [size=1em]loose_rpl_semi_sync_slave_enabled = 1 [size=1em]loose_rpl_semi_sync_master_timeout = 5000 [size=1em][mysqld-5.7] [size=1em]innodb_buffer_pool_dump_pct = 40 [size=1em]innodb_page_cleaners = 4 [size=1em]innodb_undo_log_truncate = 1 [size=1em]innodb_max_undo_log_size = 2G [size=1em]innodb_purge_rseg_truncate_frequency = 128 [size=1em]binlog_gtid_simple_recovery=1 [size=1em]log_timestamps=system [size=1em]transaction_write_set_extraction=MURMUR32 [size=1em]show_compatibility_56=on

2、初始化mysql数据库

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

3、设置密码
mysql5.7不允许密码为空了，所以必须得设置密码

grep "temporary password" /usr/local/mysql/data/error.log

2016-07-04T09:50:17.455227+08:00 1 [Note] A temporary password is generated for root@localhost: wLEM;g+?&1eF
/usr/local/mysql/bin/mysql -uroot -p  
#输入日志里的密码
mysql> alter user 'root'@'localhost' identified by '123456';
mysql> flush privileges;
4、配置数据同步

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

[size=1em]1 [size=1em]2 [size=1em]3

[size=1em]mysql> create user 'repl'@'192.168.1.%' identified by '123456'; [size=1em]mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%'; [size=1em]mysql> flush privileges;

锁一下表，追平两台机器数据，然后解锁。

先看看GTID是否打开

[size=1em]1 [size=1em]2 [size=1em]3 [size=1em]4 [size=1em]5 [size=1em]6 [size=1em]7 [size=1em]8 [size=1em]9 [size=1em]10 [size=1em]11

[size=1em]mysql> show global variables like '%gtid%'; [size=1em]+--------------------------+-------+ [size=1em]| Variable_name            | Value | [size=1em]+--------------------------+-------+ [size=1em]| enforce_gtid_consistency | ON    | [size=1em]| gtid_executed            |       | [size=1em]| gtid_mode                | ON    | [size=1em]| gtid_owned               |       | [size=1em]| gtid_purged              |       | [size=1em]+--------------------------+-------+ [size=1em]5 rows in set (0.10 sec)

#说明gtid功能已启动。

GTID同步数据不用再记录对方的log文件和位置了，用master_auto_position=1就行，不过你用老的方法查看master的logfile和logpos，同步也是可以的。

#在两台Mysql上都运行，IP得互指

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

过程中可能遇到的错误及解决方法：

错误一：

mysql>  start slave;

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

解决方法：

mysql> reset slave;

错误二：

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 be used on slave but this does not always make sense; please check the manual before using it).

解决方法：

修改my.cnf配置文件中，slave_skip_errors = all