【MySQL学习笔记】CentOS 6.6上源码编译方式安装MySQL 5.6

一、安装源码编译配置工具：cmake

先下载cmake工具的源代码：

[root@ggg2 Desktop]# wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz
--2016-08-21 22:09:21--  http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz
Resolving www.cmake.org... 66.194.253.19
Connecting to www.cmake.org|66.194.253.19|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: http://cmake.org/files/v2.8/cmake-2.8.4.tar.gz [following]
--2016-08-21 22:09:22--  http://cmake.org/files/v2.8/cmake-2.8.4.tar.gz
Resolving cmake.org... 66.194.253.19
Connecting to cmake.org|66.194.253.19|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://cmake.org/files/v2.8/cmake-2.8.4.tar.gz [following]
--2016-08-21 22:09:23--  https://cmake.org/files/v2.8/cmake-2.8.4.tar.gz
Connecting to cmake.org|66.194.253.19|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5477628 (5.2M) [application/x-gzip]
Saving to: “cmake-2.8.4.tar.gz”

100%[=========================================================>] 5,477,628    101K/s   in 63s     

2016-08-21 22:10:27 (84.8 KB/s) - “cmake-2.8.4.tar.gz” saved [5477628/5477628]

然后切换目录，进行 配置，需要注意的是 ./configure 是源码安装的很重要的一步，对要安装的软件进行配置，检查当前的环境是否满足要安装软件的依赖关系：

[root@ggg2 Desktop]# cd cmake-2.8.4
[root@ggg2 cmake-2.8.4]# ./configure
---------------------------------------------
CMake 2.8.4, Copyright 2000-2009 Kitware, Inc.
---------------------------------------------
Error when bootstrapping CMake:
Cannot find appropriate C compiler on this system.
Please specify one using environment variable CC.
See cmake_bootstrap.log for compilers attempted.

---------------------------------------------
Log of errors: /root/Desktop/cmake-2.8.4/Bootstrap.cmk/cmake_bootstrap.log
---------------------------------------------

---------------------------------------------
CMake 2.8.4, Copyright 2000-2009 Kitware, Inc.
C compiler on this system is: cc 
---------------------------------------------
Error when bootstrapping CMake:
Cannot find appropriate C++ compiler on this system.
Please specify one using environment variable CXX.
See cmake_bootstrap.log for compilers attempted.
---------------------------------------------
Log of errors: /root/Desktop/cmake-2.8.4/Bootstrap.cmk/cmake_bootstrap.log
---------------------------------------------

从上面的输出，发现是报错了，仔细查看报错信息，发现是 没有找到合适的c、c++编译器，所以接下来要安装编译器：

[root@ggg2 cmake-2.8.4]# yum -y install gcc-c++
Loaded plugins: fastestmirror, refresh-packagekit, security
Setting up Install Process
Loading mirror speeds from cached hostfile
 * base: centos.ustc.edu.cn
 * extras: ftp.sjtu.edu.cn
 * updates: centos.ustc.edu.cn
Resolving Dependencies
--> Running transaction check
---> Package gcc-c++.x86_64 0:4.4.7-17.el6 will be installed
--> Processing Dependency: libstdc++-devel = 4.4.7-17.el6 for package: gcc-c++-4.4.7-17.el6.x86_64
--> Processing Dependency: libstdc++ = 4.4.7-17.el6 for package: gcc-c++-4.4.7-17.el6.x86_64
--> Running transaction check
---> Package libstdc++.x86_64 0:4.4.7-11.el6 will be updated
---> Package libstdc++.x86_64 0:4.4.7-17.el6 will be an update
---> Package libstdc++-devel.x86_64 0:4.4.7-17.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===================================================================================================
 Package                     Arch               Version                     Repository        Size
===================================================================================================
Installing:
 gcc-c++                     x86_64             4.4.7-17.el6                base             4.7 M
Installing for dependencies:
 libstdc++-devel             x86_64             4.4.7-17.el6                base             1.6 M
Updating for dependencies:
 libstdc++                   x86_64             4.4.7-17.el6                base             295 k

Transaction Summary
===================================================================================================
Install       2 Package(s)
Upgrade       1 Package(s)

