MySQL：关于“create database …”
（本文是阅读君三思的《涂抹MySQL》的读书笔记。）
在介绍MySQL的数据库的章节里，君三思详细的描述了从“mysql> create database xxx;”到对文件系统的改变过程。
按照他的描述，我在自己的环境里也做了相同的实验，具体如下：
首先，查看下MySQL数据存放目录的当前状态：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
[root@rhel71 ~]# cd /mydata/mysql/data/ [root@rhel71 data]# ll total 124084 drwxr-x--- 2 mysql mysql 19 Dec 6 13:40 adamhuandb -rw-r----- 1 mysql mysql 56 Dec 3 00:35 auto.cnf -rw-r----- 1 mysql mysql 287 Dec 3 20:48 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 Dec 6 13:36 ibdata1 -rw-r----- 1 mysql mysql 50331648 Dec 6 13:36 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 Dec 3 00:31 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 Dec 6 13:36 ibtmp1 drwxr-x--- 2 mysql mysql 4096 Dec 3 00:31 mysql -rw-r----- 1 mysql mysql 32068 Dec 3 00:31 mysql-bin.000001 -rw-r----- 1 mysql mysql 597 Dec 3 00:45 mysql-bin.000002 -rw-r----- 1 mysql mysql 334 Dec 3 20:36 mysql-bin.000003 -rw-r----- 1 mysql mysql 177 Dec 3 20:37 mysql-bin.000004 -rw-r----- 1 mysql mysql 177 Dec 3 20:46 mysql-bin.000005 -rw-r----- 1 mysql mysql 177 Dec 3 20:48 mysql-bin.000006 -rw-r----- 1 mysql mysql 659 Dec 6 13:40 mysql-bin.000007 -rw-r----- 1 mysql mysql 133 Dec 6 13:36 mysql-bin.index drwxr-x--- 2 mysql mysql 19 Dec 3 00:31 performance_schema -rw-r----- 1 mysql mysql 1154235 Dec 6 13:49 rhel71.log drwxr-x--- 2 mysql mysql 4096 Dec 3 00:31 sys [root@rhel71 data]# |
进入MySQL终端，查看当前数据库的状态：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
[root@rhel71 ~]# mysqlplus.sh Login MySQL Service @ rhel71:3306 mysql: [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 5 Server version: 5.7.7-rc-log Source distribution Copyright (c) 2000, 2015, 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | adamhuandb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> |
创建一个名为：miyue的数据库：
|
1 2 3 4 |
mysql> create database miyue; Query OK, 1 row affected (0.00 sec) mysql> |
再次查看数据库的状态：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | adamhuandb | | miyue | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> |
可以看到，名为“miyue”的数据库已经被成功创建。
看看文件系统中的变化：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[root@rhel71 data]# pwd /mydata/mysql/data [root@rhel71 data]# ls adamhuandb ibdata1 ibtmp1 mysql-bin.000001 mysql-bin.000004 mysql-bin.000007 rhel71.log auto.cnf ib_logfile0 miyue mysql-bin.000002 mysql-bin.000005 mysql-bin.index sys ib_buffer_pool ib_logfile1 mysql mysql-bin.000003 mysql-bin.000006 performance_schema [root@rhel71 data]# cd miyue [root@rhel71 miyue]# ls db.opt [root@rhel71 miyue]# file db.opt db.opt: ASCII text [root@rhel71 miyue]# cat db.opt default-character-set=utf8 default-collation=utf8_general_ci [root@rhel71 miyue]# |
可以看到，文件系统中新增了一个“miyue”的文件路径。
其中，db.opt中存放了字符集相关的信息。
字符集的信息是与创建时候的语句有关的：
|
1 2 3 4 5 6 7 8 9 |
mysql> show create database miyue; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | miyue | CREATE DATABASE `miyue` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> |
如果你要查看完整的数据库的字符集的信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from information_schema.schemata; +--------------+--------------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+--------------------+----------------------------+------------------------+----------+ | def | information_schema | utf8 | utf8_general_ci | NULL | | def | adamhuandb | utf8 | utf8_general_ci | NULL | | def | mysql | utf8 | utf8_general_ci | NULL | | def | performance_schema | utf8 | utf8_general_ci | NULL | | def | sys | utf8 | utf8_general_ci | NULL | +--------------+--------------------+----------------------------+------------------------+----------+ 5 rows in set (0.00 sec) mysql> |
通过文件系统中新增路径，给MySQL创建数据库：
Before：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | adamhuandb | | miyue | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> |
操作：“mkdir xxxxx”。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[root@rhel71 data]# pwd /mydata/mysql/data [root@rhel71 data]# ls adamhuandb ibdata1 ibtmp1 mysql-bin.000001 mysql-bin.000004 mysql-bin.000007 rhel71.log auto.cnf ib_logfile0 miyue mysql-bin.000002 mysql-bin.000005 mysql-bin.index sys ib_buffer_pool ib_logfile1 mysql mysql-bin.000003 mysql-bin.000006 performance_schema [root@rhel71 data]# [root@rhel71 data]# mkdir alienware [root@rhel71 data]# [root@rhel71 data]# ls adamhuandb ib_buffer_pool ib_logfile1 mysql mysql-bin.000003 mysql-bin.000006 performance_schema alienware ibdata1 ibtmp1 mysql-bin.000001 mysql-bin.000004 mysql-bin.000007 rhel71.log auto.cnf ib_logfile0 miyue mysql-bin.000002 mysql-bin.000005 mysql-bin.index sys [root@rhel71 data]# ls alienware/ [root@rhel71 data]# |
After：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | adamhuandb | | alienware | | miyue | | mysql | | performance_schema | | sys | +--------------------+ 7 rows in set (0.00 sec) mysql> |
通过文件系统中配置db.opt，修改MySQL中的字符集属性：
Before：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select * from information_schema.schemata; +--------------+--------------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+--------------------+----------------------------+------------------------+----------+ | def | information_schema | utf8 | utf8_general_ci | NULL | | def | adamhuandb | utf8 | utf8_general_ci | NULL | | def | alienware | utf8 | utf8_general_ci | NULL | | def | miyue | utf8 | utf8_general_ci | NULL | | def | mysql | utf8 | utf8_general_ci | NULL | | def | performance_schema | utf8 | utf8_general_ci | NULL | | def | sys | utf8 | utf8_general_ci | NULL | +--------------+--------------------+----------------------------+------------------------+----------+ 7 rows in set (0.00 sec) mysql> |
操作：“vi xxxx”。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[root@rhel71 alienware]# pwd /mydata/mysql/data/alienware [root@rhel71 alienware]# ls [root@rhel71 alienware]# vi db.opt [root@rhel71 alienware]# cat db.opt #default-character-set=utf8 #default-collation=utf8_general_ci default-character-set=gbk default-collation=gbk_chinese_ci [root@rhel71 alienware]# [root@rhel71 alienware]# ll total 4 -rw-r--r-- 1 root root 123 Dec 6 14:37 db.opt [root@rhel71 alienware]# [root@rhel71 alienware]# chown -R mysql.mysql ../alienware/ [root@rhel71 alienware]# [root@rhel71 alienware]# ll total 4 -rw-r--r-- 1 mysql mysql 123 Dec 6 14:37 db.opt [root@rhel71 alienware]# |
After：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select * from information_schema.schemata; +--------------+--------------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+--------------------+----------------------------+------------------------+----------+ | def | information_schema | utf8 | utf8_general_ci | NULL | | def | adamhuandb | utf8 | utf8_general_ci | NULL | | def | alienware | gbk | gbk_chinese_ci | NULL | | def | miyue | utf8 | utf8_general_ci | NULL | | def | mysql | utf8 | utf8_general_ci | NULL | | def | performance_schema | utf8 | utf8_general_ci | NULL | | def | sys | utf8 | utf8_general_ci | NULL | +--------------+--------------------+----------------------------+------------------------+----------+ 7 rows in set (0.00 sec) mysql> |
————————————————
Done。
