MySQL集群搭建

1，软件

MySQL版本：mysql 5.7.29

下载链接：https://downloads.mysql.com/archives/community/

注：建议使用迅雷下载，速度较快

2，系统挂盘

#查看磁盘情况
lsblk
#查看剩余情况
vgs

#2个步骤：
#1、创建逻辑分区
lvcreate -L 140G -n lvmysql rootvg
#2、格式化分区
mount /dev/rootvg/lvmysql /home/mysql

更多命令及自动挂盘请参考：https://blog.csdn.net/dongyuxu342719/article/details/82702357

3，调整磁盘IO调度规则

# 查看：
dmesg | grep scheduler
# CentOS7 io调度规则默认为“deadline”,所以不需要修改

4，配置内核参数

vim /etc/sysctl.conf

#配置交换区
vm.swappiness =1

#保存后使其生效
sysctl -p

5，MySQL用户ulimit设置

操作系统对mysql用户注意以下参数的设置，该设置为必须的设置：

vim /etc/security/limits.d/mysql.conf 

mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535

6，防火墙及seLinux

# 查看当前防火墙状态
firewall-cmd --state
#关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service

# 关闭selinux
# 使用setenforce命令可以切换SELinux应用模式
# enforcing或1：强制模式
# permissive或0：允许模式

setenforce 0
vim /etc/selinux/config
# 将 SELINUX=enforcing更改为 SELINUX=disabled 
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
cat /etc/selinux/config
# 显示结果为 
#SELINUX=disabled
#SELINUXTYPE=targe

7，关闭numa

关闭numa，解决mysql服务器swap严重以及内存泄漏的问题，需要注意的是Mysql 5.7.9引入了iinnodb_numa_interleave参数，MySQL自己解决了内存分类策略的问题前提是服务器支持numa，也就是说如果使用MySQL5.7.9之后的版本，可以不用关闭服务器的numa

# 修改grab，conf
# 1、 在/etc/default/grub 文件中修改 kernel行：
vi /etc/default/grub
GRUB_CMDLINE_LINUX 后添加 numa=off

# 2、重建配置文件
grub2-mkconfig -o /etc/grub2.cfg

# 3、重启操作系统
reboot

8，卸载旧版本rpm包

rpm -qa |grep mysql -i

rpm -e --nodeps msyql-xxx-5.1..xx86_64

# 把旧版本的mysql 配置文件移动
mv /etc/my.conf /etc/my.conf.`date+%Y%m%d`

9，安装mysql

#创建存放mysql的目录
mkdir /opt/mysql_base/

# 找到存放 mysql -tar包的目录
cd /home/mysqluser
tar zxf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz -C /opt/mysql_base/

mv /opt/mysql_base/mysql-5.7.29-linux-glibc2.12-x86_64 /opt/mysql_base/mysql-5.7.29

10，创建MySQL运行用户

组名：mysql

组id：20000

用户名：mysql

用户id：20000

用户home目录：/home/mysql

groupadd -g 20000 mysql

useradd -u 20000 -d /home/mysql -g mysql -m mysql

passwd mysql

11，创建相关文件系统

mkdir /home/mysql/data
cd /home/mysql/data
mkdir mysql_tmp #存放临时数据
mkdir mysql_backup   #存放数据库备份
mkdir -p mysql_data/4306  #存放数据
mkdir -p mysql_log/mysql-bin    #存放binlog
mkdir mysql_log/relay-bin      #relay log的存放位置
mkdir  /opt/mysql           #存放mysql介质

chown mysql.mysql -R  /home/mysql
chown mysql.mysql -R  /opt/mysql_base
chown mysql.mysql -R  /opt/mysql

12，修改my.cnf

在 opt/mysql_base/mysql-5.7.29 下创建my.cnf

su mysql  #切换用户
cd /opt/mysql_base/mysql-5.7.29
vi my.cnf 
# 如何有上传的my.cnf，直接cp过来，命令是
cp /home/my.cnf /opt/mysql_base/mysql-5.7.29/

