rocky linux mysql8.4主从环境搭建以及数据库备份全过程

准备工作

切换到root账户

ubuntu系统 执行 sudo -i 切换到root 方便操作

查看系统是否支持glibc2.28

因为安装的版本比较新需要新版glic2.28

redhat、centos系列使用以下命令

strings /lib64/libc.so.6 | grep GLIBC_2.28

1

有输出版本号证明支持

debian、ubuntu系列使用以下命令

strings /lib/x86_64-linux-gnu/libc.so.6 | grep GLIBC_2.28

1

若是不支持需要添加 GLIBC_2.28 查看这篇教程安装

移除旧版本

如果你安装过mysql 为了防止出现奇奇怪怪问题，我们把旧版本卸载删除相关包和残留文件

redhat、centos系列使用yum命令

# yum 查询已安装的 MySQL 相关包

rpm -qa | grep -i mysql

# 卸载 MySQL 相关包

yum remove mysql-*

# 卸载完再查询还有无相关包

rpm -qa | grep -i mysql

debian、ubuntu系列使用apt命令

# 卸载mysql相关

   apt-get remove --purge mysql-*

   # 清除残留

   apt-get autoremove

   apt-get autoclean

删除相关配置文件和安装目录

  rm -rf /etc/my.cnf

  rm -rf /var/lib/mysql

  rm -rf /etc/mysql

安装mysql依赖

安装mysql所需要的依赖

redhat、centos系列使用yum命令

yum install -y libaio

yum install -y ncurses-compat-libs

debian、ubuntu系列使用apt命令

apt-get install libaio1

下载MySQL

去官网下载页面 获取下载链接

注意查看系统架构 uname -m。如果你是arm架构选择arm版本的二进制包

wget命令下载安装包

# -P 指定下载目录

wget -P /soft https://cdn.mysql.com//Downloads/MySQL-8.4/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz

开始安装mysql

创建运行mysql的用户和用户组

groupadd mysql

useradd -r -g mysql -s /bin/false mysql

解压安装包

解压到 /usr/local/

tar xvf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz -C /usr/local/

如果你的tar 命令不支持解压.xz格式 使用下面命令解压

tar -Jxf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz

解压后目录名太长改成 mysql

mv /usr/local/mysql-8.4.0-linux-glibc2.28-x86_64 /usr/local/mysql

修改权限创建目录

依次执行下面命令

# 创建数据目录，根据自己需求调整

mkdir -p /disk/mysqldata

cd /usr/local/mysql

mkdir mysql-files

mkdir log

touch log/error.log

chmod 750 mysql-files

chown mysql:mysql -R /usr/local/mysql/

chown mysql:mysql /disk/mysqldata

初始化mysql

#--basedir 安装目录  --datadir数据存储目录 可以按需调整

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

查看初始化日志 root账户的初始化密码输出在日志中

#初始化日志输出到 /usr/local/mysql/log/error.log 文件中

#查看该文件

cat /usr/local/mysql/log/error.log

设置配置文件

配置文件位于 /etc/my.cnf

创建配置文件

vim /etc/my.cnf  （master服务器配置文件）

文件中写入以下内容（配置文件根据自己需要调整）

注意看下边有主备mysql配置文件

启动mysql

初始化后已经自动注册了系统服务 使用 systemctl 命令管理

启动

systemctl start mysqld

如果启动报错使用 journalctl -u mysqld.service 命令查看日志

查看状态

systemctl status mysqld

active (running) 表示运行中

停止

systemctl stop mysqld

重启

systemctl restart mysqld

设置mysql开机自启

systemctl enable mysqld

若是不能执行 systemctl mysqld 相关命令，有的系统上mysql初始化完会自动创建单元服务配置文件，有的没有。 没有关系小问题。 没有自动创建我们就手动创建

创建单元服务配置文件

sudo sudo vi /etc/systemd/system/mysql.service  （注意rocky linux是/etc下面，其他可能是/lib/systemd,别踩坑）

