MySQL 基础学习文档

MySQL 基础学习文档

一、什么是 MySQL

​ MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），由瑞典的MySQL AB公司开发，后来被Oracle公司收购。MySQL 是一种数据库软件，它基于结构化查询语言（SQL）来管理和操作数据。

二、 MySQL的作用

​ MySQL 的主要作用是帮助用户存储、检索、管理和操纵数据。它通常用于以下场景：

  1. 网站和应用程序的后端数据库：MySQL 经常用于存储网站或应用程序的用户信息、内容管理数据、订单数据等。
  2. 电子商务平台：MySQL 可以用于管理产品、订单、客户等数据。
  3. 数据分析：企业可以使用 MySQL 存储和查询大量数据，以进行数据分析和生成报告。
  4. 内容管理系统（CMS）：许多内容管理系统（如 WordPress、Joomla）都使用 MySQL 作为后台数据库。

三、 部署 MySQL

1. 使用 YUM 源安装 MySQL(Mariadb)

1
2
3
4
5
$ yum install -y mariadb mariadb-server

# 安装之后的版本是 5.5.68 的 MariaDB 与 MySQL 5.5.68 都通用
$ mysql -V
mysql  Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1

2. 使用源码编译安装

2.1 安装 MySQL

​ 将二进制免编译包上传到 /usr/local/src 目录下，然后在两台机器上都进行如下操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ cd /usr/local/src/ #自己通过源码包配置安装的软件包都推荐放到这个位置

# 解压后挪动位置
$ sudo tar -zxf mysql-5.6.49-linux-glibc2.12-x86_64.tar.gz
$ sudo mv mysql-5.6.49-linux-glibc2.12-x86_64 /usr/local/mysql

# 创建一个不能用于登录的账户 mysql，因为启动 MySQL 需要用到此账户
$ sudo useradd -s /sbin/nologin mysql

# 创建 MySQL 数据目录，然后授予权限
$ sudo mkdir -p /data/mysql
$ sudo chown -R mysql:mysql /data/mysql/
$ sudo chmod -R 755 /data/mysql

# 初始化 MySQL 数据目录：指定 MySQL 服务的用户是 mysql (创建的文件和目录的 owner 都会是 mysql)，，并指定 MySQL 数据目录
$ cd /usr/local/mysql
# 安装必要环境
$ sudo yum install -y perl-Module-Install
# 开始编译
$ sudo ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

  • 检查是否安装成功：

    ​ 如果向上面一样看到了两个 OK 且在 /data/mysql 下看到了生成的文件和目录，说明执行正确

    ​ 或者是在执行完最后一条命令江批，马上执行 echo $? 看输出的结果为 **0**，说明执行正确
    ​ 3.1.1

2.2 配置 MySQL

​ 首先复制配置文件，然后打开配置文件并把配置文件改成如下内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$ cp support-files/my-default.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y
# 这里提示是否覆盖是因为系统内默认就有 /etc/my.cnf，直接按y就行

$ sudo vi support-files/my-default.cnf

# 替换成以下内容
[mysqld]

innodb_buffer_pool_size = 128M #第11行

log_bin = moka #第15行

basedir = /usr/local/mysql #第18行，是MySQL包的所在地
datadir = /data/mysql #第19行，存放数据的地方
port = 3306 #第20行，默认端口号就是3306
server_id = 128 #第21行，服务的ID号
socket = /tmp/mysql.sock #第22行，MySQL服务监听的嵌套字地址

## 关于内存的配置参数，保持默认即可
join_buffer_size = 128M #第27行
sort_buffer_size = 2M #第28行
read_rnd_buffer_size = 2M #第29行
  • 备注：
    嵌套字地址：在 Linux 系统下，很多服务不仅可以监听一个端口（通过 TCP/IP 的方式通信），也可以监听 socket，两个进程就可以通过这个 socket 文件通信

​

​ 然后复制启动脚本并修改其属性

1
2
$ sudo cp support-files/mysql.server /etc/init.d/mysqld
$ sudo chmod 755 /etc/init.d/mysqld