[client]
port = 4306
socket=/home/mysql/data/mysql_data/mysql.sock
default-character-set = utf8mb4

[mysql] 
default-character-set = utf8mb4 

[mysqld]
server-id=8008     ## 复制ID,主备机保持唯一
port = 3306
user=mysql
basedir=/opt/mysql_base/mysql-5.7.29
datadir=/home/mysql/data/mysql_data/4306
tmpdir=/home/mysql/data/mysql_tmp
socket=/home/mysql/data/mysql_data/mysql.sock
pid-file=mysqld.pid
default-storage-engine = INNODB
lower_case_table_names = 1
transaction_isolation = READ-COMMITTED

character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci 
init_connect='SET NAMES utf8mb4'

# connection
back_log = 400
max_connections = 1000
interactive-timeout = 28800
wait_timeout = 29900
slave_net_timeout = 300
skip_name_resolve = on

# binlog
log_bin=/home/mysql/data/mysql_log/mysql-bin/mysql-bin.index
relay-log=/home/mysql/data/mysql_log/relay-bin/relay-bin.index
expire_logs_days=7
binlog_cache_size = 8M
binlog_format = ROW
relay_log_purge = 1 
log-bin-trust-function-creators = 1
binlog_group_commit_sync_no_delay_count = 20
binlog_group_commit_sync_delay = 10

#replica
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers = 8
master_info_repository=TABLE
relay_log_info_repository=TABLE
slave_preserve_commit_order=on

## Semi sync Config
plugin-load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = 0
rpl_semi_sync_slave_enabled = 1
rpl_semi_sync_master_timeout = 10000
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_wait_no_slave = 0 
rpl_semi_sync_master_wait_point = AFTER_SYNC  ##默认就是AFTER_SYNC

#password plugin
validate_password_policy = STRONG
validate-password=FORCE_PLUS_PERMANENT

#caches & limits
bulk_insert_buffer_size = 8M
innodb_buffer_pool_size = 4G  #适当调整，可以选取物理内存的70%
innodb_buffer_pool_instances = 8
innodb_log_buffer_size = 32M
innodb_sort_buffer_size = 4M
join_buffer_size = 4M
sort_buffer_size = 4M
key_buffer_size = 16M
innodb_max_dirty_pages_pct = 50
max_allowed_packet = 128M
max_heap_table_size = 16M
read_buffer_size = 1M ##推荐 8-16G 配 1M
read_rnd_buffer_size = 32M
open-files-limit = 10240
table-definition_cache = 4096
query_cache_type = 0
table_open_cache = 4096
table_open_cache_instances = 16
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:20G

# thread
thread_cache_size = 256
innodb_thread_concurrency = 128
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
innodb_write_io_threads = 8
innodb_read_io_threads =8 ##推荐核数的两倍
innodb_purge_threads = 4
innodb_page_cleaners = 4

# innodb log
innodb_flush_method = O_DIRECT
innodb_autoextend_increment = 128
innodb_flush_log_at_trx_commit = 1
innodb_print_all_deadlocks
sync_binlog = 1

# innodb  file
innodb_data_file_path = ibdata:2G:autoextend
innodb_file_per_table = 1
tmp_table_size = 16M
innodb_log_file_size = 2048M
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_online_alter_log_max_size = 1G

# logging
slow_query_log = 1
log_timestamps = SYSTEM
log-error=mysql-error.log
slow_query_log_file=mysql_slow.log
lc_messages_dir=/opt/mysql_base/mysql-5.7.29/share

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# performance_schema settings
performance-schema-instrument='memory/%=COUNTED'
performance_schema-digests_size = 40000
performance_schema_max_table_handles = 40000
performance_schema_max_table_instances = 40000
performance_schema_max_sql_text_length = 4096
performance_schema_max_digest_length = 4096

其中主库、从库部分不同

# 主库
server-id=8008     ## 复制ID,主备机保持唯一

## Semi sync Config
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 0

# 从库
server-id=8009     ## 复制ID,主备机保持唯一

