【centos安装mysql数据库】详细版

1、下载二进制文件

如果内网或者断网环境，提前下载好文件
https://cdn.mysql.com/archives/mysql-5.6/mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
若果公网环境

[root@localhost ～]# cd /root
[root@localhost ～]# wget https://cdn.mysql.com/archives/mysql-5.6/mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
[root@localhost ～]# ll mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
-rw-r--r--. 1 root root 314581668 2月   6 14:37 mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

2、创建mysql用户以及用户组

[root@localhost ～]# groupadd mysql
[root@localhost ～]# useradd mysql -r -g mysql
# 验证用户组和用户
[root@localhost ～]# id mysql
uid=498(mysql) gid=501(mysql) 组=501(mysql)

3、创建程序、数据存放目录

[root@localhost ～]# mkdir /home/mysql/{program,data,conf} -p
[root@localhost～]#mkdir/home/mysql/data/mysqldata1/{mydata,sock,tmpdir,log,innodb_ts,innodb_log,undo,slowlog, binlog,relaylog} -p
# 查看创建目录结果
[root@localhost ～]# tree /home/mysql/  #如果没有这个命令，就使用yum install tree -y 安装
/home/mysql/
├—— conf
├—— data
|   └—— mysqldata1
|       ├—— binlog
|       ├—— innodb_log
|       ├—— innodb_ts
|       ├—— log
|       ├—— mydata
|       ├—— slowlog
|       ├—— sock
|       ├—— tmpdir
|       └—— undo
|       └—— relaylog
└—— program
13 directories, 0 files

4、解压缩二进制文件并设置目录权限

[root@localhost ～]# cd /root
[root@localhost ～]# tar xf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz -C/home/mysql/\program/
[root@localhost ～]# chown mysql.mysql /home/mysql -R
# 查看datadir关键目录的权限是否正确
[root@localhost ～]# ll /home/mysql/data/mysqldata1/
总用量 36
drwxr-xr-x 2 mysql mysql 4096 2月  12 23:07 binlog
drwxr-xr-x 2 mysql mysql 4096 2月  12 23:07 innodb_log
drwxr-xr-x 2 mysql mysql 4096 2月  12 23:07 innodb_ts
drwxr-xr-x 2 mysql mysql 4096 2月  12 23:07 log
drwxr-xr-x 2 mysql mysql 4096 2月  12 23:07 mydata
drwxr-xr-x 2 mysql mysql 4096 2月  12 23:07 slowlog
drwxr-xr-x 2 mysql mysql 4096 2月  12 23:07 sock
drwxr-xr-x 2 mysql mysql 4096 2月  12 23:07 tmpdir
drwxr-xr-x 2 mysql mysql 4096 2月  12 23:07 undo
drwxr-xr-x 2 mysql mysql 4096 2月  12 23:07 relaylog

5、软链路径

[root@localhost ～]# ln -s\
/home/mysql/program/mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql
# 查看basedir关键程序目录是否可用
[root@localhost ～]# ll /usr/local/mysql/
总用量 68
drwxr-xr-x  2 mysql mysql  4096 2月  12 23:05 bin
-rw-r--r--  1 mysql mysql 17987 11月 28 21:36 COPYING
drwxr-xr-x  3 mysql mysql  4096 2月  12 23:04 data
drwxr-xr-x  2 mysql mysql  4096 2月  12 23:05 docs
drwxr-xr-x  3 mysql mysql  4096 2月  12 23:05 include
drwxr-xr-x  3 mysql mysql  4096 2月  12 23:04 lib
drwxr-xr-x  4 mysql mysql  4096 2月  12 23:05 man
drwxr-xr-x 10 mysql mysql  4096 2月  12 23:05 mysql-test
-rw-r--r--  1 mysql mysql  2496 11月 28 21:36 README
drwxr-xr-x  2 mysql mysql  4096 2月  12 23:04 scripts
drwxr-xr-x 28 mysql mysql  4096 2月  12 23:04 share
drwxr-xr-x  4 mysql mysql  4096 2月  12 23:05 sql-bench
drwxr-xr-x  2 mysql mysql  4096 2月  12 23:04 support-files
[root@localhost ～]# export PATH=$PATH:/usr/local/mysql/bin/
[root@localhost ～]# echo 'export PATH=$PATH:/usr/local/mysql/bin/' >> /etc/profile
# 查看环境变量配置是否成功添加到/etc/profile文件中
[root@localhost ～]# tail -1 /etc/profile
export PATH=$PATH:/usr/local/mysql/bin/

6、配置my.cnf 配置文件

