安装

http://lansgg.blog.51cto.com/all/5675165

参考

http://www.iyunv.com/thread-19624-1-1.html

主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引擎，防止表损坏后自行修复

4.xtarbackup or mysqldump

xtarbackup

/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf --user=mysql & 

cat /data/mysql/xtrabackup_binlog_info

mysql-bin.008370        147669327

change master to master_host="172.28.29.152", master_port=3306, master_user='slave', master_password='123456', master_log_file='mysql-bin.008370', master_log_pos=147669327;