## Semi sync Config
rpl_semi_sync_master_enabled = 0
rpl_semi_sync_slave_enabled = 1

# 从库
server-id=8010     ## 复制ID,主备机保持唯一

## Semi sync Config
rpl_semi_sync_master_enabled = 0
rpl_semi_sync_slave_enabled = 1

13，初始化

# 使用mysql用户
/opt/mysql_base/mysql-5.7.29/bin/mysqld --defaults-file=/opt/mysql_base/mysql-5.7.29/my.cnf  --initialize --user=mysql

14，修改root密码

在 /home/mysqluser/data/mysql_data/4306/mysql-error.log 中查看初始化完成后生成的随机密码，记得保存下来，修改root密码时使用。

/opt/mysql_base/mysql-5.7.29/bin/mysqld_safe --defaults-file=/opt/mysql_base/mysql-5.7.29/my.cnf &
# 建立软连接
ln -s /home/mysql/data/mysql_data/mysql.sock /tmp/mysql.sock
# 配置一下mysql的环境变量
vi /etc/profile.d/mysql.sh
# mysql.sh添加如下内容
MYSQL_HOME=/opt/mysql_base/mysql-5.7.29
PATH=$PATH:$MYSQL_HOME/bin
export MYSQL_HOME PATH
# 让配置文件生效
source /etc/profile

# 登录mysql
mysql -uroot -p 
# 修改密码
alter user 'root'@'localhost' identified by '1qazZSE#39;;

二，搭建复制

1，主库建立复制用户

在主库上运行如下命令，新建用户 repl 并授权

create user 'repl'@'182.%' identified by '1qazZSE#39;;
grant replication slave on *.* to 'repl'@'182.%';

从安全方面考虑，可以对ip进行范围限制，不适用%

2，主库操作

# 使用场景 --第一次搭建主从数据库时，用于主库的初始化binglog操作；
mysql> reset master;
# 展示binlog 日志文件
mysql> show master statusG

3，从库操作

3.1, 在从库建立复制通道

mysql> change master to master_host='192.168.222.224',
        master_port=3306,
        master_user='repl',
        master_password='1qazZSE#39;,
        master_auto_position=1;

3.2，启动复制

在从库上启动复制并检查 slave状态

mysql> start slave;

mysql> show slave statusG;
查看Slave_IO_State、Slave_SQL_Running

3.3，查看主从库半复制同步开启状态

主库：

mysql> show status like 'rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | on   |
+-----------------------------+-------+

从库：

mysql> show global status like 'rpl_semi_sync_slave_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | on   |
+-----------------------------+-------+

3.4 注意问题

注：如果3.2 步骤出错，在重新操作3.1之前，需要使用以下命令重置：

stop slave;
reset slave;

4，测试

4.1, 主节点插入数据

主节点建立数据库，并插入一条 @数据：

create database testdb;
use testdb;
create table test_tb(id int(3),name char(10));
insert into test_tb values(001,'hi');

4.2，从节点查询数据

show databases;
use testdb;
select * from test_tb;

三，安装PerconaToolkit维护工具

当前选择的版本是2.2版本，版本号为 percona-toolkit-2.2.20-1,安装rpm包之前需要安装如下依赖包

cd /home/mysqluser/PerconaToolkit_depend
# 批量安装 依赖
rpm -Uvh --force --nodeps  *rpm
 
cd /home/mysqluser/PerconaToolkit
# 安装PerconaToolkit
rpm -ivh percona-toolkit-2.2.20-1.noarch.rpm

# 验证是否安装成功
pt-query-digest --help

四，安装 percona-xtrabackup

当前选择的版本是2.4.7版本，版本号为 percona-xtrabackup-2.4.7，

tar zxvf percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz -C /opt
mv percona-xtrabackup-2.4.7-Linux-x86_64 percona-xtrabackup-2.4.7
ln -s /opt/percona-xtrabackup-2.4.7/bin/* /usr/bin/
xtrabackup --version # 验证版本

展开阅读全文

页面更新：2024-04-02

标签：主从节点集群初始化防火墙命令内存版本操作数据用户