引言

数据库的创建和管理是构建可靠数据的关键，关系到所存储数据的安全与稳定。在 MySQL 这个强大的关系型数据库系统中，数据库的创建与管理需要精准的步骤和妥善的配置。下面，将深入探讨如何使用MySQL 来管理数据库，我们不仅关注了基础的语法操作，还涉及了一些潜在的问题及实践，从创建数据库、授予权限到处理数据库名称大小写问题，再到配置数据库的属性等，带你走进数据库管理的正式通道。

1 创建数据库

使用create database语句创建数据库，用户拥有create权限才能创建数据库。

-- 例子：创建dba用户，拥有创建数据库名称为db_x的权限mysql> create user 'dba'@'%' identified by 'dba';Query OK, 0 rows affected (0.00 sec)mysql>  grant create on `db\__`.* to 'dba'@'%';Query OK, 0 rows affected (0.00 sec)

授权时可指定授权的数据库名称，使用*表示所有数据库，使用_匹配一个字符，使用%匹配任意数量的字符。使用反斜杠()对_和%进行转意。
使用刚刚创建的账号创建数据库

[root@box3 ~]# mysql -udba -pdba -h172.16.20.51Welcome to the MySQL monitor.  Commands end with ; or \g.mysql> create database biz_db;ERROR 1044 (42000): Access denied for user 'dba'@'%' to database 'biz_db'mysql> create database db02;ERROR 1044 (42000): Access denied for user 'dba'@'%' to database 'db02'mysql> create database db_x;Query OK, 1 row affected (0.00 sec)mysql> create database db_y;Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || db_x               || db_y               |+--------------------+mysql> show grants;+----------------------------------------+| Grants for dba@%                       |+----------------------------------------+| GRANT USAGE ON *.* TO 'dba'@'%'        || GRANT CREATE ON `db\__`.* TO 'dba'@'%' |+----------------------------------------+

创建数据库之后，会在datadir指定的路径创建数据库同名目录

mysql> show variables where variable_name in ('datadir');+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| datadir       | /var/lib/mysql/ |[root@box1 mysql]# ls -ld /var/lib/mysql/db_*drwxr-x---. 2 mysql mysql 20 4月   6 22:53 /var/lib/mysql/db_xdrwxr-x---. 2 mysql mysql 20 4月   6 22:58 /var/lib/mysql/db_y

2 数据库名称的大小写问题