Total download size: 6.6 M
Downloading Packages:
(1/3): gcc-c++-4.4.7-17.el6.x86_64.rpm                                      | 4.7 MB     00:21     
(2/3): libstdc++-4.4.7-17.el6.x86_64.rpm                                    | 295 kB     00:00     
(3/3): libstdc++-devel-4.4.7-17.el6.x86_64.rpm                              | 1.6 MB     00:11     
---------------------------------------------------------------------------------------------------
Total                                                              195 kB/s | 6.6 MB     00:34     
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Updating   : libstdc++-4.4.7-17.el6.x86_64                                                   1/4 
  Installing : libstdc++-devel-4.4.7-17.el6.x86_64                                             2/4 
  Installing : gcc-c++-4.4.7-17.el6.x86_64                                                     3/4 
  Cleanup    : libstdc++-4.4.7-11.el6.x86_64                                                   4/4 
  Verifying  : gcc-c++-4.4.7-17.el6.x86_64                                                     1/4 
  Verifying  : libstdc++-4.4.7-17.el6.x86_64                                                   2/4 
  Verifying  : libstdc++-devel-4.4.7-17.el6.x86_64                                             3/4 
  Verifying  : libstdc++-4.4.7-11.el6.x86_64                                                   4/4 

Installed:
  gcc-c++.x86_64 0:4.4.7-17.el6                                                                    

Dependency Installed:
  libstdc++-devel.x86_64 0:4.4.7-17.el6                                                            

Dependency Updated:
  libstdc++.x86_64 0:4.4.7-17.el6                                                                  

Complete!

安装完成后，可以查看一下c编译器的版本：

[root@ggg2 ~]# gcc --version
gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17)
Copyright (C) 2010 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

然后，再次尝试配置，且进行编译、安装：

[root@ggg2 cmake-2.8.4]# ./configure
此处省略1w行

[root@ggg2 cmake-2.8.4]# gmake && make install
此处省略1k行

至此，cmake工具安装完成， 不容易啊

二、下载MySQL源代码，解压

[root@ggg2 Desktop]# wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-5.6.12.tar.gz
--2016-08-21 23:01:28--  http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-5.6.12.tar.gz
Resolving dev.mysql.com... 137.254.60.11
Connecting to dev.mysql.com|137.254.60.11|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://cdn.mysql.com//archives/mysql-5.6/mysql-5.6.12.tar.gz [following]
--2016-08-21 23:01:29--  http://cdn.mysql.com//archives/mysql-5.6/mysql-5.6.12.tar.gz
Resolving cdn.mysql.com... 23.77.240.248
Connecting to cdn.mysql.com|23.77.240.248|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 35874671 (34M) [application/x-tar-gz]
Saving to: “MySQL-5.6.12.tar.gz”

100%[=========================================================>] 35,874,671   504K/s   in 72s     

2016-08-21 23:02:41 (487 KB/s) - “MySQL-5.6.12.tar.gz” saved [35874671/35874671]

[root@ggg2 Desktop]# tar -zxvf MySQL-5.6.12.tar.gz 

[root@ggg2 Desktop]# cd MySQL-5.6.12

三：用cmake工具配置MySQL源代码

创建mysql的安装目录，在cmake时指定参数 -DCMAKE_INSTALL_PREFIX ，就是指定mysql程序要安装的目录：

[root@ggg2 Desktop]# mkdir -p /usr/local/mysql

不过运行中报错了：

[root@ggg2 mysql-5.6.12]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BALACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DCOMPLILATION_COMMENT='ggg for mysql' \
-DWITH_READLINE=0N \
-DSYSCONFDIR=/data/mysqldata/3306 \
-DMYSQL_UNIX_ADDR=/data/mysqldata/3306/mysql.sock


-- Could NOT find Curses (missing:  CURSES_LIBRARY CURSES_INCLUDE_PATH) 
CMake Error at cmake/readline.cmake:85 (MESSAGE):
  Curses library not found.  Please install appropriate package,

      remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
  cmake/readline.cmake:128 (FIND_CURSES)
  cmake/readline.cmake:202 (MYSQL_USE_BUNDLED_EDITLINE)
  CMakeLists.txt:325 (MYSQL_CHECK_EDITLINE)


-- Configuring incomplete, errors occurred!

从报错信息中可以看出，要删除 CMakeCache.txt文件，然后，对于rehat以及派生的版本，要安装 ncurses-devel：

