MySQL

Table of Contents

1 MySQL基本介绍

MySQL 是著名的开源关系型数据库。

参考:
MySQL 8.0 Reference Manual
MySQL Reference Manual, SQL Statement Syntax

1.1 MySQL历史版本

MySQL部分历史版本如表 1 所示。

Table 1: MySQL历史版本
Release General availability Latest minor version Latest release End of support
5.1 2008-11-14 5.1.73 2013-12-03 December 2013
5.5 2010-12-03 5.5.60 2018-04-19 December 2018
5.6 2013-02-05 5.6.40 2018-04-19 February 2021
5.7 2015-10-21 5.7.22 2018-04-19 October 2023
8.0 2018-04-19 8.0.11 2018-04-19 N/A

1.2 安装和启动MySQL

在Ubuntu中,可用下面命令安装MySQL:

$ sudo apt-get install mysql-server     # 安装 server
$ sudo apt-get install mysql-client     # 安装 client

使用下面命令可以管理mysql服务:

$ service mysql start
$ service mysql status
$ service mysql stop

2 基本使用

2.1 连接MySQL服务器

使用客户端 mysql 可以连接MySQL服务器,格式如下:

$ mysql -h 主机地址  -u 用户名 -p

2.1.1 MySQL服务器配置文件

MySQL服务器配置文件一般名为 my.cnf ,它的所在目录可以通过下面命令得到:

$ /usr/sbin/mysqld --help --verbose |more

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

2.2 查看在线帮助文档(help)

使用 help 可以查看相关帮助文档。

如,执行 help create table 可以查看语句 create table 的帮助文档:

mysql> help create table
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

......

