MySQL运维实战(3.3) 管理数据库(database)
数据库
使用create database语句创建数据库。创建数据库的账号需要有create权限。
创建数据库
用户拥有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.51 Welcome 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_x
drwxr-x---. 2 mysql mysql 20 4月 6 22:58 /var/lib/mysql/db_y数据库名称的大小写问题
数据库名称是否区分大小写？这跟文件系统文件名是否区分大小写有关。
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_DB ERROR 1049 (42000): Unknown database 'BIZ_DB' mysql> use Biz_db Database changed [root@box1 mysql]# ls -ld /var/lib/mysql/*iz_db drwxr-x---. 2 mysql mysql 20 4月 6 23:03 /var/lib/mysql/biz_db drwxr-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 restart Redirecting to /bin/systemctl restart mysqld.service Job 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/*db drwxr-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 exist mysql> 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 exists mysql> 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 | +--------------------+
数据库的属性
创建数据库时，可以指定数据库的默认字符集和排序规则。
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=latin1 default-collation=latin1_swedish_ci :::::::::::::: /var/lib/mysql/db_1/db.opt :::::::::::::: default-character-set=utf8 default-collation=utf8_general_ci :::::::::::::: /var/lib/mysql/db_2/db.opt :::::::::::::: default-character-set=utf8 default-collation=utf8_bin
mysql建表时如果不指定字符集和collate，会使用所在数据库的默认字符集和collate。
建议创建数据库的时候 就指定数据库的字符集和collate。
数据库其他相关操作
切换当前数据库
在mysql client中，使用use命令切换当前数据库。
mysql> show databases; +--------------------+ | Database | +--------------------+ | db_0 | | db_1 | | db_2 | mysql> use db_0; Database changed
查看当前数据库
使用函数database()或schema()查看会话当前所在数据库
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> select schema(); +----------+ | schema() | +----------+ | mysql | +----------+ 1 row in set (0.00 sec) mysql> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec)
修改数据库默认属性
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)
删除数据库
使用drop命令删除数据库。删除需要drop权限。经过测试，5.7.32版本，需要drop on *.*的权限才能成功执行drop database命令。
grant drop on *.* to 'dba'@'%'; [root@box3 ~]# mysql -udba -pdba -h172.16.20.51 mysql> 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'
删除数据库是一个高危操作，生产环境下需要严格规范删除数据库的操作流程。需要严格控制drop database的权限。
1、drop database会递归删除数据库内的所有对象
2、如果数据库内的表的数量大或表占用的空间大，drop database可能会影响实例性能，影响正常业务访问。
3、误删除恢复比较耗时。
4、如果没有备份，可能无法恢复数据。
