mysql5.7常用操作

准备环境

• centos7.2(需要配置好网络)
• mysql5.7

安装mysql5.7

Last login: Tue Jan 26 11:09:34 2021 from 172.16.1.1
[root@localhost ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.44-2.el7.centos.x86_64
[root@localhost ~]# rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64
[root@localhost src]# wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz

[root@localhost src]# tar -xzf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz 

[root@localhost src]#  yum install ncurses-devel autoconf -y
[root@localhost src]# useradd -s /sbin/nologin -d /usr/local/mysql -M mysql
[root@localhost src]# mkdir /data/mysql/data -p
[root@localhost ]# chown -R mysql. /data/mysql

[root@localhost src]# ln -s /usr/local/src/mysql-5.7.32-linux-glibc2.12-x86_64 /usr/local/mysql
[root@localhost src]# vi /etc/my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/data
port = 3306
socket = /tmp/mysql.sock
log-error = error.log
slow_query_log_file = slow.log
character-set-server = utf8
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
lower_case_table_names =1

[root@localhost src]# cp mysql-5.7.32-linux-glibc2.12-x86_64/support-files/mysql.server /etc/init.d/mysqld

初始化mysql
[root@localhost src]# /usr/local/mysql/bin/mysqld --initialize

[root@localhost src]# echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile
[root@localhost src]# source /etc/profile
[root@localhost src]# service mysqld start
Starting MySQL. SUCCESS!

常用操作

获取mysql5.7初始密码

[root@localhost ~]# grep 'temporary password' /data/mysql/data/error.log
2021-01-26T06:06:02.045346Z 1 [Note] A temporary password is generated for root@localhost: k%2jhbdplEM(

k%2jhbdplEM(   就是初始密码

补充：mysql5.6在刚刚安装时没有初始密码

设置与修改密码

设置密码

[root@localhost ~]# mysqladmin -uroot password '123456' -p'k%2jhbdplEM('
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
补充：mysql5.6 在最开始设置密码时不需要参数-p

也可以利用初始密码登录mysql来修改mysql密码

mysql> alter user user() identified by '12345';
Query OK, 0 rows affected (0.00 sec)

修改密码

在知道原有密码的情况下，可以通过shell命令行修改mysql用户密码
[root@localhost ~]# mysqladmin -uroot -p123456 password 123321
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

重置root密码

场景：当忘记mysql密码时，可以通过跳过验证修改mysql表达到目的

• 在/etc/my.cnf中的
  [mysqld]字段
  添加

  [root@localhost ~]# vim /etc/my.cnf
  ......
  [mysqld]
  skip-grant-tables=1
  ......
  
  补充：mysql5.6是在/etc/my.cnf中的
      [mysqld]字段
      添加skip-grant
  

• 重启并修改密码

  [root@localhost ~]# /etc/init.d/mysqld restart
  Shutting down MySQL.. SUCCESS! 
  Starting MySQL. SUCCESS!
  mysql> update mysql.user set authentication_string=password('asdfjkl') where user='root';    
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 1
  
  补充：mysql5.7的mysql数据库中已经没有password这个字段了，password字段改成了authentication_string。
  	mysql5.6更改密码时，请把authentication_string替换成password
  	
  

• 删除skip-grant-tables=1字段并重启

  [root@localhost ~]# vim /etc/my.cnf
  ......
  [mysqld]
  #skip-grant-tables=1
  ......
  
  可以注释掉skip-grant-tables=1
  [root@localhost ~]# service mysqld restart
  Shutting down MySQL.. SUCCESS! 
  Starting MySQL. SUCCESS!
  

连接MySQL

• 一般连接方式

  格式 mysql  -u指定用户 -p密码 [-h主机名] [数据库名]
  [root@localhost ~]# mysql -uroot -p -h localhost mysql
  注意:使用-p参数时，如果要明文输密码-p与密码之间不要有空格，否则会被认为是数据库名
          
  

• 指定端口方式

  使用-P参数指定端口
  [root@localhost ~]# mysql -uroot -pasdfjkl -P3306
  

• 指定套接字文件方式

  使用-s参数指定套接字文件
  [root@localhost ~]# mysql -uroot -pasdfjkl -S /tmp/mysql.sock
  

• 非交互式使用

  [root@localhost ~]# mysql -uroot -pasdfjkl -e 'show databases;'
  mysql: [Warning] Using a password on the command line interface can be insecure.
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | sys                |
  +--------------------+
  

常用命令

• 查看字段属性

  mysql> desc mysql.user;
  

• 查看建表语句

  mysql> show create table mysql.user;
  

• 查看当前用户

  mysql> select user();
  +----------------+
  | user()         |
  +----------------+
  | root@localhost |
  +----------------+
  1 row in set (0.00 sec)
  
  

• 查看当前数据库

  mysql> select database();
  +------------+
  | database() |
  +------------+
  | NULL       |
  +------------+
  1 row in set (0.00 sec)
  

• 查看DBMS版本

  mysql> select version();
  +-----------+
  | version() |
  +-----------+
  | 5.7.32    |
  +-----------+
  1 row in set (0.01 sec)
  

• 查看DBMS状态

  mysql> show status;
  

• 查看重要参数

  mysql> show variables like 'max_connect%';
  +--------------------+--------+
  | Variable_name      | Value  |
  +--------------------+--------+
  | max_connect_errors | 100000 |
  | max_connections    | 100    |
  +--------------------+--------+
  2 rows in set (0.00 sec)
  

• 修改重要参数

  mysql> set global max_connect_errors=1000;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> show variables like 'max_connect%';
  +--------------------+-------+
  | Variable_name      | Value |
  +--------------------+-------+
  | max_connect_errors | 1000  |
  | max_connections    | 100   |
  +--------------------+-------+
  2 rows in set (0.00 sec)
  

• 查看队列

  mysql> show processlist;
  +----+------+-----------+------+---------+------+----------+------------------+
  | Id | User | Host      | db   | Command | Time | State    | Info             |
  +----+------+-----------+------+---------+------+----------+------------------+
  | 21 | root | localhost | NULL | Query   |    0 | starting | show processlist |
  +----+------+-----------+------+---------+------+----------+------------------+
  1 row in set (0.00 sec)
  
  mysql> show full processlist;
  +----+------+-----------+------+---------+------+----------+-----------------------+
  | Id | User | Host      | db   | Command | Time | State    | Info                  |
  +----+------+-----------+------+---------+------+----------+-----------------------+
  | 21 | root | localhost | NULL | Query   |    0 | starting | show full processlist |
  +----+------+-----------+------+---------+------+----------+-----------------------+
  1 row in set (0.00 sec)
  

创建用户及授权

• 创建用户

  格式：CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
  
  mysql> create user 'user1'@'172.16.1.27' identified by '111111'; 
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> create user 'user2'@'%' identified by '000000';
  Query OK, 0 rows affected (0.00 sec)
  

• 进行授权

  格式：GRANT privileges ON databasename.tablename TO 'username'@'hostname'
  mysql> grant all on *.* to 'user1'@'172.16.1.27';
  Query OK, 0 rows affected (0.00 sec)
  mysql> grant select,insert,update on mysql.* to 'user2'@'%';
  Query OK, 0 rows affected (0.00 sec)
  

• 创建用户并同时授权

  mysql> grant all on mysql.* to 'user3'@'%' identified by 'asdf';
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  
  mysql> grant select on *.* to 'user4'@'172.16.1.27' identified by '123456';         
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  

• 查看授权表

  查看当前用户授权表
  mysql> show grants;
  +---------------------------------------------------------------------+
  | Grants for root@localhost                                           |
  +---------------------------------------------------------------------+
  | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
  | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
  +---------------------------------------------------------------------+
  2 rows in set (0.00 sec)
  
  查看指定用户授权表
  mysql> show grants for user1@172.16.1.27;
  +------------------------------------------------------+
  | Grants for user1@172.16.1.27                         |
  +------------------------------------------------------+
  | GRANT ALL PRIVILEGES ON *.* TO 'user1'@'172.16.1.27' |
  +------------------------------------------------------+
  1 row in set (0.00 sec)
  

常用的SQL语句

注：该图由WPS生成，没有会员只能导出图片

备份及恢复

• 库

  备份库
  [root@localhost ~]# mysqldump -uroot -pasdfjkl mysql > /tmp/mysql.sql
  恢复库
  [root@localhost ~]# mysqldump -uroot -pasdfjkl mysql < /tmp/mysql.sql
  备份所有库 
  [root@localhost ~]# mysqldump -uroot -pasdfjkl -A > /tmp/all_databases.sql
  
  

• 表

  备份表
  [root@localhost ~]# mysqldump -uroot -pasdfjkl mysql user > /tmp/user.sql
  恢复表
  [root@localhost ~]# mysqldump -uroot -pasdfjkl mysql < /tmp/user.sql
  只备份表结构
  [root@localhost ~]# mysqldump -uroot -pasdfjkl -d mysql > /tmp/test.sql