2023031701 - mysql8高可用部署

mysql8部署架构对比

1.两台服务器安装mysql8

# 解压
tar xvJf mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz
# 改名
mv mysql-8.0.30-linux-glibc2.12-x86_64 mysql-8.0.30

cd mysql-8.0.30 ; mkdir data ; mkdir log ; mkdir tmp 
cd log ; touch mysqld.log 

# 增加mysql用户
groupadd mysql
useradd -g mysql mysql
cd ..
# 授权给mysql 
chown -R mysql.mysql ./mysql-8.0.30

# 初始化
./bin/mysqld --initialize --user=mysql --basedir=/data/software/mysql-8.0.30 --datadir=/data/software/mysql-8.0.30/data

# 编辑配置文件/etc/my.cnf
# 配置环境变量
vim /etc/profile
	MYSQL_HOME=/data/software/mysql-8.0.30
	PATH=$PATH:$MYSQL_HOME/bin
source /etc/profile

# 启动mysql服务
./support-files//mysql.server start

# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '********';

my.cnf

[client]
socket=/data/software/mysql-8.0.30/mysql.sock

[mysqld]
port=7788
basedir=/data/software/mysql-8.0.30
datadir=/data/software/mysql-8.0.30/data
socket=/data/software/mysql-8.0.30/mysql.sock
max_connections=1000
group_concat_max_len=102400000
skip-name-resolve
slow-query_log=ON
slow_query_log_file=/data/software/mysql-8.0.30/log/mysql-slow.log
long_query_time=2
innodb_file_per_table = 1
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
log_bin=/data/software/mysql-8.0.30/log/mysql-bin
binlog_format=ROW
expire_logs_days=5

[mysqld_safe]
log-error=/data/software/mysql-8.0.30/log/mysqld.log
pid-file=/data/software/mysql-8.0.30/mysqld.pid

# 创建用户
CREATE USER 'root'@'132.151.9.%' IDENTIFIED BY '';
grant all privileges on *.* to 'root'@'132.151.9.%' with grant option;
flush privileges;

2.配置主从

# 主节点修改/etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

# 从节点修改/etc/my.cnf
[mysqld]
server-id=2

# 修改/etc/my.cnf后要重启下，不然从节点server-id默认是1，未生效为2 

# 在主节点创建同步用户
CREATE USER 'master'@'132.151.7.9' IDENTIFIED WITH mysql_native_password BY '';
GRANT REPLICATION SLAVE ON *.* TO 'master'@'132.151.7.9';
flush privileges;

# 在从节点切换主节点
CHANGE MASTER TO MASTER_HOST='132.151.7.8',  -- 主服务器IP
MASTER_USER='master',                       -- 主服务器用户
MASTER_PASSWORD='',                    -- 主服务器用户密码
MASTER_LOG_FILE='mysql-bin.000001',                -- 主服务器
MASTER_PORT=7788,                            -- 端口号默认3306
MASTER_LOG_POS=1924;                         --  位置

show slave status \G;
start slave;
show slave status \G;