MySQL的安装、配置文件和初始化

[root@localhost objs]# groupadd mysql
[root@localhost objs]# useradd -g mysql mysql

[root@localhost objs]# pwd
/home/motadou/objs
[root@localhost objs]# tar -zxvf mysql-5.1.73.tar.gz 

STEP03 按使用惯例，MySQL的库以及可执行程序，我们一般都会安装在/usr/local/mysql目录下。但为了版本升级和替换的便利，一般我都会将每个版本的文件安装在不同的目录。比如MySQL5.1.73，安装在目录/usr/local/myql-5.1.73，然后做一个软连接/usr/local/mysql指向真正的版本路径。后续目录的指定方法都遵循上述原则。

[root@localhost mysql-5.1.73]# pwd
/home/motadou/objs/mysql-5.1.73
[root@localhost mysql-5.1.73]# ./configure
    --prefix=/usr/local/mysql-5.1.73
    --datadir=/usr/local/mysql-5.1.73/data
    --without-debug
    --enable-thread-safe-client
    --with-charset=utf8
    --with-extra-charsets=latin1,gbk
    --with-mysqld-user=mysql
    --with-unix-socket-path=/usr/local/mysql-5.1.73/sock/mysql.sock 
    --with-plugins=innobase,partition

[root@localhost mysql-5.1.73]# pwd
/home/motadou/objs/mysql-5.1.73
[root@localhost mysql-5.1.73]# make

......
make[1]: Entering directory `/home/motadou/objs/mysql-5.1.73/win'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/home/motadou/objs/mysql-5.1.73/win'
[root@localhost mysql-5.1.73]# 

[root@localhost mysql-5.1.73]# pwd
/home/motadou/objs/mysql-5.1.73
[root@localhost mysql-5.1.73]# make install
......
make[1]: Entering directory `/home/motadou/objs/mysql-5.1.73/win'
make[2]: Entering directory `/home/motadou/objs/mysql-5.1.73/win'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/home/motadou/objs/mysql-5.1.73/win'
make[1]: Leaving directory `/home/motadou/objs/mysql-5.1.73/win'

STEP05 前面的步骤我们都是使用root或其他用户执行的命令，下面我们把MySQL的路径权限分配给mysql.mysql：

[root@localhost local]# pwd
/usr/local
[root@localhost local]# chown -R mysql:mysql /usr/local/mysql-5.1.73/

执行命令之后，我们再看下/usr/local/mysql-5.1.73的目录结构：

[root@localhost local]# ln -s /usr/local/mysql-5.1.73/ /usr/local/mysql 
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql

执行命令之后，我们有如下的目录结构：

MySQL的配置文件

进行完上述步骤之后，MySQL已然安装成功。下面就可以初始化和启动了。但在初始化之前，我们首先要了解下my.cnf这个配置文件，因为它与后续的初始化和运行有着莫大的关系。当mysqld服务启动时，系统默认按一定的顺序读取配置文件。该搜索顺序，我们可以使用如下的命令得到。

[root@localhost libexec]# ./mysqld --verbose --help | more
./mysqld  Ver 5.1.73 for unknown-linux-gnu on x86_64 (Source distribution)
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.

Starts the MySQL database server.

Usage: ./mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql-5.1.73/etc/my.cnf ~/.my.cnf 

红色文字就是搜索顺序，系统默认是按/etc/my.cnf，/etc/mysql/my.cnf，/usr/local/mysql-5.1.73/etc/my.cnf，~/.my.cnf的顺序读取配置文件（其中/usr/local/mysql-5.1.73为安装目录），当有多个配置文件时，mysql会以读取到的第一个配置文件中的参数为准。

MySQL的初始化

MySQL安装后，还不能直接启动服务，必须先对数据库进行初始化。初始化的工作主要包括：
** 初始化日志、表空间等数据库必须的文件;
** 创建并初始化系统数据库。

[root@localhost mysql-5.1.73]# bin/mysql_install_db --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/local/mysql-5.1.73/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql-5.1.73/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
/usr/local/mysql-5.1.73/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr/local/mysql-5.1.73 ; /usr/local/mysql-5.1.73/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysql-5.1.73/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/local/mysql-5.1.73/bin/mysqlbug script!

