mysql8配置主从

1.背景

mysql8已安装，现在配置主从.

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 '123';
GRANT REPLICATION SLAVE ON *.* TO 'master'@'132.151.7.9';
flush privileges;

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

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

3.问题

3.1 安装目录不对

[ERROR] [MY-013276] [Server] Failed to set datadir to '/home/gx/mysql-8.0.30/data/' (OS errno: 13 - Permission denied)

解决办法

将解压目录移动到mysql用户的home目录下，这样mysql用户才有权限执行操作。
mv /home/gx/mysql-8.0.30 /home/mysql/

3.2 网络不通

# telnet 132.151.21.15 3306
Trying 132.151.21.15...
telnet: connect to address 132.151.21.15: No route to host

# 关闭防火墙
sudo systemctl stop firewalld.service

3.3 不通过useradd添加用户失败

# 执行 sudo echo 'mysql:x:1001:1001::/home/mysql:/bin/bash' >> /etc/passwd 失败
vi test.sh
sudo sh test.sh

4.引用

mysql8主从配置