mysql5.6 主从

324242

主my.cnf
[client]
port = 3306
socket = /data/3306/data/mysql.sock
[mysqld]
user=msql
port=3306
bind-address=192.168.5.20
socket = /data/3306/data/mysql.sock.3306
pid-file = /data/3306/data/mysql.pid
basedir = /opt/mysql/mysql-5.6.29
datadir = /data/3306/data
server-id=1
log-bin=mysql-bin
log-bin-index= mysql-bin.index
binlog_format=row
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=192.168.5.20

# LOGGING
log_error=/data/3306/logs/mysql-error.log
slow_query_log_file= /data/3306/logs/mysql-slow.log
slow_query_log=1

character-sets-dir = /opt/mysql/mysql-5.6.29/share/charsets
back_log = 2000
max_connections = 1000
connect-timeout = 60
wait-timeout = 28800
net_buffer_length = 16384
max_allowed_packet = 64M
thread_stack = 192K
thread_cache_size = 20
thread_concurrency = 128
query_cache_size = 256M
query_cache_limit = 2M
query_cache_min_res_unit = 2

default-time-zone = system
character-set-server = utf8
default-storage-engine = InnoDB

tmp_table_size = 512M
max_heap_table_size = 512M

max_binlog_size = 1G
max_relay_log_size = 1G

[mysql]
disable-auto-rehash
default-character-set = utf8


从my.cnf
[client]
port = 3306
socket = /data/3306/data/mysql.sock
[mysqld]
user=msql
port=3306
bind-address=192.168.5.21
socket = /data/3306/data/mysql.sock.3306
pid-file = /data/3306/data/mysql.pid
basedir = /opt/mysql/mysql-5.6.29
datadir = /data/3306/data
server-id=2
log-bin=mysql-bin
log-bin-index= mysql-bin.index
binlog_format=row
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=192.168.5.21

# LOGGING
log_error=/data/3306/logs/mysql-error.log
slow_query_log_file= /data/3306/logs/mysql-slow.log
slow_query_log=1

character-sets-dir = /opt/mysql/mysql-5.6.29/share/charsets
back_log = 2000
max_connections = 1000
connect-timeout = 60
wait-timeout = 28800
net_buffer_length = 16384
max_allowed_packet = 64M
thread_stack = 192K
thread_cache_size = 20
thread_concurrency = 128
query_cache_size = 256M
query_cache_limit = 2M
query_cache_min_res_unit = 2

default-time-zone = system
character-set-server = utf8
default-storage-engine = InnoDB

tmp_table_size = 512M
max_heap_table_size = 512M

max_binlog_size = 1G
max_relay_log_size = 1G

[mysql]
disable-auto-rehash
default-character-set = utf8


注意事项
当设置隔离级别为读提交READ-COMMITED必须设置二进制日志格式为ROW
auto.cnf
sync_binlog = 1
innodb-safe-binlog

步骤
1.grant replication slave,replication client on *.* to repluser@'192.168.1.%'identified by 'replpass';
2.change master to master_host='192.168.1.20', master_user='repluser',master_password='replpass', master_auto_position=1;
start slave;
show slave status\G;
show processlist;
测试主从测试

GTID的局限性： (鉴于这些局限性，慎用)
1.GTID同步复制是基于事务。所以Myisam表不支持，这可能导致多个GTID分配给同一个事务。
2.CREATE TABLE ... SELECT语句不支持。因为该语句会被拆分成create table和insert两个事务，并且这个两个事务被分配了同一个GTID，这会导致insert被备库忽略掉。
3.不支持CREATE TEMPORARY TABLE、DROP TEMPORARY TABLE 临时表操作。
启用之后进行同步语句：
CHANGE MASTER TO MASTER_HOST="60.2.212.190", MASTER_PORT=3306, MASTER_USER="repl",MASTER_PASSWORD="H7RYbCkGHmm_P1XO", master_auto_position=1;
不启动的话同步语句和之前一样：
change master to
master_host="60.2.212.190",
master_user="repl",
master_password="H7RYbCkGHmm_P1XO",
master_port=3306,
master_log_file="binlog.000001",
master_log_pos=1287;
start slave;

binlog-row-image=minimal，这个选项允许应用程序只能对行的镜像数据进行复制，而不在关心行是否已经进行了DML操作。这提高了主从机器的复制吞吐量，减少了二进制日志所占用的磁盘空间、
网络资源和内存占用。
binlog_checksum = CRC32
slave_allow_batching = 1
master_verify_checksum = 1
slave_sql_verify_checksum = 1
binlog_rows_query_log_events = 1
这四个参数是启用binlog/relaylog的校验，防止日志出错
relay_log_purge = 1
relay_log_recovery = 1
这两个是启用relaylog的自动修复功能，避免

由于网络之类的外因造成日志损坏，主从停止。
master_info_repository = TABLE
relay_log_info_repository = TABLE
这两个参数会将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议用
alter table slave_master_info engine=innodb;
alter table slave_relay_log_info engine=innodb;
alter table slave_worker_info engine=innodb;
改为Innodb引擎，防止表损坏后自行修复