Innodb cluster 利用组复制的 pxos 协议,保障数据一致性,组复制支持单主模式和多主模式,
本文推荐的高可用架构为单主模式下,基于mysqlrouter的 innodb cluster:
系统:centos 7.5
Mysql:8.0.12 二进制包
Mysqlshell: 8.0.12 rpm 包
Mysql router: 8.0.12 二进制包
192.168.181.101 my-manager1 Keepalived、MySQL-shell、MySQL-Router、MySQL-client
192.168.181.102 my-manager2 Keepalived、MySQL-shell、MySQL-Router、MySQL-client
192.168.181.103 mysql-1 MySQL服务端、MySQL-shell
192.168.181.104 mysql-2 MySQL服务端、MySQL-shell
192.168.181.105 mysql-3 MySQL服务端、MySQL-shell
172.16.50.191 uatvfc-db-route01
172.16.50.192 uatvfc-db-route02
172.16.50.193 uatvfc-db-route03
172.16.50.197 uatvfc-db-data01
172.16.50.198 uatvfc-db-data02
172.16.50.199 uatvfc-db-data03
yum -y install gcc glibc libaio
(1)、关闭SElinux
setenforce 0
#修改/etc/selinux/config
vim /etc/selinux/config
SELINUX=disabled
(2)、关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
cat>>/etc/sysctl.conf <<EOF
fs.aio-max-nr = 1048576
fs.file-max = 681574400
kernel.shmmax = 137438953472
kernel.shmmni = 4096
kernel.sem = 250 32000 100 200
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
EOF
cat>>/etc/security/limits.conf <<EOF
mysql soft nproc 65536
mysql hard nproc 65536
mysql soft nofile 65536
mysql hard nofile 65536
EOF
cat>>/etc/pam.d/login <<EOF
session required /lib64/security/pam_limits.so
session required pam_limits.so
EOF
注意:有的文档写成: session required /lib/security/pam_limits.so
会造成linux无法启动,需要用光盘--->rescue centos---->trouble shooting,进入shell,修改/etc/pam.d/login <<EOF,再重新启动.大坑.
cat>>/etc/profile<<EOF
if [ $USER = "mysql" ]; then
ulimit -u 16384 -n 65536
fi
EOF
source /etc/profile
cd /data
tar -xzvf mysql-8.0.12-el7-x86_64.tar.gz -C /usr/local
mv /usr/local/mysql-8.0.12-el7-x86_64 /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql
mkdir -p /data/mysql_3310_vfc/{data,log,binlog,conf,tmp}
chown -R mysql.mysql /data/mysql_3310_vfc
mkdir -p /data/mysql_3320_vfc/{data,log,binlog,conf,tmp}
chown -R mysql.mysql /data/mysql_3320_vfc
mkdir -p /data/mysql_3330_vfc/{data,log,binlog,conf,tmp}
chown -R mysql.mysql /data/mysql_3330_vfc
su - mysql
Vim /data/mysql_3310_vfc/conf/my.cnf
[mysqld]
lower_case_table_names = 1
user = mysql
server_id = 197
port = 3310
default-time-zone = '+08:00'
enforce_gtid_consistency = ON
gtid_mode = ON
binlog_checksum = none
default_authentication_plugin = mysql_native_password
datadir = /data/mysql_3310_vfc/data
pid-file = /data/mysql_3310_vfc/tmp/mysqld.pid
socket = /data/mysql_3310_vfc/tmp/mysqld.sock
tmpdir = /data/mysql_3310_vfc/tmp/
skip-name-resolve = ON
# open_files_limit does not take effect
open_files_limit = 65535
table_open_cache = 2000
#################innodb########################
innodb_data_home_dir = /data/mysql_3310_vfc/data
innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend
innodb_buffer_pool_size = 6000M
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 600
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8M
innodb_log_file_size = 200M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 85
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 32
innodb_file_per_table
innodb_rollback_on_timeout
innodb_undo_directory = /data/mysql_3310_vfc/data
innodb_log_group_home_dir = /data/mysql_3310_vfc/data
innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend
innodb_buffer_pool_size = 6000M
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 600
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8M
innodb_log_file_size = 200M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 85
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 32
innodb_file_per_table
innodb_rollback_on_timeout
innodb_undo_directory = /data/mysql_3310_vfc/data
innodb_log_group_home_dir = /data/mysql_3310_vfc/data
###################session###########################
join_buffer_size = 8M
key_buffer_size = 256M
bulk_insert_buffer_size = 8M
max_heap_table_size = 96M
tmp_table_size = 96M
read_buffer_size = 8M
sort_buffer_size = 2M
max_allowed_packet = 64M
read_rnd_buffer_size = 32M
############log set###################
log-error = /data/mysql_3310_vfc/log/mysqld.err
log-bin = /data/mysql_3310_vfc/binlog/binlog
log_bin_index = data/mysql_3310_vfc/binlog/binlog.index
max_binlog_size = 500M
slow_query_log_file = /data/mysql_3310_vfc/log/slow.log
slow_query_log = 1
long_query_time = 10
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = ON
log_output = FILE,TABLE
master_info_file = /data/mysql_3310_vfc/binlog/master.info
##########################mgr set##############################
mysqlx_port=33102
mysqlx_socket=/data/mysql_3310_vfc/tmp/mysqlx.sock
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.16.50.197:33101"
loose-group_replication_group_seeds= "172.16.50.197:33101,172.16.50.198:33101,172.16.50.199:33101"
loose-group_replication_bootstrap_group= off
loose-group_replication_ip_whitelist="172.16.50.0/24"
loose-group_replication_single_primary_mode = on
大写的注意: 多主模式存在很多限制和不确定性问题,不建议使用。具体原因和读写分离解决方案,看下文
mysqld --defaults-file=/data/mysql_3310_vfc/conf/my.cnf --initialize --user=mysql 日志中密码:8?_BO,8k.+DX
mysqld --defaults-file=/data/mysql_3320_vfc/conf/my.cnf --initialize --user=mysql 日志中密码:JN&MiMtb5hkj
mysqld --defaults-file=/data/mysql_3330_vfc/conf/my.cnf --initialize --user=mysql 日志中密码:v+-/pcu)M3zo
mysqladmin --defaults-file=my.cnf password 'your password';
mysql -uroot -p -S /data/mysql_3310_vfc/tmp/mysql.sock
mysql -uroot -p -S /data/mysql_3320_vfc/tmp/mysql.sock
mysql -uroot -p -S /data/mysql_3330_vfc/tmp/mysql.sock
alter user root@’localhost’ identified by 'your password';
mysql -uroot -p -S /data/mysql_3306_test/tmp/mysqld.sock
create user root@'127.0.0.1' identified by 'your password';
GRANT all ON *.* TO `root`@`127.0.0.1` WITH GRANT OPTION;
create user root@'172.16.50.197' identified by 'your password';
create user root@'172.16.50.198' identified by 'your password';
create user root@'172.16.50.199' identified by 'your password';
create user root@'172.16.50.191' identified by ‘your password’';
create user root@'172.16.50.192' identified by 'your password’;
create user root@'172.16.50.193' identified by ’your password‘;
关闭主库节点,打包,传递,解压.在做增加从库的复制是,已经对从库做了操作,比如增加账号等,在最开始操作时可以让其不记录日志
SET SQL_LOG_BIN=0;
DDL语句;
DML语句;
SET SQL_LOG_BIN=1;
注意修改 server-id 和删掉 data里面的auto.con
rpm -ivh mysql-shell-8.0.11-1.el7.x86_64.rpm
rpm -ivh mysql-router*.rpm
路由节点:这里有个小技巧,官方建议将路由节点部署在应用端,这样,应用可以直接使用 app_user@'127.0.0.1‘这样的用户进行访问
使用mysql命令
(1)、检查并配置实例(每个mysql节点)
mysqlsh --log-level=DEBUG3
##日志位置 `~/.mysqlsh/mysqlsh.log`
检查实例
dba.configureLocalInstance('root@node01:3306');
dba.chekInstanceConfiguration('root@node01:3306')
(2)、创建cluster集群(确认每个SQL节点的实例都完成上述实例配置并且验证成功)
在任意一台mysql实例节点执行以下命令:
mysqlsh
shell.connect('root@172.16.50.197:3310')
var cluster = dba.createCluster('qwCluster1');
如果创建成功输出的信息中会有类似“Cluster successfully created.”的语句
(3) 将另外两个节点加入到Cluster集群中
cluster.addInstance('root@node02:3306');
cluster.addInstance('root@172.16.50.199:3310');
cluster.addInstance('root@172.16.50.198:3320');
cluster.addInstance('root@172.16.50.199:3320');
cluster.addInstance('root@172.16.50.198:3330');
cluster.addInstance('root@172.16.50.199:3330');
dba.dropMetadataSchema() 清空集群
mysql> stop group_replication;
mysql> reset master; (清空日志,确保和从库的表没有冲突奥,)
mysql> reset slave
这部分内容主要涉及到几个系统表格,有点类似于 SQL SERVER中的DMV视图,详见下表。
库名 | 表名 | type | description |
---|---|---|---|
performance_schema | replication_group_members | 重要,常用 | 查看GROUP成员。 |
performance_schema | replication_group_member_stats | 重要,常用 | 当前SERVER在GROUP中的同步情况,查看applier通道的同步情况。 |
performance_schema | replication_connection_stats | 重要,常用 | 当前server中各个通道的使用情况,applier通道是一定有显示,recovery通道看是否使用过,如果有则显示,没有则不显示。 |
performance_schema | replication_applier_stats | 重要,常用 | 当前server中各个通道是否启用。 |
performance_schema | global_status | 重要,常用 | 单主模式下,可以查看当前主库是哪个。 |
performance_schema | replication_applier_configuration | 不常用,了解即可 | |
performance_schema | replication_applier_status_by_coordinator | 不常用,了解即可 | |
performance_schema | replication_applier_status_by_worker | 不常用,了解即可 | |
performance_schema | replication_connection_configuration | 不常用,了解即可 | |
Mysql | slave_master_info | 重要,不常用 | 设置了master_info_repository=TABLE,所以master的相关信息会存储在这个表格。 如果使用GROUP中的SERVER备份数据库,恢复到时候,注意要清理这个表格。 |
Mysql | slave_relay_log_info | 重要,不常用 | 设置了relay_log_info_repository=TABLE,所以master的相关信息会存储在这个表格。 如果使用GROUP中的SERVER备份数据库,恢复到时候,注意要清理这个表格。 |
l 组复制成员
select * from performance_schema.replication_group_members;
l 组复制状态
select ***** from performance_schema.global_status where variable_name like '%group%'****;
l 单主模式下主库是哪个
SELECT * FROM performance_schema.replication_group_members;
SELECT * FROM performance_schema. global_status;
l 检查数据库是否正常提供读写服务
show global variables like 'super%';
SELECT * FROM performance_schema.replication_group_members;
如果super_read_only是启动的,那么该成员仅提供读服务;
如果super_read_only是关闭的,并且 replication_group_members 中正常的成员n 满足 2n+1 > 整个GROUP成员个数,并且该成员的 member state是online,则该成员可提供读写服务。
l 检查数据库是否复制出现问题
可以通过表格replication_group_members ,replication_group_member_stats ,replication_connection_stats ,replication_applier_stats 查看
重点注意各个 组成员的 ERROR LOG详细信息,因为报错描述最清楚都在这里了。
# 在启动第一个节点时
SET GLOBAL group_replication_bootstrap_group=ON; #表示本节点作为组复制的起始节点
START GROUP_REPLICATION; #开启组复制
SET GLOBAL group_replication_bootstrap_group=OFF; # 关闭组复制作为起始节点
# 启动其他节点
START GROUP_REPLICATION;
#关闭组复制
stop group replication
mysqlsh --log-level=debug3
dba.help() 看到所有支持的命令:
The following functions are currently supported.
\- checkInstanceConfiguration 校验实例配置
\- configureInstance 配置实例集群
\- configureLocalInstance 配置本地实例 8.0.11以后放弃了
\- createCluster 创建集群.
\- dropMetadataSchema 删除集群.
\- getCluster 提取cluster 从元数据中
\- rebootClusterFromCompleteOutage 当集群成员都OFFLINE 可以启动起来.
shell.connect('root@192.168.181.103:3306')
var cluster = dba.getCluster()
cluster.status() #集群状态查看
var cluster = dba.createCluster('qwCluster')
cluster.describe();
使用一个存在的组复制创建集群
var cluster = dba.createCluster('qwCluster', {adoptFromGR: true});
dba.dropMetadataSchema()
移调实例
cluster.removeInstance('root@localhost:3310')
cluster.rejoinInstance()
cluster.checkInstanceState('root@127.0.0.1:3306')
dba.configureInstance('root@192.168.181.103:3306',{mycnfPath:'/data/mysql_3306_test/conf/my.cnf',clusterAdmin:'cadmin@mysql-1%',clusterAdminPassword:'fangfang'})
var cluster = dba.rebootClusterFromCompleteOutage();
官网说,如果配置了innodbcluster 请物自己配置 集群信息
mysqlrouter --bootstrap root@node01:3306 --user=mysqlrouter
设置开机启动
Systemctl enable mysqlrouter
Systemctl start mysqlrouter
在远端访问路由执行命令
mysql -uroot -p -h192.168.181.101 -P6446 -e "select @@hostname"
将所有节点全部关机,重启后
开启所有数据库节点 mysqld_safe –defaults-file=/data/mysql_3306_test/conf/my.cnf &
找到 super_read_only=off 的节点就是最后的主节点
在主节点执行,如下命令,就是先将本节点作为组复制的起始节点。
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
所有节点全部关闭的情况下,宕机情况下:
var cluster = dba.rebootClusterFromCompleteOutage(‘qwCluster1’);
启动一个节点,然后其他节点重启自动加入
观看日志,连接mysqlsh
mysqlsh --log-level=debug3
主节点关闭,会立即切换一个从节点为主库,mysqlroute 无感知
启动新的节点,会自动恢复复制状态,无需关心。
从节点关闭,不影响读写,重新启动数据库后自动恢复同步。
等同于,有一个节点已经挂掉,无法启动。此时需要添加节点
mysqlbackup是一个热备份工具、也就是说它不像mysqldump那样给表上一个全局锁,由于mysqldump上了这个锁,所以就造成客户端只能对数据库进行读操作不能写,这也就是称mysqldump为温备份的原因。但是mysqlbackup真的有这么吊吗?答案是并没有。对于innodb引擎的表mysqlbackup 热备的;但是对于非innodb表mysqlbackup就只能温备了,原因是这类引擎不支持事务也就是说不能通过事务日志来保证备份的一致性,所以就只能给表加上一个全局锁来解决了。为了得到一致的备份mysqlbackup要不停的去追踪mysql数据库的sln号,也就是说mysqlbackup要执行备份那么它一定要连接上数据库。mysqlbackup对数据库的备份是通过复制文件的方式进行的,也就是说mysqlbackup要和数据库在同一台机器上。
Mysqlbackup 是企业版mysql中的,但可以拿来使用,登录oracle云下载,搜索mysql backup,下载二进制文件
mysql-commercial-backup-8.0.11-linux-glibc2.12-x86_64.tar.gz
传到主机上并解压,会有 bin 和lib 文件夹,将其中的文件拷贝到 /usr/local/mysql 对应的目录中, 这里要把 软连接,库全部拷贝的,不然会报错
(官方指导命令)
CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'new-password';
GRANT RELOAD ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'mysqlbackup'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'mysqlbackup'@'localhost';
GRANT SUPER ON *.* TO 'mysqlbackup'@'localhost';
GRANT PROCESS ON *.* TO 'mysqlbackup'@'localhost';
GRANT ALTER ON mysql.backup_history TO 'mysqlbackup'@'localhost';
GRANT LOCK TABLES, SELECT, CREATE, DROP, FILE ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_sbt_history TO 'mysqlbackup'@'localhost';
mysqlbackup --user=mybackup --password=131417 --backup-image=/data/backup/mysql_server.mbi --backup-dir=/data/backup/temp backup-to-image –with-timestap
mysqlbackup –backup-dir=/data/backup/tmp –socket=/data/mysql_3306_test/tmp/mysql.sock –user=root –password=NTg1Z@mYxZjdhZWI –backup-image=/data/backup/full_20180718.bki --with-timestap backup-to-image
mysqlbackup --backup-dir=/backups --backup-image=- backup-to-image > /backup/mybackup.mbi
--user:用户名。
--password:密码。
--port:端口,默认值为3306。
--backup-dir:可以看成是mysqlback的工作目录,临时用的。
--backup-image:备份文件名。
backup-to-image:把所有的备份信息输出到一个备份文件当中
--with-timepstap: 会自动生成一个带日期文件夹
备份的操作:先进行全备后进行增备
mysqlbackup --user=mysqlbackup --password=123 --backup-image=backup.mbi --backup-dir=/backup --with-timestamp backup-to-image #先备份成image
mysqlbackup --defaults-file=/etc/my.cnf --user=mysqlbackup --password=123 --with-timestamp --incremental --incremental-backup-dir=/backup/ --incremental-base=dir:/backup/2017-09-18_15-53-57 backup #在原来备份的基础上进行增量备份
#########备份到其他服务器
mysqlbackup --defaults-file=~/my_backup.cnf --backup-image=- --backup-dir=/tmp backup-to-image | \
ssh <user name>@<remote host name> 'cat > ~/backups/my_backup.img'
./mysqlbackup --backup-image=/home/admin/backups/my.mbi validate
mysqlbackup --backup-image=/backup/my.mbi list-image
mysqlbackup --backup-image=/backup/2017-09-18_15-53-57/backup.mbi extract
mysqlbackup --backup-dir=/backup/backup --backup-image=/backup/2017-09-18_15-53-57/backup.mbi image-to-backup-dir
mysqlbackup --defaults-file=/backup/2017-09-15_17-06-07/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/2017-09-15_17-06-07 copy-back
1 主库做全备份 备份会锁myisam表,如master是生产库,需要注意,可留意业务进程是否有活动的。
/test/mysql/base/bin/mysqlbackup --defaults-file=/test/my3352/my.cnf --socket=/test/my3352/var/mysql.sock --user=root --password --with-timestamp --backup-dir=/test/my3352/backup backup-and-apply-log
2 备份文件拷到从库主机,做恢复 保证从库的data和log目录为空,用从库的my.cnf进行恢复 恢复时可能也会将主库备份的binlog和my.cnf直接恢复到data目录下,可以清理掉
mv data data_bak
mv log log_bak
mkdir data
mkdir log
停从库,清空目录
mysqlbackup --defaults-file=/test/my3309/my.cnf --backup-dir=/test/myback/BOT/temp copy-back
$ ps -ef | grep my3330 3 启动从库
Cd $MYSQL_HOME
mysqld_safe --defaults-file=/test/mydat/$PORT/my.cnf &
mysql -uroot --socket=/test/my3330/var/mysql.sock
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。