[root@wc1 mysql-5.6.12]# rm CMakeCache.txt
rm: remove regular file `CMakeCache.txt'? y

[root@ggg2 Desktop]# yum -y install ncurses-devel

再次运行cmake：

[root@ggg2 mysql-5.6.12]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BALACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DCOMPLILATION_COMMENT='ggg for mysql' \
-DWITH_READLINE=0N \
-DSYSCONFDIR=/data/mysqldata/3306 \
-DMYSQL_UNIX_ADDR=/data/mysqldata/3306/mysql.sock
.....................
.....................
-- Check size of wchar_t
-- Check size of wchar_t - done
-- Check size of wctype_t
-- Check size of wctype_t - done
-- Check size of wint_t
-- Check size of wint_t - done
-- Found Curses: /usr/lib64/libcurses.so 
-- Looking for tputs in /usr/lib64/libcurses.so
-- Looking for tputs in /usr/lib64/libcurses.so - found
-- Performing Test HAVE_DECL_TGOTO
-- Performing Test HAVE_DECL_TGOTO - Success
-- Looking for strvis
-- Looking for strvis - not found
-- Looking for strunvis
-- Looking for strunvis - not found
-- Looking for include files HAVE_LIBAIO_H
-- Looking for include files HAVE_LIBAIO_H - not found.
-- Looking for io_queue_init in aio
-- Looking for io_queue_init in aio - not found
-- Looking for sched_getcpu
-- Looking for sched_getcpu - found
-- Performing Test HAVE_IB_GCC_ATOMIC_BUILTINS
-- Performing Test HAVE_IB_GCC_ATOMIC_BUILTINS - Success
-- Performing Test HAVE_IB_GCC_ATOMIC_BUILTINS_64
-- Performing Test HAVE_IB_GCC_ATOMIC_BUILTINS_64 - Success
-- Performing Test HAVE_IB_ATOMIC_PTHREAD_T_GCC
-- Performing Test HAVE_IB_ATOMIC_PTHREAD_T_GCC - Success
-- Looking for asprintf
-- Looking for asprintf - found
-- Check size of pthread_t
-- Check size of pthread_t - done
-- Using cmake version 2.8.4
-- Not building NDB
-- Performing Test HAVE_PEERCRED
-- Performing Test HAVE_PEERCRED - Success
-- Library mysqlclient depends on OSLIBS -lpthread;m;rt;dl
-- Googlemock was not found. gtest-based unit tests will be disabled. You can run cmake . -DENABLE_DOWNLOADS=1 to automatically download and build required components from source.
-- If you are inside a firewall, you may need to use an http proxy: export http_proxy=http://foo.bar.com:80
Warning: Bison executable not found in PATH
-- Library mysqlserver depends on OSLIBS -lpthread;m;rt;crypt;dl
-- Configuring done
-- Generating done
CMake Warning: The variable, 'COMPLILATION_COMMENT', specified manually, was not used during the generation.
CMake Warning: The variable, 'WITH_BALACKHOLE_STORAGE_ENGINE', specified manually, was not used during the generation.
CMake Warning: The variable, 'WITH_READLINE', specified manually, was not used during the generation.
-- Build files have been written to: /root/Desktop/mysql-5.6.12

四：编译安装

整个过程大概要20分钟左右：

[root@ggg2 mysql-5.6.12]# make && make install

至此，安装完成 ， 不过接下来，还要对MySQL进行配置，才能让MySQL跑起来

五：创建数据库服务

增加mysql用户、用户组。

先创建mysql用户组，然后useradd 命令是指新创建用户mysql，-g指定要把这个新用户加入到mysql组

[root@ggg2 mysql]# groupadd mysql

[root@ggg2 mysql]# useradd -g mysql mysql

修改/usr/local/mysql目录的owner为 mysql用户：

[root@ggg2 Desktop]# cd /usr/local/mysql
[root@ggg2 mysql]# ls
bin      data  include         lib  mysql-test  scripts  sql-bench
COPYING  docs  INSTALL-BINARY  man  README      share    
support-files

[root@ggg2 mysqldata]# chown -R mysql:mysql /usr/local/mysql

修改/home/mysql/.bash_profile文件，在最后增加：

export LANG=zh_CN.UTF-8
export PATH=/usr/local/mysql/bin:$PATH

之所以要增加这2行，是以后当mysql用户登陆后，就可以直接执行mysql命令行，不用在切换目录。

[root@ggg2 mysqldata]# cd /home/mysql
[root@ggg2 mysql]# ls -al
total 28
drwx------. 4 mysql mysql 4096 Aug 21 22:47 .
drwxr-xr-x. 4 root  root  4096 Aug 21 22:47 ..
-rw-r--r--. 1 mysql mysql   18 Oct 16  2014 .bash_logout
-rw-r--r--. 1 mysql mysql  176 Oct 16  2014 .bash_profile
-rw-r--r--. 1 mysql mysql  124 Oct 16  2014 .bashrc
drwxr-xr-x. 2 mysql mysql 4096 Nov 12  2010 .gnome2
drwxr-xr-x. 4 mysql mysql 4096 Aug 18 23:13 .mozilla

[root@ggg2 mysql]# vi /home/mysql/.bash_logout
[root@ggg2 mysql]# cat /home/mysql/.bash_logout
# ~/.bash_logout


[root@ggg2 mysql]# vi /home/mysql/.bash_profile
[root@ggg2 mysql]# cat /home/mysql/.bash_profile
# .bash_profile


# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi


# User specific environment and startup programs


PATH=$PATH:$HOME/bin


export PATH


export LANG=zh_CN.UTF-8
export PATH=/usr/local/mysql/bin:$PATH

创建数据文件的存储目录：

[root@ggg2 Desktop]# mkdir -p /data/mysqldata/{3306/{data,tmp,binlog},backup,scripts}
[root@ggg2 Desktop]# ls -al /data/mysqldata
total 20
drwxr-xr-x. 5 root root 4096 Aug 24 22:24 .
drwxr-xr-x. 3 root root 4096 Aug 22 22:39 ..
drwxr-xr-x. 5 root root 4096 Aug 24 22:24 3306
drwxr-xr-x. 2 root root 4096 Aug 24 22:24 backup
drwxr-xr-x. 2 root root 4096 Aug 24 22:24 scripts
[root@ggg2 Desktop]# ls -al /data/mysqldata/3306
total 20
drwxr-xr-x. 5 root root 4096 Aug 24 22:24 .
drwxr-xr-x. 5 root root 4096 Aug 24 22:24 ..
drwxr-xr-x. 2 root root 4096 Aug 24 22:24 binlog
drwxr-xr-x. 2 root root 4096 Aug 24 22:24 data
drwxr-xr-x. 2 root root 4096 Aug 24 22:24 tmp
[root@ggg2 Desktop]# 

改变数据目录的owner，切换到msyql用户：

[root@ggg2 Desktop]# chown -R mysql:mysql /data/mysqldata
[root@ggg2 Desktop]# su - mysql
[mysql@ggg2 ~]$ cd /usr/local/mysql
[mysql@ggg2 mysql]$ 

创建.cnf 配置文件，实际上在/user/local/mysql/support-files 目录下个my-default.cnf文件可以参考：

[mysql@ggg2 mysql]$ ll support-files
总用量 36
-rwxr-xr-x. 1 mysql mysql  1153 8月  22 23:18 binary-configure
-rw-r--r--. 1 mysql mysql   773 7月   3 2013 magic
-rw-r--r--. 1 mysql mysql  1126 8月  22 23:18 my-default.cnf
-rwxr-xr-x. 1 mysql mysql  1061 8月  22 23:18 mysqld_multi.server
-rwxr-xr-x. 1 mysql mysql   894 8月  22 23:18 mysql-log-rotate
-rwxr-xr-x. 1 mysql mysql 10880 8月  22 23:18 mysql.server
drwxr-xr-x. 2 mysql mysql  4096 8月  22 23:35 solaris
[mysql@ggg2 mysql]$ 

创建my.cnf文件，接下来可以按需写上各个配置参数：

[root@ggg2 Desktop]# touch /data/mysqldata/3306/my.cnf

[client]
port = 3306
socket = /data/mysqldata/3306/mysql.sock

#the mysql server
[mysqld]
port = 3306
user = mysql
socket =/data/mysqldata/3306/mysql.sock
pid-file = /data/mysqldata/3306/mysql.pid

basedir = /usr/local/mysql
datadir = /data/mysqldata/3306/data
tmpdir  = /data/mysqldata/3306/tmp
open_files_limit = 10240
explicit_defaults_for_timestamp
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#buffer
max_allowed_packet = 256M
max_heap_table_size = 16M
net_buffer_length = 8K
sort_buffer_size = 1M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 8M

#log
log-bin = /data/mysqldata/3306/binlog/mysql-bin
binlog_cache_size = 32M
max_binlog_cache_size = 512M
max_binlog_size = 512M
binlog_format = mixed
log_output = FILE
log_error = ../mysql-error.log
slow_query_log = 1
slow_query_log_file = ../slow_query.log
expire-logs_days = 14

#innodb
innodb_data_file_path = ibdata1:2018M:autoextend
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_buffer_pool_size = 300M

[mysql]
no-auto_rehash
prompt     =(\u@\h) [\d]>\_
default-character-set = gbk

先切换到/usr/local/mysql/scripts 目录下，然后初始化MySQL数据库，在输出中包含了很多有用的信息，比如 通过mysqld_safe来启动服务 ，下面就会用到：

[mysql@ggg2 scripts]$ ./mysql_install_db --datadir=/data/mysqldata/3306/data --basedir=/usr/local/mysql
WARNING: The host 'ggg2.localdomain' could not be looked up with /usr/local/mysql/bin/resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !

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/bin/mysqladmin -u root password 'new-password'
  /usr/local/mysql/bin/mysqladmin -u root -h ggg2.localdomain password 'new-password'

Alternatively you can run:

  /usr/local/mysql/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/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./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/local/mysql/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server

[mysql@ggg2 scripts]$ 

六：启动数据库服务

首先，用su命令切换到mysql用户下，然后用mysqld_safe 脚本，指定 配置文件，就可以启动mysql：

[root@ggg2 Desktop]# su - mysql
[mysql@ggg2 ~]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf &
[1] 3144
[mysql@ggg2 ~]$ 160825 22:17:11 mysqld_safe Logging to '/data/mysqldata/3306/data/../mysql-error.log'.
160825 22:17:11 mysqld_safe Starting mysqld daemon with databases from /data/mysqldata/3306/data

检查3306端口、mysqld进程是否正常：

[root@ggg2 Desktop]# netstat -lnt | grep 3306
tcp        0      0 :::3306                     :::*                        LISTEN      
[root@ggg2 Desktop]# ps -ef | grep bin/mysql | grep -v grep
mysql     3144  3115  0 22:17 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf
mysql     3612  3144  3 22:17 pts/0    00:00:03 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysqldata/3306/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysqldata/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/mysqldata/3306/data/../mysql-error.log --open-files-limit=10240 --pid-file=/data/mysqldata/3306/mysql.pid --socket=/data/mysqldata/3306/mysql.sock --port=3306
[root@ggg2 Desktop]# 

七：配置MySQL数据库

用mysql客户端命令，登陆mysql 服务器，然后执行sql，可以看到当前系统有6个账号：

[root@ggg2 Desktop]# mysql
bash: mysql: command not found
[root@ggg2 Desktop]# su - mysql
[mysql@ggg2 ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.12-log 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@localhost)[(none)]> select user,host from mysql.user;
+------+------------------+
| user | host             |
+------+------------------+
| root | 127.0.0.1        |
| root | ::1              |
|      | ggg2.localdomain |
| root | ggg2.localdomain |
|      | localhost        |
| root | localhost        |
+------+------------------+
6 rows in set (0.03 sec)

(root@localhost)[(none)]> 

删除不需要的账号，就一个delete语句看似很简单，但是很容易产生重大的问题。我在安装的时候执行sql写出了 where user <>'' ，导致再次登录系统显示没有操作权限。。。后来把/data/mysqldata目录下的文件都删除，重新安装数据库才恢复

(root@localhost)[(none)]> delete from mysql.user where user ='';
Query OK, 2 rows affected (0.02 sec)

(root@localhost)[(none)]> select user,host from mysql.user;
+------+------------------+
| user | host             |
+------+------------------+
| root | 127.0.0.1        |
| root | ::1              |
| root | ggg2.localdomain |
| root | localhost        |
+------+------------------+
4 rows in set (0.00 sec)

修改账号名称为 system，这主要是为了安全：

(root@localhost)[(none)]> update mysql.user set user='system',password=password('yupeigu');
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

清除权限信息：

(root@localhost)[(none)]> truncate table mysql.db;
Query OK, 0 rows affected (0.01 sec)

刷新权限，然后退出：

(root@localhost)[(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

(root@localhost)[(none)]> exit
Bye

再次登陆，发现直接登陆不了了，必须要输入用户名才可以（这里也输入了密码）：

[mysql@ggg2 ~]$ mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[mysql@ggg2 ~]$ mysql -usystem -pyupeigu
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 6
Server version: 5.6.12-log 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(system@localhost)[(none)]> 

如果不输入密码，必须要指定-p参数，否则也会报错：

[mysql@ggg2 ~]$ mysql -usystem 
ERROR 1045 (28000): Access denied for user 'system'@'localhost' (using password: NO)

[mysql@ggg2 ~]$ mysql -usystem -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.12-log 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(system@localhost)[(none)]> 

八：MySQL服务管理配置

创建中间定义文件，提高代码可用性：

[mysql@ggg2 ~]$ cat /data/mysqldata/scripts/mysql_env.ini

#set env
MYSQL_USER=system
MYSQL_PASS='yupeigu'

#check parameter
if [ $# -ne 1 ]
then
    HOST_PORT=3306
else
    HOST_PORT=$1
fi

修改权限，只有所有者才能读取、修改文件：

[mysql@ggg2 ~]$ chmod 600 /data/mysqldata/scripts/mysql_env.ini

切换到/data/mysqldata/scripts目录下，创建3个文件，以方便启动、关闭、登陆mysql，下面通过cat命令显示创建的文件内容。

mysql_db_startup.sh：启动mysql服务，实际上就是调用的mysqld_safe脚本。

[mysql@ggg2 scripts]$ cat mysql_db_startup.sh
#!/bin/sh


source /data/mysqldata/scripts/mysql_env.ini


echo "Startup MySQL Service: localhost_"${HOST_PORT}


/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/${HOST_PORT}/my.cnf &

mysql_db_shutdown.sh：调用了mysqladmin命令，且指定了 shutdown关闭mysql。

[mysql@ggg2 scripts]$ cat mysql_db_shutdown.sh

#!/bin/sh


source /data/mysqldata/scripts/mysql_env.ini


echo "Shutdown  MySQL Service: localhost_"${HOST_PORT}


/usr/local/mysql/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${HOST_PORT}/mysql.sock shutdown

mysqlplus.sh：登陆mysql，是调用了mysql的客户端命令 “mysql”，指定用户名、密码、sock文件。

[mysql@ggg2 scripts]$ cat mysqlplus.sh


#!/bin/sh


source /data/mysqldata/scripts/mysql_env.ini


echo "Login  MySQL Service: localhost_"${HOST_PORT}


/usr/local/mysql/bin/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${HOST_PORT}/mysql.sock $2

给这些脚本加上可执行的权限，接下来通过echo命令，把文本追加到 .bash_profile 文件中，这个脚本是是在mysql用户的目录下面，当在linux上用mysql用户登陆，就会自动运行这个脚本，来初始化（个性化）PATH、LANG等环境变量：

[mysql@ggg2 scripts]$ chmod +x /data/mysqldata/scripts/mysql*.sh
[mysql@ggg2 scripts]$ echo "export PATH=/data/mysqldata/scripts:\$PATH" >> /home/mysql/.bash_profile

运行 .bash_profile 脚本，让刚添加上去的路径生效，这里的~就是指mysql用户的家目录，也就是/home/mysql 目录：

[mysql@ggg2 scripts]$ source ~/.bash_profile

虽然，通过上面的脚本，可以启动mysql，不过作为生产环境，必须要让mysql自启动，所以接下来要配置mysql开机自启动，非常简单。

在这之前，必须通过 visudo命令（会自动修改/etc/sudoer文件，在保存时会自动检查格式是否正确），把 Defaults requiretty注释掉，也就是改成：#Defaults requiretty，之所以要这么处理的原因是因为在一般情况下，sudo命令需要在一个tty终端上执行，但是开机自启动脚本在执行时，并没有终端，所以就会导致这个命令不会被执行。

[root@ggg2 Desktop]# echo "">>  /etc/rc.local
[root@ggg2 Desktop]# echo "#autostart mysql">> vi /etc/rc.local
[root@ggg2 Desktop]# echo "sudo -i -u mysql /data/mysqldata/scripts/mysql_db_startup.sh 3306 > /home/mysql/mysql_db_startup.log 2>&1" >> /etc/rc.local

命令解释：

1、可以通过vi命令编辑/etc/rc.local 文件，追加2行，也可以直接把echo的输出重定向追加到 /etc/rc.local文件中。

2、sudo命令是指模拟mysql用户，来执行启动脚本。

3、3306是mysql_db_startup.sh脚本的第1个参数

4、脚本的运行结果，重定向到文件 /home/mysql/mysql_db_startup.log 中

5、2 > &1 ，就是把标准错误输出 重定向到 标准输出，也就是如果在运行脚本时报错了，报错信息也会输出到 /home/mysql/mysql_db_startup.log 文件。

最后，系统重启之后，再验证一下MySQL运行是否正常：

[root@ggg2 Desktop]# ps -ef | grep mysql
mysql     2399     1  0 15:43 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf
mysql     2902  2399  8 15:43 ?        00:00:03 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysqldata/3306/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysqldata/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/mysqldata/3306/data/../mysql-error.log --open-files-limit=10240 --pid-file=/data/mysqldata/3306/mysql.pid --socket=/data/mysqldata/3306/mysql.sock --port=3306
root      3588  3577  0 15:43 pts/0    00:00:00 grep mysql

[root@ggg2 Desktop]# su - mysql
[mysql@ggg2 ~]$ mysql -usystem -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.12-log 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(system@localhost)[(none)]> 

九、修改mysql用户的限制

主要是在 /etc/security/limits.conf 文件的最后，加上4行，这4行主要是设置mysql用户，能打开的文件个数nofile、能打开的进程数nproc：

mysql		 soft    nproc           2047
mysql            hard    nproc           16384
mysql            soft    nofile          1024
mysql            hard    nofile          65536

[root@ggg2 Desktop]# cat /etc/security/limits.conf
# /etc/security/limits.conf
#
#Each line describes a limit for a user in the form:
#
#<domain>        <type>  <item>  <value>
#
#Where:
#<domain> can be:
#        - a user name
#        - a group name, with @group syntax
#        - the wildcard *, for default entry
#        - the wildcard %, can be also used with %group syntax,
#                 for maxlogin limit
#
#<type> can have the two values:
#        - "soft" for enforcing the soft limits
#        - "hard" for enforcing hard limits
#
#<item> can be one of the following:
#        - core - limits the core file size (KB)
#        - data - max data size (KB)
#        - fsize - maximum filesize (KB)
#        - memlock - max locked-in-memory address space (KB)
#        - nofile - max number of open file descriptors
#        - rss - max resident set size (KB)
#        - stack - max stack size (KB)
#        - cpu - max CPU time (MIN)
#        - nproc - max number of processes
#        - as - address space limit (KB)
#        - maxlogins - max number of logins for this user
#        - maxsyslogins - max number of logins on the system
#        - priority - the priority to run user process with
#        - locks - max number of file locks the user can hold
#        - sigpending - max number of pending signals
#        - msgqueue - max memory used by POSIX message queues (bytes)
#        - nice - max nice priority allowed to raise to values: [-20, 19]
#        - rtprio - max realtime priority
#
#<domain>      <type>  <item>         <value>
#

#*               soft    core            0
#*               hard    rss             10000
#@student        hard    nproc           20
#@faculty        soft    nproc           20
#@faculty        hard    nproc           50
#ftp             hard    nproc           0
#@student        -       maxlogins       4

mysql		 soft    nproc           2047
mysql            hard    nproc           16384
mysql            soft    nofile          1024
mysql            hard    nofile          65536

# End of file

总结一下：

相对于rpm安装方式是一站式把安装、配置服务、开机自启动都搞定，源码安装稍微要复杂一点，从创建程序目录、创建文件目录、创建用户、修改目录属主和权限、创建my.cnf文件、安装mysql服务、配置服务、编写数据库管理脚本、设置开机自启动，因为都是按照定制来安装的，所以非常灵活。

不过缺点是不能像rpm安装方式那样升级，以后版本更新了，只能重新安装。。。