MySQL之基本操作

作为一个Linux运维工程师，还必须掌握一些MySQL等数据库的基本操作，以满足日常管理工作所需。至于更深层次的内容，那是DBA（专门管理数据库的技术人员）所必须掌握的。也就是说，目前只需要掌握一下内容即可，等到工作以后，再继续研究MySQL的技术。

---

1. MySQL密码

1.1. 更改MySQL数据库root的密码

首次进入数据库是不用密码的，如下所示：

[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

退出时直接输入quit、exit或者\q即可。

一般我们源码安装完MySQL后，对MySQL数据库的操作必须使用绝对路径，那如何把MySQL的执行程序所在的路径加入到PATH环境变量以满足我们直接用命令而不是绝对路径的方式进行管理呢？方法如下：

[root@localhost ~]# PATH=$PATH:/usr/local/mysql/bin

当然，这样设置的话，在我们重启Linux后还会失效，所以需要让它开机加载，如下：

[root@localhost ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@localhost ~]# source /etc/profile
[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

说明：

• -u参数的含义是，用来指定要登陆的用户，后面有无空格均可。
• root用户是MySQL自带的管理员账户，默认是没有密码的。

那如何给MySQL的root用户设定密码呢？操作方法如下：

[root@localhost ~]# mysqladmin -uroot password '123456'
Warning: Using a password on the command line interface can be insecure.

这样就给MySQL的root账户设置了密码，密码为123456，注意在生产环境中千万不要设置这么简单的密码。在执行过程中它会返回一条警告信息，意思是在命令行下面暴露了密码，这样不安全。

再执行一次上面的登陆命令，如下所示：

[root@localhost ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

这里报错了，它提示我们root账号是需要密码登陆的。

接下来重新输入密码登陆，如下所示：

[root@localhost ~]# mysql -uroot -p'123456'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

输入密码时需要加-p选项，后面可以直接跟密码。-p选项后面不可以有空格，密码可以不加单引号（但是密码中有特殊字符时就会出现问题，所以最好还是加上单引号）。

当然，-p选项后面也可以不加密码，以和用户交互的方式输入密码，如下所示：

[root@localhost ~]# mysql -uroot -p
Enter password:

1.2. MySQL的root密码重置

当在工作中遇到MySQL的root密码忘记的时候，我们应该利用下面的方法将MySQL的root密码重置。步骤如下：

第一步：编辑MySQL的主配置文件my.cnf

# vim /etc/my.cnf
#<==在[mysqld]字段下添加参数

skip-grant

第二步：重启数据库服务

# service mysqld restart

第三步：这样就可以进入数据库而不用通过授权了

# /usr/local/mysql/bin/mysql -uroot

第四步：修改相应用户密码

>use mysql;
>update user set password=password('your password') where user='root';
>flush privileges;
>quit;

第五步：还原my.cnf

修改my.cnf主配置文件，去掉skip-grant，重启mysqld服务。

---

2. 连接数据库

2.1. TCP/IP方式

可以使用这个命令来连接数据库：mysql -uroot -p。但连接的只是本地的数据库localhost。

很多时候我们都需要去连接网络中的某一个主机上的MySQL，如下所示：

# mysql -uroot -p -h192.168.1.111 -P3306
Enter password:

其中，后面的-P（大写）用来指定远程主机MySQL的绑定端口，默认都是3306，-h用来指定远程主机的IP。

2.2. socket方式

这种只适合连接本机的MySQL，命令为：

# mysql -uroot -S /tmp/mysql.cosk -p

这里的-S可以省略掉。

---

3. MySQL基本操作的常用命令

在日常工作中，难免会遇到一些与MySQL相关的操作，比如建库、建表、查询MySQL状态等。尽管我们不是专业的数据库管理员，但是最基本的操作还是要掌握的。

3.1. 查询当前库

查询当前库的命令如下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

注意，MySQL命令的结尾处需要加一个分号。

3.2. 查询某个库的表

首先需要切换到某个库里，如下所示：

mysql> 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

在切换库的时候，它提示这个操作会把当前库里的所有表的字段全部读一遍，你可以在启动MySQL的时候加上-A选项关闭这个特性（即使有这个特性倒也不影响什么，你可以忽略它）。

然后再把表列出来，如下所示：

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| 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                      |
+---------------------------+
28 rows in set (0.00 sec)

3.3. 查看某个表的全部字段

查看表的全部字段的命令如下：

mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| 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       |       |
| 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       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_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       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.01 sec)

另外，也可以使用下面这条命令，显示的信息更详细，而且还可以把建表语句全部列出来，如下所示：

mysql> show create table db\G;
*************************** 1. row ***************************
       Table: db
Create Table: CREATE TABLE `db` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_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',
  `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',
  `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',
  `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',
  `Execute_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',
  PRIMARY KEY (`Host`,`Db`,`User`),
  KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
1 row in set (0.00 sec)

ERROR:
No query specified

这条命令后面加了一个\G，目的是让列出来的结果竖排显示，这样看起来更清晰；如果不加，会有点乱。

3.4. 查看当前是哪个用户

查看当前用户的命令如下：

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

3.5. 查看当前所使用的数据库

查看当前数据库的命令如下：

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

3.6. 创建一个新库

新建一个库的命令如下：

mysql> create database db1;
Query OK, 1 row affected (0.06 sec)

3.7. 创建一个新表

新建一个表的命令如下：

mysql> use db1;
Database changed
mysql> create table t1 (`id` int(4), `name` char(40));
Query OK, 0 rows affected (0.34 sec)

注意，这里的字段名id和name都需要用反引号括起来。

3.8. 查看当前数据库的版本

查看MySQL版本的命令如下：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.35-log |
+------------+
1 row in set (0.00 sec)

3.9. 查看MySQL的当前状态

查看MySQL当前状态的命令如下：

mysql> show status;
+-----------------------------------------------+-------------+
| Variable_name                                 | Value       |
+-----------------------------------------------+-------------+
| Aborted_clients                               | 0           |
| Aborted_connects                              | 1           |
| Binlog_cache_disk_use                         | 0           |
| Binlog_cache_use                              | 0           |
| Binlog_stmt_cache_disk_use                    | 1           |
| Binlog_stmt_cache_use                         | 115         |
| Bytes_received                                | 1095        |
| Bytes_sent                                    | 25439       |
| Com_admin_commands                            | 0           |
| Com_assign_to_keycache                        | 0           |
| Com_alter_db                                  | 0           |
.....

由于内容太长，并没有在上面示例中列出全部信息。

3.10. 查看MySQL的参数

查看MySQL各参数的命令如下：

mysql> show variables\G;
*************************** 1. row ***************************
Variable_name: auto_increment_increment
        Value: 1
*************************** 2. row ***************************
Variable_name: auto_increment_offset
        Value: 1
*************************** 3. row ***************************
Variable_name: autocommit
        Value: ON
*************************** 4. row ***************************
Variable_name: automatic_sp_privileges
        Value: ON
*************************** 5. row ***************************
Variable_name: avoid_temporal_upgrade
        Value: OFF
*************************** 6. row ***************************
Variable_name: back_log
        Value: 80
*************************** 7. row ***************************
Variable_name: basedir
        Value: /usr/local/mysql/
*************************** 8. row ***************************
Variable_name: big_tables
...

也是列出的信息非常多，在上面的示例中并没有列出全部参数。其中有很多参数都是可以在/etc/my.cnf中定义的，并且有部分参数是可以在线编辑的。

3.11. 修改MySQL的参数

举例来说，修改参数max_connect_errors的操作方法如下：

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

mysql> set global max_connect_errors = 1000;
Query OK, 0 rows affected (0.01 sec)

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

在MySQL命令行，符号%类似于Shell下的*，表示通配。使用命令set global可以临时修改某些参数，但是重启MySQL服务后这些修改会失效。所以，如果你想让这些修改永久生效，就要在配置文件my.cnf中定义。

3.12. 查看当前MySQL服务器的队列

查看服务器队列在日常的管理工作中最为频繁。因为使用它可以查看当前MySQL在干什么，也可以发现是否有锁表，如下所示：

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

3.13. 修改一个普通用户并授权

授权命令如下：

mysql> grant all on *.* to user1 identified by '123456';
Query OK, 0 rows affected (0.00 sec)

其中，all表示所有的权限（如读、写、查询、删除等操作）；.周围有两个星号，前面的表示所有的数据库，后面的表示所有的表；indentified by后面跟密码，用单引号括起来。这里的user1特指localhost上的user1。

如果是网络给其它机器上的某个用户授权，则要执行如下命令：

mysql> grant all on db1.* to 'user2'@'192.168.1.111' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

其中，用户和主机的IP之间有一个符号@。另外，命令中主机IP可以用%代替，表示所有主机，如下所示：

mysql> grant all on db1.* to 'user3'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

4. 常用的SQL语句

关系型数据库的SQL语句都是一样的。假如你之前学过SQL Server或者Oracle，便会觉得这部分内容非常熟悉。

4.1. 查询语句

最常见的查询语句就是下面这两种形式。

• 第一种形式

  mysql> select count(*) from mysql.user;
  +----------+
  | count(*) |
  +----------+
  |        9 |
  +----------+
  1 row in set (0.00 sec)

其中，mysql.user表示MySQL库的user表，count(*)表示表中共有多少行。

• 第二种形式

  mysql> select * from mysql.db;

它表示查询MySQL库的db表中的所有数据。当然也可以查询单个字段或者多个字段，如下所示：

mysql> select db from mysql.db;
mysql> select db,user from mysql.db;

同样，在查询语句中也可以是哟个万能匹配符%，如下所示：

mysql> select * from mysql.db where host like '192.168. %';

4.2. 插入一行

插入操作在MySQL中也很普遍，如下所示：

mysql> insert into db1.t1 values (1, 'abc');
Query OK, 1 row affected (0.31 sec)

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

4.3. 更改表的某一行

MySQL表里存放的数据支持更改某个字段，如下所示：

mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.00 sec)

4.4. 清空某个表的数据

有时我们不希望删除表，而只是想清空某个表的数据，如下所示：

mysql> truncate table db1.t1;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from db1.t1;
Empty set (0.00 sec)

4.5. 删除表

如果某个表不需要了，可以直接删除，如下所示：

mysql> drop table db1.t1;
Query OK, 0 rows affected (0.01 sec)

4.6. 删除数据库

表可以删除，当然数据库也可以删除，如下所示：

mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)

