percona-mysql-5.7二进制多实例安装
规划：
端口号 配置文件 备注
3306 /data/mysql/mysql_3306/my_3306.cnf
3307 /data/mysql/mysql_3307/my_3307.cnf
3308 /data/mysql/mysql_3308/my_3308.cnf
3309 /data/mysql/mysql_3309/my_3309.cnf
3310 /data/mysql/mysql_3310/my_3310.cnf
# percona-server二进制多实例安装
一、系统配置-物理机-可能会有差异
#手工的执行如下的动作,使之立刻生效:
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
echo deadline > /sys/block/sda/queue/scheduler
echo "" > /sys/block/sda/queue/read_ahead_kb
echo "" > /sys/block/sda/queue/nr_requests #减少预读：/sys/block/sda/queue/read_ahead_kb，默认128，调整为16
#增大队列：/sys/block/sda/queue/nr_requests，默认128，调整为512 echo "" > /sys/block/sda/queue/read_ahead_kb
echo "" > /sys/block/sda/queue/nr_requests #如果是使用普通SAS盘的话,使用elevator=deadline
#如果是使用SSD/FLASH卡的话,使用elevator=noop
echo noop > /sys/block/sda/queue/scheduler
echo deadline > /sys/block/sda/queue/scheduler #对于关闭透明大页的问题,也执行如下的操作:编辑 /etc/rc.local,添加如下内容 cat >> /etc/rc.local <<EOF
#echo noop > /sys/block/sda/queue/scheduler
echo deadline > /sys/block/sda/queue/scheduler
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
echo "" > /sys/block/sda/queue/read_ahead_kb
echo "" > /sys/block/sda/queue/nr_requests if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
EOF
cat >> /etc/sysctl.conf <<EOF
fs.file-max=655360
fs.aio-max-nr = 1048576
kernel.sem = 5050 646400 5050 128
kernel.shmmax = 137438953472
kernel.shmall = 4294967296
kernel.shmmni = 4096
net.ipv4.ip_local_port_range = 9000 65500
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.core.netdev_max_backlog = 32768
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_max_syn_backlog = 32768
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_timestamps = 0
net.ipv4.conf.default.accept_source_route = 0
vm.swappiness=1
EOF
# 使其生效
sysctl -p
# 安装依赖包
yum -y install gcc gcc-c++ make autoconf automake ncurses-devel bison ncurses cmake libaio libaio-devel boost
# 基本初始化 #
mkdir -p /opt/mysql
cd /opt/mysql/
wget https://www.percona.com/downloads/Percona-Server-LATEST/Percona-Server-5.7.20-18/binary/tarball/Percona-Server-5.7.20-18-Linux.x86_64.ssl101.tar.gz
tar -zxf Percona-Server-5.7.20-18-Linux.x86_64.ssl101.tar.gz
cd /usr/local/
ln -s /opt/mysql/Percona-Server-5.7.20-18-Linux.x86_64.ssl101 mysql
# 创建所需要的目录：
mkdir -p /data/mysql/mysql_3306/{data,logs,tmp}
mkdir -p /data/mysql/mysql_3307/{data,logs,tmp}
mkdir -p /data/mysql/mysql_3308/{data,logs,tmp}
mkdir -p /data/mysql/mysql_3309/{data,logs,tmp}
mkdir -p /data/mysql/mysql_3310/{data,logs,tmp}
# 更改权限
groupadd mysql
useradd -g mysql mysql -d /home/mysql -s /sbin/nologin
# 配置文件： vim /data/mysql/mysql_3306/my_3306.cnf 详见附录
# 附录：
cat >/data/mysql/mysql_3306/my_3306.cnf <<EOF
#my.cnf
[client]
port = 3306
socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock
[mysql]
prompt="\\u@\\h:\p \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql_3306/data/query.log
no-auto-rehash
[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
port = 3306
socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/mysql_3306/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
skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log
log-error = /data/mysql/mysql_3306/logs/mysql_3306_error.log
log_error_verbosity = 3
pid-file = mysql_3306.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1
#binlog
binlog_format = row
server-id = 623306
log-bin = /data/mysql/mysql_3306/logs/mysql-bin
log-bin-index = /data/mysql/mysql_3306/logs/mysql-bin.index
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 1
expire_logs_days = 90
#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062
explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
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 = 10G
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
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
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay-log-info-repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
# Two-Master configure
#server-1
#auto-increment-offset = 1
#auto-increment-increment = 2
#server-2
#auto-increment-offset = 2
#auto-increment-increment = 2
slave_preserve_commit_order = 1
slave_transaction_retries = 128
log_timestamps = system
show_compatibility_56 = on
slave_parallel_workers = 16
slave_parallel_type = LOGICAL_CLOCK
# group replication
##log-bin = mysql
##server-id = 613306
##gtid_mode = ON
##enforce_gtid_consistency = ON
##master_info_repository = TABLE
##relay-log-info-repository = TABLE
##binlog_checksum = NONE
##log_slave_updates = ON
##binlog_format = row
##transaction_write_set_extraction=XXHASH64
##loose-group_replication_group_name = '3db33b36-0e51-409f-a61d-c99756e90154'
##loose-group_replication_start_on_boot = off
##loose-group_replication_local_address= "10.0.0.62:23306" # 不能超过5位数字
##loose-group_replication_group_seeds= "10.0.0.62:23306,10.0.0.62:23307,10.0.0.62:23308" # 不能超过5位数字
##loose-group_replication_bootstrap_group= off
# loose-group_replication_single_primary_mode=FALSE ###本次搭建的是mutil_mode
# loose-group_replication_enforce_update_everywhere_checks= TRUE
[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so
nice=-19
open-files-limit=65535
EOF
cp /data/mysql/mysql_3306/my_3306.cnf /data/mysql/mysql_3307/my_3307.cnf
cp /data/mysql/mysql_3306/my_3306.cnf /data/mysql/mysql_3308/my_3308.cnf
cp /data/mysql/mysql_3306/my_3306.cnf /data/mysql/mysql_3309/my_3309.cnf
cp /data/mysql/mysql_3306/my_3306.cnf /data/mysql/mysql_3310/my_3310.cnf
chown -R mysql.mysql /data/mysql/
sed -i 's/3306/3307/g' /data/mysql/mysql_3307/my_3307.cnf
sed -i 's/3306/3308/g' /data/mysql/mysql_3308/my_3308.cnf
sed -i 's/3306/3309/g' /data/mysql/mysql_3309/my_3309.cnf
sed -i 's/3306/3310/g' /data/mysql/mysql_3310/my_3310.cnf
#1、修改server-id
sed -i 's/623306/1413306/g' /data/mysql/mysql_3306/my_3306.cnf
sed -i 's/623307/1413307/g' /data/mysql/mysql_3307/my_3307.cnf
sed -i 's/623308/1413308/g' /data/mysql/mysql_3308/my_3308.cnf
sed -i 's/623309/1413309/g' /data/mysql/mysql_3309/my_3309.cnf
sed -i 's/623310/1413310/g' /data/mysql/mysql_3310/my_3310.cnf
#2、修改server-id
sed -i 's/623306/1423306/g' /data/mysql/mysql_3306/my_3306.cnf
sed -i 's/623307/1423307/g' /data/mysql/mysql_3307/my_3307.cnf
sed -i 's/623308/1423308/g' /data/mysql/mysql_3308/my_3308.cnf
sed -i 's/623309/1423309/g' /data/mysql/mysql_3309/my_3309.cnf
sed -i 's/623310/1423310/g' /data/mysql/mysql_3310/my_3310.cnf
# 初始化
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --initialize-insecure &
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3307/my_3307.cnf --initialize-insecure &
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3308/my_3308.cnf --initialize-insecure &
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3309/my_3309.cnf --initialize-insecure &
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3310/my_3310.cnf --initialize-insecure &
# 启动
# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &
# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3307/my_3307.cnf &
# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3308/my_3308.cnf &
# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3309/my_3309.cnf &
# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3310/my_3310.cnf &
# 启动推荐做法
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3307/my_3307.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3308/my_3308.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3309/my_3309.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3310/my_3310.cnf &
# 进入mysql
/usr/local/mysql/bin/mysql -uroot -p -P3306 -S /data/mysql/mysql_3306/tmp/mysql_3306.sock
/usr/local/mysql/bin/mysql -uroot -p -P3307 -S /data/mysql/mysql_3307/tmp/mysql_3307.sock
/usr/local/mysql/bin/mysql -uroot -p -P3308 -S /data/mysql/mysql_3308/tmp/mysql_3308.sock
/usr/local/mysql/bin/mysql -uroot -p -P3309 -S /data/mysql/mysql_3309/tmp/mysql_3309.sock
/usr/local/mysql/bin/mysql -uroot -p -P3310 -S /data/mysql/mysql_3310/tmp/mysql_3310.sock
set sql_log_bin = 0;
create user 'rpl_user'@'%';
grant replication slave on *.* to 'rpl_user'@'10.%' identified by 'BR8KLwXCKHgN';
update mysql.user set authentication_string=password('rootpwd2017') where user='root';
flush privileges;
set sql_log_bin = 1;
reset master ; reset slave all;
CHANGE MASTER TO MASTER_HOST='10.0.0.138',MASTER_USER='rpl_user',
MASTER_PASSWORD='BR8KLwXCKHgN',MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10,MASTER_AUTO_POSITION =1;
start slave;
show slave status\G;
# 设置便捷使用方式
/usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=3306_localhost_login \
--user=root --port=3306 --password --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sock
/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --login-path=3306_localhost_login
/usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=3307_localhost_login \
--user=root --port=3307 --password --socket=/data/mysql/mysql_3307/tmp/mysql_3307.sock
/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3307/my_3307.cnf --login-path=3307_localhost_login
/usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=3308_localhost_login \
--user=root --port=3308 --password --socket=/data/mysql/mysql_3308/tmp/mysql_3308.sock
/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3308/my_3308.cnf --login-path=3308_localhost_login
/usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=3309_localhost_login \
--user=root --port=3309 --password --socket=/data/mysql/mysql_3309/tmp/mysql_3309.sock
/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3309/my_3309.cnf --login-path=3309_localhost_login
/usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=3310_localhost_login \
--user=root --port=3310 --password --socket=/data/mysql/mysql_3310/tmp/mysql_3310.sock
/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3310/my_3310.cnf --login-path=3310_localhost_login
cat >>/root/.bashrc <<EOF
alias mysql.3306.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &'
alias mysql.3307.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3307/my_3307.cnf &'
alias mysql.3308.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3308/my_3308.cnf &'
alias mysql.3309.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3309/my_3309.cnf &'
alias mysql.3310.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3310/my_3310.cnf &'
alias mysql.3306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --login-path=3306_localhost_login'
alias mysql.3307.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3307/my_3307.cnf --login-path=3307_localhost_login'
alias mysql.3308.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3308/my_3308.cnf --login-path=3308_localhost_login'
alias mysql.3309.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3309/my_3309.cnf --login-path=3309_localhost_login'
alias mysql.3310.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_3310/my_3310.cnf --login-path=3310_localhost_login'
EOF
source /root/.bash_profile
#################################### gtid故障处理开始 ####################################
模拟在从库删除库，然后再主库删除该库，报如下错误
Last_SQL_Error: Error 'Can't drop database 'db1'; database doesn't exist' on query. Default database: 'db1'. Query: 'drop database db1'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 623306
Master_UUID: 11526eb0-fcbc-11e6-af7d-005056b937e2
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 170227 15:44:06
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 11526eb0-fcbc-11e6-af7d-005056b937e2:1-2
Executed_Gtid_Set: 11526eb0-fcbc-11e6-af7d-005056b937e2:1,
1760a7a5-fcbc-11e6-8f14-005056b90358:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
处理方法：
stop slave;
set gtid_next='11526eb0-fcbc-11e6-af7d-005056b937e2:2';
begin;
commit;
set gtid_next='automatic';
start slave;
show slave status\G;
#################################### gtid故障处理开始 ####################################