Mysql PXC 集群运维

Mysql PXC 集群

Percona Software downloads for databases

同步复制，事务要么在全部节点都提交成功，要么不提交
多主复制，每个节点都可以读写
数据同步强一致性，所有节点数据保持一致
需要控制节点数量，节点越多同步越慢，推荐3个
需要所有节点配置相同，集群同步数据速度取决于最慢的那个节点
只支持InnoDB引擎

安装

每个节点都需要操作

1. 卸载其他mysql组件 如centos捆绑了mariadn-libs
yum -y remove mari*
2. 防火墙开放端口 PXC集群依赖4个端口
3306 mysql服务端口
4444 请求全量同步(SST)端口
4567 数据库节点之间通信端口
4568 请求增量同步(IST)端口
eg: firewall-cmd --zone=public --add-port=3306/tcp --permanent
eg: firewall-cmd reload
3. 关闭SELINUX 重启reboot
vim /etc/selinux/config
修改SELINUX=disabled
4. 下载安装包 
https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/LATEST/
https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
额外安装一个qpress-11-1.el7.x86_64

最后软件包list
percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm
percona-xtrabackup-24-debuginfo-2.4.24-1.el7.x86_64.rpm
percona-xtrabackup-test-24-2.4.24-1.el7.x86_64.rpm
Percona-XtraDB-Cluster-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-57-debuginfo-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-client-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-devel-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-full-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-garbd-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-server-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-shared-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-shared-compat-57-5.7.35-31.53.1.el7.x86_64.rpm
Percona-XtraDB-Cluster-test-57-5.7.35-31.53.1.el7.x86_64.rpm
qpress-11-1.el7.x86_64.rpm

上传至home目录 tar需要解压
yum -y localinstall *.rpm

# 启动数据库
systemctl start mysqld

# 查看默认密码
cat /var/log/mysqld.log | grep "A temporary password"
# 改密码
mysql_secure_installation
y/2/y/y/y/y/y/y
新密码Abc*123456
# 创建远程可访问用户
CREATE USER 'admin'@'%' IDENTIFIED BY 'Abc*123456';
GRANT all privileges ON *.* TO 'admin'@'%';
FLUSH PRIVILEGES;

初始化集群

vim /etc/my.cnf，每个节点都不一样

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
# 数据库字符集
character_set_server=utf8
# 允许远程访问
bind_address=0.0.0.0
# 跳过dns解析
skip-name-resolve
# 表名大小写不敏感
lower_case_table_names=1
# 最大连接数
max-connections=2000

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# PXC集群中mysql实例的唯一ID，不能重复，必须是数字
server-id=1
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
# PXC集群的名称
wsrep_cluster_name=pxc-cluster
# 集群所有节点IP
wsrep_cluster_address=gcomm://192.168.45.51,192.168.45.52,192.168.45.53
# 当前节点名称
wsrep_node_name=pxc-1
# 当前节点IP
wsrep_node_address=192.168.45.51
# 同步方法 mysqldump rsync xtrabackup
wsrep_sst_method=xtrabackup-v2
# 同步使用账户
wsrep_sst_auth=admin:Abc*123456
# 同步严厉模式 强一致性
pxc_strict_mode=ENFORCING
# 基于ROW复制安全可靠
binlog_format=ROW
# 默认引擎
default_storage_engine=InnoDB
# 主键自增长不锁表
innodb_autoinc_lock_mode=2

创建集群

先全部关闭mysql
systemctl stop mysql
# 选192.168.45.51作为主节点
主节点 systemctl start mysql@bootstrap.service
其他节点 systemctl start mysql

登录任意节点查询集群状态

show status like 'wsrep_cluster%';

wsrep_cluster_weight:3
wsrep_cluster_conf_id:3
wsrep_cluster_size:3 # 集群节点数量
wsrep_cluster_state_uuid:61b64b6d-75c3-11ec-a4b2-ef8a94cef881
wsrep_cluster_status:Primary
# 更多状态信息
show status like 'wsrep%';

PXC节点上下线

# 怎么启动怎么关闭
主节点 
systemctl start mysql@bootstrap.service
systemctl stop mysql@bootstrap.service
其他节点 
systemctl start mysql
systemctl stop mysql

# 重要文件
cat /var/lib/mysql/grastate.dat

# GALERA saved state
version: 2.1
uuid:    61b64b6d-75c3-11ec-a4b2-ef8a94cef881
seqno:   -1
# 最后关闭的节点 这个值将变成1 下次启动的时候要以此节点作为主节点启动 因为它的数据是最新的
safe_to_bootstrap: 0

