mysql部署和密码管理
一、SQL数据库
MySQL简介
MySQL是由瑞典MySQL AB 公司开发，目前属于 Oracle（甲骨文）旗下产品。 是最流行的关系型数据库管理系统之一，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
关系型数据库 关系型数据库是依据关系模型来创建的数据库。所谓关系模型就是“一对一、一对多、多对多”等关系模型，关系模型就是指二维表格模型,因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。常见的关系型数据库有：SQLServer ，Oracle ，Mysql等。
有了关系型数据库那自然也少不了非关系型数据库，非关系型数据库主要是基于“非关系模型”的数据库，由于关系型数据库太多了，所以一般用“非关系型”来表示其他类型的数据库。非关系型数据库有：redis ，mongodb ，memcached。
关系型数据库与非关系型数据库的优缺点
SQL语句 SQL代表结构化查询语言(Structured Query Language)。SQL是用于访问数据库的标准化语言。
SQL包含三个部分： 数据定义语言包含定义数据库及其对象的语句，例如表，视图，触发器，存储过程等。 数据操作语言包含允许您更新和查询数据的语句。 数据控制语言允许授予用户权限访问数据库中特定数据的权限。
SQL语句
部署MySQL以及掌握密码管理
推荐步骤：
1、安装Mysql 5.5模拟root密码遗忘修改root密码登录
2、安装Mysql 8.0，创建自己名字用户设置密码，修改root密码，锁定解锁自己名字密码，模拟root本地主机登录密码遗忘修改密码
实验步骤：
一、安装Mysql 5.5模拟root密码遗忘修改root密码登录
1、挂载centos7系统光盘
[root@centos01 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护，将以只读方式挂载
[root@centos01 ~]# ls /mnt/
CentOS_BuildTag EULA images LiveOS repodata RPM-GPG-KEY-CentOS-Testing-7
EFI GPL isolinux Packages RPM-GPG-KEY-CentOS-7 TRANS.TBL
2、安装mysql依赖程序
[root@centos01 ~]# yum -y install ncurses-devel cmake
3、创建管理用户和组
[root@centos01 ~]# groupadd mysql
[root@centos01 ~]# useradd -M -s /sbin/nologin -g mysql mysql
4、挂载云计算光盘
[root@centos01 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护，将以只读方式挂载
5、解压mysql文件
[root@centos01 ~]# tar zxf /mnt/mysql-5.5.22.tar.gz -C /usr/src/
[root@centos01 ~]# cd /usr/src/mysql-5.5.22/
6、编译mysql
[root@centos01 mysql-5.5.22]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc
7、安装mysql
[root@centos01 mysql-5.5.22]# make && make install
8、修改所属用户和组
[root@centos01 mysql-5.5.22]# chown -R mysql:mysql /usr/local/mysql/
9、添加执行权限设置开机自动启动
[root@centos01 mysql-5.5.22]# chmod +x /etc/init.d/mysqld
[root@centos01 mysql-5.5.22]# chkconfig --add mysqld
[root@centos01 mysql-5.5.22]# chkconfig --level 35 mysqld on
[root@centos01 mysql-5.5.22]# cd
10、优化Mysql命令
[root@centos01 ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@centos01 ~]# source /etc/profile
11、启动mysql服务
[root@centos01 ~]# systemctl start mysqld
[root@centos01 ~]# netstat -anptu | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 11683/mysqld
[root@centos01 ~]# netstat -anptu | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 11683/mysqld
12、设置MySQL密码登录MySQL
[root@centos01 ~]# mysqladmin -uroot password
New password:
Confirm new password:
[root@centos01 ~]# mysql -uroot -ppwd@123
13、模拟root密码遗忘修改root密码登录
1）停止mysql服务
[root@centos01 ~]# systemctl stop mysqld
2）跳过密码验证禁止网络用户登录
[root@centos01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
3）登录mysql
[root@centos01 ~]# mysql -uroot -pwdw
4）更新用户表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
5）修改root密码
mysql> update mysql.user set password=password('123123') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 06）启动服务
[root@centos01 ~]# systemctl start mysqld
7）登录root用户
[root@centos01 ~]# mysql -uroot -p123123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, 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 8.0，创建自己名字用户设置密码，修改root密码，锁定解锁自己名字密码，模拟root本地主机登录密码遗忘修改密码
1、使用winSCP传输mysql8.0压缩包到centos02
2、安装mysql 8.0
1）解压安装
[root@centos02 ~]# ls
anaconda-ks.cfg mysql-8.0.32-el7-x86_64.tar.gz 模板 图片 下载 桌面
initial-setup-ks.cfg 公共 视频 文档 音乐
[root@centos02 ~]# tar zxf ./mysql-8.0.32-el7-x86_64.tar.gz -C /usr/src/
[root@centos02 ~]# mv /usr/src/mysql-8.0.32-el7-x86_64/ /usr/local/mysql
2）优化Mysql命令
[root@centos02 ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@centos02 ~]# source /etc/profile
3）检查mysql版本
[root@centos02 ~]# mysql -V
mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
3、配置Mysql配置文件管理用户
1）创建管理用户和组
[root@centos02 ~]# groupadd mysql
[root@centos02 ~]# useradd -M -s /sbin/nologin -g mysql mysql
2）创建数据存储目录修改所属用户和组
[root@centos02 ~]# mkdir /usr/local/mysql/data
[root@centos02 ~]# chown -R mysql:mysql /usr/local/mysql/
3）修改my.conf配置文件
[root@centos02 ~]# vim /etc/my.cnf
[mysqld]
user=mysql //管理服务用户
basedir=/usr/local/mysql/ //mysql安装目录
datadir=/usr/local/mysql/data //mysql数据目录
socket=/tmp/mysql.sock //服务进程
[mysql]
socket=/tmp/mysql.sock //服务进程
4、配置Mysql服务
1）生成服务控制文件
[root@centos02 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
2）添加执行权限设置开机自动启动
[root@centos02 ~]# chmod +x /etc/init.d/mysqld
[root@centos02 ~]# chkconfig --add mysqld
[root@centos02 ~]# chkconfig --level 35 mysqld on
3）初始化Mysql
[root@centos02 ~]# mysqld --initialize-insecure
2023-02-24T16:16:21.115593Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.32) initializing of server in progress as process 2079
2023-02-24T16:16:21.121630Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-02-24T16:16:22.189335Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-02-24T16:16:23.254304Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
4）启动mysql服务
[root@centos02 ~]# systemctl start mysqld
[root@centos02 ~]# netstat -anptu | grep mysqld
tcp6 0 0 :::33060 :::* LISTEN 2283/mysqld
tcp6 0 0 :::3306 :::* LISTEN 2283/mysqld
5、密码管理
1）mysql数据库设置密码
[root@centos02 ~]# mysqladmin -uroot password
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety
2）修改root密码
mysql> alter user root@'localhost' identified by 'pwd@1234';
Query OK, 0 rows affected (0.00 sec)
3）验证密码修改
[root@centos02 ~]# mysql -uroot -ppwd@1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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.
6、创建huyan用户设置登录密码
mysql> create user 'huyan'@'localhost' identified by 'pwd@123';
Query OK, 0 rows affected (0.01 sec)
7、锁定用户
mysql> alter user 'huyan'@'localhost' account lock;
Query OK, 0 rows affected (0.00 sec)
8、查看用户是否被锁定
mysql> select User,Host,account_locked from mysql.user;
+------------------+-----------+----------------+
| User | Host | account_locked |
+------------------+-----------+----------------+
| huyan | localhost | Y |
| mysql.infoschema | localhost | Y |
| mysql.session | localhost | Y |
| mysql.sys | localhost | Y |
| root | localhost | N |
+------------------+-----------+----------------+
5 rows in set (0.00 sec)
9、解锁huyan用户
mysql> alter user 'huyan'@'localhost' account unlock;
Query OK, 0 rows affected (0.00 sec)
10、查看用户是否被解锁
mysql> select User,Host,account_locked from mysql.user;
+------------------+-----------+----------------+
| User | Host | account_locked |
+------------------+-----------+----------------+
| huyan | localhost | N |
| mysql.infoschema | localhost | Y |
| mysql.session | localhost | Y |
| mysql.sys | localhost | Y |
| root | localhost | N |
+------------------+-----------+----------------+
5 rows in set (0.00 sec)
11、模拟root本地主机登录密码遗忘修改密码
1）停止mysql服务
root@centos02 ~]# systemctl stop mysqld
2）跳过密码验证禁止网络用户登录
[root@centos02 ~]# mysqld_safe --skip-grant-tables --skip-networking &
[1] 2481
[root@centos02 ~]# 2023-02-24T16:27:56.990261Z mysqld_safe Logging to '/usr/local/mysql/data/centos02.err'.
2023-02-24T16:27:57.006207Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
3）登录mysql
[root@centos02 ~]# mysql -uroot -pwdad
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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.
4）更新用户表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
5）修改root密码
mysql> alter user root@'localhost' identified by '123123';
Query OK, 0 rows affected (0.00 sec)
6）启动服务
[root@centos02 ~]# systemctl start mysqld
[root@centos02 ~]# mysql -uroot -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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.