填入以下内容

ini

[Unit]

Description=MySQL Community Server

After=network.target

[Service]

User=mysql

Group=mysql

ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf

LimitNOFILE = 5000

[Install]

WantedBy=multi-user.target

然后重新加载单元配置

sudo systemctl daemon-reload

sudo systemctl enable mysql

现在就可以执行 systemctl start mysqld 相关命令了

修改默认密码

/usr/local/mysql/bin/mysql_secure_installation

配置主从同步

修改配置文件/etc/my.cnf  (master)

[client]

# 指定默认字符集

default-character-set=utf8mb4

[mysqld_safe]

# 错误日志的存放位置。

log_error = /usr/local/mysql/data/error.log

# 存放服务器进程 ID 的文件

pid_file = /usr/local/mysql/data/mysqld.pid

[mysql]

socket = /tmp/mysql.sock

[mysqld]

# mysql 的运行用户 这里的用户一定要和初始化时的用户一致

user = mysql

# 指定 MySQL 服务监听的端口为 3306

port = 3306

# 指定 MySQL 的安装目录

basedir = /usr/local/mysql

# 指定数据文件的存储位置。

datadir = /usr/local/mysql/data

socket  = /tmp/mysql.sock

# 日志目录

log_error = /usr/local/mysql/log/error.log

innodb_file_per_table = 1 #每个表有自己的表空间

character_set_server=utf8mb4 #字符集

skip_name_resolve = 1 #禁用DNS主机名查找

max_connections = 500

max_connect_errors = 100 #某台host连接错误次数等于max_connect_errors（默认10） ，主机'host_name'再次尝试时被屏蔽。

slow_query_log = 1 #是否开启慢查询日志收集

slow_query_log_file = /var/log/mysql/mysql-slow.log

log_queries_not_using_indexes = 1 #是否记录未使用索引的语句

log_slow_admin_statements = 1 #慢查询也记录那些慢的optimize table，analyze table和alter table语句

long_query_time = 10 #设置记录慢查询超时时间

validate_password_policy=0

innodb_large_prefix=on

skip_ssl #禁用ssl

log_bin_trust_function_creators=1 #用于在bin-log中记录创建的函数

#主从配置

server-id=1

log-bin=master-bin

expire_logs_days = 40

max_binlog_size = 1G

#配置不同步的库

binlog-ignore-db = mysql

binlog-ignore-db = sys

binlog-ignore-db = information_schema

binlog-ignore-db = performance_schem

vi /etc/my.cnf   (slave)

[client]

# 指定了客户端与服务器通信的套接字文件路径

socket = /tmp/mysql.sock

[mysqld_safe]

# 错误日志的存放位置。

log_error = /usr/local/mysql/log/mysql-error.log

# 存放服务器进程 ID 的文件

pid_file = /usr/local/mysql/data/mysqld.pid

[mysql]

socket = /tmp/mysql.sock

[mysqld]

# skip-grant-tables

socket = /tmp/mysql.sock

# mysql 的运行用户 这里的用户一定要和初始化时的用户一致

user = mysql

# 指定 MySQL 服务监听的端口为 3306

port = 3306

# 指定 MySQL 的安装目录

basedir = /usr/local/mysql

# 指定数据文件的存储位置。

datadir = /usr/local/mysql/data

# 日志目录

log_error = /usr/local/mysql/log/error.log

join_buffer_size = 128M

sort_buffer_size = 32M #是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存

read_rnd_buffer_size = 32M #随机读缓冲区大小，当按任意顺序读取行时（列如按照排序顺序）将分配一个随机读取缓冲区，进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度

innodb_file_per_table = 1 #每个表有自己的表空间

character_set_server=utf8mb4 #字符集

skip_name_resolve = 1 #禁用DNS主机名查找

replica_skip_errors = all

max_connections = 500

max_connect_errors = 100 #某台host连接错误次数等于max_connect_errors（默认10） ，主机'host_name'再次尝试时被屏蔽。