1. 如果是安全退出 那么先启动最后一个关闭节点作为主节点
2. 如果是意外退出，查看/var/lib/mysql/grastate.dat找safe_to_bootstrap=1的节点作为主节点退出
3. 如果是意外退出，查看/var/lib/mysql/grastate.dat中safe_to_bootstrap都是0， 则修改一个节点配置文件改成1在作为主节点启动
4. 服务上线，如果还有集群还可用 则正常启动其他节点即可systemctl start mysql
适用场景 如果是3节点
安全退出2节点
或者意外退回1个节点
意外退出2节点不行，因为剩下的节点已经无法使用了 需要重新构建PXC集群

注意事项

# 去掉mysql自启动，因为PXC节点宕机了 一旦节点重新启动 就会开始同步数据 造成mysql变慢，正确做法是从其他节点中复制数据再启动，减少同步数据的影响
chkconfig mysqld off

# 执行完发现systemctl start mysqld无法使用了,刚才的命令似乎把link删掉了
systemctl enable mysql.service
or
service mysqld restart

mysql找回密码

1.vim /etc/my.cnf
在[mysqld]下面加上skip-grant-tables
2.重启systemctl restart mysqld
3.输入mysql直接登录
mysql>use mysql;
mysql>update user set password=password('newpwd') where user = 'root';

5.7以上 passowrd 字段改成authentication_string
4. 删除之前的配置 重启 之前是mysqld 现在是mysql
systemctl restart mysql

Mysql中间件

安装jdk

yum install -y java-1.8.0-openjdk-devel.x86_64
配置环境变量JAVA_HOME
ls -lrt /etc/alternatives/java
/etc/alternatives/java -> /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.322.b06-1.el7_9.x86_64/jre/bin/java
vim /etc/profile
export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.322.b06-1.el7_9.x86_64
source /etc/profile

mycat安装启动

下载路径 http://dl.mycat.org.cn/

