基于GTID的主从搭建

IP架构

主：172.17.100.106：3306

从：172.17.100.107：3306

        172.17.100.107：3307

前置工作

#关闭SElinux

sed -i 's/enforcing/disabled/g' /etc/sysconfig/selinux

setenforce=0

分别在主从库上部署MySQL

主库部署MySQL不再赘述，记录一下107从库部署多实例MySQL

防火墙打开3306，3307端口

配置106和107的互信

#3306实例

创建用户组

groupadd mysql

创建用户，设置mysql为禁止登陆系统，设置用户目录为/usr/local/mysql，并且设置后不创建该路径（-MN）

useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql

设置下载解压路径为/opt/mysql并创建

mkdir -p /opt/mysql

设置使用3306端口的实例所使用的数据文件路径为/data/mysql/mysql3306并创建

mkdir /data/mysql/mysql3306/{data,logs,tmp} -p

修改路径权限

chown -R mysql.mysql /opt/mysql

chown -R mysql.mysql /data

下载安装5.7.22版本

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

tar -xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

mv  mysql-5.7.22-linux-glibc2.12-x86_64 /opt/mysql/

做软链接到/usr/local/mysql

ln -s /opt/mysql/mysql-5.7.22-linux-glibc2.12-x86_64 /usr/local/mysql

chown -R mysql.mysql /usr/local/mysql

拷贝一份my3306.cnf（在后面会贴出）到/data/mysql/mysql3306/下

执行初始化

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize 

没有任何返回，说明初始化成功（最好看一下/data/mysql/mysql3306/data/error.log）

#3307实例

接下来，创建3307的相关路径

mkdir /data/mysql/mysql3307/{data,logs,tmp} -p

修改路径权限

chown -R mysql.mysql /data

拷贝一份my3307.cnf到/data/mysql/mysql3307/下

执行初始化

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf --initialize 

启动2个实例

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf &

成功的话会看到2个进程号

关于登陆密码

cat /data/mysql/mysql3307/data/error.log |grep 'temporary password'

可以得到临时密码，对密码进行修改后可以进行登陆操作

my3306.cnf如下

（注意其中的server-id，命名规则为ip最后3位+端口号，我这里就是1073306）

（此外前文提到还需要my3307.cnf，只需要sed把3306.cnf里关于3306的替换为3307即可）

------------------------------------------------------------------------------------

[client]

port            = 3306

socket          = /tmp/mysql3306.sock

[mysql]

prompt="\\u@\\h [\\d]>"

#pager="less -i -n -S"

#tee=/opt/mysql/query.log

no-auto-rehash

[mysqld]

#misc

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/mysql3306/data

port = 3306

socket = /tmp/mysql3306.sock

event_scheduler = 0

tmpdir = /data/mysql/mysql3306/tmp

#timeout

interactive_timeout = 300

wait_timeout = 300

#character set

character-set-server = utf8

open_files_limit = 65535

max_connections = 100

max_connect_errors = 100000

lower_case_table_names =1

#

explicit_defaults_for_timestamp=1

#symi replication

#rpl_semi_sync_master_enabled=1

#rpl_semi_sync_master_timeout=1000 # 1 second

#rpl_semi_sync_slave_enabled=1

#logs

log-output=file

slow_query_log = 1

slow_query_log_file = slow.log

log-error = error.log

pid-file = mysql.pid

long_query_time = 1

#log-slow-admin-statements = 1

#log-queries-not-using-indexes = 1

log-slow-slave-statements = 1

#binlog

#binlog_format = STATEMENT

binlog_format = row

server-id = 1073306

log-bin = /data/mysql/mysql3306/logs/mysql-bin

binlog_cache_size = 1M

max_binlog_size = 256M

sync_binlog = 0

expire_logs_days = 10

#procedure

log_bin_trust_function_creators=1

#GTID

gtid-mode = on

enforce-gtid-consistency=on

log-slave-updates=1

#relay log

skip_slave_start = 1

max_relay_log_size = 128M

relay_log_purge = 1

relay_log_recovery = 1

relay-log=relay-bin

relay-log-index=relay-bin.index

#slave-skip-errors=1032,1053,1062

#skip-grant-tables

#buffers & cache

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache = 2048

max_heap_table_size = 96M

sort_buffer_size = 128K

join_buffer_size = 128K

thread_cache_size = 200

query_cache_size = 0

query_cache_type = 0

query_cache_limit = 256K

query_cache_min_res_unit = 512

thread_stack = 192K

tmp_table_size = 96M

key_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 32M

#myisam

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

#innodb

innodb_buffer_pool_size = 100M

innodb_buffer_pool_instances = 1

innodb_data_file_path = ibdata1:100M:autoextend

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 8M

innodb_log_file_size = 100M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 50

innodb_file_per_table = 1

innodb_rollback_on_timeout

innodb_status_file = 1

innodb_io_capacity = 2000

transaction_isolation = READ-COMMITTED

innodb_flush_method = O_DIRECT

loose_tokudb_cache_size=100M

loose_tokudb_directio=ON

loose_tokudb_fsync_log_period=1000

loose_tokudb_commit_sync=0

-------------------------------------------------------------------------------------

双实例的MySQL已经部署完毕，接下来配置基于GTID的主从

配置主从互信（因为双实例不太好操作切换，后来我又增加了一台GTID03）

在GTID01端

echo '172.17.100.106  GTID01' >> /etc/hosts

echo '172.17.100.107  GTID02' >> /etc/hosts

在GTID02端

echo '172.17.100.106  GTID01' >> /etc/hosts

echo '172.17.100.107  GTID02' >> /etc/hosts

在GTID01端

ssh-keygen -P '' -f /root/.ssh/id_rsa -t rsa

ssh-copy-id -i /root/.ssh/id_rsa.pub GTID02

在GTID02端

ssh-keygen -P '' -f /root/.ssh/id_rsa -t rsa

ssh-copy-id -i /root/.ssh/id_rsa.pub GTID01

防火墙里配置2个node的ip可以互访(我这里配置了整个段可以互访)

iptables -I INPUT -s 172.17.100.0/24 -j ACCEPT

service iptables save

在主从库上均创建账号rpel

对其执行授权

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.17.100.%'

将主库逻辑导出

mysqldump -S /tmp/mysql3306.sock -uroot -pbeacon --master-data=2 --single-transaction -A > db_`date +%Y%m%d`.sql

将导出的sql导入到从库

mysql -uroot -pbeacon < db_20180607.sql

因为这里是测试环境，两个库均没有数据，所以搭建比较随意

在从库上进行配置

change master to master_host='172.17.100.106',master_port=3306,master_user='repl',master_password='beacon',master_auto_position=1;

在slave上show slave status ，确保是双yes证明搭建成功