数据库名称是否区分大小写？这跟文件系统文件名是否区分大小写有关。
linux系统下(ext系统文件系统、xfs），文件名称区分大小写，默认lower_case_tables_names设置为0，mysql数据库名称区分大小写。

mysql> show variables like '%lower_case%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| lower_case_file_system | OFF   || lower_case_table_names | 0     |+------------------------+-------+mysql> create database biz_db;Query OK, 1 row affected (0.00 sec)mysql> create database Biz_db;Query OK, 1 row affected (0.00 sec)mysql> show databases like '%iz%';+-----------------+| Database (%iz%) |+-----------------+| Biz_db          || biz_db          |+-----------------+mysql> use BIZ_DBERROR 1049 (42000): Unknown database 'BIZ_DB'mysql> use Biz_dbDatabase changed[root@box1 mysql]# ls -ld /var/lib/mysql/*iz_dbdrwxr-x---. 2 mysql mysql 20 4月   6 23:03 /var/lib/mysql/biz_dbdrwxr-x---. 2 mysql mysql 20 4月   6 23:03 /var/lib/mysql/Biz_db

lower_case_file_system是一个只读参数。会根据mysql所在文件系统的特性设置相应的值。

mysql> set global lower_case_file_system=ON;ERROR 1238 (HY000): Variable 'lower_case_file_system' is a read only variable

如果把lower_case_file_system配置到配置文件，实例会无法启动

[root@box1 mysql]# service mysqld restartRedirecting to /bin/systemctl restart mysqld.serviceJob for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.2021-04-07T03:14:52.203804Z 0 [ERROR] unknown variable 'lower_case_file_system=ON'2021-04-07T03:16:09.587798Z 0 [ERROR] unknown option '--lower_case_file_system'2021-04-07T03:18:06.738497Z 0 [ERROR] unknown option '--lower-case-file-system'

如果参数lower_case_table_names设置为1，则数据库名称和表名不区分大小写。

mysql> show variables like '%lower_case%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| lower_case_file_system | OFF   || lower_case_table_names | 1     |+------------------------+-------+2 rows in set (0.03 sec)mysql> create database USER_DB;Query OK, 1 row affected (0.01 sec)mysql> create database user_db;ERROR 1007 (HY000): Cant create database 'user_db'; database exists[root@box1 mysql]# ls -ld /var/lib/mysql/*dbdrwxr-x---. 2 mysql mysql 20 4月   7 01:35 /var/lib/mysql/user_db

注意事项

对已有的数据库实例修改lower_case_table_names参数，可能会出现问题。

1、lower_case_table_names从0改到1
将参数从0改成1，如果原来的数据库中存在大写的数据库名或表名，修改参数后将无法访问这些数据库和表。

mysql> show variables like '%lower%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| lower_case_file_system | OFF   || lower_case_table_names | 1     |+------------------------+-------+mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || ORDER_DB           |mysql> use ORDER_DB;ERROR 1049 (42000): Unknown database 'order_db'

2、lower_case_table_names从1改到0
将参数从1改成0，则原先的库、表都是以小写方式存在。如果SQL语句中使用大写的方式访问这些库表，会报错。

mysql> show variables like '%lower%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| lower_case_file_system | OFF   || lower_case_table_names | 0     |+------------------------+-------+2 rows in set (0.00 sec)mysql> show tables;+---------------+| Tables_in_db1 |+---------------+| tab           |+---------------+1 row in set (0.00 sec)mysql> select * from Tab;ERROR 1146 (42S02): Table 'db1.Tab' doesnt existmysql> select * from tab;+------+------+| col  | COL2 |+------+------+| tab  | tab  |+------+------+1 row in set (0.00 sec)

3、将数据库迁移到不同的环境时，如果新老环境的lower_case_table_names参数不一样，则有可能会发生问题。
比如windows和linux环境下，lower_case_table_names参数的默认值不一样，换操作系统时，可能会遇到问题。
4、建议将所有环境的lower_case_table_names参数都设置成1。 数据库名称、表的名称只使用小写字符。

关于windows

在windows系统中，文件名不区分大小写，默认lower_case_table_names也为1，对应的数据库也不区分大小写。

mysql> show variables like '%lower%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| lower_case_file_system | ON    || lower_case_table_names | 1     |+------------------------+-------+2 rows in set, 1 warning (0.00 sec)mysql> create database biz_db;Query OK, 1 row affected (0.00 sec)mysql> create database Biz_db;ERROR 1007 (HY000): Cant create database 'biz_db'; database existsmysql> create database ORDER_DB;Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || biz_db             || mysql              || order_db           || performance_schema || sys                |+--------------------+

3 数据库的属性

创建数据库时，可以指定数据库的默认字符集和排序规则。

mysql> create database db_0;Query OK, 1 row affected (0.00 sec)mysql> create database db_1 default charset utf8;Query OK, 1 row affected (0.00 sec)mysql> create database db_2 default charset utf8 collate utf8_bin;Query OK, 1 row affected (0.00 sec)mysql> show create database db_0;+----------+-----------------------------------------------------------------+| Database | Create Database                                                 |+----------+-----------------------------------------------------------------+| db_0     | CREATE DATABASE `db_0` /*!40100 DEFAULT CHARACTER SET latin1 */ |+----------+-----------------------------------------------------------------+1 row in set (0.00 sec)mysql> show create database db_1;+----------+---------------------------------------------------------------+| Database | Create Database                                               |+----------+---------------------------------------------------------------+| db_1     | CREATE DATABASE `db_1` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+---------------------------------------------------------------+1 row in set (0.00 sec)mysql> show create database db_2;+----------+--------------------------------------------------------------------------------+| Database | Create Database                                                                |+----------+--------------------------------------------------------------------------------+| db_2     | CREATE DATABASE `db_2` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |+----------+--------------------------------------------------------------------------------+

数据库的字符集和collate属性保存在数据库目录下的db.opt文件中（8.0版本的机制有所变化，不再有db.opt文件，元数据存储在系统表中，系统表的存储引擎为innodb）

[root@box1 mysql]# ls -l /var/lib/mysql/db_?/db.opt-rw-r-----. 1 mysql mysql 65 4月   7 02:07 /var/lib/mysql/db_0/db.opt-rw-r-----. 1 mysql mysql 61 4月   7 02:07 /var/lib/mysql/db_1/db.opt-rw-r-----. 1 mysql mysql 54 4月   7 02:07 /var/lib/mysql/db_2/db.opt[root@box1 mysql]# more /var/lib/mysql/db_?/db.opt::::::::::::::/var/lib/mysql/db_0/db.opt::::::::::::::default-character-set=latin1default-collation=latin1_swedish_ci::::::::::::::/var/lib/mysql/db_1/db.opt::::::::::::::default-character-set=utf8default-collation=utf8_general_ci::::::::::::::/var/lib/mysql/db_2/db.opt::::::::::::::default-character-set=utf8default-collation=utf8_bin

mysql建表时如果不指定字符集和collate，会使用所在数据库的默认字符集和collate。
建议创建数据库的时候 就指定数据库的字符集和collate。

4 数据库其他相关操作

1.切换当前数据库

在mysql client中，使用use命令切换当前数据库。

mysql> show databases;+--------------------+| Database           |+--------------------+| db_0               || db_1               || db_2               |mysql> use db_0;Database changed

2.查看当前数据库

使用函数database()或schema()查看会话当前所在数据库

mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select schema();+----------+| schema() |+----------+| mysql    |+----------+1 row in set (0.00 sec)mysql> select database();+------------+| database() |+------------+| mysql      |+------------+1 row in set (0.00 sec)

3.修改数据库默认属性

mysql> alter database db_0 default charset utf8;Query OK, 1 row affected (0.00 sec)mysql> show create database db_0;+----------+---------------------------------------------------------------+| Database | Create Database                                               |+----------+---------------------------------------------------------------+| db_0     | CREATE DATABASE `db_0` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+---------------------------------------------------------------+1 row in set (0.00 sec)mysql> alter database db_0 default collate utf8_bin;Query OK, 1 row affected (0.00 sec)mysql> show create database db_0;+----------+--------------------------------------------------------------------------------+| Database | Create Database                                                                |+----------+--------------------------------------------------------------------------------+| db_0     | CREATE DATABASE `db_0` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |+----------+--------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> alter database db_0 default collate latin1_bin;Query OK, 1 row affected (0.00 sec)mysql> show create database db_0;+----------+------------------------------------------------------------------------------------+| Database | Create Database                                                                    |+----------+------------------------------------------------------------------------------------+| db_0     | CREATE DATABASE `db_0` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ |+----------+------------------------------------------------------------------------------------+1 row in set (0.00 sec)

4.删除数据库

使用drop命令删除数据库。删除需要drop权限。经过测试，5.7.32版本，需要drop on .的权限才能成功执行drop database命令。

grant drop on *.* to 'dba'@'%';[root@box3 ~]# mysql -udba -pdba -h172.16.20.51mysql> drop database db_0;Query OK, 0 rows affected (0.00 sec)

账号有db_2的drop权限，但无法删除该数据库：

mysql> show grants;+----------------------------------------------+| Grants for dba@%                             |+----------------------------------------------+| GRANT USAGE ON *.* TO 'dba'@'%'              || GRANT DELETE, DROP ON `mysql`.* TO 'dba'@'%' || GRANT CREATE ON `db\__`.* TO 'dba'@'%'       || GRANT DROP ON `db_1`.* TO 'dba'@'%'          || GRANT DROP ON `db_x`.* TO 'dba'@'%'          || GRANT DROP ON `db_2`.* TO 'dba'@'%'          |+----------------------------------------------+6 rows in set (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || db_2               || mysql              |+--------------------+3 rows in set (0.00 sec)mysql> drop database db_2;ERROR 1044 (42000): Access denied for user 'dba'@'%' to database 'db_2'

删除数据库是一项高危操作，生产环境下需要严格规范删除数据库的操作流程，以下是一些关键点需要注意：
1、权限控制：严格控制drop database的权限，确保只有授权人员能够执行这一操作。
2、递归删除：drop database会递归删除数据库内的所有对象，包括表、视图等。执行前需确认清楚删除的对象。
3、性能影响：在数据库内有大量表或表占用大量空间时，drop database可能对数据库实例性能产生负面影响，影响正常业务访问。需在低峰期执行，或者考虑分阶段删除。
4、误删除恢复：误删除后的恢复比较耗时，因此在执行drop database前，应该经过仔细确认和备份，以减少误操作的可能性。
5、备份策略：始终确保有可靠的数据库备份。如果没有备份，误删除可能导致数据无法恢复。

更多技术信息请查看云掣官网云掣YunChe - 可观测运维专家 | 大数据运维托管 | 云MSP服务