mysql5.7gtid主从复制_mysql5.7基于gtid的主从复制

两个节点：

mkdir -p /data/mysql-5721/data

mkdir -p /data/mysql-5721/innodb

mkdir -p /data/mysql-5721/replication

mkdir -p /data/mysql-5721/logs

mkdir -p /data/mysql-5721/conf

mkdir -p /data/mysql-5721/tmp

mkdir -p /data/mysql-5721/scripts

MASTER：

[client]

port=23306

socket=/data/mysql-5721/conf/mysql.sock

#The MySQL server

[mysqld]

server_id=0611

port=23306

user=mysql

socket=/data/mysql-5721/conf/mysql.sock

pid-file=/data/mysql-5721/conf/mysql.pid

basedir=/data/mysql-5721

datadir=/data/mysql-5721/data

tmpdir=/data/mysql-5721/tmp

open_files_limit=10240

explicit_defaults_for_timestamp

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

federated

secure_file_priv='/data/mysql-5721/tmp'

gtid-mode=on

enforce-gtid-consistency

character-set-server = utf8mb4

default_storage_engine = innodb

max_connections = 1000

max_connect_errors = 1000

#Buffer

max_allowed_packet=256M

max_heap_table_size=256M

net_buffer_length=8k

sort_buffer_size=2M

join_buffer_size=4M

read_buffer_size=2M

read_rnd_buffer_size=16M

table_open_cache = 1024

max_allowed_packet = 128M

#log

log-bin=/data/mysql-5721/binlog/mysql-bin

binlog_cache_size=32M

max_binlog_cache_size=512M

max_binlog_size=512M

binlog_format=mixed

log_output=FILE

log-error=/data/mysql-5721/logs/mysql-error.log

slow_query_log=1

slow_query_log_file=/data/mysql-5721/logs/slow_query.log

general_log=0

general_log_file=/data/mysql-5721/logs/general_query.log

expire-logs-days=14

relay-log=/data/mysql-5721/relaylog/mysql-relay-bin

relay-log-index=/data/mysql-5721/relaylog/mysql-relay-bin.index

master-info-file=/data/mysql-5721/conf/master.info

relay-log-info-file=/data/mysql-5721/conf/relay-log.info

#InnoDB

innodb_data_file_path=ibdata1:2048M:autoextend

innodb_log_file_size=512M

#replcation_slave

#rpl_semi_sync_slave_enabled=1

innodb_log_files_in_group=3

innodb_buffer_pool_size=10240M

innodb_file_per_table = 1

innodb_write_io_threads = 4

innodb_read_io_threads = 4

innodb_purge_threads = 2

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 16M

innodb_max_dirty_pages_pct = 80

innodb_lock_wait_timeout = 30

[mysql]

auto-rehash

prompt=\u@\d>\

#prompt=(\u@\h) [\d]>\

default-character-set=gbk

bin/mysqld --initialize --user=mysql --basedir=/data/mysql-5721 --datadir=/data/mysql-5721/data --explicit_defaults_for_timestamp

bin/mysql_ssl_rsa_setup --datadir=/mysql/data

cp support-files/mysql.server /etc/init.d/mysql

chkconfig --add mysql

chkconfig mysql on

service mysql start

mysql_home=/data/mysql-5721

PATH=$PATH:$mysql_home/bin

mkdir -p /data/mysql-5721/scripts

chown -R mysql:mysql /data/mysql-5721/scripts

PATH=$PATH:$mysql_home/scripts

source /etc/profile

lljsu?&so1wY

mysql -uroot -p'lljsu?&so1wY'

SET PASSWORD=PASSWORD('xxxxx');

flush privileges;

打包/data/mysql-5721下的所有文件拷贝到节点2，然后启动mysql,并修改server_id

创建mysql复制特性：

创建复制用户：mysql中的slave若想获取二进制日志，它是得主动连接master节点去请求数据。因为slave节点将关于master的配置都保存在master.info文件中，该文件明文记录连接master节点的所有配置，包括连接的用户名、密码。所以最好在主库单独建个复制账户保证安全性。

MASTER：

grant replication slave on *.* to 'repl' @'20.58.8.%' identified by 'xxxxxx';

FLUSH PRIVILEGES;

删除slave端data下的auto.cnf

rm /mysql/data/auto.cnf

SLAVE：

启动slave服务并配置slave到master的连接：

change master to master_host='20.58.8.61',master_port=23306,master_user='repl',master_password='xxxxxx',master_auto_position=1;

start slave;

测试：在从库上执行

show global variables like "%server%";

show master status;

create database jason;

create table jason.jason_v2(id int);