MySQL(构建服务器)_innodb: creating foreign key constraint system tab

本案例要求熟悉MySQL官方安装包的使用，快速构建一台数据库服务器：
安装MySQL-server、MySQl-client软件包
修改数据库用户root的密码
确认MySQL服务程序运行、root可控

步骤一：准备工作

1）卸载系统自带的mysql-server、mysql软件包（如果有的话）

[root@dbsvr1 ~]# yum -y remove mysql-server mysql
Setting up Remove Process
No Match for argument: mysql-server
rhel6dvd                                                 | 3.9 kB     00:00 ...
Package(s) mysql-server available, but not installed.
No Match for argument: mysql
Package(s) mysql available, but not installed.
No Packages marked for removal
1
2
3
4
5
6
7
8

2）清理/etc/my.cnf配置文件

此配置文件由RHEL自带的mysql-libs库提供：
[root@dbsvr1 ~]# rpm -qf /etc/my.cnf
mysql-libs-5.1.66-2.el6_3.x86_64
大量的系统软件包都需要用到mysql-libs库，因此不建议直接卸载此软件包。最好是安装新的MySQL数据库软件时，采用 -U 升级的方式来进行替换。
配置文件/etc/my.cnf若不需要使用，可以直接删除。或者保险起见，也可以将其改名备份：
[root@dbsvr1 ~]# mv /etc/my.cnf /etc/my.cnf.old
1
2
3
4
5
6

步骤二：安装MySQL-server、MySQL-client软件包

1）释放bundle整合包

[root@dbsvr1 ~]# cd /var/ftp/pub/ 
[root@dbsvr1 pub]# tar xvf MySQL-5.6.15-1.el6.x86_64.rpm-bundle.tar
MySQL-shared-5.6.15-1.el6.x86_64.rpm  			//共享库
MySQL-shared-compat-5.6.15-1.el6.x86_64.rpm  	//兼容包
MySQL-server-5.6.15-1.el6.x86_64.rpm  			//服务端程序
MySQL-client-5.6.15-1.el6.x86_64.rpm  			//客户端程序
MySQL-devel-5.6.15-1.el6.x86_64.rpm  			//库和头文件
MySQL-embedded-5.6.15-1.el6.x86_64.rpm  		//嵌入式版本
MySQL-test-5.6.15-1.el6.x86_64.rpm  			//测试包
1
2
3
4
5
6
7
8
9

2）安装MySQL数据库

[root@dbsvr1 pub]# rpm -Uvh MySQL-*.rpm
Preparing...                ############################# [100%]
   1:MySQL-devel            ############################# [ 14%]
   2:MySQL-client           ############################# [ 29%]
   3:MySQL-test             ############################# [ 43%]
   4:MySQL-embedded         ############################# [ 57%]
   5:MySQL-shared-compat    ############################ [ 71%]
   6:MySQL-shared           ############################# [ 86%]
   7:MySQL-server           ############################# [100%]
安装过程中会尝试做一些检测，然后完成基本的初始化任务，期间会给出相关的提示。比如由于MySQL 5.6对TIMESTAMP时间戳的处理不同于之前的版本，会给出警告和提示出解决办法（使用--explicit_defaults_for_timestamp选项）：
2014-11-03 19:02:39 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
MySQL 5.6默认采用的存储引擎不再是MyISAM，而是InnoDB。初始化时若相关的文件不存在，会自动创建并设置相关参数：
2014-11-03 19:02:39 2750 [Note] InnoDB: The InnoDB memory heap is disabled
2014-11-03 19:02:39 2750 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-11-03 19:02:39 2750 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-11-03 19:02:39 2750 [Note] InnoDB: Using Linux native AIO
2014-11-03 19:02:39 2750 [Note] InnoDB: Using CPU crc32 instructions
2014-11-03 19:02:39 2750 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-11-03 19:02:39 2750 [Note] InnoDB: Completed initialization of buffer pool
2014-11-03 19:02:39 2750 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2014-11-03 19:02:39 2750 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2014-11-03 19:02:39 2750 [Note] InnoDB: Database physically writes the file full: wait...
2014-11-03 19:02:40 2750 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2014-11-03 19:02:41 2750 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2014-11-03 19:02:44 2750 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2014-11-03 19:02:44 2750 [Warning] InnoDB: New log files created, LSN=45781
2014-11-03 19:02:44 2750 [Note] InnoDB: Doublewrite buffer not found: creating new
2014-11-03 19:02:44 2750 [Note] InnoDB: Doublewrite buffer created
2014-11-03 19:02:44 2750 [Note] InnoDB: 128 rollback segment(s) are active.
2014-11-03 19:02:44 2750 [Warning] InnoDB: Creating foreign key constraint system tables.
2014-11-03 19:02:44 2750 [Note] InnoDB: Foreign key constraint system tables created
2014-11-03 19:02:44 2750 [Note] InnoDB: Creating tablespace and datafile system tables.
2014-11-03 19:02:44 2750 [Note] InnoDB: Tablespace and datafile system tables created.
2014-11-03 19:02:44 2750 [Note] InnoDB: Waiting for purge to start
2014-11-03 19:02:44 2750 [Note] InnoDB: 5.6.15 started; log sequence number 0
A random root password has been set. You will find it in '/root/.mysql_secret'.
2014-11-03 19:02:45 2750 [Note] Binlog end
2014-11-03 19:02:45 2750 [Note] InnoDB: FTS optimize thread exiting.
2014-11-03 19:02:45 2750 [Note] InnoDB: Starting shutdown...
2014-11-03 19:02:47 2750 [Note] InnoDB: Shutdown completed; log sequence number 1625977
2014-11-03 19:02:47 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-11-03 19:02:47 2774 [Note] InnoDB: The InnoDB memory heap is disabled
2014-11-03 19:02:47 2774 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-11-03 19:02:47 2774 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-11-03 19:02:47 2774 [Note] InnoDB: Using Linux native AIO
2014-11-03 19:02:47 2774 [Note] InnoDB: Using CPU crc32 instructions
2014-11-03 19:02:47 2774 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-11-03 19:02:47 2774 [Note] InnoDB: Completed initialization of buffer pool
2014-11-03 19:02:47 2774 [Note] InnoDB: Highest supported file format is Barracuda.
2014-11-03 19:02:47 2774 [Note] InnoDB: 128 rollback segment(s) are active.
2014-11-03 19:02:47 2774 [Note] InnoDB: Waiting for purge to start
2014-11-03 19:02:47 2774 [Note] InnoDB: 5.6.15 started; log sequence number 1625977
2014-11-03 19:02:47 2774 [Note] Binlog end
2014-11-03 19:02:47 2774 [Note] InnoDB: FTS optimize thread exiting.
2014-11-03 19:02:47 2774 [Note] InnoDB: Starting shutdown...
2014-11-03 19:02:49 2774 [Note] InnoDB: Shutdown completed; log sequence number 1625987
关于MySQL数据库的管理员账号root，其密码也不再是空，而是安装时随机生成一个，这种处理方式一定程度上增强了MySQl服务器的安全性。随机生成的密码字串可以从保存到/root/目录下的.mysql_secret文件中找到：
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