slow_query_log = 1 #是否开启慢查询日志收集

slow_query_log_file = /usr/local/mysql/log/mysql-slow.log

log_queries_not_using_indexes = 1 #是否记录未使用索引的语句

log_slow_admin_statements = 1 #慢查询也记录那些慢的optimize table，analyze table和alter table语句

long_query_time = 10 #设置记录慢查询超时时间

log_bin_trust_function_creators=1 #用于在bin-log中记录创建的函数

#主从配置

server-id=2

log-bin=binlog-bin

max_binlog_size = 1G

read_only=on

binlog-ignore-db = mysql

binlog-ignore-db = sys

binlog-ignore-db = information_schema

binlog-ignore-db = performance_schema

建立同步账号

mysql> create user rep@'1.1.1.1' identified  by 'yourpasswd';

Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to rep@'1.1.1.2';

Query OK, 0 rows affected (0.00 sec)

mysql> show binary log status;

+---------------+----------+--------------+------------------+-------------------+

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| binlog.000002 |      106 |              |                  |                   |

+---------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

1 row in set (0.00 sec)

配置连接到主服务器的相关信息

change replication source to source_host='1.1.1.1', source_log_file='binlog.000002',source_log_pos=106,source_user='rep',source_password='yourpasswd',source_ssl=1;

start replica user='rep' password='yourpasswd';

mysql> show replica status\G

*************************** 1. row ***************************

            Replica_IO_State: Waiting for source to send event

                 Source_Host: 1.1.1.1

                 Source_User: rep

                 Source_Port: 3306

               Connect_Retry: 60

             Source_Log_File: binlog.000002

         Read_Source_Log_Pos: 106

              Relay_Log_File: slave1-relay-bin.000002

               Relay_Log_Pos: 192

       Relay_Source_Log_File: binlog.000002

          Replica_IO_Running: Yes

         Replica_SQL_Running: Yes

             Replicate_Do_DB:  

         Replicate_Ignore_DB:  

          Replicate_Do_Table:  

      Replicate_Ignore_Table:  

     Replicate_Wild_Do_Table:  

 Replicate_Wild_Ignore_Table:  

                  Last_Errno: 0

                  Last_Error:  

                Skip_Counter: 0

         Exec_Source_Log_Pos: 106

             Relay_Log_Space: 706

             Until_Condition: None

              Until_Log_File:  

               Until_Log_Pos: 0

          Source_SSL_Allowed: Yes

          Source_SSL_CA_File:  

          Source_SSL_CA_Path:  

             Source_SSL_Cert:  

           Source_SSL_Cipher:  

              Source_SSL_Key:  

       Seconds_Behind_Source: 0

Source_SSL_Verify_Server_Cert: No

               Last_IO_Errno: 0

               Last_IO_Error:  

              Last_SQL_Errno: 0

              Last_SQL_Error:  

 Replicate_Ignore_Server_Ids:  

            Source_Server_Id: 1

                 Source_UUID: 8888

            Source_Info_File: mysql.slave_master_info

                   SQL_Delay: 0

         SQL_Remaining_Delay: NULL

   Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates

          Source_Retry_Count: 10

                 Source_Bind:  

     Last_IO_Error_Timestamp:  

    Last_SQL_Error_Timestamp:  

              Source_SSL_Crl:  

          Source_SSL_Crlpath:  

          Retrieved_Gtid_Set:  

           Executed_Gtid_Set:  

               Auto_Position: 0

        Replicate_Rewrite_DB:  

                Channel_Name:  

          Source_TLS_Version:  

      Source_public_key_path:  

       Get_Source_public_key: 0

           Network_Namespace:  

1 row in set (0.00 sec)之前版本的 MySQL 中已弃用的与 MySQL 复制相关的许多功能的语法现已删除，具体清单如下。

Removed Replacements

START SLAVE START REPLICA

STOP SLAVE        STOP REPLICA

SHOW SLAVE STATUS SHOW REPLICA STATUS

