gtid主主

====================配置如下====================[client]port = 3306socket=/tmp/my3306.sock[mysql]no-auto-rehash[mysqld]user = mysqlport = 3306bind_address = *socket=/tmp/my3306.sockcharacter_set_server = utf8log-error = /home/mysql/3306/logs/my3306_error.logpid-file=/home/mysql/3306/logs/my3306.pidhomedir=/home/mysql/3306/databasedir=/opt/mysqldefault-storage-engine=InnoDB #MyISAMopen_files_limit = 10240autocommit = 1sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"secure_file_priv=‘‘lower_case_table_names=1skip-grantskip-name-resolve =on#说明：主库必须配置的参数#server-id （主从的server-id必须不同）、log_bin、log-slave-updates、gtid_mode、enforce_gtid_consistencyserver-id=1921685533gtid_mode=on #开启gtid模式enforce_gtid_consistency=on #强制gtid一致性，开启后对于特定create table不被支持log-slave-updates=1 #slave 更新是否记入日志skip_slave_start=1 #复制进程就不会随着数据库的启动而启动(想要随数据库启动而启动 注释掉)relay-log = /home/mysql/3306/binlog/relayrelay_log_purge = onlog-bin = /home/mysql/3306/binlog/binlogbinlog_cache_size = 32Mbinlog_format = ROW #MIXEDbinlog_row_image = FULLmax_binlog_cache_size = 2048Mmax_binlog_size = 512Mexpire_logs_days = 90key_buffer_size = 1024Mmax_allowed_packet = 1024Mtable_open_cache = 1024sort_buffer_size = 8Mread_buffer_size = 8Mread_rnd_buffer_size = 8Mjoin_buffer_size = 8Mmyisam_sort_buffer_size = 128Mthread_cache_size = 128query_cache_size= 128Mthread_concurrency = 8interactive_timeout = 1800wait_timeout = 1800max_connections = 1000max_user_connections = 1000max_connect_errors = 6000back_log = 600long_query_time=3slow_query_log=1slow_query_log_file=/home/mysql/3306/logs/my3306-slow-query.logskip_external_locking=1innodb_file_per_table = oninnodb_buffer_pool_size = 2048M innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 1注意：除了 server_id不一样外 其它的都一样。主库必须配置的参数：server-id （主从的server-id必须不同）、log_bin、log-slave-updates、gtid_mode、enforce_gtid_consistency从库必须配置的参数：双主read-only 是不需要配置的server-id、log_bin、relay-log、read-only、log-slave-updates===============================================1.启动数据库2.创建同步账号grant replication slave on *.* to ‘slave‘@‘%‘ identified by ‘123123‘;3.备份数据库(-A 备份所有库，该备份文件会包含一条语句需要注意set @@global.gtid_purged="a57a3eb0-5d40-11e6-9609-080027f7e774:1-3")mysqldump -uroot --default-character-set=utf8 -p -R --opt --triggers --hex-blob --single-transaction -A -S /tmp/my31306.sock > /tmp/back.sql4.另外一台恢复数据库mysql -uroot --default-character-set=utf8 -p < /data/tmp.sql(指定字符集恢复)以下操作两台都需要操作。5. 停止slavestop slave;6.进行同步 CHANGE TO 操作（）change master to master_host="192.168.1.100",master_port=3306,master_user=‘repl‘,master_password=‘repl4slave‘,master_auto_position=1;#参数解释：MASTER_HOST : 设置要连接的主服务器的ip地址MASTER_USER : 设置要连接的主服务器的用户名master_port：设置要连接的主服务器的端口MASTER_PASSWORD : 设置要连接的主服务器的密码MASTER_AUTO_POSITION : GTID模式，基于事务ID复制7. 启动slavestart slave8.查看slave状态 （Slave_IO_Running:YES 并且Slave_SQL_Running: Yes ）show slave status\G;

 slave常用命令

show master status;show slave status\G;stop slave;start slave;