Mysql8.x 基本操作

发表于 | 分类于

Mysql8.x 基本操作

记录mysql常用命令
Centos7环境 mysql8.0.15 数据库

允许root远程登录

  1. 添加远程登录的账户

    1
    create user 'root'@'%' identified with mysql_native_password by 'Admin@123';

  2. 添加授权

    1
    2
    grant all privileges on *.* to 'root'@'%' with grant option;
    flush privileges;

修改mysql8.0.15密码

  1. 编辑 /etc/my.cnf 去除注释或者添加 skip-grant-tables

  2. 重启mysql服务器，

    1
    sudo systemctl restart mysqld

  3. 登录数据库服务器，输入密码时直接回车即可

  4. 修改mysql密码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
use mysql;

# 清除之前的密码
update user set authentication_string=''  where user='root';
# 刷新一下
flush privileges;

exit;

systemctl restart mysqld

mysql -u root

use mysql;

alter user 'root'@'%' identified with mysql_native_password by 'Admin@123';

flush privileges;

exit;

mysql 自动备份脚本

#!/bin/bash
# Databases that you wish to be backed up by this script. You can have any number of databases specified; encapsilate each database name in single quotes and separate each database name by a space.
#
# Example:
# databases=( '__DATABASE_1__' '__DATABASE_2__' )
databases=('school_user' 'school_business' 'zhlh_user' 'am_api' 'zhlh_gardens' 'gitea')

# The host name of the MySQL database server; usually 'localhost'
db_host="127.0.0.1"

# The port number of the MySQL database server; usually '3306'
db_port="3306"

# The MySQL user to use when performing the database backup.
db_user="root"

# The password for the above MySQL user.
db_pass="amkj@123"

# Directory to which backup files will be written. Should end with slash ("/").
backups_dir="/oss/amkj-backup/sql/18/"

backups_user="root"

# Date/time included in the file names of the database backup files.
datetime=$(date +'%Y-%m-%d-%H:%M:%S')

for db_name in ${databases[@]}; do
        # Create database backup and compress using gzip.
        mysqldump -u $db_user -h $db_host -P $db_port --password=$db_pass $db_name | gzip -9 > $backups_dir$db_name--$datetime.sql.gz
done

# Set appropriate file permissions/owner.
chown $backups_user:$backups_user $backups_dir*--$datetime.sql.gz
chmod 0400 $backups_dir*--$datetime.sql.gz

# delte 10 days backup
find $backups_dir* -type f -mtime +15 -exec rm {} \;