chmod 777 ./*.sh
cd mycat/bin
mycat start
mycat stop
mycat restart
mycat status

mycat管理重新加载配置文件、SQL监控分析等

https://www.cnblogs.com/wxzhe/p/10290201.html

mycat配置文件

https://github.com/MyCATApache/Mycat-Server/wiki/3.0-Mycat配置入门#编辑schemaxml
https://blog.51cto.com/zero01/2465837?source=drh
schema.xml 定义mycat虚拟库

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<!-- schema name 是mycat的虚拟逻辑库名称， -->
	<schema name="nacos" checkSQLschema="false" sqlMaxLimit="-1" dataNode="dn1" />
	<!--
		dataNode节点中各属性说明：
		name：指定逻辑数据节点名称；
		dataHost：指定逻辑数据节点物理主机节点名称；
		database：指定物理主机节点上。如果一个节点上有多个库，可使用表达式db$0-99，     表示指定0-99这100个数据库；
	-->
	<!-- 配置分片 -->
	<dataNode name="dn1" dataHost="pxc" database="nacos" />
	<!-- 连接配置读写分离 
	writeType：指定写入类型；
            0，只在writeHost节点写入；
            1，在所有节点都写入。慎重开启，多节点写入顺序为默认写入根据配置顺序，第一个挂掉切换另一个；
	 balance：指定物理主机服务的负载模式。
            0，不开启读写分离机制；
            1，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡；
            2，所有的readHost与writeHost都参与select语句的负载均衡，也就是说，当系统的写操作压力不大的情况下，所有主机都可以承担负载均衡；

	
	-->
	<dataHost name="pxc" maxCon="1000" minCon="10" balance="2"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="W1" url="192.168.33.51:3306" user="admin" password="Abc*17894">
			<readHost host="R1" url="192.168.33:3306" user="admin" password="Abc*17894" weight="1"></readHost>
			<readHost host="R2" url="192.168.33:3306" user="admin" password="Abc*17894" weight="1"></readHost>
		</writeHost>
	</dataHost>
</mycat:schema>

server.xml 定义连接配置,8066是连接端口 9066是管理端口（管理集群状态）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
        <property name="nonePasswordLogin">0</property>
        <property name="ignoreUnknownCommand">0</property>
        <property name="useHandshakeV10">1</property>
        <property name="removeGraveAccent">1</property>
        <property name="useSqlStat">0</property>
        <property name="useGlobleTableCheck">0</property>
        <property name="sqlExecuteTimeout">300</property>
        <property name="sequenceHandlerType">1</property>
        <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
        <property name="subqueryRelationshipCheck">false</property>
        <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
        <property name="processorBufferPoolType">0</property>
        <property name="handleDistributedTransactions">0</property>
        <property name="useOffHeapForMerge">0</property>
        <property name="memoryPageSize">64k</property>
        <property name="spillsFileBufferSize">1k</property>
        <property name="useStreamOutput">0</property>
        <property name="systemReserveMemorySize">384m</property>
        <property name="useZKSwitch">false</property>
        <property name="strictTxIsolation">true</property>
        <property name="parallExecute">0</property>
        <property name="serverBacklog">2048</property>
    </system>
    <!-- 全局SQL防火墙设置 -->
    <!--白名单可以使用通配符%或着*-->
    <!--例如<host host="127.0.0.*" user="root"/>-->
    <!--例如<host host="127.0.*" user="root"/>-->
    <!--例如<host host="127.*" user="root"/>-->
    <!--例如<host host="1*7.*" user="root"/>-->
    <!--这些配置情况下对于127.0.0.1都能以root账户登录-->
    <firewall>
        <whitehost>
            <!-- 设置白名单用户 -->
            <host host="%" user="dev"/>
        </whitehost>
        <blacklist check="false"></blacklist>
    </firewall>
    <user name="dev">
        <property name="password">123456</property>
        <!-- 允许该用户访问的逻辑库 -->
        <property name="schemas">mall_db</property>
        <!-- 可配置多个允许访问的逻辑库，使用逗号分隔 -->
        <!-- <property name="schemas">mall_db,db1,db2</property>  -->
        <!-- 是否只读 -->
        <property name="readOnly">true</property>
    </user>
</mycat:server>

备份/还原

-（联机）冷备份：停机，将MySQL所有数据文件打包
-（联机）热备份：不停机备份（推荐）

# 每个星期日0点全量热备份数据库

0 0 * * 0  /home/backup/backup.sh > /home/backup/backup.log 2>&1

#!/bin/bash
time=$(date "+%Y-%m-%d %H:%M:%S")
echo "执行全量热备份 当前时间${time}"
filename=$(date "+%Y%m%d")
innobackupex --default-file=/etc/my.cnf --host=10.10.20.51 --user=admin --password=Abc*17894 --port=3306 --stream=xbstream -> /home/backup/${filename}.xbstream


# 全量冷还原
# 流式备份解压缩
mkdir /home/backup/20220901
xbstream -x < /home/backup/20220901.xbstream -C  /home/backup/20220901
rm -rf /var/lib/mysql
innobackupex --default-file=/etc/my.cnf --copy-back /home/backup/20220901/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql


# 增量热备份
innobackupex --default-file=/etc/my.cnf --host=10.10.20.51 --user=admin --password=Abc*17894 --port=3306 --incremental-basedir=/home/backup/20220505 --incremental /home/backup/increment


# 日志闪回工具 https://github.com/danfengcao/binlog2sql
binlog2sql

yum -y install epel-release
yum -y install python-pip
pip install -r requirements.txt

python binlog2sql.py -uadmin -p'Abc*17894' -dflash -t table_name --start-file='localhost-bin.00010' > flash.sql

常用运维sql

-- 创建新用户
create user 'mmall'@'%' IDENTIFIED BY 'MyNewPass4!';
-- 创建数据库
create database if not exists `mmall` default character set utf8 collate utf8_general_ci;
-- 分配权限
grant all privileges on mmall.* to mmall@'%' identified by 'MyNewPass4!' with grant option;
-- 刷新权限
flush privileges;
-- 撤销权限
revoke all on *.* FROM 'mmall'@'%';
-- 查看权限
show grants for 'mmall'@'%';
-- 删除用户
drop user 'mmall'@'%';
-- 导出数据库
mysqldump -u root -p123456 test > dump.sql
-- 导入数据库
mysql -u root -p123456 --default-character-set=utf8 test < change.sql
-- 查看当前连接
SHOW PROCESSLIST;
-- 清理连接
KILL [connection_id];
-- 查看当前MySQL数据库的总连接数
SHOW STATUS LIKE 'Threads_connected';
-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

单体mysql 配置文件

路径/etc/percona-server.conf.d

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
character_set_server=utf8
bind_address=0.0.0.0
skip-name-resolve
lower_case_table_names=1
max-connections=3000

symbolic-links=0

server-id=1
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

posted @ 2022-04-27 22:37 Ranger-dev 阅读(232) 评论(0) 编辑收藏举报

刷新页面返回顶部

Ranger

Mysql PXC 集群运维

Mysql PXC 集群

安装

每个节点都需要操作

初始化集群

PXC节点上下线

注意事项

mysql找回密码

Mysql中间件

安装jdk

mycat安装启动

mycat管理 重新加载配置文件、SQL监控分析等

mycat配置文件

备份/还原

常用运维sql

单体mysql 配置文件

公告

mycat管理重新加载配置文件、SQL监控分析等