CentOS 6.9 安装MySQL5.6 详细教程

移除CentOS默认的mysql-libs

[root@localhost etc]# whereis mysql
mysql: /usr/lib64/mysql /usr/share/mysql

[root@localhost etc]# yum remove mysql-libs
在此过程中如果出现 Is this ok [y/N]: 输入y后回车即可

出现 Complete! 表示移除成功

清空dbcache

[root@localhost etc]# yum clean dbcache
Loaded plugins: fastestmirror
Cleaning repos: ISO
3 sqlite files removed

下载MySQL rpm安装包

# 先切换到你要将安装包下载的目录(个人喜好, 避免下载后不知道下载到哪里去了)
[root@localhost etc]# cd /home/myfiles/
# mysql 下载地址 http://repo.mysql.com/ 可根据需要自己选择, 此教程使用如下下载链接

[root@localhost myfiles]# wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
--2018-06-12 10:40:29-- http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
Resolving repo.mysql.com... 223.119.236.209
Connecting to repo.mysql.com|223.119.236.209|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5824 (5.7K) [application/x-redhat-package-manager]
Saving to: “mysql-community-release-el6-5.noarch.rpm”

100%[====================================================================================================================================================================================================================================>] 5,824 --.-K/s in 0s

2017-05-08 10:40:29 (298 MB/s) - “mysql-community-release-el6-5.noarch.rpm” saved [5824/5824]

[root@localhost myfiles]#

# 安装下载好的rpm文件

[root@localhost myfiles]# rpm -ivh mysql-community-release-el6-5.noarch.rpm
Preparing... ########################################### [100%]
1:mysql-community-release########################################### [100%]
[root@localhost myfiles]#

# yum安装mysql-community-server

[root@localhost myfiles]# yum install mysql-community-server
在此过程中如果出现 Is this ok [y/N]: 输入y后回车即可
出现 Complete! 表示安装成功

以上步骤完成后即可启动MySql服务

# 启动服务命令

[root@localhost myfiles]# service mysqld start
在此过程中出现 Starting mysqld: [ OK ]
表示启动成功

# 停止服务命令

[root@localhost myfiles]# service mysqld stop
在此过程中出现 Stopping mysqld: [ OK ]
表示停止服务成功

# 重启命令

[root@localhost myfiles]# service mysqld restart
在此过程中出现
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
表示重启服务成功

修改密码

默认密码是空的,为了安全起见,需要重新设置root密码

# 登录mysql

[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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>

# 选择mysql数据库

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

# 修改密码

mysql> update user set password=PASSWORD("YOUR_PASSWORD") where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

# 使修改生效

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 退出mysql登录

mysql> quit
Bye

验证修改后的root密码

# 用原来不需要密码的方式登录, 无法登录

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

# 使用修改后的密码登录, 登录成功

mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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>

修改mysql配置文件

my.cnf是mysql启动时加载的配置文件，一般会放在mysql的安装目录中，也可以放在其他目录加载。
安装mysql后，系统中会有多个my.cnf文件。(如果按照本文中的安装步骤,一般只会存在一个my.cnf,路径为 /etc/my.cnf)
使用locate my.cnf命令可以列出所有的my.cnf文件

[root@localhost ~]# locate my.cnf
/etc/my.cnf

当我们需要修改配置文件时，需要找到mysql启动时是加载了哪个my.cnf文件。
1). 查看是否使用了指定目录的my.cnf
启动mysql后，我们查看mysql的进程，看看是否有设置使用指定目录的my.cnf文件，如果有则表示mysql启动时是加载了这个配置文件。

[root@localhost ~]# ps aux|grep mysql|grep 'my.cnf'

# 如果指定了则返回类似如下信息

fdipzone 25174 0.0 0.0 3087244 600 ?? S 4:12下午 0:01.14 /usr/local/Cellar/mysql/5.6.24/bin/mysqld --defaults-file=/usr/local/Cellar/mysql/5.6.24/my.cnf --basedir=/usr/local/Cellar/mysql/5.6.24 --datadir=/usr/local/var/mysql --plugin-dir=/usr/local/Cellar/mysql/5.6.24/lib/plugin --bind-address=127.0.0.1 --log-error=/usr/local/var/mysql/TerrydeMacBook-Air.local.err --pid-file=/usr/local/var/mysql/TerrydeMacBook-Air.local.pid
fdipzone 25064 0.0 0.0 2452824 4 ?? S 4:12下午 0:00.03 /bin/sh /usr/local/opt/mysql/bin/mysqld_safe --defaults-file=/usr/local/Cellar/mysql/5.6.24/my.cnf --bind-address=127.0.0.1 --datadir=/usr/local/var/mysql

可以看到/usr/local/Cellar/mysql/5.6.24/my.cnf就是mysql指定的启动加载的配置文件。

# 如果上面的命令没有输出，表示没有设置使用指定目录的my.cnf。(如下, 如果按照本文中的安装步骤为此情况)

[root@localhost ~]# ps aux|grep mysql|grep 'my.cnf'
[root@localhost ~]#

2). 查看mysql默认读取my.cnf的目录
如果没有设置使用指定目录的my.cnf，mysql启动时会读取安装目录根目录及默认目录下的my.cnf文件。

# 查看mysql启动时读取配置文件的默认目录

[root@localhost ~]# mysql --help|grep 'my.cnf'
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
[root@localhost ~]#

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 这些就是mysql默认会搜寻my.cnf的目录，顺序排前的优先。
本文中则使用的为 /etc/my.cnf

