CentOS7下Mysql安装与主从 (GTID) 集群搭建详细说明

     不可否认Mysql InnoDB Cluster 模式运维具有很高的难度，后期我会逐步的分享相关的运维经验。如果团队中力量相对薄弱，又有高可用的需求，那么 Mysql主从是一个不错的选择 。 下文有Mysql 5.7.23为例讲解， 本博文讲述的步骤同样适用于Mysql8 和 Mysql 5.7 的其他版本。 本文分四个部分进行讲解：

1.  环境初始化
2. Mysql数据库安装
3. 主从集群搭建

一、环境初始化

1. 修改系统最大文件打开数，直接执行下列脚本即可

说明： 数据库服务器建议都要调整此参数

# 可参考 https://blog.csdn.net/robin90814/article/details/86705155
cat <<EOF >>/etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
EOF

验证：

ulimit -n

2. 卸载mysql和mariadb资源包

2.1 卸载mysql 

rpm -qa | grep mysql
rpm -qa | grep -i mysql | xargs rpm -e --nodeps

2.2 卸载mariadb并删除目录（CentOS7 默认安装了mariadb客户端）

# 卸载mariadb
rpm -qa | grep mariadb
rpm -qa | grep mariadb | xargs rpm -e --nodeps

# 删除遗留目录
rm -rf /var/lib/mysql
rm -rf /usr/share/mysql

 2.3. 时钟同步（这步真的很重要，以笔者的经验但凡做集群，一定要做时钟同步，这就是规范）参考博文： https://www.cnblogs.com/xuanbjut/p/11758445.html

# 具体参考博文 https://www.cnblogs.com/xuanbjut/p/11758445.html
yum install -y chrony

二、Mysql数据库安装

2.1  将rpm安装包上传到搭建集群的宿主机上

mkdir -p /usr/local/src/mysql
cd /usr/local/src/mysql

# 上传rpm包,并分别拷贝到其他主机上

scp /usr/local/src/mysql/*rpm root@192.168.2.33:/usr/local/src/mysql/

2.2  安装Mysql数据库，如下：

ll /usr/local/src/mysql
rpm -ivh mysql-community-common-5.7.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.23-1.el7.x86_64.rpm

yum install mysql-community-common-5.7.23-1.el7.x86_64.rpm
yum install mysql-community-libs-5.7.23-1.el7.x86_64.rpm
yum install mysql-community-libs-compat-5.7.23-1.el7.x86_64.rpm
yum install mysql-community-client-5.7.23-1.el7.x86_64.rpm
yum install mysql-community-server-5.7.23-1.el7.x86_64.rpm
yum install mysql-community-devel-5.7.23-1.el7.x86_64

2.3  配置配置文件：my.cnf

 注意一定要启用 gtid_mode模式： gtid_mode=ON 

# 备份原始文件，这是个好习惯
cp /etc/my.cnf /etc/my.cnf.bak

# 写入默认配置
cat <<EOF >/etc/my.cnf
[client]
port = 13307
default-character-set = utf8mb4
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 13307
read_only=0
log_bin_trust_function_creators=TRUE
datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mysqld.log
character_set_server = utf8mb4
user = mysql
bind-address = *
default_storage_engine = InnoDB
max_allowed_packet = 512M
max_connections = 800
open_files_limit = 65535
symbolic-links=0
key_buffer_size = 64M
connect_timeout = 3600
wait_timeout = 3600
interactive_timeout = 3600
explicit_defaults_for_timestamp = true
gtid_mode=ON 

# MyIsam
myisam-recover=force,backup
key_buffer_size=2G
concurrent_insert=2
low-priority-updates=1

innodb_buffer_pool_size = 4G  # 物理内存的 75% 左右 ，虚机环境建议 60%
innodb_buffer_pool_instances=4  # 平均一个实例1G就行
EOF

2.4  启动mysql服务

systemctl start mysqld
systemctl enable mysqld
systemctl status mysqld

2.5  登录mysql

MySQL_PASS=$(cat /var/log/mysqld.log | grep "A temporary password" | awk '{print $NF}')
mysql -u root -p"${MySQL_PASS}"

2.6 更新密码，需要关闭Binlog：

# 关闭Bin log很重要，避免各个节点之间出现GTID不一致
SET SQL_LOG_BIN=0;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'sysroot123';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'sysroot123' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
exit

三、 Mysql 主从集群搭建

3.1 主节点创建复制用户

create user 'repl'@'%' identified by 'repl123456';

grant replication slave on *.* to 'repl'@'%'  WITH GRANT OPTION;

ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl123456';

flush privileges;

3.2 创建主从

change master to master_host='192.168.2.32',master_port=13307,master_user='repl',master_password='repl123456',master_auto_position=1;
start slave;