​ 然后修改启动脚本，并把启动脚本加入系统服务项，然后设置开机启动并启动 MySQL

1
2
3
4
5
6
7
8
$ vi /etc/init.d/mysqld

# 修改的内容
datadir=/data/mysql #第47行

$ sudo chkconfig --add mysqld #把mysqld服务加入系统服务列表中
$ sudo chkconfig mysqld on #设置开机启动
$ sudo service mysqld start #启动服务

  • 备注：

    ​ 如果启动不了，就到 data/mysql 目录下查看错误日志，这个日志名通常是 [主机名].err
    检查 MySQL 是否启动的命令为：

    1
    2
    3
    4
    5
    6
    7
    8
    # 检查 mysqld 服务的状态
    $ sudo service mysqld status

    # 查看 mysqld 相关进程（结果应该大于2行）
    $ sudo ps aux | grep mysqld

    # 看看有没有监听3306端口
    $ sudo netstat -lnp | grep 3306
    • 最后一个要是提示 -bash: netstat: 未找到命令 的，就是用命令 sudo yum install -y net-tools ，安装之后就可以用了

四、MySQL 常用命令

1. 更改 root 密码

1.1 更改环境变量

​ 修改 /etc/profile 文件，文件末尾添加 mysql 的绝对路径

1
2
3
4
5
6
$ vi /etc/profile
# 在最末尾添加上以下内容
export PATH=$PATH:/usr/local/mysql/bin/

# 然后刷新应用一下
$ source /etc/profile

4.1.1

1.2 创建 MysSQL 密码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 为 root 用户创建初始密码 123456
$ mysqladmin -uroot password '123456'

# 登录 MySQL
$ mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

4.1.2

1.3 密码重置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
$ vi /etc/my.cnf
[mysqld]

skip-grant #就新增这一项
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

[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/my.cnf.d

# 修改完成之后重启 mysql 服务
$ systemctl restart mariadb

# 修改的部分是完成忽略授权的操作，可以直接登录MySQL，无需输入用户名密码
$ mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

4.1.3_1

​ 进入到 MySQL 后，切换到 mysql 数据库，对 user 表进行更新操作

1
2
3
4
5
6
7
8
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> update user set password=password('mokamokamoka') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4  Changed: 3  Warnings: 0

4.1.3_2

​ 修改完成后，确认新密码登录有效后，把 /etc/my.cnf 改回原有状态，并重启 mysql 服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
$ vi /etc/my.cnf
[mysqld]

#skip-grant #就注释这一项
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

[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/my.cnf.d

# 修改完成之后重启 mysql 服务
$ systemctl restart mariadb

$ mysql -uroot -pmokamokamoka
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

4.1.3_3

4.1.3_4

2. 连接 MySQL

2.1 mysql -uroot -p123456
1
2
3
4
5
6
7
8
9
10
$ mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

4.2.1

2.2 mysql -uroot -p123456 -h127.0.0.1 -P3306
1
2
3
4
5
6
7
8
9
10
$ mysql -uroot -p123456 -h127.0.0.1 -P3306
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

4.2.2

2.3 mysql -uroot -p123456 -S/var/lib/mysql/mysql.sock
1
2
3
4
5
6
7
8
9
10
$ mysql -uroot -p123456 -S/var/lib/mysql/mysql.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

4.2.3

2.4 mysql -uroot -p123456 -e 'show databases'
1
2
3
4
5
6
7
8
9
$ mysql -uroot -p123456 -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

4.2.4

3. MySQL 常用命令

3.1 查询库：SHOW DATABASES;
1
2
3
4
5
6
7
8
9
10
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

4.3.1

3.2 切换库 USE mysql;
1
2
3
4
5
6
MariaDB [(none)]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]>

4.3.2

3.3 查询库里的表 SHOW TABLES;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
MariaDB [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

4.3.3

3.4 显示表结构 DESCRIBE <tb_name>; 或者是 SHOW COLUMNS FROM <tb_name>;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
MariaDB [mysql]> DESCRIBE plugin;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(64)  | NO   | PRI |         |       |
| dl    | varchar(128) | NO   |     |         |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [mysql]> SHOW COLUMNS FROM plugin;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(64)  | NO   | PRI |         |       |
| dl    | varchar(128) | NO   |     |         |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

4.3.4

3.5 查询表里的字段 DESC <tb_name>;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
MariaDB [mysql]> DESC user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11)                           | NO   |     | 0       |       |
| plugin                 | char(64)                          | NO   |     |         |       |
| authentication_string  | text                              | NO   |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)