2.3 help支持通配符( %_

使用 help 查找帮助文档时,支持下面通配符:
1、 % 表示任意个(可以是零个)字符;
2、 _ 表示任意单个字符。

例如, help create% 可查找所有以 create 开头的语句:

mysql> help create%
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   CREATE DATABASE
   CREATE EVENT
   CREATE FUNCTION
   CREATE FUNCTION UDF
   CREATE INDEX
   CREATE PROCEDURE
   CREATE RESOURCE GROUP
   CREATE ROLE
   CREATE SERVER
   CREATE SPATIAL REFERENCE SYSTEM
   CREATE TABLE
   CREATE TABLESPACE
   CREATE TRIGGER
   CREATE USER
   CREATE VIEW

例如, help create ____ 可查找所有 create 后接任意4个字符的语句:

mysql> help create ____
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   CREATE ROLE
   CREATE USER
   CREATE VIEW

2.4 查看服务器基本信息

登录服务器后,使用 status 可以查看服务器基本信息。如:

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

Connection id:		3
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.23-0ubuntu0.18.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			3 min 13 sec

Threads: 1  Questions: 5  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.025
--------------

2.5 Server System Variables

2.5.1 查看系统变量

通过 show variables; 可以查看服务器所有系统变量。如:

mysql> show variables;
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| auto_increment_increment        | 1                                 |
| auto_increment_offset           | 1                                 |
| automatic_sp_privileges         | ON                                |
| back_log                        | 50                                |
| basedir                         | /home/mysql/                      |
| binlog_cache_size               | 32768                             |
| bulk_insert_buffer_size         | 8388608                           |
| character_set_client            | utf8                              |
| character_set_connection        | utf8                              |
| character_set_database          | latin1                            |
| character_set_filesystem        | binary                            |
| character_set_results           | utf8                              |
| character_set_server            | latin1                            |
| character_set_system            | utf8                              |
| character_sets_dir              | /home/mysql/share/mysql/charsets/ |
| collation_connection            | utf8_general_ci                   |
| collation_database              | latin1_swedish_ci                 |
| collation_server                | latin1_swedish_ci                 |
...
| innodb_additional_mem_pool_size | 1048576                           |
| innodb_autoextend_increment     | 8                                 |
| innodb_buffer_pool_size         | 8388608                           |
| innodb_checksums                | ON                                |
| innodb_commit_concurrency       | 0                                 |
| innodb_concurrency_tickets      | 500                               |
| innodb_data_file_path           | ibdata1:10M:autoextend            |
| innodb_data_home_dir            |                                   |
...
| version                         | 5.5.56-log                        |
| version_comment                 | Source distribution               |
| version_compile_machine         | i686                              |
| version_compile_os              | suse-linux                        |
| wait_timeout                    | 28800                             |
+---------------------------------+-----------------------------------+

通过 show variables like; 可以配置指定模式的系统变量。如:

mysql> show variables like '%commit%';              -- 包含commit的系统变量
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| autocommit                              | ON    |
| binlog_group_commit_sync_delay          | 0     |
| binlog_group_commit_sync_no_delay_count | 0     |
| binlog_order_commits                    | ON    |
| innodb_api_bk_commit_interval           | 5     |
| innodb_commit_concurrency               | 0     |
| innodb_flush_log_at_trx_commit          | 1     |
| slave_preserve_commit_order             | OFF   |
+-----------------------------------------+-------+
8 rows in set (0.01 sec)

mysql> show variables like '%commit';              -- 以commit结尾的系统变量
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | ON    |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

通过 select @@varname; 可以查看某个系统变量的当前值。如:

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

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

参考:Server System Variable Reference

2.5.2 修改系统变量(set)

修改“全局系统变量”,有下面两种形式:

SET GLOBAL max_connections = 1000;
SET @@GLOBAL.max_connections = 1000;    // 同上

修改“session系统变量”,有下面很多种形式:

SET SESSION sql_mode = 'TRADITIONAL';
SET LOCAL sql_mode = 'TRADITIONAL';      // 同上
SET @@SESSION.sql_mode = 'TRADITIONAL';  // 同上
SET @@LOCAL.sql_mode = 'TRADITIONAL';    // 同上
SET @@sql_mode = 'TRADITIONAL';          // 同上
SET sql_mode = 'TRADITIONAL';            // 同上

3 数据库相关操作

3.1 列出已有数据库

执行语句 show databases; 可查看所有数据库。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

3.2 设置、查询当前数据库

执行语句 use your_db_name; 可设置当前想要使用的数据库,如:

mysql> use mytest;
Database changed

执行语句 select database(); 可以查询当前所使用的数据库(如果你还有设置当前数据库,会返回 NULL )。如:

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

3.3 创建、删除数据库

执行语句 create database db_name; 可以创建数据库,如:

mysql> create database mytest;
Query OK, 1 row affected (0.04 sec)

执行语句 drop database db_name; 可以删除数据库,如:

mysql> drop database mytest;
Query OK, 0 rows affected (0.10 sec)

3.4 列出当前数据库所有的表

执行语句 show tables; 可列出当前数据库所有的表。如:

mysql> use mysql;     -- 设置当前数据库为mysql
Database changed
mysql> show tables;   -- 查看当前数据库中所有的表
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

4 用户管理

4.1 创建、授权、删除用户

使用语句 CREATE USER 可以创建新用户。
使用语句 GRANT 可以对用户进行授权。
使用语句 DROP USER 可以删除用户。

下面例子创建是用户youruser,且可以从任何主机登录:

mysql> CREATE USER 'youruser'@'%' IDENTIFIED BY 'yourpw';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON *.* TO 'youruser'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4.2 修改用户密码

使用 mysqladmin 可修改用户密码。如修改root密码:

$ mysqladmin -u root password '新密码' -p '旧密码'      # 修改root用户密码

4.3 列出所有用户

执行语句 select user, host from mysql.user; 可以列出所有用户。如:

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

上面的host列表示用户可以从哪些主机登录MySQL服务器。

4.4 显示当前登录用户

执行语句 select user(); 或者 select current_user(); 可显示当前登录用户。如:

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

4.5 重置root密码

如果你忘记了root的密码,可以使用下面步骤重置root密码:
第一步,停止mysql服务器:

$ service mysql stop

第二步,在MySQL服务器配置文件中添加 skip-grant-tables 参数。此参数的作用是登录MySQL数据库不进行用户密码验证。

[mysqld]
skip-grant-tables

注:mysql服务器配置文件的位置因系统不同而不同,可能为“/etc/mysql/mysql.conf.d/mysqld.cnf”。

第三步,再启动mysql服务器:

$ service mysql start

第四步,连接MySQL服务器,设置root的新密码:

$ mysql
mysql> flush privileges;  -- reload the grant tables so that account-management statements work
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_pw';

第五步,去掉MySQL服务器配置文件中的skip-grant-tables参数,再重启服务器。

参考:https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

5 Tip

5.1 结尾指定 \G 让输出按列显式

一般地,我们使用 ; 指定语句结尾符,如:

mysql> select * from userinfo;
+-------+------+
| name  | age  |
+-------+------+
| John  |   25 |
| Herry |   30 |
+-------+------+
2 rows in set (0.00 sec)

如果把 ; 换为 \G ,则可以让输出按列显示,如:

mysql> select * from userinfo\G
 *************************** 1. row ***************************
name: John
 age: 25
 *************************** 2. row ***************************
name: Herry
 age: 30
2 rows in set (0.00 sec)

注:当行比较少,而列很多时,使用 \G 作为结尾符会使结果的查看更方便。

5.2 导出DDL语句(mysqldump)

使用工具 mysqldump 可以导出表定义等DDL语句。如:

$ mysqldump -d -u <username> -p <password> -h <hostname> <dbname>

其中,参数 -d (--no-data) 表示不要dump表中数据。

5.2.1 查看某个表的定义(show create table)

如果仅想查看某个表的定义,可以使用 show create table table_name 。如:

mysql> show create table userinfo\G
 *************************** 1. row ***************************
       Table: userinfo
Create Table: CREATE TABLE `userinfo` (
  `name` varchar(12) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

5.3 设置和查询事务隔离级别

下面语句可查询MySQL中当前的事务隔离级别:

mysql> select @@tx_isolation;     -- Mysql8 has renamed tx_isolation to transaction_isolation
ERROR 1193 (HY000): Unknown system variable 'tx_isolation'
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

使用语句 SET TRANSACTION 可修改事务隔离级别。

SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {
    ISOLATION LEVEL level
  | READ WRITE
  | READ ONLY
}

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

在上面语句中,选择GLOBAL,意思是此语句将应用于之后的所有session,而当前已经存在的session不受影响;选择SESSION,意思是此语句将应用于当前session内之后的所有事务;如果省略GLOBAL和SESSION,意思是此语句将应用于当前session内的下一个还未开始的事务。

5.4 查看当前有哪些连接

使用 show processlist; 或者 show full processlist; 可以查看当前有哪些线程。
使用 kill processlist_id; 可以结束指定线程。

参考:
https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html
https://dev.mysql.com/doc/refman/8.0/en/kill.html

5.5 查看数据库占用的大小

使用下面命令可以查看MySQL中数据库占用的大小:

SELECT table_schema "DB Name",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;

下面是一个例子:

mysql> SELECT table_schema "DB Name",
    ->         ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
    -> FROM information_schema.tables
    -> GROUP BY table_schema;
+--------------------+---------------+
| DB Name            | DB Size in MB |
+--------------------+---------------+
| test1              |         611.5 |
| information_schema |           0.2 |
| mysql              |           2.4 |
| performance_schema |           0.0 |
| sys                |           0.0 |
+--------------------+---------------+
5 rows in set (0.02 sec)

Author: cig01

Created: <2018-08-04 六 00:00>

Last updated: <2018-11-14 三 01:09>

Creator: Emacs 25.3.1 (Org mode 9.1.4)