centos8 安装mysql 制作备份数据库脚本

1、配置阿里云源
cd /etc/yum.repos.d/

vim CentOS-Stream-AppStream.repo

[appstream]
name=CentOS Stream $releasever - AppStream
#mirrorlist=http://mirrorlist.centos.org/?release=$stream&arch=$basearch&repo=AppStream&infra=$infra
baseurl=http://mirrors.cloud.aliyuncs.com/$contentdir/$stream/AppStream/$basearch/os/
gpgcheck=1
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial

vim CentOS-Stream-Extras-common.repo

[extras-common]
name=CentOS Stream $releasever - Extras common packages
#mirrorlist=http://mirrorlist.centos.org/?release=$stream&arch=$basearch&repo=extras-extras-common
#baseurl=http://mirror.centos.org/$contentdir/$stream/extras/$basearch/extras-common/
baseurl=http://mirrors.cloud.aliyuncs.com/$contentdir/$stream/extras/$basearch/extras-common/
gpgcheck=1
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-Extras

vim  CentOS-Stream-BaseOS.repo

[baseos]
name=CentOS Stream $releasever - BaseOS
#mirrorlist=http://mirrorlist.centos.org/?release=$stream&arch=$basearch&repo=BaseOS&infra=$infra
baseurl=http://mirrors.cloud.aliyuncs.com/$contentdir/$stream/BaseOS/$basearch/os/
gpgcheck=1
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial

2、安装
yum -y install mysql-server

3、启动
systemctl start mysqld

4、创建用户
mysql

use mysql;
select host,user from user;
CREATE USER 'pw'@'localhost' IDENTIFIED BY 'pw234.';
CREATE USER 'pw'@'%' IDENTIFIED BY 'pw234.';

GRANT ALL PRIVILEGES ON *.* TO 'pw'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'pw'@'%' WITH GRANT OPTION;

flush privileges;

5、配置(远程数据库的用户名密码)
vim  /etc/my.cnf.d/mysql-server.cnf

[mysqldump]
user=pw
password=pw234.

6、测试命令
mysqldump -h 10.10.10.10 --column-statistics=0  pw_db > /home/pw/bf.sql

7、脚本内容

#!/bin/bash

# 设置变量
SERVER="10.10.10.10"
USERNAME="pw"
PASSWORD="pw234.."
DATABASE="pw_db"

SERVER2="localhost"
USERNAME2="pw"
PASSWORD2="pw234."
DATABASE2="pw_db"

BACKUP_PATH="/home/pw/data/"
DATE=$(date +"%m_%d-%H-%M-%S")

# 创建备份文件名
BACKUP_FILE="${DATE}.sql"

# 执行 mysqldump 命令
echo "正在备份数据库: $DATABASE"
mysqldump -h "$SERVER" --column-statistics=0 "$DATABASE" > "${BACKUP_PATH}${BACKUP_FILE}"

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "备份成功: ${BACKUP_PATH}${BACKUP_FILE}"
else
    echo "备份失败，错误代码: $?"
fi

8、查看数据库内容

查看表
show databases;

use pw_db;

show tables;