[root@localhost ～]# cp -ar /usr/local/mysql/support-files/my-default.cnf /home/mysql/conf/my.cnf
[root@localhost ～]# ln -s /home/mysql/conf/my.cnf  /etc/my.cnf
# my.cnf配置文件内容如下
[root@localhost ～]# vim /home/mysql/conf/my.cnf
[client]
socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径
[mysqld]
user=mysql
basedir = /usr/local/mysql
socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径
pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid # pid文件所在路径
datadir=/home/mysql/data/mysqldata1/mydata # 数据文件路径
tmpdir=/home/mysql/data/mysqldata1/tmpdir # 存放临时文件的路径
log-error=/home/mysql/data/mysqldata1/log/error.log
slow_query_log
slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log
log-bin=/home/mysql/data/mysqldata1/binlog/mysql-bin
relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin
innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts
innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log
innodb_undo_directory = /home/mysql/data/mysqldata1/undo/

7、初始化MySQL(二选其一)

# 使用该选项初始化时会在错误日志中写一个随机的root密码，初始化完成之后在错误日志中搜索password
[root@localhost mysql]# mysqld --defaults-file=/etc/my.cnf --initialize
#使用该选项初始化时不会产生随机密码，而是像MySQL 5.7之前的版本一样，初始化完成之后，第一次登录数据库使用空的root密码
[root@localhost mysql]# mysqld --defaults-file=/etc/my.cnf --initialize-insecure

8、启动MySQL

[root@localhost mysql]# cp -ar /usr/local/mysql/support-files/mysql.server /etc/init.d/\mysqld
[root@localhost mysql]# chmod +x /etc/init.d/mysqld
# 查看/etc/init.d/mysqld是否被成功赋予执行权限
[root@localhost mysql]# ll /etc/init.d/mysqld
-rwxr-xr-x 1 mysql mysql 10875 11月 28 23:32 /etc/init.d/mysqld
[root@localhost mysql]# service mysqld start
Starting MySQL..                                         [确定]
# 查看进程和端口
[root@localhost mysql]# ps aux |grep mysqld
root     10475  0.0  0.0  11472  1384 pts/2    S    23:37   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/home/mysql/data/mysqldata1/mydata --pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid
mysql   10743  0.0 24.21078428464964 pts/2  Sl  23:37  0:00 /usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql --datadir=/home/ mysql/ data/mysqldata1/mydata --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/home/mysql/data/mysqldata1/log/error.log --pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid
--socket=/home/mysql/data/mysqldata1/sock/mysql.sock
root     10791  0.0  0.0103256   860 pts/2    S+   23:46   0:00 grep mysqld
[root@localhost mysql]# netstat -ntupl |grep mysqld
tcp        0      0 :::3306              :::＊               LISTEN      10743/mysqld
# 查看错误日志 日志这儿文件名可能不同需要看的在目录下看一下文件名
[root@localhost mysql]# vim /home/mysql/data/mysqldata1/log/error.log
# 注意：日志中不能出现ERROR错误，看到最后一行输出版本号和socket信息就表示MySQL启动成功
Version:  '5.6.35-log'   socket:  '/home/mysql/data/mysqldata1/sock/mysql.  sock'   port:3306  MySQL Community Server（GPL）

9、简单加固

9.1、登录数据库

