Mysql-MGR集群
文章目录
1、集群规划
注意:本文档按照MGR单主多从模式进行搭建
节点规划
本示例地址以下,部署时候按照实际情况进行修改
hostname | IP | 节点规划 |
---|---|---|
zqt003 | 100.4.17.4 | Master(主节点) |
zqt005 | 100.4.17.5 | Slaver1(从节点1) |
zqt009 | 100.4.17.9 | Slaver2(从节点2) |
Mysql Server版本
本示例操作系统为:Centos7 x64
Mysql Server版本:8.0.21 (mysql-8.0.21-el7-x86_64.tar.gz)
下载地址:MySQL :: Download MySQL Community Server (Archived Versions)
2、Mysql部署(离线安装)
一)Server安装
注意:所有节点都需要部署
-
将mysql8_install.sh(安装脚本)、my_test.cnf(默认安装配置)、mysql-8.0.21-el7-x86_64.tar.gz(安装文件名)放在同一目录下,本示例放在/opt/mysql_install下
-
修改脚本里的实际的ip地址、端口、hosts对应的主机名
-
使用如下命令进行安装
/bin/bash mysql8_install.sh
-
按照提示输入
数据库服务名:例如 dataReport
数据库服务端口:默认3306,根据需要进行输入
MySQL serverId: 必须为纯数字,本示例采用ip地址后3位当成serverId,例如 100.4.17.4,那么本节点上serverId就取4174
innodb_buffer_pool_size大小:示例输入 2,2G大小,默认大小为128M
注:安装完成后my.cnf配置文件默认在/etc/目录下,文件名是以你的数据库名命名,例my_test.cnf,mysql.sock在/tmp目录下
-
更新root用户权限
#连接数据库,root密码默认123456 /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql_master.sock #切换database到mysql mysql>use mysql; mysql>update user set host='%' where user='root'; mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; #此步骤如果提示错误,再执行一遍 mysql>FLUSH PRIVILEGES;
-
使用navicat等客户端工具进行连接测试
二)配置主从复制
注意:只在从节点都需要部署
- 执行安装
/bin/bash mysql8_install.sh repl
三)搭建GMR
注意:先把3个节点MySQL实例启动后再开始搭建mgr,先在Primary节点上执行,再到Secondary节点上执行
-
执行安装
/bin/bash mysql8_install.sh mgr
-
查看安装错误日志
#错误日志目录:/data/mysql/test/log/err.log tailf -n 300 /data/mysql/test/log/err.log #关注错误信息
-
因为上一步部署过主从,需要重启一下主从
#连接数据库,root密码默认123456 /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql_master.sock #切换database到mysql mysql>use mysql; mysql>stop slave; mysql>reset master; mysql>reset slave;
-
节点加入白名单
#连接数据库,root密码默认123456 /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql_master.sock #切换database到mysql mysql>use mysql; mysql>STOP GROUP_REPLICATION; mysql>SET GLOBAL group_replication_ip_whitelist="100.4.17.5,100.4.17.4,100.4.17.9"; mysql>start GROUP_REPLICATION;
-
查看集群状态
mysql>select * from performance_schema.replication_group_members;
-
重点:一定要多看错误日志!!!!
附件:部署脚本
文件名: mysql8_install.sh
#!/bin/bash
#文件名: mysql8_install.sh
echo "正在安装MySQL软件......."
useradd mysql
useradd nagios
useradd zabbix
sleep 2
######--配置参数--######
mysql8_version=mysql-8.0.21-el7-x86_64.tar.gz
mysql8_version_dir=mysql-8.0.21-el7-x86_64
######--数据库同步复制用户--######
repl_user=repl
repl_passwd=sysrepl
#######################
######--数据库root密码--######
root_passwd=123456
#######################
######--数据库DBA管理用户--######
dba_user=admin
dba_passwd=123456
#######################
######--数据库8.0克隆用户--######
clone_user=clone_user
clone_passwd=123456
#######################
######--修改hosts文件--######
cat << EOF >> /etc/hosts
100.4.17.4 zqt003
100.4.17.5 zqt005
100.4.17.9 zqt009
EOF
#######################
######--Mysql MGR配置--######
mysql_port=3306
primary_ip=100.4.17.4
secondary1_ip=100.4.17.5
secondary2_ip=100.4.17.9
primary_port=33061
secondary1_port=33061
secondary2_port=33061
local_ip=100.4.17.4
local_port=33061
#############################
######MySQL主从复制同步#######
if [ "$1" = "repl" ]
then
while true
do
read -t 30 -p "输入你的主库IP: " master_ip
read -t 30 -p "输入你的主库端口号: " master_port
if [[ -z $master_ip || -z $master_port ]]
then
continue
else
echo ""
echo "主库IP是: $master_ip"
echo "主库端口号是: $master_port"
break
fi
done
/usr/local/mysql/bin/mysql -h127.0.0.1 -u"$dba_user" -p"$dba_passwd" -P"$master_port" --connect-expired-password -e "CHANGE MASTER TO MASTER_HOST='$master_ip',MASTER_USER='$repl_user',MASTER_PASSWORD='$repl_passwd',MASTER_PORT=$master_port,MASTER_AUTO_POSITION = 1,MASTER_CONNECT_RETRY=10; START SLAVE;"
echo "MySQL主从复制同步已经初始化完毕。"
exit 0
fi
################################################
######MySQL MGR安装#######
if [ "$1" = "mgr" ]
then
while true
do
read -t 30 -p "是Primary吗?是请输入yes,否输入no: " is_primary
if [[ -z $is_primary ]]
then
continue
else
if [ $is_primary == "yes" ] || [ $is_primary == "no" ]
then
break
else
echo "你输入一个错误的字符$is_primary,请重新输入..."
continue
fi
fi
done
if [ $is_primary == "yes" ]
then
/usr/local/mysql/bin/mysql -h127.0.0.1 -u"$dba_user" -p"$dba_passwd" -P"$mysql_port" --connect-expired-password -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so'; set persist group_replication_group_name = '3b12b5bd-f0c6-11e9-9778-000c2900afc6';set persist group_replication_local_address = '${local_ip}:${local_port}'; set persist group_replication_group_seeds = '${primary_ip}:${primary_port},${secondary1_ip}:${secondary1_port},${secondary2_ip}:${secondary2_port}';SET GLOBAL group_replication_bootstrap_group=ON; CHANGE MASTER TO MASTER_USER='$repl_user',MASTER_PASSWORD='$repl_passwd' FOR CHANNEL 'group_replication_recovery';START GROUP_REPLICATION;select sleep(5);select * from performance_schema.replication_group_members;SET GLOBAL group_replication_bootstrap_group=OFF;"
else
/usr/local/mysql/bin/mysql -h127.0.0.1 -u"$dba_user" -p"$dba_passwd" -P"$mysql_port" --connect-expired-password -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so'; set persist group_replication_group_name = '3b12b5bd-f0c6-11e9-9778-000c2900afc6';set persist group_replication_local_address = '${local_ip}:${local_port}'; set persist group_replication_group_seeds = '${primary_ip}:${primary_port},${secondary1_ip}:${secondary1_port},${secondary2_ip}:${secondary2_port}'; SET GLOBAL group_replication_bootstrap_group=OFF; CHANGE MASTER TO MASTER_USER='$repl_user',MASTER_PASSWORD='$repl_passwd' FOR CHANNEL 'group_replication_recovery';START GROUP_REPLICATION;select sleep(5);select * from performance_schema.replication_group_members;"
fi
echo "MySQL Mgr组复制已经初始化完毕。"
exit 0
fi
################################################
ps aux | grep 'mysql' | grep -v 'grep' | grep -v 'bash'
if [ $? -eq 0 ]
then
echo "MySQL进程已经启动,无需二次安装。"
exit 0
fi
if [ ! -d /usr/local/${mysql8_version_dir} ]
then
#yum install xz -y
tar -zxvf ${mysql8_version} -C /usr/local/
ln -s /usr/local/${mysql8_version_dir} /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql/
chown -R mysql.mysql /usr/local/mysql
else
ln -s /usr/local/${mysql8_version_dir} /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql/
chown -R mysql.mysql /usr/local/mysql
fi
while true
do
read -t 30 -p "输入你的数据库服务名: " dbname
read -t 30 -p "输入你的数据库端口号: " dbport
read -t 30 -p "输入MySQL serverId: " serverId
read -t 30 -p "输入innodb_buffer_pool_size大小,单位G: " innodb_bp_size
if [[ -z $dbname || -z $dbport || -z $serverId || -z $innodb_bp_size ]]
then
continue
else
echo "数据库服务名字是: $dbname"
echo "数据库端口是: $dbport"
echo "MySQL serverId: $serverId"
echo "BP大小是: $innodb_bp_size GB"
break
fi
done
sed "s/test/$dbname/g;s/3306/$dbport/;s/413306/$serverId/;/innodb_buffer_pool_size/s/1/$innodb_bp_size/" my_test.cnf > /etc/my_$dbname.cnf
DATA_DIR=/data/mysql/$dbname
[ ! -d $DATA_DIR ] && mkdir -p $DATA_DIR/{data,binlog,relaylog,tmp,slowlog,log}; touch $DATA_DIR/log/error.log; chown -R mysql.mysql /data/mysql/
if [ `ls -A $DATA_DIR/data/ | wc -w` -eq 0 ]
then
cd /usr/local/mysql
echo ""
echo "初始化MySQL数据目录......"
echo ""
bin/mysqld --defaults-file=/etc/my_$dbname.cnf --initialize --lower-case-table-names=1 --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/$dbname/data
sleep 2
bin/mysqld_safe --defaults-file=/etc/my_$dbname.cnf --user=mysql &
fi
while true
do
netstat -ntlp | grep $dbport
if [ $? -eq 1 ]
then
echo "MySQL启动中,稍等......"
sleep 5
continue
else
break
fi
done
ps aux | grep 'mysql' | grep -v 'grep' | grep -v 'bash'
if [ $? -eq 0 ]
then
echo "MySQL安装完毕。"
else
echo "MySQL安装失败。"
fi
###更改root账号随机密码
random_passwd=`grep 'temporary password' $DATA_DIR/log/error.log | awk -F 'root@localhost: ' '{print $2}'`
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p"$random_passwd" --connect-expired-password -e "set sql_log_bin=0;alter user root@'localhost' identified by '$root_passwd';"
echo "root账号随机密码更改完毕。"
###创建同步账号和管理员账号
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock --connect-expired-password -p"$root_passwd" -e "set sql_log_bin=0;create user '$repl_user'@'%' IDENTIFIED BY '$repl_passwd'; GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO '$repl_user'@'%'; create user '$dba_user'@'%' IDENTIFIED BY '$dba_passwd'; GRANT ALL on *.* to '$dba_user'@'%' WITH GRANT OPTION;"
sed -i -r "s/(PATH=)/\1\/usr\/local\/mysql\/bin:/" /root/.bash_profile
source /root/.bash_profile
echo "MySQL账号初始化完毕。"
###安装clone插件
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock --connect-expired-password -p"$root_passwd" -e "set sql_log_bin=0;INSTALL PLUGIN CLONE SONAME 'mysql_clone.so'; CREATE USER '$clone_user'@'%' IDENTIFIED BY '$clone_passwd';GRANT BACKUP_ADMIN,CLONE_ADMIN ON *.* TO '$clone_user'@'%';"
echo ""
echo "clone克隆插件安装完毕。"
附件:默认配置文件
可根据具体需要进行更改
文件名: my_test.cnf
[client]
port = 3306
socket = /tmp/mysql_test.sock
#default-character-set = utf8
prompt=(\\u@\\h) [\\d]>\\_
# The MySQL server
#########Basic##################
[mysqld]
##bind_address = 127.0.0.1
server-id = 413306
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/test/data
tmpdir = /data/mysql/test/tmp
socket = /tmp/mysql_test.sock
skip-external-locking
skip-name-resolve
##init_connect = 'SET global sql_mode = ""'
sql_mode = ''
default_authentication_plugin = mysql_native_password
activate_all_roles_on_login = 1
### Percona ###
#extra_port = 13306
#extra_max_connections = 100
###skip-networking
default-storage-engine = INNODB
character-set-server = utf8
wait_timeout = 3600
connect_timeout = 20
interactive_timeout = 3600
back_log = 500
#event_scheduler = ON
open_files_limit = 65535
#thread_handling = pool-of-threads
lower_case_table_names = 1
###### binlog ######
log-bin = /data/mysql/test/binlog/mysql-bin
binlog_format = ROW
binlog_checksum = NONE
binlog_transaction_dependency_tracking = WRITESET
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = on
log_slave_updates = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
max_binlog_size = 128M
binlog_cache_size = 1M
expire-logs-days = 7
#########replication#############
relay-log = /data/mysql/test/relaylog/relay-log
slave-net-timeout = 10
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_wait_no_slave = 1
#rpl_semi_sync_master_timeout = 1000
#rpl_semi_sync_slave_enabled = 1
#skip-slave-start
log_slave_updates = 1
relay_log_recovery = 1
#slave_skip_errors = 1062
read_only = 0
###### Mgr config ######
loose-transaction_write_set_extraction = XXHASH64
loose-group_replication_start_on_boot = ON ###是否随mysql启动Group Replication
loose-group_replication_bootstrap_group = OFF ###是否是Group Replication的引导节点,初次搭建集群的时候需要有一个节点设置为ON来启动Group Replication
###参数设置为ON,是为了标示以后加入集群的服务器都已这台服务器为基准。以后加入的就不需要进行设置
loose-group_replication_group_name = 850ce522-a85e-11e9-8ea3-48df3749e199
#loose-group_replication_local_address = 192.168.148.41:33061
#loose-group_replication_group_seeds = 192.168.148.41:33061,192.168.148.42:33072,192.168.148.39:33083
loose-group_replication_single_primary_mode = ON
loose-group_replication_exit_state_action = OFFLINE_MODE
loose-group_replication_transaction_size_limit = 150000000 ###默认143M事务大小,最大值2147483647(大约2G),当此系统变量设置为0时,该组接受的事务大小没有限制。
loose-group_replication_enforce_update_everywhere_checks = OFF ###在单主模式下设置为OFF,多主模式下设置为ON。
offline_mode = ON
###MHA ############
relay_log_purge = 1
###################
###parallel replication####
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 24
slave_preserve_commit_order = ON
##################################
#########slow log#############
slow_query_log = 1
slow_query_log_file = /data/mysql/test/log/mysql-slow.log
#log-slow-verbosity = query_plan,explain
long_query_time = 5
#########error log#############
log-error = /data/mysql/test/log/error.log
log_timestamps = system
#######per_thread_buffers############
max_connections=4100
max_user_connections=4000
max_connect_errors=100000000
max_allowed_packet = 256M
table_open_cache = 6144
table_definition_cache = 4096
sort_buffer_size = 128K
read_buffer_size = 128K
read_rnd_buffer_size = 128k
join_buffer_size = 128K
tmp_table_size = 64M
max_heap_table_size = 64M
bulk_insert_buffer_size = 32M
thread_cache_size = 64
#thread_concurrency = 32
thread_stack = 256K
##MyISAM##
key_buffer_size = 64M
myisam_sort_buffer_size = 8M
concurrent_insert=2
low_priority_updates=1
myisam_recover_options=BACKUP,FORCE
######### InnoDB #############
innodb_adaptive_hash_index = 0
innodb_autoinc_lock_mode = 2
innodb_numa_interleave = 1
innodb_sort_buffer_size = 32M
innodb_rollback_on_timeout = 1
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 16
innodb_lru_scan_depth = 2048
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 95
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
log_bin_trust_function_creators = 1
innodb_flush_method = O_DIRECT
#innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_file_per_table = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_use_native_aio = 1
innodb_io_capacity = 800
innodb_purge_threads = 16
innodb_purge_batch_size = 32
innodb_old_blocks_time = 1000
innodb_change_buffering = all
transaction_isolation = READ-COMMITTED
innodb_stats_on_metadata = 0
####### NEW ########
##innodb_kill_idle_transaction = 5
###### SSD #########
#innodb_flush_neighbors = 0
#innodb_log_block_size = 4096
####################
[mysqldump]
quick
max_allowed_packet = 128M
[mysql]
no-auto-rehash
#prompt=(\\u@\\h) [\\d]>\\_
prompt=(\\u@\\h) [\\d]>\\_
[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 256k
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 65535
#flush_caches = 1
#numa_interleave = 1
#malloc-lib = /usr/lib64/libjemalloc.so
3、常见问题汇总
-
hostname查看,修改
#查看主机名 hostname #修改主机名 vim /etc/hostname
-
查看错误日志
/data/mysql/test/数据库服务名称/log/err.log
-
数据库服务重启
#注意:my_slaver01.cnf以当前节点服务配置文件为准 /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_slaver01.cnf --user=mysql &
-
卸载MGR
#在mysql库中执行 mysql>uninstall plugin group_replication;