mysql-5.7.30安装配置

1. MySQL软件下载

下载地址：
http://mysql.mirror.kangaroot.net/Downloads/MySQL-5.7/
http://mirrors.163.com/mysql/Downloads/MySQL-5.7/

wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-community-client-5.7.26-1.el7.x86_64.rpm
wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-community-common-5.7.26-1.el7.x86_64.rpm
wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-community-devel-5.7.26-1.el7.x86_64.rpm
wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-community-libs-5.7.26-1.el7.x86_64.rpm
wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm
wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-community-server-5.7.30-1.el7.x86_64.rpm

2. MySQL安装

yum localinstall *.rpm

3. MySQL服务启动

语法：
systemctl start | stop | restart | status mysqld

# 启动MySQL服务，并将服务加入开机启动
systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld

4. 登录并配置MySQL

说明：mysql在安装完成，启动mysqld服务后，MySQL会自动生成一个随机的root用户密码，可以到日志里面查看。

[root@cn-prom ~]# vim /var/log/mysqld.log

2020-09-16T01:48:30.469772Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-09-16T01:48:30.760416Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-09-16T01:48:30.809246Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-09-16T01:48:30.868591Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b90cb3b5-f7be-11ea-8843-00505687264b.
2020-09-16T01:48:30.869372Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-09-16T01:48:31.540430Z 0 [Warning] CA certificate ca.pem is self signed.
2020-09-16T01:48:31.716721Z 1 [Note] A temporary password is generated for root@localhost: l5s;PyK%(odX
2020-09-16T01:48:34.315475Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-09-16T01:48:34.317738Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.30) starting as process 14208 ...

可以看到类似如下信息：
2020-03-03T06:24:40.573789Z 1 [Note] A temporary password is generated for root@localhost: l5s;PyK%(odX

说明：用该临时密码登录mysql后，必须要先修改密码

[root@mydb-server001 opt]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.26

Copyright (c) 2000, 2019, 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> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password = PASSWORD('cbMiu3@db.com');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

说明：如果不符合MySQL密码策略要求，会出现上述错误。
MySQL密码复杂度配置是通过参数validate_password_policy来控制的，你可以去修改，但是建议你不要修改（如果要修改，参考下文描述）。
validate_password_policy 有以下取值：

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

Policy Tests Performed
0 or LOW Length
1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file
默认是1，即MEDIUM，所以设置MySQL的密码必须不小于8位，且必须含有数字，小写或大写字母，特殊字符。

5. 修改mysql密码

mysql> set password = PASSWORD('NMm#t87TO2JL&Zq2');
mysql> set password = PASSWORD('cbMiu936@mydb.com');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

6. 设置mysql的中文编码支持

# 修改/etc/my.cnf
vim /etc/my.cnf

在[mysqld]中添加参数，使得mariadb服务端支持中文
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

character_set_server = utf8
collation-server = utf8_bin

# 重启mariadb服务，读取my.cnf新配置
systemctl restart mysqld

# 登录数据库，查看字符编码

mysql -uroot -p
输入 \s 查看编码

7. mysql常用命令

desc              #查看表结构
create database   #数据库名
create table      #表名

# 查看如何创建db的
show create  database  #库名

# 查看如何创建table结构的
show create table 表名;

# 修改mysql的密码
set password = PASSWORD('mysqlrootuser686');

# 创建mysql的普通用户，默认权限非常低
create user mcb@'%' identified by 'changbin.miao';

# 查询mysql数据库中的用户信息
use mysql;
select host,user,password  from user;

--模糊查询匹配
show status like 'Thread_%';
SHOW VARIABLES LIKE 'validate_password%';

如果我们的show status语句中不包含统计范围关键字，则默认统计范围为SESSION，也就是只统计当前连接的状态信息。
如果我们需要查询自当前MySQL启动后所有连接执行的SELECT语句总数，我们可以执行如下语句：
show global status like 'com_select';

--查看MySQL本次启动后的运行时间(单位：秒)
show status like 'uptime';

--查看select语句的执行数
show [global] status like 'com_select';

--查看insert语句的执行数
show [global] status like 'com_insert';

--查看update语句的执行数
show [global] status like 'com_update';

--查看delete语句的执行数
show [global] status like 'com_delete';

--查看试图连接到MySQL(不管是否连接成功)的连接数
show status like 'connections';

--查看线程缓存内的线程的数量。
show status like 'threads_cached';

--查看当前打开的连接的数量。
show status like 'threads_connected';

--查看当前打开的连接的数量。
show status like 'threads_connected';

--查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。
show status like 'threads_created';

--查看激活的(非睡眠状态)线程数。
show status like 'threads_running';

--查看立即获得的表的锁的次数。
show status like 'table_locks_immediate';

--查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
show status like 'table_locks_waited';

--查看创建时间超过slow_launch_time秒的线程数。
show status like 'slow_launch_threads';

--查看查询时间超过long_query_time秒的查询的个数。
show status like 'slow_queries';

8. 给用户添加权限

# 对所有库和所有表授权所有权限
grant all privileges on *.* to 账户@主机名
# 给mcb用户授予所有权限
grant all privileges on *.* to mcb@'%';
# 刷新授权表
flush privileges;

9. 授予远程登录权限

# 给apollo用户授予所有权限
grant all privileges on *.* to mcb@'%';
# 给与root权限授予远程登录的命令
# centos这是密码随意设置
grant all privileges on *.* to root@'%' identified by 'centos';
# 此时可以在windows登录linux的数据库
# 连接服务器的mysql
mysql -umcb -p -h 服务器的地址