【Web 集群实战】12_LNMP 之 MySQL 的安装与配置

Web集群实战

安装 MySQL 数据库

安装方式

• yum/rpm 包安装
• 二进制安装
• 源码编译安装
• 源码软件结合 yum/rpm 安装

安装步骤

1）创建 mysql 用户的账号

[root@ylt001 ~]# groupadd mysql
[root@ylt001 ~]# useradd -s /sbin/nologin -g mysql -M  mysql

• useradd 命令的参数简要说明：

  • -s /sbin/nologin 表示禁止该用户登录，只需要角色存在即可
  • -g mysql 指定 mysql 用户属于 mysql 组
  • -M 表示不创建家目录

• 检查刚刚创建的 mysql 用户和组

[root@ylt001 ~]# tail -1 /etc/passwd
mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin
[root@ylt001 ~]# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)

• 建立存放安装软件的固定目录

[root@ylt001 ~]# mkdir -p /home/ylt/tools
# 如若已创建则无需
[root@ylt001 ~]# cd /home/ylt/tools

2）获取 MySQL 软件包

注：

• 本文以 ftp 方式将宿主机中的二进制安装包传至虚拟机，即需要搭建 ftp 服务，具体方法可参照 vsftpd + PAM + MySQL + 内外网 + 磁盘配额 配置全过程

  • mysql 安装包下载地址：MySQL Community Server 5.5.61，系统选择 Linux-Generic

3) 采用二进制方式安装 MySQL

• 解压并移动 MySQL 二进制软件包到指定的安装路径

[root@ylt001 tools]# tar xf mysql-5.5.61-linux-glibc2.12-x86_64.tar.gz
[root@ylt001 tools]# mkdir -p /application
# 如若已创建则无需
[root@ylt001 tools]# mv mysql-5.5.61-linux-glibc2.12-x86_64 /application/mysql-5.5.61
# 移动并更改目录名

• 创建软链接，生成去掉版本号的路径并查看

[root@ylt001 tools]# ln -s /application/mysql-5.5.61 /application/mysql
[root@ylt001 tools]# ll /application
total 12
lrwxrwxrwx  1 mysql mysql   26 Sep 21 20:23 mysql -> /application/mysql-5.5.61/
drwxr-xr-x 13 root  root  4096 Sep 21 20:10 mysql-5.5.61
lrwxrwxrwx  1 root  root    25 Sep 17 15:15 nginx -> /application/nginx-1.14.0
drwxr-xr-x 11 root  root  4096 Sep 17 15:16 nginx-1.14.0

4) 初始化 MySQL 配置文件 my.cnf

[root@ylt001 tools]# cd /application/mysql/
[root@ylt001 mysql]# ll support-files/*.cnf
-rw-r--r-- 1 7161 31415  4691 Jun 16 02:20 support-files/my-huge.cnf
-rw-r--r-- 1 7161 31415 19759 Jun 16 02:20 support-files/my-innodb-heavy-4G.cnf
-rw-r--r-- 1 7161 31415  4665 Jun 16 02:20 support-files/my-large.cnf
-rw-r--r-- 1 7161 31415  4676 Jun 16 02:20 support-files/my-medium.cnf
-rw-r--r-- 1 7161 31415  2840 Jun 16 02:20 support-files/my-small.cnf
[root@ylt001 mysql]# /bin/cp support-files/my-small.cnf /etc/my.cnf

5) 初始化 MySQL 数据库文件

[root@ylt001 mysql]# mkdir -p /application/mysql/data
# 建立 MySQL  数据文件目录
[root@ylt001 mysql]# chown -R mysql.mysql /application/mysql/
[root@ylt001 mysql]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data --user=mysql
# 初始化 MySQL 数据库文件，会有很多提示信息，如果没有 ERROR 级别的错误，会有两个 OK 的字样，表示初始化成功，否则就要解决初始化的问题
...
...
...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
...
...
...

配置并启动 MySQL 数据库

1) 设置 MySQL 启动脚本

[root@ylt001 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
# 拷贝 MySQL 启动脚本到 MySQL 的命令路径
[root@ylt001 mysql]# chmod +x /etc/init.d/mysqld
# 使脚本可执行

2) MySQL 二进制默认安装路径是 /usr/local/mysqld，启动脚本里是 /usr/local/mysql 的路径都需要替换

[root@ylt001 mysql]# sed -i 's#/usr/local/mysql#/application/mysql#g' /application/mysql/bin/mysqld_safe /etc/init.d/mysqld

3) 启动 MySQL 数据库

[root@ylt001 mysql]# /etc/init.c/mysqld start

4) 检查 MySQL 数据库是否启动

[root@ylt001 mysql]# netstat -lntup|grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      1169/mysqld  

5) 查看 MySQL 数据库启动结果日志

[root@ylt001 mysql]# tail -10 /application/mysql/data/ylt001.err
180922 20:53:03 InnoDB: Completed initialization of buffer pool
180922 20:53:03 InnoDB: highest supported file format is Barracuda.
180922 20:53:03  InnoDB: Waiting for the background threads to start
180922 20:53:04 InnoDB: 5.5.61 started; log sequence number 1595685
180922 20:53:04 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
180922 20:53:04 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
180922 20:53:04 [Note] Server socket created on IP: '0.0.0.0'.
180922 20:53:04 [Note] Event Scheduler: Loaded 0 events
180922 20:53:04 [Note] /application/mysql/bin/mysqld: ready for connections.
Version: '5.5.61'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

6）设置 MySQL 开机自启动

[root@ylt001 mysql]# chkconfig --add mysqld
[root@ylt001 mysql]# chkconfig mysqld on
[root@ylt001 mysql]# chkconfig --list mysqld
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

7) 配置 mysql 命令的全局使用路径

[root@ylt001 mysql]# echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
[root@ylt001 mysql]# tail -1 /etc/profile
export PATH=/application/mysql/bin:$PATH
[root@ylt001 mysql]# source /etc/profile
[root@ylt001 mysql]# echo $PATH 
/application/mysql/bin:/application/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

8) 登录 MySQL 测试

[root@ylt001 mysql]# mysql
mysql> show databases;
# 查看当前所有的数据库
mysql> select user();
查看当前的登录用户
mysql> quit

MySQL 安全配置

1） 为 MySQL 的 root 用户设置密码

[root@ylt001 mysql]# mysqladmin -u root password 'ylt123'
# 更改默认密码
[root@ylt001 mysql]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# 无法直接登录了
[root@ylt001 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.61 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>

2） 清理无用的 MySQL 用户及库

mysql> select user,host from mysql.user;
mysql> drop user "root"@"::1";
mysql> drop user ""@"localhost";
mysql> drop user ""@"ylt001";
mysql> drop user "root"@"ylt001";
mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)
mysql> flush privileges;
mysql> drop database test;
mysql> show databases;
mysql> quit