构建MySQL服务器以及基本应用

1.构建MySQL服务器
问题
本案例要求熟悉MySQL官方安装包的使用，快速构建一台数据库服务器：
安装MySQL-server、MySQl-client软件包
修改数据库用户root的密码
确认MySQL服务程序运行、root可控
方案
本课程将使用64位的RHEL 6操作系统，MySQL数据库的版本是5.6.15。
访问http://dev.mysql.com/downloads/mysql/，找到MySQL Community Server下载页面，平台选择“Red Hat Enterprise Linux 6 / Oracle Linux 6”，然后选择64位的bundle整合包下载，如图-1所示。

图-1
注意：下载MySQL软件时需要以Oracle网站账户登录，如果没有请根据页面提示先注册一个（免费） 。
步骤
实现此案例需要按照如下步骤进行。
步骤一：准备工作
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
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
步骤二：安装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 //测试包
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 se