构建mysql服务器

构建MySQL服务器

1.1 问题

要求如下：

• 在IP地址192.168.4.50主机上部署mysql服务
• 设置数据库管理员root本机登录密码为tarena

1.2 方案

克隆新的虚拟机：

eth0网卡:192.168.4.50

主机名称:host50

下载软件mysql-5.7.17.tar

关闭防火墙（如果有的话）

关闭SELinux（如果有的话）

1.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：准备工作

1）如果之前有mariadb，则需要先卸载，并删除对应的配置与数据：

1. [root@localhost ~]# systemctl stop mariadb

2）删除/etc/my.cnf配置文件

此配置文件由RHEL自带的mariadb-libs库提供：

[root@localhost ~]# rm -rf /etc/my.cnf

3）删除数据

1. [root@localhost ~]# rm -rf /var/lib/mysql/*

4）卸载软件包（没有会显示未安装软件包）

1. [root@localhost ~]# rpm -e --nodeps mariadb-server mariadb
2. 警告：/var/log/mariadb/mariadb.log 已另存为/var/log/mariadb/mariadb.log.rpmsave

步骤二：安装mysql软件包

1）解压mysql-5.7.17.tar 软件包

1. [root@host50 ~]# tar -xvf mysql-5.7.17.tar //解压mysql整合包
2. ./mysql-community-client-5.7.17-1.el7.x86_64.rpm
3. ./mysql-community-common-5.7.17-1.el7.x86_64.rpm
4. ./mysql-community-devel-5.7.17-1.el7.x86_64.rpm
5. ./mysql-community-embedded-5.7.17-1.el7.x86_64.rpm
6. ./mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm
7. ./mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm
8. ./mysql-community-libs-5.7.17-1.el7.x86_64.rpm
9. ./mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
10. ./mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
11. ./mysql-community-server-5.7.17-1.el7.x86_64.rpm
12. ./mysql-community-test-5.7.17-1.el7.x86_64.rpm

2）安装MySQL软件包

1. [root@host50 ~]# yum -y install mysql-community-*.rpm //yum安装自动解决依赖
2. ./mysql-community-client-5.7.17-1.el7.x86_64.rpm
3. ./mysql-community-common-5.7.17-1.el7.x86_64.rpm
4. ./mysql-community-devel-5.7.17-1.el7.x86_64.rpm
5. ./mysql-community-embedded-5.7.17-1.el7.x86_64.rpm
6. ./mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm
7. ./mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm
8. ./mysql-community-libs-5.7.17-1.el7.x86_64.rpm
9. ./mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
10. ./mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
11. ./mysql-community-server-5.7.17-1.el7.x86_64.rpm
12. ./mysql-community-test-5.7.17-1.el7.x86_64.rpm

3）启动MySQL数据库服务并设置开机自启

提示：第一次启动，需要初始化数据，会比较慢

1. [root@host50 ~]# systemctl start mysqld //启动mysql服务
2. [root@host50 ~]# systemctl enable mysqld //设置开机自启
3. [root@host50 ~]# systemctl status mysqld //查看mysql服务状态
4. ● mysqld.service - MySQL Server
5. Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
6. Active: active (running) since 二 2018-08-28 10:03:24 CST; 8min ago
7. Docs: man:mysqld(8)
8. http://dev.mysql.com/doc/refman/en/using-systemd.html
9. Main PID: 4284 (mysqld)
10. CGroup: /system.slice/mysqld.service
11. └─4284 /usr/sbin/mysqld --daemonize --pid-file=/var/r...
12.  
13. 8月 28 10:02:56 localhost.localdomain systemd[1]: Starting MySQ...
14. 8月 28 10:03:24 localhost.localdomain systemd[1]: Started MySQL...
15. Hint: Some lines were ellipsized, use -l to show in full.

步骤三：连接MySQL服务器，修改密码

查看初始密码

1. [root@host50 ~]#grep –i 'password' /var/log/mysqld.log
2. 2017-04-01T18:10:42.948679Z 1 [Note] A temporary password is generated for root@localhost: mtoa>Av<p6Yk //随机生成的管理密码为mtoa>Av<p6Yk

2）使用初始密码连接mysql服务

1. [root@host50 ~]# mysql -u root -p'mtoa>Av<p6Yk' //初始密码登录，
2. mysql: [Warning] Using a password on the command line interface can be insecure.
3. Welcome to the MySQL monitor. Commands end with ; or \g.
4. Your MySQL connection id is 11
5. Server version: 5.7.17
6.  
7. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
8.  
9. Oracle is a registered trademark of Oracle Corporation and/or its
10. affiliates. Other names may be trademarks of their respective
11. owners.
12.  
13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14. mysql>                                     //登录成功后，进入SQL操作环境

3）重置数据库管理员roo本机登录密码

1. mysql> show databases;
2. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement //提示必须修改密码
3. mysql> alter user root@”localhost” identified by "123qqq…A"; //修改登陆密码
4. Query OK, 0 rows affected (0.00 sec)
5. mysql> exit //断开连接
6. [root@host50 ~]#

4）修改密码策略

1. [root@host50 ~]# mysql -uroot –p123qqq…A
2. mysql>
3. mysql>set global validate_password_policy=0; //只验证长度
4. Query OK, 0 rows affected (0.00 sec)
5. mysql>set global validate_password_length=6； //修改密码长度,默认值是8个字符
6. Query OK, 0 rows affected (0.00 sec)
7. mysql> alter user root@”localhost” identified by "tarena"; //修改登陆密码
8. Query OK, 0 rows affected (0.00 sec)
9. mysql>exit

5）使用修改后的密码登录

1. [root@host50 ~]# mysql -uroot -ptarena        //登录
2. Welcome to the MySQL monitor. Commands end with ; or \g.
3. Your MySQL connection id is 15
4. Server version: 5.7.17 MySQL Community Server (GPL)
5.  
6. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
7.  
8. Oracle is a registered trademark of Oracle Corporation and/or its
9. affiliates. Other names may be trademarks of their respective
10. owners.
11. mysql> show databases; //查看数据库
12. +--------------------+
13. | Database |
14. +--------------------+
15. | information_schema |
16. | mysql |
17. | performance_schema |
18. | sys                |
19. +--------------------+
20. 4 rows in set (0.00 sec)
21. mysql>