4.7. 修复表

mysql> repair table tb1 [use frm];

---

5. 在Shell的命令行下执行mysql操作

还有一个很重要的知识点，那就是在Shell的命令行下面直接执行mysql的操作。如下所示：

# mysql -uroot -p123456 mysql -e "show tables"

说明：

• -e前面的mysql指的是库的名字
• -e后面双括号所括起来的就是mysql的命令

---

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

备份和恢复MySQL数据库这部分内容非常重要，请牢固掌握。

6.1. MySQL备份

本分MySQL要使用mysqldump命令，具体用法如下：

[root@localhost ~]# mysqldump -uroot -p'theshu..' mysql > /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /tmp/mysql.sql
-rw-r--r-- 1 root root 652186 3月   7 19:30 /tmp/mysql.sql

其中，-u和-p两个选项的使用方法和前面介绍的一样；后面的mysql指的是库名，然后重定向到一个文本文档里。备份做完后，你可以查看/tmp/mysql.sql这个文件里的内容。

6.2. MySQL恢复

MySQL的恢复和备份正好相反，如下所示：

[root@localhost ~]# mysql -uroot -p'theshu..' mysql < /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.

6.3. 只备份一个表

有时候我们会有只备份一个表的情况：

# mysqldump -uroot -p db tb1 > /tmp/tb1.sql

说明：

• db为数据库名字
• tb1为表的名字
• 恢复时不用加表明，只需要加数据库名字即可
  • mysql -uroot -p db < /tmp/tb1.sql

6.4. 备份时指定字符集

备份时指定字符集：

# mysqldump -uroot --default-character-set=utf8 db > /tmp/1.sql

恢复时也指定字符集：

# mysql -uroot -p --default-character-set=utf8 db < /tmp/1.sql

说明：指定字符集的目的是为了避免有的建表sql中并没有指定字符集，而直接使用MySQL默认字符集的情况，这样会造成乱码。

---

关于MySQL的基本操作就先介绍这么多。当然学会了这些还远远不够，希望在工作中能够学到更多的知识。如果你对MySQL很感兴趣，不妨深入研究一下。

---