04、MySQL 管理

通过以下命令来检查 MySQL 服务器是否启动
```python
ps -ef | grep mysql
```

例如在我的苹果电脑上运行如下
```python
[root@ddkk.com ~]# ps -ef | grep mysql
mysql      1520      1  0 18:21 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql      1682   1520  0 18:21 ?        00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root       1774   1744  0 18:29 pts/0    00:00:00 grep --color=auto mysql

```

如果MySql 已经启动，上面的命令会输出 mysql 进程列表 `mysqld_safe` 和 `mysqld`

### **启动 MySQL 命令**

------------

如果MySQL 未启动，使用以下命令来启动 MySQL 服务器

```python
[root@ddkk.com ~]# mysqld_safe &
```

### **关闭 MySQL 命令**

------------

如果想关闭目前运行的 MySQL 服务器, 可以执行以下命令
```python
[root@ddkk.com ~]# mysqladmin -u root -p shutdown
Enter password: ******

```

### **MySQL 添加用户**

------------

MySQL 服务器默认已经添加了 **root** 用户

如果需要添加 MySQL 用户，我们只需要在数据库 `mysql` 中的表 `user` 中插入新用户即可

下面的SQL 语句可以添加一个新的用户

用户名为 `demo` 密码为 `demo123` 并授权用户可进行` SELECT,INSERT,UPDATE` 操作权限

`INSERT INTO mysql.user (host, user, password, select_priv, insert_priv, update_priv) VALUES ('localhost', 'demo', PASSWORD('demo123'), 'Y', 'Y', 'Y');
`

然后刷新权限

`FLUSH PRIVILEGES;`

你可以按照下面的步骤尝试一下

```python
[root@ddkk.com ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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]> INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES ('localhost', 'demo', PASSWORD('demo123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected, 4 warnings (0.01 sec)
MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SELECT host, user, password FROM user WHERE user = 'demo';
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | demo | *3055544BD641D0814B910C4ACA5799F51B80F460 |
+-----------+------+-------------------------------------------+
1 row in set (0.01 sec)

```

在添加用户时，请注意使用 MySQL 提供的 `PASSWORD()` 函数来对密码进行加密

我们在上面的范例中看到用户密码加密后为：

```python
3055544BD641D0814B910C4ACA5799F51B80F460
```

**注意**

1、 在MySQL5.7中user表的password已换成了authentication_string；
2、 添加用户后需要执行FLUSHPRIVILEGES语句，这个命令执行后会重新载入授权表如果使用该命令，无法使用新创建的用户来连接MySQL服务器，除非重启MySQL服务器；

**用户权限**

可以在创建用户时，为用户指定权限，在对应的权限列中，在插入语句中设置为 ‘Y’ 即可

用户权限说明

| 命令标识 | 授权表中对应的列 | 说明 |
| --- | --- | --- |
|CREATE	|Create_priv|	创建数据库、表或索引 |
|CREATE TEMPORARY TABLES|	Create_tmp_table_priv|	创建临时数据表|
|CREATE ROUTINE|	Create_routine_priv|	创建函数或存储|
|CREATE VIEW|	Create_view_priv	|创建视图|
|CREATE USER	|Create_user_priv|	创建用户|
|EXECUTE|	Execute_priv|	执行函数或存储过程|
|INDEX|	Index_priv	|建立索引|
|REFERENCES|	References_priv	|建立约束||
|DROP|	Drop_priv	|删除表|
|SELECT|	Select_priv	|查询数据|
|INSERT|	Insert_priv	|插入数据|
|UPDATE|	Update_priv	|更新数据|
|DELETE|	Delete_priv	|删除数据|
|LOCK TABLES|	Lock_tables_priv	|锁定表格|
|SHOW DATABASES|	Show_db_priv	|列出数据库|
|SHOW VIEW|	Show_view_priv	|列出视图|
|USAGE|		|只有登录权限， 其它权限都没有|
|ALL|		|所有权限,除了 WITH GRANT OPTION|
|ALTER|	Alter_priv	|更改数据表|
|ALTER ROUTINE|	Alter_routine_priv	|更改函数或存储过程|
|PROCESS|	Process_priv	|显示连接进程和中断连接进程||
|FILE|	File_priv	|载入文件|
|RELOAD|	Reload_priv	|可以用 FLUSH|
|REPLICATION CLIENT|	Repl_client_priv	|可以检查 Masters 和 Slaves|
|REPLICATION SLAVE|	Repl_slave_priv	|在 Slave 里的特殊权限|
|SHUTDOWN|	Shutdown_priv	|关闭 MySQL|
|WITH GRANT OPTION|	Grant_priv	|可以将自己拥有的权限赋给其它用户|
|SUPER|	Super_priv	|执行 kill 线程，change master、purge master logs、set global等命令的权限|
|create tablespace|	Create_tablespace_priv	|创建表空间|
|Event|	Event_priv	|确定用户能否创建、修改和删除事件|
|Trigger|	Trigger_priv|	确定用户能否创建和删除触发器|

### **GRANT 命令添加用户**

------------

下面的SQL 语句会给指定数据库 test 添加用户 souyunku1 ，密码为 souyunku123xyz

`GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON test.* TO 'souyunku1'@'localhost' IDENTIFIED BY 'souyunku123xyz';
`

操作演示

```python
[root@ddkk.com ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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]> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON test.* TO 'souyunku1'@'localhost' IDENTIFIED BY 'souyunku123xyz';
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SELECT host, user, password FROM user;
+-----------------------+-------+-------------------------------------------+
| host                  | user  | password                                  |
+-----------------------+-------+-------------------------------------------+
| localhost             | root  |                                           |
| localhost.localdomain | root  |                                           |
| 127.0.0.1             | root  |                                           |
| ::1                   | root  |                                           |
| localhost             |       |                                           |
| localhost.localdomain |       |                                           |
| localhost             | demo  | *3055544BD641D0814B910C4ACA5799F51B80F460 |
| localhost             | souyunku1 | *302D386BA9E00875ADDF9ED0AD447B19FAE2FEDB |
+-----------------------+-------+-------------------------------------------+
8 rows in set (0.00 sec)
MariaDB [mysql]>
```

### **/etc/my.cnf 配置文件**

------------

一般情况下，我们不需要修改该配置文件，该文件默认配置如下：

```python
[root@ddkk.com ~]# cat /etc/my.cnf

```
显示内容如下
```python
[mysqld]
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
```

在配置文件中，我们可以指定不同的错误日志文件存放的目录，但一般不需要改动这些配置