基于centos7.5安装mysql8主从

环境初始化

mysql官网下载

使用环境VMware17，centos7.5

节点	IP
mysql01	192.168.200.20
mysql02	192.168.200.21

初始化两台节点；免密，主机名，主机映射等

vi init.sh

#!/bin/bash

# 定义节点信息
NODES=("192.168.200.20 mysql01" "192.168.200.21 mysql02" )

# 定义当前节点的密码（默认集群统一密码）
HOST_PASS="000000"

# 时间同步的目标节点
TIME_SERVER= mysql01

# 时间同步的地址段
TIME_SERVER_IP= 192.160.200.0/24

# 欢迎界面
cat > /etc/motd <<EOF
 ################################
 #   Welcome  to  mysqlcluster  #
 ################################
EOF
# 配置仓库，使用本地离线源
mount /dev/sr0 /mnt/
mv /etc/yum.repos.d/* /media/
cat > /etc/yum.repos.d/centos.repo << eof
[c]
name=c
baseurl=file:///mnt
gpgcheck=0
enabled=1
eof

# 优化ssh连接
sed -i -e 's/#UseDNS yes/UseDNS no/g' -e 's/GSSAPIAuthentication yes/GSSAPIAuthentication no/g' /etc/ssh/sshd_config
systemctl reload sshd

# 修改主机名
for node in "${NODES[@]}"; do
  ip=$(echo "$node" | awk '{print $1}')
  hostname=$(echo "$node" | awk '{print $2}')

  # 获取当前节点的主机名和 IP
  current_ip=$(hostname -I | awk '{print $1}')
  current_hostname=$(hostname)

  # 检查当前节点与要修改的节点信息是否匹配
  if [[ "$current_ip" == "$ip" && "$current_hostname" != "$hostname" ]]; then
    echo "Updating hostname to $hostname on $current_ip..."
    hostnamectl set-hostname "$hostname"

    if [ $? -eq 0 ]; then
      echo "Hostname updated successfully."
    else
      echo "Failed to update hostname."
    fi

    break
  fi
done

# 遍历节点信息并添加到 hosts 文件
for node in "${NODES[@]}"; do
  ip=$(echo "$node" | awk '{print $1}')
  hostname=$(echo "$node" | awk '{print $2}')

  # 检查 hosts 文件中是否已存在相应的解析
  if grep -q "$ip $hostname" /etc/hosts; then
    echo "Host entry for $hostname already exists in /etc/hosts."
  else
    # 添加节点的解析条目到 hosts 文件
    sudo sh -c "echo '$ip $hostname' >> /etc/hosts"
    echo "Added host entry for $hostname in /etc/hosts."
  fi
done

if [[ ! -s ~/.ssh/id_rsa.pub ]]; then
    ssh-keygen -t rsa -N '' -f ~/.ssh/id_rsa -q -b 2048
fi

# 检查并安装 expect 工具
if ! which expect &> /dev/null; then
    echo "expect 工具未安装，正在安装 expect..."
    sudo yum install -y expect
fi

# 遍历所有节点
for node in "${NODES[@]}"; do
    ip=$(echo "$node" | awk '{print $1}')
    hostname=$(echo "$node" | awk '{print $2}')

    expect -c "
        set timeout -1
        spawn ssh-copy-id -i /root/.ssh/id_rsa.pub $hostname
        expect {
            \"*password:*\" { send -- \"$HOST_PASS\r\"; exp_continue }
            \"*(yes/no)*\" { send -- \"yes\r\"; exp_continue }
            eof { exit 1 }
        }
    "
done

# 时间同步
if [[ $name == $TIME_SERVER ]]; then
    # 配置当前节点为时间同步源
    sed -i '3,6s/^/#/g' /etc/chrony.conf
    sed -i "7s/^/server $TIME_SERVER iburst/g" /etc/chrony.conf
    echo "allow $TIME_SERVER_IP" >> /etc/chrony.conf
    echo "local stratum 10" >> /etc/chrony.conf
else
    # 配置当前节点同步到目标节点
    sed -i '3,6s/^/#/g' /etc/chrony.conf
    sed -i "7s/^/server $TIME_SERVER iburst/g" /etc/chrony.conf
fi

# 重启并启用 chrony 服务
systemctl restart chronyd
systemctl enable chronyd

echo "###############################################################"
echo "#################      集群初始化成功     ######################"
echo "###############################################################"

部署mysql

以下操作双节点执行

vi start_mysql.sh

#!/bin/bash
#将mysql包上传到两台节点进行解压
tar -xf mysql-8.0.37-1.el7.x86_64.rpm-bundle.tar -C /opt/

#卸载节点自带的mariadb
rpm -e --nodeps mariadb-libs-*

#安装mysql
 yum install -y /opt/*

#初始化mysql
mysqld --initialize --console

# 授权目录权限
chown -R mysql:mysql /var/lib/mysql/

# 启动并配置开机自启
systemctl enable --now mysqld	

查看初始化密码

[root@mysql01 ~]# cat /var/log/mysqld.log  | grep localhost
2024-08-08T06:54:36.401770Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ;hR(eKAPZ6Ts
[root@mysql01 ~]#

登录数据库并修改密码为000000，开启远程

[root@mysql01 ~]# mysql -uroot -p';hR(eKAPZ6Ts'  #可mysql-uroot -p 然后输入密码
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.37

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

ALTER USER 'root'@'localhost' IDENTIFIED BY '000000';

use mysql;

update user set Host='%' where User='root';

flush privileges;

关闭防火墙和selinux，生产环境则开放端口

systemctl stop firewalld
setenforce 0

配置主从

修改主数据库的配置文件

cat  >> /etc/my.cnf <<EOF 
server-id = 1  
log-bin = mysql-bin  
binlog-ignore-db = mysql 
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-format = ROW 
EOF

参数详解

• server-id

  • 作用：唯一标识每个MySQL服务器，主从服务器必须有不同的server-id。
    示例：server-id=1

• log-bin

  • 作用：开启二进制日志功能，记录所有修改数据的SQL语句，必须开启才能
    示例：log-bin=mysql-bin

• binlog-format

  • 作用：设置二进制日志的格式，有三种模式：STATEMENT、ROWROW、MIXED。 示例：binlog-format=ROW（推荐使用ROW模式

• binlog-do-db

  • 作用：指定需要记录到二进制日志的数据库，仅当该参数被配置时，指定
    示例：binlog-do-db=mydb

• binlog-ignore-db

  • 作用：指定不需要记录到二进制日志的数据库，
    示例：binlog-ignore-db=test

• expire_logs_days

  • 作用：指定二进制日志的自动过期天数，过期
    示例：expire_logs_days=7

systemctl restart mysqld

创建用于主从复制的数据库用户

mysql -uroot -p000000

use mysql;

CREATE USER 'db_sync'@'%' IDENTIFIED BY '000000';

ALTER USER 'db_sync'@'%' REQUIRE NONE;
 
GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%';

FLUSH PRIVILEGES;

查看

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000001 |      157 |              | mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+

配置从服务器

cat >>/etc/my.cnf <<EOF

server-id = 2
relay-log = mysql-relay-bin 
log_bin = mysql-bin 
replicate-ignore-db = mysql 
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
EOF

参数详解

• server-id

  • 作用：同样用于唯一标识服务器，必须不同于主服务器和其他从服务器的server-id。
    示例：server-id=2

• relay-log

  • 作用：指定中继日志文件的名称。中继日志存储从主服务器接收的二进制日志事件，然后从服务器根据这些事件来执行相应的SQL语句
    示例：relay-log=relay-bin

• log-slave-updates

  • 作用：当从服务器执行从主服务器接收的二进制日志事件时，是否将这些事件也记录到自己的二进制日志中。如果需要将当前从服务器作为其他从服务器的主服务器时，必须启用此选项
    示例：log-slave-updates=1

• read-only

  • 作用：将从服务器设置为只读模式，防止在从服务器上直接进行数据修改操作。read-only模式会使普通用户无法执行写操作，但具有SUPER权限的用户仍然可以进行写操作
    示例：read-only=1

• skip-slave-start

  • 作用：配置从服务器在MySQL服务器启动时不自动启动复制进程。这样可以在服务器启动后手动控制何时启动复制，通常用于防止服务器重启时自动开始同步可能不完整的数据
    示例：skip-slave-start=1

• replicate-do-db

  • 作用：指定从服务器只复制某些数据库的操作。只有在replicate-do-db中列出的数据库中的操作才会被从服务器执行。这对需要从主服务器复制特定数据库的场景很有用
    示例：replicate-do-db=mydb

• replicate-ignore-db

  • 作用:指定从服务器忽略某些数据库的操作。这些数据库中的操作将不会在从服务器上执行。此选项用于排除不需要复制的数据库
    示例：replicate-ignore-db=test

• auto_increment_increment

  • 作用：用于控制在多主复制架构中，AUTO_INCREMENT列的步长。通过设置不同的步长，可以避免多个服务器在插入数据时产生冲突。
    示例：auto_increment_increment=2

• auto_increment_offset

  • 作用：指定AUTO_INCREMENT列的起始偏移量。当有多个从服务器时，设置不同的偏移量可以保证每个从服务器生成的AUTO_INCREMENT值是唯一的，防止冲突。
    示例：auto_increment_offset=1

• slave-skip-errors

  • 作用：在从服务器复制过程中，允许忽略指定的错误，避免因为某些特定错误导致复制进程停止。这在处理某些非关键错误时非常有用，但需要谨慎使用以避免数据不一致。
    示例：slave-skip-errors=1062

重启生效

systemctl restart mysqld

配置从库连接

mysql -uroot -p000000

STOP SLAVE;

CHANGE MASTER TO
 MASTER_HOST = '192.168.200.20',
 MASTER_USER = 'db_sync',
 MASTER_PASSWORD = '000000',
 MASTER_PORT=3306,
 MASTER_LOG_FILE = 'mysql-bin.000001',
 MASTER_LOG_POS = 157;

start slave;

查看slave信息为yes即可

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.200.20
                  Master_User: db_sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 157
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,information_schema,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 157
              Relay_Log_Space: 536
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: ef14edbb-55fd-11ef-939f-000c29e8a432
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

主库创建测试

从库查看

报错问题解决

Error connecting to source ‘db_sync@192.168.200.20:3306’. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.

MySQL 8.0及以上版本中，因为默认的身份验证插件已更改为caching_sha2_password，

关闭ssl认证连接（感觉没用）

mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+

echo ssl=0 >> /etc/my.cnf

systemctl restart mysqld

mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |
+---------------+----------+

关闭ssl连接，配置文件添加后重启，感觉也没用

[mysqld]
caching_sha2_password_auto_generate_rsa_keys=0

systemctl restart mysqld

主库修改同步用户的认证方式，有用，但是可能会让另外一个slave sql报错

ALTER USER 'db_sync'@'%' IDENTIFIED WITH mysql_native_password BY '000000';
FLUSH PRIVILEGES;

最后的办法，重新同步从库，主库上获取当前的二进制日志位置

mysql> show master status
    -> ;
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000004 |      894 |              | mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+

在从库上重置复制位置

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=894;
START SLAVE;

重启集群操作

重启主库： 确保所有客户端应用已停止写入操作。 在主库上执行：

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

从库停止slave

STOP SLAVE;

主库重启

systemctl restart mysqld

重启完成后，回到之前的MySQL会话，解锁表：

UNLOCK TABLES;

. 从库重启MySQL服务

systemctl restart mysqld

重启完成后，重新启动，检查从库状态：

start slave;

检查从库状态，确保 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes：

SHOW SLAVE STATUS\G

如果重启后发现复制出现问题，可能需要重新设置复制起点：

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='recorded_file', MASTER_LOG_POS=recorded_position;
START SLAVE;