Mysql-使用记录-持续更新
mysql安装(docker 版本)
- 准备docker 镜像文件
#创建docker镜像资源目录
mkdir /usr/local/docker/images
#上传mysql.tar 到/usr/local/docker/images下
#加载镜像
docker load -i mysql.tar
- 准备mysql文件
// 准备mysql 数据存储目录
mkdir -p /data/mysql-master/data
// 准备mysql 配置目录
mkdir -p /data/mysql-master/conf/conf.d
// 在/data/mysql-master/conf 下创建 my.cnf
touch /data/mysql-master/conf/my.cnf
my.cnf内容
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
server_id=100
log-bin=master-bin
binlog_format=row
bind-address=0.0.0.0
wait_timeout=31536000
interactive_timeout=31536000
character-set-server=utf8
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections = 1000
default_authentication_plugin=mysql_native_password
# Custom config should go here
!includedir /etc/mysql/conf.d/
- 启动mysql
docker run --restart always -itd --name mysql -e TZ=Asia/Shanghai -e MYSQL_ROOT_PASSWORD=ZHzbVUzF1g4EGh -p 3306:3306 -v /data/mysql-master/data:/var/lib/mysql -v /data/mysql-master/conf:/etc/mysql mysql --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
自动备份数据库
- 准备文件
// 准备crontab 脚本
mkdir -p /data/crontab
vi mysqlbackup.sh
//赋予执行权限
chmod +x mysqlbackup.sh
- 对应mysqlbackup.sh 内容
#!/bin/bash
docker exec mysql /var/lib/mysql/labwaydb-backup/backup.sh
在mysql数据安装目录准备具体备份脚本
// 切换到mysql数据目录
cd /data/mysql-master/data/
mkdir labwaydb-backup
vi backup.sh
//输入脚本内容
//赋予执行权限
chmod +x backup.sh
- 对应backup.sh脚本内容
#!/bin/bash
# 设置备份目录和文件名
backdir=/var/lib/mysql/labwaydb-backup
sqlfile=$(date +%Y%m%d).sql
tarfile=$sqlfile.tar.gz
logfile=$backdir/backup.log
# 定义日志文件最大大小(1GB)1GB in bytes
max_log_size=$((1024 * 1024 * 1024))
# 检查并处理日志文件大小
if [ -f "$logfile" ]; then
logsize=$(stat -c%s "$logfile")
if [ $logsize -ge $max_log_size ]; then
echo "$(date): Log file size exceeded 1GB, archiving..." >> $logfile
# 归档日志文件
mv "$logfile" "$logfile.$(date +%Y%m%d%H%M%S).bak"
touch "$logfile"
# 保留最新的2个归档日志文件,其余删除
archived_logs=($(ls -t $logfile.*.bak))
if [ ${#archived_logs[@]} -gt 2 ]; then
for ((i=2; i<${#archived_logs[@]}; i++)); do
rm -f "${archived_logs[$i]}"
done
fi
fi
fi
# 切换到备份目录
cd $backdir || {
echo "Failed to change directory to $backdir";
exit 1;
}
# 备份数据库
{
mysqldump -h localhost -uroot -pC53jeiX5AEHwYX --databases \
labway-lis-region labway-lis-base union-data xxl_job labway-report-pdf \
> $backdir/$sqlfile &&
echo "$(date): Database backup successful" >> $logfile;
} || {
echo "$(date): Database backup failed" >> $logfile;
exit 1;
}
# 压缩 SQL 文件
{
tar -czf $tarfile $sqlfile &&
echo "$(date): Compression successful" >> $logfile;
} || {
echo "$(date): Compression failed" >> $logfile;
exit 1;
}
# 删除原始 SQL 文件
rm -rf $sqlfile
# 删除超过5天的备份文件
find . -mtime +5 -name '*.tar.gz' -exec rm -rf {} \;
echo "$(date): Backup script completed" >> $logfile
- 增加定时任务
crontab -e
//每天凌晨2点执行
0 2 * * * /data/crontab/mysqlbackup.sh
数据库最大连接数量(max_connections)
- 查看数据最大连接数量
SHOW VARIABLES LIKE 'max_connections';
- 修改数据库最大连接数
- 方法一:
SET GLOBAL max_connections = 1000;
这个修改当mysql重启后最大连接数量会重置为默认151 - 方法二:
在my.cnf中增加配置,这个修改会始终生效
[mysqld] max_connections = 200
- 方法一:
用户信息
SELECT user, host, plugin FROM mysql.user;
修改用户密码验证方式
ALTER USER 'root'@'localhost' identified with mysql_native_password by 'test';