mysql/MariaDB学习2.1 mysql创建数据库、用户管理、授权及常用查询设置

环境：

CentOS 7,mysql5.7

摘要说明：

本篇文章主要讲述mysql如何创建数据库，如何创建用户，如何进行授权及常用系统设置；

步骤：

1.数据库创建，删除及查询

连接到root用户下mysql数据库：

# 连接mysql
mysql -u root -p
#切换到mysql数据库
mysql> use mysql;

用户创建，查询及删除：

#创建数据库并指定数据库编码
mysql> create database test_db default character set utf8 COLLATE utf8_general_ci;
#查询数据库
mysql> show databases;
#删除数据库
mysql> drop database test_db;

2.用户创建及授权

创建用户：CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username - 你将创建的用户名,

host - 指定该用户在哪个主机上可以登陆，此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录，如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录;也可以指定某台机器可以远程登录;

password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。

#创建用户并支持远程连接
mysql> create user 'user_test'@'%' identified by 'Test@123';
#如何创建错了可以删除
mysql> DROP USER 'user_test';

修改用户密码：

#指定用户及host修改密码
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword'); 
#如果是当前登陆用户用: 
SET PASSWORD = PASSWORD('newpassword');

授权：

#授权某用户某数据库部分权限
mysql> grant select,update on test_db.* to user_test@'%' identified by 'Test@123';
#授权某用户某数据库全部权限
mysql> grant all privileges on test_db.* to user_test@'%' identified by 'Test@123';
#授权某用户所有数据库的部分权限
mysql> grant select,update on *.* to user_test@'%' identified by 'Test@123';
#立即生效
mysql> FLUSH PRIVILEGES;

注：privileges - 用户的操作权限,如select,delete,update,create,drop等(详细列表可自行百度)，如果要授予所有的权限可使用all（参考第二种授权方式）;% 表示对所有非本地主机授权，不包括localhost。

解除授权：

--解除用户所有数据库（表）的所有权限(不包含赋权权限) 
mysql> REVOKE ALL PRIVILEGES ON *.* FROM test_user;
--解除赋权权限  
mysql> REVOKE GRANT OPTION ON *.* FROM test_user;

3.其他查询及设置

连接数（max_connections）查询设置：

#查询mysql最大连接数
mysql> show variables like '%max_connections%';
#查询mysql最大响应连接数即最大使用
mysql> show global status like 'Max_used_connections';
#当前连接数
mysql> show status like 'threads_connected';
#设置连接数
mysql> set GLOBAL max_connections=256;

临时表缓存(tmp_table_size)查询配置：

mysql> SHOW VARIABLES LIKE '%tmp_table_size%';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
1 row in set (0.01 sec)

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 6     |服务器执行语句时在硬盘上自动创建的临时表的数量
| Created_tmp_files       | 6     |mysqld已经创建的临时文件的数量
| Created_tmp_tables      | 118   |服务器执行语句时自动创建的内存中的临时表的数量。如果 
|                                 |Created_tmp_disk_tables较大，你可能要增加tmp_table_size值 
|                                 |使临时 表基于内存而不基于硬盘
+-------------------------+-------+
3 rows in set (0.00 sec)

#实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。如果临时表大于这两个的任何一
#个，都会存硬盘缓存：自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下
mysql> SHOW VARIABLES LIKE '%max_heap_table_size%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set (0.00 sec)
#每次创建临时表，Created_tmp_tables增加，如果临时表大小超过tmp_table_size，则是在磁盘上创建临
#时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，比较#理想的配置是：
#Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
#比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，应该相当好了
#默认大小是 32M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞

索引缓冲区（key_buffer_size）查询配置：

mysql> show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |索引缓冲区的大小
+-----------------+---------+
1 row in set (0.00 sec)

mysql> show global status like 'key%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |索引缓存内已经更改，但还没有清空到硬盘上的索引的数据块数量。
| Key_blocks_unused      | 6688  |索引缓存内未使用的块数量。你可以使用该值来确定使用了多少键缓存
| Key_blocks_used        | 10    |索引缓存内使用的块数量。该值为高水平线标记，说明已经同时最多使用了多少块
| Key_read_requests      | 63    |一共有XXX个索引读取请求
| Key_reads              | 10    |索引读取请求在内存中没有找到，直接从硬盘读取索引
| Key_write_requests     | 11    |将索引的数据块写入缓存的请求数。
| Key_writes             | 9     |将索引向硬盘写入数据块的物理写操作的次数。
+------------------------+-------+
7 rows in set (0.00 sec)

mysql> show variables like 'key_cache_block_size';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| key_cache_block_size | 1024  |
+----------------------+-------+
1 row in set (0.00 sec)
#比例key_reads /key_read_requests应该尽可能的低，至少是1:100，1:1000更好
#如果Key_reads太大,则应该把my.cnf中key_buffer_size变大.可以用Key_reads/Key_read_requests计算#出cache失败率
#Key_writes/Key_write_requests：比例接近1较好