SHOW SLAVE HOSTS    SHOW REPLICAS

RESET SLAVE                         RESET REPLICA

CHANGE MASTER TO         CHANGE REPLICATION SOURCE TO

RESET MASTER                 RESET BINARY LOGS AND GTIDS

SHOW MASTER STATUS SHOW BINARY LOG STATUS

PURGE MASTER LOGS   PURGE BINARY LOGS

SHOW MASTER LOGS  SHOW BINARY LOGS

测试主从同步

在主上运行

create database test;

mysql> create table t1( name char(15) , homeadd char(20) , class char(7) );

Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values("tom","beijing","2016");

Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values("lisi","baoding","2006"),("zhangsan","zjk","2006");

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

在从上查看表格的变化是否一致。

下面附上备份脚本实例

#!/bin/bash

# 数据库连接信息

USER="root"

PASSWORD="yourpasswd"

HOST="1.1.1.1"

# 需要备份的数据库列表

DATABASES=("data1" "data2" "data3")

# 备份存储路径

BACKUP_DIR="/home/backup"

DATE=$(date +"%Y%m%d")

BACKUP_FILE=$DATE.sql.gz

# FTP 服务器信息

FTP_HOST="192.1.13.1"

FTP_USER="ftpuser"

FTP_PASSWORD="ftppasswd"

FTP_DIR="/mysqlbackup"

# 创建备份目录（如果不存在）

mkdir -p "$BACKUP_DIR"/$DATE

# 循环备份每个数据库

for DB in "${DATABASES[@]}"; do

   BACKUP_FILE="$BACKUP_DIR/$DATE/$DB-$DATE.sql.gz"   

#这里设置的是备份不锁表，快速备份，并备份触发器和存储过程，参数根据环境而定

   mysqldump -u "$USER" -p"$PASSWORD" -h "$HOST" --single-transaction --quick --triggers --routines --events "$DB" | gzip > $BACKUP_FILE

   

   if [ $? -eq 0 ]; then

       echo "Backup of database '$DB' completed successfully."

       

       # 上传备份文件到 FTP 服务器

       ftp -inv $FTP_HOST <<EOF

user $FTP_USER $FTP_PASSWORD

binary

prompt

cd $FTP_DIR

mkdir $DATE

cd $DATE

lcd "$BACKUP_DIR"/$DATE

put $DB-$DATE.sql.gz

bye

EOF

       if [ $? -eq 0 ]; then

           echo "Uploaded $BACKUP_FILE to FTP server successfully."

      #删除本地备份文件，这个根据需求而定

      rm -rf $BACKUP_FILE

       else

           echo "Error occurred while uploading $BACKUP_FILE to FTP server."

       fi

   else

       echo "Error occurred while backing up database '$DB'."

   fi

done

创建zabbix监控主从健康状况脚本：

#!/bin/bash

# MySQL 配置

MYSQL_USER="username"

MYSQL_PASSWORD="yourpasswd"

#MYSQL_HOST="1.1.1.2"

# 检查主从状态

REPL_STATUS=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD  -e "SHOW REPLICA STATUS\G")

# 获取 Slave_IO_Running 和 Slave_SQL_Running 状态

SLAVE_IO_RUNNING=$(echo "$REPL_STATUS" | grep "Replica_IO_Running" | awk '{print $2}')

SLAVE_SQL_RUNNING=$(echo "$REPL_STATUS" | grep "Replica_SQL_Running" | grep -v "Replica_SQL_Running_State" | awk '{print $2}')

# 将表示运行结果的数字输入日志方便zabbix监控，脚本日志输入到log中可以查询主从状态的历史

if [[ "$SLAVE_IO_RUNNING" == "Yes" && "$SLAVE_SQL_RUNNING" == "Yes" ]]; then

   echo "2" > /home/backup/mysqlcheckreplica.log # 主从正常

   echo "`date`  :主从正常"

else

   echo "0" /home/backup/mysqlcheckreplica.log # 主从异常

   echo "`date` :主从异常"

fi