前提:

因为服务器本身是用的docker环境,所以为了便于方便管理,MYSQL的安装也采用了docker方式。

1.创建目录:

    mkdir -p /usr/local/docker/mysql/logs
    mkdir -p /usr/local/docker/mysql/data
    mkdir -p /usr/local/docker/mysql/conf

2.创建my.cnf文件:

    cd /usr/local/docker/mysql/conf
    vim my.cnf
    [mysql]
    #设置mysql客户端默认字符集
    default-character-set=utf8
    socket=/var/lib/mysql/mysql.sock

    [mysqld]
    #mysql5.7以后的不兼容问题处理
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    # Settings user and group are ignored when systemd is used.
    # If you need to run mysqld under a different user or group,
    # customize your systemd unit file for mariadb according to the
    # instructions in http://fedoraproject.org/wiki/Systemd
    #允许最大连接数
    max_connections=200
    #服务端使用的字符集默认为8比特编码的latin1字符集
    character-set-server=utf8
    #创建新表时将使用的默认存储引擎
    default-storage-engine=INNODB
    lower_case_table_names=1
    max_allowed_packet=16M 
    #设置时区
    default-time_zone='+8:00'
    [mysqld_safe]
    log-error=/var/log/mariadb/mariadb.log
    pid-file=/var/run/mariadb/mariadb.pid

    #
    # include all files from the config directory
    #

    !includedir /etc/mysql/conf.d/
    !includedir /etc/mysql/mysql.conf.d/

3.启动容器:

    docker run --name webuy-test -p 3306:3306 --privileged=true --restart=always -d -v /usr/local/docker/mysql/logs:/logs -v /usr/local/docker/mysql/data:/var/lib/mysql -v  /usr/local/docker/mysql/conf/my.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf -e MYSQL_ROOT_PASSWORD=Utouchable2020@# mysql:5.7

4.常见命令

  1. 创建数据库并设置格式:
    CREATE database 数据库名 default character set utf8mb4 collate utf8mb4_general_ci;
  1. 创建用户并设置密码:
    CREATE USER 'xxx'@'%' IDENTIFIED BY 'xxx';
  1. 赋予用户某个数据库的所有权限:
    GRANT ALL PRIVILEGES ON 数据库名.* TO 'xxx'@'%';
  1. 赋予用户某个数据库的某个权限(如查询):
    GRANT SElECT ON 数据库名.* TO 'xxx'@'%' IDENTIFIED BY "xxx!";
  1. 收回赋予用户的所有权限:
    REVOKE ALL PRIVILEGES,GRANT OPTION  on 数据库名.* FROM 'xxx'@'%';
    REVOKE ALL PRIVILEGES on 数据库名.*  from 'xxx'@'%';
  1. 收回赋予用户的某个数据库的所有权限:
    REVOKE ALL PRIVILEGES,GRANT OPTION on 数据库名.* from 'xxx'@'%';
  1. 查询用户以及权限:
    select User, host from mysql.user;
  1. 修改用户密码:
    update user set authentication_string=password('xxx') where user='root' and Host='localhost'
  1. 刷新权限:
    FLUSH PRIVILEGES;
  1. 设置数据库模式:
    set @@global.sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
  1. 备份指定数据库:
    docker exec -it {container:ID} mysqldump -uroot -p 数据库名> /usr/local/mysqlbk/数据库名.sql
  1. 导入指定数据库:
    docker exec -i {container:ID} mysqldump -uroot -pxxx 数据库名< /usr/local/mysqlbk/数据库名.sql