Centos7.9安装mysql8.0

#!/bin/bash

MYSQL_TAR_FILE="mysql-8.0.32-linux-glibc2.17-x86_64-minimal.tar.xz"
MYSQL_DIR="mysql-8.0.32-linux-glibc2.17-x86_64-minimal"
MYSQL_INSTALL_DIR="/usr/local/mysql"
MYSQL_DATA_DIR="/var/lib/mysql"
MYSQL_CNF_FILE="/etc/my.cnf"
MYSQL_RUN_DIR="/var/run/mysqld"
MYSQL_SOCK_FILE="$MYSQL_RUN_DIR/mysqld.sock"

# 打印并执行命令
run_command() {
    echo "执行命令: $@"
    "$@"
}

# 等待MySQL服务器启动
wait_for_mysql() {
    max_attempts=30
    attempt=1
    while ! run_command mysql -u root --socket=$MYSQL_SOCK_FILE -e "SELECT 1" >/dev/null 2>&1; do
        if [ $attempt -gt $max_attempts ]; then
            echo "等待MySQL服务器启动超时！"
            exit 1
        fi
        echo "等待MySQL服务器启动... 尝试 $attempt/$max_attempts"
        sleep 1
        attempt=$((attempt + 1))
    done
}

# 卸载MySQL函数
uninstall_mysql() {
    run_command systemctl stop mysql
    run_command systemctl disable mysql
    run_command yum remove -y mysql-community-server mysql-community-client mysql-community-libs mysql-community-common
    run_command rm -rf $MYSQL_INSTALL_DIR
    run_command rm -rf $MYSQL_DATA_DIR
    run_command rm -f $MYSQL_CNF_FILE
    run_command rm -f $MYSQL_RUN_DIR
    run_command userdel mysql
    run_command groupdel mysql
    echo "MySQL已卸载并清理完成！"
}

# 创建MySQL systemd服务文件
create_mysql_service() {
    service_file="/etc/systemd/system/mysql.service"

    echo "[Unit]" > $service_file
    echo "Description=MySQL Server" >> $service_file
    echo "After=network.target" >> $service_file
    echo "" >> $service_file
    echo "[Service]" >> $service_file
    echo "ExecStart=$MYSQL_INSTALL_DIR/bin/mysqld_safe --defaults-file=$MYSQL_CNF_FILE --user=mysql" >> $service_file
    echo "ExecStop=$MYSQL_INSTALL_DIR/bin/mysqladmin -uroot shutdown" >> $service_file
    echo "User=mysql" >> $service_file
    echo "Group=mysql" >> $service_file
    echo "Restart=always" >> $service_file
    echo "PrivateTmp=true" >> $service_file
    echo "" >> $service_file
    echo "[Install]" >> $service_file
    echo "WantedBy=multi-user.target" >> $service_file

    echo "创建MySQL systemd服务文件完成！"
}

# 选择安装或卸载选项
select_option() {
    echo "请选择要执行的操作:"
    echo "1. 安装 MySQL"
    echo "2. 卸载 MySQL"
    read -p "输入选项数字: " choice

    case $choice in
        1) install_mysql ;;
        2) uninstall_mysql ;;
        *) echo "无效的选项！" ;;
    esac
}