3). 启动时没有使用配置文件
a. 如果没有设置使用指定目录my.cnf文件及默认读取目录没有my.cnf文件，表示mysql启动时并没有加载配置文件，而是使用默认配置。

b. 需要修改配置，可以在mysql默认读取的目录中，创建一个my.cnf文件(例如:/etc/my.cnf)，把需要修改的配置内容写入，重启mysql后即可生效。

注意: 如果按照本文中的安装步骤则配置文件为 /etc/my.cnf

设置MySQL允许外网访问

mysql 安装后默认外网是无法访问的, 需修改配置文件后才可以
1). 修改配置文件(路径为 7 中的配置文件路径/etc/my.cnf)

[root@localhost ~]# vi /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
\#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

\#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

\#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

# 如果按照本文的安装步骤此处没有port的属性 如需修改则在此处加入port=Your port

port=3307

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Recommended in standard MySQL setup

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
~
~
~

# 如果此配置文件中存在 bind-address 参数, 则将其直接注释掉

# 按照此文中的安装步骤,该配置文件中不存在此参数则不需要调整

2). 登录数据库

# 登录

[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

# 选择mysql数据库

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

# 查询host

mysql> select user,host from user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
7 rows in set (0.00 sec)

mysql>

# 创建host

# 如果没有"%"这个host值,就执行下面这两句:

mysql> update user set host='%' where user='root';

# 执行此语句时有可能会报错, 忽略即可

mysql> flush privileges;

# 再次查询host,出现有"%"这个host值即成功添加

mysql> select user,host from user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| root | % |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
7 rows in set (0.00 sec)

mysql>

# 授权用户

# 任意主机以用户root和密码mypwd连接到mysql服务器

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;
mysql> flush privileges;

# IP为192.168.1.102的主机以用户myuser和密码mypwd连接到mysql服务器

mysql> GRANT ALL PRIVILEGES ON *.* TO 'your_user'@'192.168.1.102' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;
mysql> flush privileges;

PS：以上为授权方法，以下为改表方法：
mysql> use mysql;
mysql> update user set host='%' where user='root';
mysql> flush privileges;

######经上述操作后有可能存在在使用 mysql -uroot -p 登录时出现
######Access denied for user 'root'@'localhost' (using password YES)问题
按照以下步骤操作即可:

首先关闭MySQL服务
[root@localhost ~]# service mysqld stop 

安全启动MySQL（跳过密码验证）
[root@localhost ~]# mysqld_safe --skip-grant-table  
 
此时如果卡住不动了,不用担心,其实安全模式已经启动了,只需要在开一个ssh窗口即可

在新开的ssh窗口登录MySQL
 [root@localhost ~]# mysql -u root mysql

清理用户为空的数据
 mysql> delete from user where USER='';

重新更新一下root密码
 mysql> grant all privileges on *.* to 'root'@'localhost' identified by 'YOUR_PASSWORD' with grant option;

使配置生效
 mysql> flush privileges;

重启数据库
[root@localhost ~]# service mysqld restart

3). 检测服务器的mysql端口是否对外开放, 比如本教程中修改后的port=3307是否开放

4). 开放iptable

[root@localhost ~]# /sbin/iptables -I INPUT -p tcp --dport 3307 -j ACCEPT
[root@localhost ~]# /etc/rc.d/init.d/iptables save
[root@localhost ~]# /etc/init.d/iptables restart
[root@localhost ~]# /sbin/iptables -L -n

5). 完成mysql外网访问配置
此时可以通过ip地址 + 端口号 ,用户名和密码进行外网连接

修改mysql默认字符集

1). 登录myslq控制台查看当前字符集设置

# 登录

[root@localhost etc]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

# 查看字符集设置

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql>

# 其中

- character_set_client 客户端来源数据使用的字符集编码；

- character_set_connection 为建立连接使用的编码(从客户端接收到数据，然后传输的字符集)；

- character_set_database 默认数据库的字符集编码；

- character_set_filesystem
  把os上文件名转化成此字符集，即把 character_set_client转换character_set_filesystem，默认binary是不做任何转换的

- character_set_results 查询结果字符集编码；

- character_set_server 数据库服务器的默认字符集编码；

- character_set_system 系统元数据(字段名等)字符集,是为存储系统元数据的字符集；

2). 修改字符集
修改上文中的mysql配置文件 /etc/my.cnf

#编辑mysql配置文件
#修改前的配置文件
[root@localhost etc]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
port=3307
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

"/etc/my.cnf" 38L, 1173C

#观察发现配置文件中没有发现 [client] 的配置内容,需我们手动添加到 [mysqld] 的上方
#修改编码方式共需要设置以下 a , b 两个地方

#a. 在[mysqld] 的上方增加以下代码
[client]
default-character-set=utf8

#b.在[mysqld]配置中增加以下代码
character-set-server=utf8
collation-server=utf8_general_ci

#c.保存后退出(:wq)

#d. 最终修改后的配置文件
[root@localhost etc]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[client]
default-character-set=utf8

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

character-set-server=utf8
collation-server=utf8_general_ci

port=3307
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

3). 重启mysql服务

[root@localhost myfiles]# service mysqld restart
在此过程中出现 
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
表示重启服务成功

4). 再次登录myslq控制台查看当前字符集设置

#登录
[root@localhost etc]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> 

#查看字符集设置
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> 

#结果如上即为字符集修改完成