目录
环境:
系统:CentOS7.9
主机名: mysql-01 IP: 10.3.0.1
主机名: mysql-02 IP: 10.3.0.2
主机名: mysql-03 IP: 10.3.0.3
1. 关闭selinux、firewall、写入hosts、配置主机名
sed -i s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config
setenforce 0
systemctl disable firewalld.service
systemctl stop firewalld.service
systemctl status firewalld.service
# 添加hosts,并修改主机名
# 变量
# 相关IP前3字符
Host_Net_Ip="10.3.0"
cat > /etc/hosts << EOF
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.3.0.1 mysql-01
10.3.0.2 mysql-02
10.3.0.3 mysql-03
EOF
# 配置主机名
oneself_ip=`ip a | grep ${Host_Net_Ip} | awk '{print $2}' | awk -F/ '{print $1}'`
grep -w "$oneself_ip" /etc/hosts | awk '{print $2}' | xargs hostnamectl set-hostname && bash
2. 添加软件源【多台机器都要执行】
# 这里添加的是:“中国科学技术大学的软件源”
cat > /etc/yum.repos.d/MariaDB.repo << END
[mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.5/centos7-amd64/
gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
END
3. 安装【多台机器都要执行】
# 注意: 请提前挂载数据盘
yum -y install MariaDB-client MariaDB-server galera
4. 初始化mysql的根目录【多台机器都要执行】
mysql_install_db --datadir=/var/lib/mysql/ --force
chown -R mysql.mysql /var/lib/mysql/
5. 第一次启动mysql【多台机器都要执行】
systemctl enable mariadb.service
systemctl start mariadb.service
6. 执行第一次的配置【多台机器都要执行】
# 为root设置密码,删除匿名账号,取消root远程登录,删除test库,属性策略生效
mysql_secure_installation
7. 停止mysql【多台机器都要执行】
systemctl stop mariadb.service
8. 写入配置文件,所有节点都要【多台机器都要执行】
# 注意: 必须写入这个文件,注意里边的IP地址、主机名,修改为本机的IP、本机的主机名
vim /etc/my.cnf.d/server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
[mysqld]
skip-name-resolve=1
character-set-server=utf8
collation-server=utf8_general_ci
max_connections=40960
query_cache_size=0
query_cache_type=0
datadir=/var/lib/mysql
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://10.3.0.1,10.3.0.2,10.3.0.3"
binlog_format=row
default_storage_engine=InnoDB
# innodb_autoinc_lock_mode # 选择适合自己的模式
innodb_autoinc_lock_mode=0
innodb_locks_unsafe_for_binlog=1
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0
#
# Optional setting
wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
wsrep_provider_options="gcache.size=1G"
wsrep_cluster_name=MariaDB-Galera-Cluster-DH
wsrep_node_name="mysql-02"
wsrep_node_address=10.3.0.2
wsrep_sst_method=rsync
# this is only for embedded server
[embedded]
# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
# This group is only read by MariaDB-10.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.5]
9. 集群第一个节点启动【仅在其中一台执行,切记】
galera_new_cluster
10. 把其余节点加入集群
systemctl start mariadb.service
11. 修改数据库的连接数【多台机器都要执行】
11.1 系统打开文件数
#系统打开文件数【单独下边两步需要重启才生效】
grep -w "^* soft nofile.*" /etc/security/limits.conf || echo "* soft nofile 61440" >> /etc/security/limits.conf
grep -w "^* hard nofile.*" /etc/security/limits.conf || echo "* hard nofile 61440" >> /etc/security/limits.conf
grep -w "^DefaultLimitNOFILE.*" /etc/systemd/system.conf || echo "DefaultLimitNOFILE=61440" >> /etc/systemd/system.conf
grep -w "^DefaultLimitNPROC.*" /etc/systemd/system.conf || echo "DefaultLimitNPROC=61440" >> /etc/systemd/system.conf
#【不重启生效】
ulimit -Hn 61440 # 【改大后,不可改小,直到重启恢复默认】
ulimit -Sn 61440
11.2 mysql允许的连接数
vim /usr/lib/systemd/system/mariadb.service
...
[Service]
LimitNOFILE=40960
LimitNPROC=40960
...
11.3 重启各节点,使其打开连接数生效
systemctl daemon-reload && systemctl restart mariadb.service
12. 验证集群
12.1查看wsrep信息
# 此命令需要在登录数据库的情况下才能执行,在shell命令行是执行不了的(初学的同学注意)
# 查看wsrep信息,wsrep_connected 和 wsrep_ready 都是on状态,就可以确定数据库集群已经起来了
SHOW STATUS LIKE 'wsrep%';
# 要是能出现这么多信息,那这个集群肯定是没有问题的,初学的同学请注意
12.2 查看集群中有几台主机
# 此为mysql命令,不是shell命令
SHOW STATUS LIKE 'wsrep_cluster_size';
12.3 查看集群中的主机IP
show variables like 'wsrep_%address%';
13. 授权【在其中一台授权即可,会自动同步】
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'centos';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.3.0.%' IDENTIFIED BY 'centos';
select user,host,password from mysql.user ;
FLUSH PRIVILEGES;
14. 扩展命令
14.1 查看数据库使用的字符集
show variables like '%char%';
14.2 允许最大的连接数
show variables like "max_connections";
14.3 查看当前的连接数
show status like '%connect%';
14.4 查看被锁表
show open tables where in_use>0;