cento7 安装mysql8教程-优快云博客

本文链接：https://blog.youkuaiyun.com/zaq12346zaqwsx/article/details/88648195

#mysql8 centos7

mysql官网下载mysql-server的rpm安装包

[root@greenvm-wys1946v1 mysql]# ll
total 28
-rw-r--r--. 1 root root 25820 Jan 16 14:18 mysql80-community-release-el7-1.noarch.rpm
[root@greenvm-wys1946v1 mysql]# pwd
/mysql

开始执行安装

[root@greenvm-wys1946v1 mysql]# rpm -ivh mysql80-community-release-el7-1.noarch.rpm

检查mysql源是否安装成功

[root@greenvm-wys1946v1 mysql]# yum repolist enabled | grep "mysql.*-community.*"
mysql-connectors-community/x86_64       MySQL Connectors Community            74
mysql-tools-community/x86_64            MySQL Tools Community                 74
mysql80-community/x86_64                MySQL 8.0 Community Server            49
[root@greenvm-wys1946v1 mysql]#

以上代表源安装成功,开始安装install

[root@greenvm-wys1946v1 mysql]# yum install -y mysql-community-server

启动mysql服务

systemctl start mysqld
或者
service mysqld start

查看mysql的启动状态

systemctl status mysqld
或者
service mysqld status

[root@Campusnetwork mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 三 2019-01-16 22:28:57 CST; 51min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 5739 (mysqld)
   Status: "SERVER_OPERATING"
   CGroup: /system.slice/mysqld.service
           └─5739 /usr/sbin/mysqld

1月 16 22:28:39 Campusnetwork.net.hziee.edu.cn systemd[1]: Starting MySQL Server...
1月 16 22:28:57 Campusnetwork.net.hziee.edu.cn systemd[1]: Started MySQL Server.
[root@Campusnetwork mysql]#

重启mysql

systemctl restart mysqld
或者
service mysqld restart

停止mysql

systemctl stop mysqld
或者
service mysqld stop

设置开机启动

[root@Campusnetwork mysql]# systemctl enable mysqld
[root@Campusnetwork mysql]# systemctl daemon-reload

查看默认密码

/var/log/mysqld.log

查看mysql端口号

mysql> show variables like '%port%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| large_files_support      | ON    |
| mysqlx_port              | 33060 |
| mysqlx_port_open_timeout | 0     |
| port                     | 3306  |
| report_host              |       |
| report_password          |       |
| report_port              | 3306  |
| report_user              |       |
| require_secure_transport | OFF   |
+--------------------------+-------+
9 rows in set (0.00 sec)

查看mysql字符编码

mysql> show variables like '%character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)

查看密码策略

mysql> show variables like '%password%';
+----------------------------------------------+-----------------+
| Variable_name                                | Value           |
+----------------------------------------------+-----------------+
| caching_sha2_password_auto_generate_rsa_keys | ON              |
| caching_sha2_password_private_key_path       | private_key.pem |
| caching_sha2_password_public_key_path        | public_key.pem  |
| default_password_lifetime                    | 0               |
| disconnect_on_expired_password               | ON              |
| mysql_native_password_proxy_users            | OFF             |
| password_history                             | 0               |
| password_require_current                     | OFF             |
| password_reuse_interval                      | 0               |
| report_password                              |                 |
| sha256_password_auto_generate_rsa_keys       | ON              |
| sha256_password_private_key_path             | private_key.pem |
| sha256_password_proxy_users                  | OFF             |
| sha256_password_public_key_path              | public_key.pem  |
| validate_password.check_user_name            | ON              |
| 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               |
+----------------------------------------------+-----------------+
21 rows in set (0.01 sec)

密码策略介绍

validate_password_policy：密码策略，默认为MEDIUM策略
validate_password_dictionary_file：密码策略文件，策略为STRONG才需要
validate_password_length：密码最少长度
validate_password_mixed_case_count：大小写字符长度，至少1个
validate_password_number_count ：数字至少1个
validate_password_special_char_count：特殊字符至少1个
上述参数是默认策略MEDIUM的密码检查规则。
mysql主要有三种密码策略

Policy	DESC
0 or LOW	Length
1 or MEDIUM	Length;numeric;lowercase/uppercase and special character
2 or STRONG	Length;numeric;lowercase/uppercase and special character and dictionary file

更改密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'shiqi2019';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'shiqi2019@';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Shiqi2019@';
Query OK, 0 rows affected (0.01 sec)

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

远程连接

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> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> select user,host,plugin,authentication_string from user;
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| user             | host      | plugin                | authentication_string                                                  |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| root             | %         | caching_sha2_password | $A$005$hocX4U2@R%bkw_	Z:YB3G0wW9b9u6aor.NLk.yL5pqK.3aqc7w9DKgoUu4uF2 |
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> alter user 'root'@'%' identified with mysql_native_password by 'Shiqi2019@';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password by 'Shiqi2019@';
Query OK, 0 rows affected (0.04 sec)

mysql> select user,host,plugin,authentication_string from user;
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| user             | host      | plugin                | authentication_string                                                  |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| root             | %         | mysql_native_password | *1C010A5F5FFBEFCCBF18420A68EEBCF0845A9A75                              |
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

部分参数查询命令

#查询mysql最大连接数设置
show global variables like 'max_conn%';
SELECT @@MAX_CONNECTIONS AS 'Max Connections';

# 查看最大链接数
show global status like 'Max_used_connections';

# 查看慢查询日志是否开启以及日志位置
show variables like 'slow_query%';

# 查看慢查询日志超时记录时间
show variables like 'long_query_time';

# 查看链接创建以及现在正在链接数
show status like 'Threads%';

# 查看数据库当前链接
show processlist;

# 查看数据库配置
show variables like '%quer%';！