上述的打印信息给了我们一个重要的提醒，就是不要忘记给root设置一个密码，防止恶意用户获得数据库最高权限，或者被其他合法用户误使用root权限而造成事故。

MySQL的管理：启动和停止

初始化数据库之后，我们就可以启动mysqld守护进程，开始访问数据库了。MySQL的启停大致有如下这么几种方法，在实际环境中，我们根据使用场景来选取最合适的方法：

$mysql_dir/bin/mysqladmin -u root -p shutdown
$mysql_dir/bin/mysqld_safe &

[root@localhost mysql-5.1.73]# bin/mysqld_safe --user=mysql &
[1] 15275
[root@localhost mysql-5.1.73]# 150513 00:22:09 mysqld_safe Logging to '/var/log/mysqld.log'.
150513 00:22:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150513 00:22:10 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
[root@localhost lib]# ps aux | grep mysql
root     16035  0.0  0.1 106100  1356 pts/0    S+   00:36   0:00 /bin/sh bin/mysqld_safe
mysql    16116  0.0  2.3 351888 24288 pts/0    Sl+  00:36   0:00 /usr/local/mysql-5.1.73/libexec/mysqld --basedir=/usr/local/mysql-5.1.73 --datadir=/usr/local/mysql-5.1.73/data --user=mysql --log-error=/usr/local/mysql-5.1.73/mysqld.log --pid-file=/usr/local/mysql-5.1.73/mysqld.pid --socket=/usr/local/mysql-5.1.73/mysql.sock
root     16185  0.0  0.0 103252   820 pts/2    S+   00:41   0:00 grep mysql

MySQL的测试程序

#include <string>
#include <iostream>
#include "mysql.h"

using namespace std;

const string DB_CONFIG_HOST   = "127.0.0.1";
const string DB_CONFIG_USER   = "easenote";
const string DB_CONFIG_PASSWD = "easenote@123456";
const string DB_CONFIG_DBNAME = "mysql";
const int    DB_CONFIG_PORT   = 3306;

int main() {
	//STEP01 连接数据库
	MYSQL * mHandle = mysql_init(NULL);

	if (mysql_real_connect(mHandle, 
		DB_CONFIG_HOST.c_str(), 
		DB_CONFIG_USER.c_str(), 
		DB_CONFIG_PASSWD.c_str(), 
		DB_CONFIG_DBNAME.c_str(), 
		DB_CONFIG_PORT, NULL, 0) == NULL) 
    {
		cout << "mysql_real_connect fault: " << mysql_errno(mHandle) << "," << string(mysql_error(mHandle)) << endl;
		return -1;
    }

	//STEP02 从mysql.test读取数据
	string sCommand = "SELECT Host,User FROM mysql.user LIMIT 1";
	if (mysql_real_query(mHandle, sCommand.c_str(), sCommand.length()) != 0)
    {
        cout << "mysql_real_query fault: " << mysql_errno(mHandle) << "," << string(mysql_error(mHandle)) << endl;
		return -1;
    }
	
	MYSQL_RES *pstRes = mysql_store_result(mHandle);
    if (pstRes == NULL)
    {
        cout << "mysql_store_result fault: " << mysql_errno(mHandle) << "," << string(mysql_error(mHandle)) << endl;
		return -1;
    }

	//STEP03 从结果集中读出数据
	MYSQL_ROW stRow;
    while((stRow = mysql_fetch_row(pstRes)) != (MYSQL_ROW)NULL)
    {
		unsigned long * arrLens = mysql_fetch_lengths(pstRes);

		cout << "HOST:" << string(stRow[0], arrLens[0]) << endl;
		cout << "USER:" << string(stRow[1], arrLens[1]) << endl;
    }

    mysql_free_result(pstRes);

	mysql_close(mHandle);
	return 0;
}

现在我们再写一个Makefile文件来编译上述代码：

MYSQL_INC := /usr/local/mysql/include/mysql
MYSQL_LIB := /usr/local/mysql/lib/mysql

all:main.cpp
	g++ -o main main.cpp -I${MYSQL_INC} -L${MYSQL_LIB} -lmysqlclient

clean:
	-rm -f main

MySQL的管理：启动多个实例MySQL的管理：授权MySQL开发环境测试