# 安装MySQL函数
install_mysql() {
    # 检查本地是否已有MySQL安装包
    if [ -f "$MYSQL_TAR_FILE" ]; then
        echo "找到本地已有的MySQL安装包：$MYSQL_TAR_FILE"
    else
        echo "未找到本地MySQL安装包，开始下载..."
        run_command wget https://cdn.mysql.com/archives/mysql-8.0/$MYSQL_TAR_FILE
    fi

    # 检查是否存在MySQL进程，存在则终止
    mysql_processes=$(pgrep mysql)
    if [ -n "$mysql_processes" ]; then
        echo "检测到存在MySQL进程，开始终止..."
        run_command pkill -9 mysql
        while pgrep mysql >/dev/null 2>&1; do
            echo "等待MySQL进程终止..."
            sleep 1
        done
    fi

    # 删除MySQL数据目录
    run_command rm -rf $MYSQL_DATA_DIR

    # 解压缩MySQL压缩包
    run_command tar xf $MYSQL_TAR_FILE

    # 重命名解压后的目录
    run_command mv $MYSQL_DIR mysql

    # 移动MySQL目录到适当的位置
    run_command mv mysql $MYSQL_INSTALL_DIR

    # 创建MySQL组和用户
    run_command groupadd mysql
    run_command useradd -m -g mysql mysql

    # 创建MySQL数据目录并设置权限
    run_command mkdir -p $MYSQL_DATA_DIR
    run_command chown -R mysql:mysql $MYSQL_DATA_DIR

    run_command mkdir -p $MYSQL_RUN_DIR
    run_command chown -R mysql:mysql $MYSQL_RUN_DIR

    # 创建MySQL配置文件
    run_command touch $MYSQL_CNF_FILE
    run_command chmod 644 $MYSQL_CNF_FILE
    echo "[mysqld]" > $MYSQL_CNF_FILE
    echo "basedir=$MYSQL_INSTALL_DIR" >> $MYSQL_CNF_FILE
    echo "datadir=$MYSQL_DATA_DIR" >> $MYSQL_CNF_FILE
    echo "socket=$MYSQL_SOCK_FILE" >> $MYSQL_CNF_FILE
    echo "port=3306" >> $MYSQL_CNF_FILE
    echo "user=mysql" >> $MYSQL_CNF_FILE

    # 初始化MySQL数据库
    run_command $MYSQL_INSTALL_DIR/bin/mysqld --defaults-file=$MYSQL_CNF_FILE --initialize-insecure --user=mysql

    # 创建MySQL systemd服务文件
    create_mysql_service

    # 启动MySQL服务
    run_command systemctl daemon-reload
    run_command systemctl start mysql

    export PATH="$MYSQL_INSTALL_DIR/bin:$PATH"

    # 等待MySQL服务器启动
    wait_for_mysql

    # 生成随机密码
    new_password=$(openssl rand -base64 12)

    # 设置MySQL的root用户密码
    run_command mysql -u root --socket=$MYSQL_SOCK_FILE <<EOF
ALTER USER 'root'@'localhost' IDENTIFIED BY '$new_password';
FLUSH PRIVILEGES;
EOF

    # 添加MySQL到系统PATH
    if ! grep -q "export PATH=\"$MYSQL_INSTALL_DIR/bin:\$PATH\"" ~/.bashrc; then
        echo "export PATH=\"$MYSQL_INSTALL_DIR/bin:\$PATH\"" >> ~/.bashrc
        source ~/.bashrc
    fi

    # 在mysql用户下设置MySQL到系统PATH
    if ! grep -q "export PATH=\"$MYSQL_INSTALL_DIR/bin:\$PATH\"" ~mysql/.bashrc; then
        echo "export PATH=\"$MYSQL_INSTALL_DIR/bin:\$PATH\"" | sudo -u mysql tee -a ~mysql/.bashrc > /dev/null
        sudo -u mysql sh -c '. ~mysql/.bashrc'
    fi

    echo "MySQL安装已完成！"
    echo "MySQL root 用户密码已更新为: $new_password"
}

# 选择安装或卸载选项
select_option

#!/bin/bash

XTRABACKUP_URL="https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.23-16/binary/tarball/percona-xtrabackup-8.0.23-16-Linux-x86_64.glibc2.17.tar.gz"
INSTALL_DIR="/usr/local/xtrabackup"
XTRABACKUP_ARCHIVE="percona-xtrabackup-8.0.23-16-Linux-x86_64.glibc2.17.tar.gz"

# 打印并执行命令
run_command() {
    echo "执行命令: $@"
    "$@"
}