In addition, you can run:

  /usr/bin/mysql_secure_installation

which will also give you the option of removing the test database.
This is strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as /usr/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings
[root@dbsvr1 pub]#
根据上述提示还可以看到，MySQL要求在首次连接时必须通过“SET PASSWORD”指令修改密码（其他方式无效）。
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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

3）确认安装后的服务脚本、服务状态

查看服务脚本：
[root@dbsvr1 pub]# ls -lh /etc/init.d/mysql
-rwxr-xr-x. 1 root root 11K 11月 18 02:28 /etc/init.d/mysql
查看服务状态（默认未运行）：
[root@dbsvr1 pub]# service mysql status
MySQL is not running                                       [失败]
	mysql服务的自启状态为on：
[root@dbsvr1 ~]# chkconfig --list mysql
mysql           0:off   1:off   2:on    3:on    4:on    5:on    6:off
1
2
3
4
5
6
7
8
9

步骤三：启动MySQL服务器程序，确认状态

1）启动MySQL服务程序

	启动服务并查看状态：
[root@dbsvr1 pub]# service mysql start
Starting MySQL... 									[确定]
[root@dbsvr1 pub]# service mysql status
MySQL running (31724)									[确定]
服务器进程为mysqld，监听的默认端口为TCP 3306：
[root@dbsvr1 pub]# netstat -anpt | grep mysql
tcp        0      0 :::3306        :::*       LISTEN      31724/mysqld
1
2
3
4
5
6
7
8

2）查看MySQL服务器进程、运行用户

	提供连接服务的进程为mysqld，由其父进程mysqld_safe启动。
[root@dbsvr1 pub]# ps -elf | grep mysqld
4 S root     31619     1  0  80   0 -  2834 wait   15:14 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/dbsvr1.tarena.com.pid
4 S mysql    31724 31619  0  80   0 - 252496 poll_s 15:14 pts/0   00:00:01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/dbsvr1.tarena.com.err --pid-file=/var/lib/mysql/dbsvr1.tarena.com.pid
数据库的默认存放位置为 /var/lib/mysql：
[root@dbsvr1 pub]# ls /var/lib/mysql/
auto.cnf               ibdata1      mysql               RPM_UPGRADE_HISTORY
dbsvr1.tarena.com.err  ib_logfile0  mysql.sock          RPM_UPGRADE_MARKER-LAST
dbsvr1.tarena.com.pid  ib_logfile1  performance_schema  test
1
2
3
4
5
6
7
8
9

步骤四：启动MySQL服务器程序，确认状态

1）查看随机生成的root管理密码

[root@dbsvr1 ~]# cat /root/.mysql_secret
# The random password set for the root user at Tue Jan  7 14:52:44 2014 (local time): 6ptWNWwx  								//密码为6ptWNWwx
1
2

2）使用客户端命令mysql连接到MySQL服务器

提示验证时，填入前一步获得的随机密码，验证成功后即可进入“mysql> ”环境：
[root@dbsvr1 ~]# mysql -u root -p
Enter password:  						//首次连接时，输入安装时生成的随机密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.15

Copyright (c) 2000, 2013, 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> 									//登录成功后，进入SQL操作环境
3）执行SET PASSWORD命令修改密码
可参考下列指令：
mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('1234567');
Query OK, 0 rows affected (0.13 sec)
上述操作的结果是——更改数据库用户root从本机访问时的密码，设为1234567。
退出“mysql> ”环境，重新登录验证，必须采用新的密码才能登入：
mysql> exit  								//退出 mysql> 环境
Bye
[root@dbsvr1 ~]# mysql -u root –p			//重新登录
Enter password:  							//输入新设置的密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> 
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
29
30
31
32
33
34
35
36
37
38
39