LiuYuan's Blog

#MySQL备份脚本
#!/bin/sh
#定义数据库host及备份目录
backup_host="10.0.0.1"
backup_dir=/data/mysql_bak
# 定义数据库账号密码及备份语句
mysql_user="user"
mysql_password="passwd"
mysqldump_opts="-h $backup_host -u $mysql_user -p$mysql_password --default-character-set=utf8"
            date=$(date '+%Y%m%d%H%M')
#以下为定义数据库库名，二选一
# 定义数据库库名
databases='databasename1 databasename2'
# 自动获取数据库库名
databases=$(mysql -h $backup_host -u $mysql_user -p$mysql_password -e 'SHOW DATABASES;' | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")
#测试数据库
umask 0077
test ! -d "$backup_dir" && mkdir -p "$backup_dir"
test ! -w "$backup_dir" && echo "Error: $backup_dir is un-writeable." && exit 0
# 循环备份
for database in $databases
              do
              /usr/local/mysql/bin/mysqldump $mysqldump_opts $database > "$backup_dir/$database-$date.sql"
  done
# 打包备份文件并删除7天前的备份
cd "$backup_dir"
            tar zcvf "mysqlbak_$date.tar.gz" *.sql
rm -rf *.sql
find "$backup_dir" -type f -mtime +7 

CentOS7安装

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum install mysql-community-server
systemctl start mysqld
systemctl enable mysqld
grep "A temporary password" /var/log/mysqld.log
mysql_secure_installation

其他常用命令

#清空某张表的数据
TRUNCATE TABLE tablename;
#给某用户某个数据库的权限
grant all on dbname.* to 'username'@'%';
flush privileges;
#给某个用户所有数据库权限
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;