# 下载并安装 XtraBackup
install_xtrabackup() {
    # 检查本地是否已经下载 xtrabackup 包
    if [ -f "$XTRABACKUP_ARCHIVE" ]; then
        echo "找到本地已有的XtraBackup安装包：$XTRABACKUP_ARCHIVE"
    else
        echo "未找到本地XtraBackup安装包，开始下载..."
        run_command wget "$XTRABACKUP_URL" -O "$XTRABACKUP_ARCHIVE"
    fi

    # 创建安装目录
    run_command mkdir -p "$INSTALL_DIR"

    # 解压缩 XtraBackup
    run_command tar xf "$XTRABACKUP_ARCHIVE" -C "$INSTALL_DIR" --strip-components=1

    # 添加 XtraBackup 到系统 PATH
    if ! grep -q "export PATH=\"$INSTALL_DIR/bin:\$PATH\"" ~/.bashrc; then
        echo "export PATH=\"$INSTALL_DIR/bin:\$PATH\"" >> ~/.bashrc
        source ~/.bashrc
    fi

    # 在mysql用户下设置MySQL到系统PATH
    if ! grep -q "export PATH=\"$INSTALL_DIR/bin:\$PATH\"" ~mysql/.bashrc; then
        echo "export PATH=\"$INSTALL_DIR/bin:\$PATH\"" | sudo -u mysql tee -a ~mysql/.bashrc > /dev/null
        sudo -u mysql sh -c '. ~mysql/.bashrc'
    fi

    echo "XtraBackup安装已完成！"
}

# 卸载 XtraBackup
uninstall_xtrabackup() {
    # 移除 XtraBackup 目录
    run_command rm -rf "$INSTALL_DIR"

    # 从系统 PATH 中移除 XtraBackup
    sed -i '/export PATH=\"\$INSTALL_DIR\/bin:\$PATH\"/d' ~/.bashrc

    echo "XtraBackup已卸载！"
}

# 选择安装或卸载选项
select_option() {
    echo "请选择要执行的操作:"
    echo "1. 安装 XtraBackup"
    echo "2. 卸载 XtraBackup"
    read -p "输入选项数字: " choice

    case $choice in
        1) install_xtrabackup ;;
        2) uninstall_xtrabackup ;;
        *) echo "无效的选项！" ;;
    esac
}

# 选择安装或卸载选项
select_option

mysql相关操作

create user skyline93@'%' identified by 'password';
grant all privileges on *.* to skyline93@'%' with grant option;
flush privileges;

修改加密规则

alter user skyline93@'%' identified with mysql_native_password by 'password';

删除用户

drop user 'username'@'host';

创建数据库（例如backupdb）

create database backupdb;
create database backupdb character set utf8mb4 collate utf8mb4_bin;

设置login-path

/usr/local/mysql/8.0.28/bin/mysql_config_editor set --login-path=local --host=DB主机IP --user=skyline93 --socket=/var/lib/mysql/mysql.sock --password

qpress安装

wget http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/183466/cn_zh/1608011575185/qpress-11-linux-x64.tar

mock数据

#!/bin/bash

# MySQL登录路径
login_path=local

# 数据库名和表名
dbname=dbtest
tbname=tbtest

# 创建数据库
mysql --login-path=$login_path -e "CREATE DATABASE IF NOT EXISTS $dbname;"

# 进入数据库
mysql --login-path=$login_path $dbname << EOF

# 创建表
CREATE TABLE IF NOT EXISTS $tbname (
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

EOF

# 循环插入数据
while true; do
    # 生成随机8位字符串
    name=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 8 | head -n 1)

    # 插入数据
    mysql --login-path=$login_path $dbname -e "INSERT INTO $tbname (name) VALUES ('$name');COMMIT;"

    # 获取当前时间戳
    timestamp=$(date +"%Y-%m-%d %H:%M:%S")

    # 记录日志
    echo "Inserted data: created_at=$timestamp, name=$name"

    # 等待1秒
    sleep 1
done