国庆系列|如何在公有云服务器上快速部署Mysql

破晓之翼

发布于 2022-12-02 16:26:12

3.1K0

发布于 2022-12-02 16:26:12

文章被收录于专栏：产品能力产品能力

前言：

今天是2022.10.1伟大祖国的73岁生日，我们都坚守在自己的工作岗位上为国庆献礼。

问题描述

某事业单位，数据稽核项目DEMO临时通知上线联调，需要部署一台MYSQL数据库，之前一直玩oracle,国庆给大家放假也不好意思在扫大家兴，自己搞定就是了。登录电信云服务器，安装好系统，挂载硬盘，分区，配置好yum，准备下载mysql 5.8,ORACLE 账户密码过期了，无法登录oracle 官网下载部署，咨询了某个运维的大牛，本来打算问问人家要个包，或者让他直接通过SCP帮我传一下，结果学习到了一个新的快速部署的方法，做一个记录，mysql和reids等数据库的部署运维开发知识需要加强学习。下面是具体步骤：

1.rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm（下载安装包）； 2.yum -y install mysql-community-server（安装mysql）； 3.systemctl enable mysqld（加入开机启动）； 4.systemctl start mysqld（启动mysql服务进程）； 5.mysql_secure_installation（设置用户名密码）； 6.设置用户权限，没啥特殊需求直接Y/N/Y/Y就好； 7.部署完成。 ps：云服务器需要到控制台设置3306端口开放，一般的端口都需要配置，为了安全考虑防止漏扫，建议映射为其他不常用端口如33036等。

在orale 完成静默安装后，我直接sqlplus / as sysdba,进入管理员权限，在建立表空间，用户，设置密码授权即可，但是mysql不是这样的。

具体遇到的问题：

mysql 安装完后需要设置root密码，结果我自己设置的空，造成登录异常。

root@ecs-a148-0928859 ~]# mysql -h localhost -P 3306 -u root -proot
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
ERROR 1045 (28000): Access denied for user '-root'@'localhost' (using password: YES)
[root@ecs-a148-0928859 ~]# mysql -u -root -p
Enter password: 
ERROR 1045 (28000): Access denied for user '-root'@'localhost' (using password: NO)
[root@ecs-a148-0928859 ~]#  select user,host,password from user;
// -bash: syntax error near unexpected token 
[root@ecs-a148-0928859 ~]# mysql -u root mysql

解决方案：

停止mysql 服务：service mysqld stop

[root@ecs-a148-0928859 ~]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service

修改 /etc/my.cnf

启动mysql

systemctl start mysqld

登录mysql：mysql -u root -p

[root@ecs-a148-0928859 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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密码 use mysql update user set password=password(“XXXX”) where user=“root”

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> 
mysql> update user set password=password(“XXXX”) where user=“root”
    -> ;
ERROR 1054 (42S22): Unknown column '“root”' in 'where clause'  //这个错误经过检查是“”的问题，更正后解决。
mysql> update user set password=password("XXX") where user="root";
Query OK, 0 rows affected (0.01 sec)
Rows matched: 3  Changed: 0  Warnings: 0

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

mysql> quit;

修改配置文件还原，重启数据库

[root@ecs-a148-0928859 ~]# vi /etc/my.cnf
[root@ecs-a148-0928859 ~]# mysql service mysql restart
mysql  Ver 14.14 Distrib 5.6.51, for Linux (x86_64) using  EditLine wrapper
Copyright (c) 2000, 2021, 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.

问题解决，登录数据库验证。

[root@ecs-a148-0928859 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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.
//至此mysql完全部署完毕，开始配置接口文件和应用，准备抽取数据校验。

10.1 的乌鲁木齐迎来了冬天的初雪，洋洋洒洒，美不胜收。我在祖国的西北边陲，祝大家节日快乐，阖家安康。乌鲁木齐现在的疫情还是不太明朗，假期间就不到处浪给防疫添乱了，做好保障工作，顺便趁假期学习学习，梳理下自己的知识体系短板，给自己赋能。