4.3.5

3.6 查看建表语句 SHOW CREATE TABEL <tb_name>\G;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
MariaDB [mysql]> SHOW CREATE TABLE user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) NOT NULL DEFAULT '0',
  `plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `authentication_string` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)
3.7 查看当前用户 SELECT USER();
1
2
3
4
5
6
7
MariaDB [mysql]> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

4.3.7

3.8 查看当前使用的数据库 SELECT DATABASE();
1
2
3
4
5
6
7
MariaDB [mysql]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

4.3.8

3.9 创建库 CREATE DATABASE db1;
1
2
MariaDB [mysql]> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)

4.3.9

3.10 创建表
1
2
3
4
MariaDB [(none)]> USE db1;
Database changed
MariaDB [db1]> CREATE TABLE t1(`id` int(4), `name` char(40));
Query OK, 0 rows affected (0.01 sec)

4.3.10

3.11 更改表的结构 ALTER TABLE <tb_name> ADD <column_name> <datatype>;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
MariaDB [mysql]> ALTER TABLE mysql.user ADD last_login_time DATETIME;
Query OK, 9 rows affected (0.01 sec)               
Records: 9  Duplicates: 0  Warnings: 0

MariaDB [mysql]> DESC user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11)                           | NO   |     | 0       |       |
| plugin                 | char(64)                          | NO   |     |         |       |
| authentication_string  | text                              | NO   |     | NULL    |       |
| last_login_time        | datetime                          | YES  |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.00 sec)

4.3.11

3.12 查看当前数据库版本 SELECT VERSION();
1
2
3
4
5
6
7
MariaDB [(none)]> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 5.5.68-MariaDB |
+----------------+
1 row in set (0.00 sec)

4.3.12

3.13 查看数据库状态 SHOW STATUS;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
MariaDB [(none)]> SHOW STATUS;
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 0           |
| Aborted_connects                         | 0           |
| Access_denied_errors                     | 0           |
| Aria_pagecache_blocks_not_flushed        | 0           |
| Aria_pagecache_blocks_unused             | 15737       |
| Aria_pagecache_blocks_used               | 0           |
| Aria_pagecache_read_requests             | 0           |
| Aria_pagecache_reads                     | 0           |
| Aria_pagecache_write_requests            | 0           |
| Aria_pagecache_writes                    | 0           |
| Aria_transaction_log_syncs               | 0           |
| Binlog_commits                           | 0           |
| Binlog_group_commits                     | 0           |
| Binlog_snapshot_file                     |             |
| Binlog_snapshot_position                 | 0           |
| Binlog_bytes_written                     | 0           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Busy_time                                | 0.000000    |
| Bytes_received                           | 273         |
| Bytes_sent                               | 632         |
......
| Syncs                                    | 4           |
| Table_locks_immediate                    | 36          |
| Table_locks_waited                       | 0           |
| Tc_log_max_pages_used                    | 0           |
| Tc_log_page_size                         | 0           |
| Tc_log_page_waits                        | 0           |
| Threadpool_idle_threads                  | 0           |
| Threadpool_threads                       | 0           |
| Threads_cached                           | 0           |
| Threads_connected                        | 1           |
| Threads_created                          | 3           |
| Threads_running                          | 1           |
| Uptime                                   | 3450        |
| Uptime_since_flush_status                | 3450        |
+------------------------------------------+-------------+
413 rows in set (0.00 sec)

4.3.13

3.14 查看各参数 SHOW VARIABLES; SHOW VARIABLES LIKE 'max_connect%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MariaDB [(none)]> SHOW variables;
+---------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                                     | Value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+---------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aria_block_size                                   | 8192                                                                                                                         
| basedir                                           | /usr      
......
+---------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
433 rows in set (0.00 sec)

SHOW VARIABLES LIKE 'max_connect%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 10    |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)

4.3.14

3.15 修改参数 SET GLOBAL max_connect_errors=1000;
1
2
MariaDB [(none)]> SET GLOBAL max_connect_errors=1000;
Query OK, 0 rows affected (0.00 sec)

4.3.15

3.16 查看队列 SHOW PROCESSLIST; SHOW FULL PROCESSLIST;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MariaDB [(none)]> SET GLOBAL max_connect_errors=1000;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host      | db   | Command | Time | State | Info             | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
|  4 | root | localhost | NULL | Query   |    0 | NULL  | SHOW PROCESSLIST |    0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW FULL PROCESSLIST;
+----+------+-----------+------+---------+------+-------+-----------------------+----------+
| Id | User | Host      | db   | Command | Time | State | Info                  | Progress |
+----+------+-----------+------+---------+------+-------+-----------------------+----------+
|  4 | root | localhost | NULL | Query   |    0 | NULL  | SHOW FULL PROCESSLIST |    0.000 |
+----+------+-----------+------+---------+------+-------+-----------------------+----------+
1 row in set (0.00 sec)

4.3.16

4. MySQL 创建用户以及授权

4.1 进行授权
1
2
3
4
5
6
7
8
9
10
11
# 授予用户 user1 对所用数据库和表所有权限，并使用密码 passwd 进行认证
MariaDB [(none)]> GRANT ALL ON *.* TO 'user1' IDENTIFIED BY 'passwd';
Query OK, 0 rows affected (0.00 sec)

# 授予用户 user2 对数据库 db1 中所有表的 SELECT（查询）、UPDATE（更新）和 INSERT（插入）权限，并限制 user2 只能在本地主机进行登录，且使用密码 passwd 进行认证
MariaDB [(none)]> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'127.0.0.1' identified by 'passwd';
Query OK, 0 rows affected (0.01 sec)

# 授予用户 user3 对数据库 db1 中所有表的所有权限，并使用密码 passwd 进行认证
MariaDB [(none)]> grant all on db1.* to 'user3'@'%' identified by 'passwd';
Query OK, 0 rows affected (0.00 sec)

4.4.1

4.2 查看授权表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
MariaDB [(none)]> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

MariaDB [(none)]> SHOW GRANTS FOR user2@127.0.0.1;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user2@127.0.0.1                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'127.0.0.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'127.0.0.1'                                               |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

4.4.2

4.3 刷新权限

​ 在更改用户权限后执行该命令，使更改生效

1
2
MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

4.4.3

5. MySQL 常用 SQL 语句

5.1 查看表内行数 SELECT COUNT(*) FROM mysql.user;
1
2
3
4
5
6
7
MariaDB [(none)]> SELECT COUNT(*) FROM mysql.user;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

4.5.1

5.2 查看 db 表内的内容 SELECT * FROM mysql.db;
1
2
3
4
5
6
7
8
9
10
MariaDB [(none)]> SELECT * FROM mysql.db;
+-----------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db      | User  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| %         | test    |       | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | N                  | N            | Y          | Y            |
| %         | test\_% |       | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | N                  | N            | Y          | Y            |
| 127.0.0.1 | db1     | user2 | Y           | Y           | Y           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
| %         | db1     | user3 | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
+-----------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
4 rows in set (0.00 sec)

4.5.2

5.3 创建索引 CREATE INDEX <index_name> ON <table_name (column_name)>;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 创建索引
MariaDB [(none)]> CREATE INDEX idx_host ON mysql.user (Host);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

# 查看创建的索引
MariaDB [(none)]> SELECT * FROM mysql.user WHERE Host = 'localhost';
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+-----------------+
| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | last_login_time |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+-----------------+
| localhost |      |                                           | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       | NULL            |
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       | NULL            |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+-----------------+
2 rows in set (0.01 sec)

4.5.3

5.4 搜索查看多个字段 SELECT db,user FROM mysql.db;
1
2
3
4
5
6
7
8
9
10
MariaDB [(none)]> SELECT db,user FROM mysql.db;
+---------+-------+
| db      | user  |
+---------+-------+
| db1     | user3 |
| test    |       |
| test\_% |       |
| db1     | user2 |
+---------+-------+
4 rows in set (0.01 sec)

4.5.4

5.5 查询 host 为 127.0 的内容 SELECT * FROM mysql.db WHERE HOST LIKE '127.0.%';
1
2
3
4
5
6
7
MariaDB [(none)]> SELECT * FROM mysql.db WHERE HOST LIKE '127.0.%';
+-----------+-----+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db  | User  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+-----+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| 127.0.0.1 | db1 | user2 | Y           | Y           | Y           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
+-----------+-----+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
1 row in set (0.01 sec)

4.5.5

5.6 向 db1.t1 中插入内容 INSERT INTO db1.t1 VALUES (1, 'abc');
1
2
3
4
5
6
7
8
9
10
11
MariaDB [(none)]> INSERT INTO db1.t1 VALUES (1, 'abc');
Query OK, 1 row affected (0.01 sec)

# 查询 db1.t1 数据库表单
MariaDB [(none)]> SELECT * FROM db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

4.5.6

5.7 把 id=1 的字段内容更新成 aaa UPDATE db1.t1 SET NAME='aaa' WHERE id=1;
1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [(none)]> UPDATE db1.t1 SET NAME='aaa' WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 查询 db1.t1 数据库表单
MariaDB [(none)]> SELECT * FROM db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.00 sec)

4.5.7

5.8 清空 db1.t1 表内的内容 TRUNCATE TABLE db1.t1;
1
2
3
4
5
6
MariaDB [(none)]> TRUNCATE TABLE db1.t1;
Query OK, 0 rows affected (0.01 sec)

# 查询 db1.t1 数据库表单
MariaDB [(none)]> SELECT * FROM db1.t1;
Empty set (0.00 sec)

4.5.8

5.9 删除 db1.t1 表内的内容 DROP TABLE db1.t1;
1
2
3
4
5
6
MariaDB [(none)]> DROP TABLE db1.t1;
Query OK, 0 rows affected (0.01 sec)

# 查询 db1.t1 表单的内容
MariaDB [(none)]> DESC db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
  • PS：清空后表的结构仍然存在

4.5.9

5.10 清空 db1.t1 数据库 DROP DATABASE db1;
1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [(none)]> DROP DATABASE db1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

4.5.10

6. MySQL 数据库的备份与恢复

6.1 备份库
1
2
# 把数据库 mysql 的内容导出到 /tmp/mysql.sql 文件中
$ mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
6.2 恢复库
1
2
# 将 /tmp/mysql.sql 文件中的内容导入到 mysql 数据库
$ mysql -uroot -p123456 mysql < /tmp/mysql.sql
6.3 备份表
1
2
# 把 mysql 数据库中的 user 表导出到 /tmp/user.sql 文件中
$ mysqldump -uroot -p123456 mysql user > /tmp/user.sql
6.4 恢复表
1
2
# 从 /tmp/user.sql 文件中读取 SQL 语句，并在 mysql 数据库中执行这些语句
$ mysql -uroot -p123456 mysql < /tmp/user.sql
6.5 备份所有库
1
$ mysqldump -uroot -p123456 -A > /tmp/123.sql
6.6 只备份表结构
1
$ mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql

4.6

#MySQL
MySQL 基础学习文档
https://moka.anitsuri.top/2024/08/29/MySQL_Basic/
作者
アニつり
发布于
2024年8月30日
许可协议