[root@localhost mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35-log MySQL Community Server（GPL）
Copyright（c）2000, 2016, 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> select user（）;
+----------------+
| user（）        |
+----------------+
| root@localhost |
+----------------+
1 row in set（0.00 sec）
# 查看当前MySQL版本是否正确
mysql> select version（）;
+------------+
| version（） |
+------------+
| 5.6.35-log |
+------------+
1 row in set（0.00 sec）

9.2、删除非root或非localhost的用户并修改root密码

mysql> select user, host from mysql.user;
+------+------------------------+
| user | host                   |
+------+------------------------+
| root | 127.0.0.1              |
| root | ::1                    |
|      | localhost              |
| root | localhost              |
|      | localhost.localdomain  |
| root | localhost.localdomain  |
+------+------------------------+
6 rows in set（0.00 sec）
mysql> delete from mysql.user where user! ='root' or host! ='localhost';
Query OK, 5 rows affected（0.01 sec）
## 如果是MySQL 5.7.x 较新的版本或者8.0.x版本，则删除操作需要排除几个系统用户
mysql>DELETE FROM mysql.user WHERE user NOT IN（'mysql.sys', 'mysql.session', 'mysqlxsys', 'root', 'mysql.infoschema'）OR host NOT IN（'localhost'）;
# 查看删除结果是否正确
mysql> select user, host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set（0.00 sec）
mysql> set password for 'root'@'localhost' = PASSWORD('admin');  # 在 MySQL 5.7.x 版本中可以不需要PASSWORD函数，直接使用明文密码也可以自动转换为加密格式密码写入mysql.user表中，且该用法将在后续版本中移除
Query OK, 0 rows affected（0.00 sec）
mysql> flush privileges;
Query OK, 0 rows affected（0.00 sec）
mysql>
# 重新使用新密码登录MySQL
[root@localhost mysql]# 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.35-log MySQL Community Server（GPL）
Copyright（c）2000, 2016, 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>

9.3、删除test库，清理mysql.db表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set（0.00 sec）
mysql> drop database test;
Query OK, 0 rows affected（0.00 sec）
# 查看删除结果是否正确
mysql> show databases;
+--------------------+
| Database            |
+--------------------+
| information_schema |
| mysql               |
| performance_schema |
+--------------------+
3 rows in set（0.00 sec）
mysql> select ＊ from mysql.db\G  # MySQL 5.7.x版本移除了test库之后，该库的权限也没有了，但增加了sys库，有对应的sys库的默认权限，所以5.7.x版本忽略清理该表
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ 1. row ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊Host: %Db: testUser:Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YGrant_priv: NReferences_priv: YIndex_priv: YAlter_priv: Y
Create_tmp_table_priv: YLock_tables_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: NExecute_priv: NEvent_priv: YTrigger_priv: Y
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ 2. row ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊Host: %Db: test\_%User:Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YGrant_priv: NReferences_priv: YIndex_priv: YAlter_priv: Y
Create_tmp_table_priv: YLock_tables_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: NExecute_priv: NEvent_priv: YTrigger_priv: Y
2 rows in set（0.00 sec）
mysql> truncate mysql.db;
Query OK, 0 rows affected（0.00 sec）
## 如果是MySQL 5.7.x 较新的版本或者8.0.x版本，则清理操作需要排除几个系统用户
mysql>DELETE FROM mysql.db where user NOT IN（'mysql.sys', 'mysql.session', 'mysqlxsys', 'root', 'mysql.infoschema'）OR host NOT IN（'localhost'）;
# 查看清理结果是否正确
mysql> select ＊ from mysql.db\G
Empty set（0.00 sec）
mysql> flush privileges;
Query OK, 0 rows affected（0.00 sec）
mysql>

10、删除test库，清理mysql.db表

主要原因：
在默认情况下，MySQL 5.6.x初始化安装之后会生成一个测试用途的test库，这个库在生产环境中一般不需要使用，如果确定不使用，请删除。
在默认情况下，MySQL 5.6.x初始化完成MySQL之后，在mysql.db库级别权限表中会有针对test库的任意用户、任意地址的访问权限，即：无任何权限用户或匿名用户登录到MySQL中都可以对test库进行任意操作。因此，建议MySQL完成初始化安装之后，清理这些不安全的用户或删除mysql.db表中对test库预设的访问权限。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set（0.00 sec）
mysql> drop database test;
Query OK, 0 rows affected（0.00 sec）
# 查看删除结果是否正确
mysql> show databases;
+--------------------+
| Database            |
+--------------------+
| information_schema |
| mysql               |
| performance_schema |
+--------------------+
3 rows in set（0.00 sec）
mysql> select ＊ from mysql.db\G  # MySQL 5.7.x版本移除了test库之后，该库的权限也没有了，但增加了sys库，有对应的sys库的默认权限，所以5.7.x版本忽略清理该表
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ 1. row ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊Host: %Db: testUser:Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YGrant_priv: NReferences_priv: YIndex_priv: YAlter_priv: Y
Create_tmp_table_priv: YLock_tables_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: NExecute_priv: NEvent_priv: YTrigger_priv: Y
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ 2. row ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊Host: %Db: test\_%User:Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YGrant_priv: NReferences_priv: YIndex_priv: YAlter_priv: Y
Create_tmp_table_priv: YLock_tables_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: NExecute_priv: NEvent_priv: YTrigger_priv: Y
2 rows in set（0.00 sec）
mysql> truncate mysql.db;
Query OK, 0 rows affected（0.00 sec）
## 如果是MySQL 5.7.x 较新的版本或者8.0.x版本，则清理操作需要排除几个系统用户
mysql>DELETE FROM mysql.db where user NOT IN('mysql.sys', 'mysql.session', 'mysqlxsys', 'root', 'mysql.infoschema') OR host NOT IN('localhost');
# 查看清理结果是否正确
mysql> select * from mysql.db\G
Empty set（0.00 sec）
mysql> flush privileges;
Query OK, 0 rows affected（0.00 sec）
mysql>

精简后的数据库有