yum安装mysql8.0

IT
IT
IT
335
文章
1
评论
2020年5月15日13:51:27 评论 1,266 8998字阅读29分59秒

查看系统环境

yum安装mysql8.0

[root@localhost ~]# getconf LONG_BIT
64
[root@localhost ~]# cat /etc/os-release
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

[root@localhost ~]# cat /etc/redhat-release 
CentOS Linux release 7.3.1611 (Core)

安装

查看系统中有没有自带的mysql内容

[root@localhost ~]# find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql
/usr/lib64/mysql
/usr/share/mysql

删除

[root@localhost ~]# find / -name mysql|xargs rm -rf;

开始安装

[root@gitlab ~]# yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm
[root@gitlab ~]# yum install mysql-community-server

启动mysql

[root@gitlab ~]# systemctl start mysqld
[root@gitlab ~]# systemctl enable mysqld

查看默认设置的密码

[root@localhost ~]# grep "A temporary password" /var/log/mysqld.log
2019-04-30T02:54:56.357226Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ic.Hm=4e=eep

登陆并修改密码

[root@gitlab ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21

Copyright (c) 2000, 2020, 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> select version();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password.length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

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

mysql> exit
Bye

退出, 重新使用新密码登录mysql

[root@gitlab ~]# mysql -p123456
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.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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> select version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)
  • 查看mysql连接的授权信息
mysql> select host,user,authentication_string from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
| host      | user             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | $A$005$Z[e*_Qiq9,o69
                                                     sL^mrraTuX/DnE4uIupdBjL61nevs38bZ.a7MrJww0FMV2 |
+-----------+------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)
  • mysql8.0修改用户密码命令
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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

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

设置允许远程登录和开启防火墙

Mysql默认不允许远程登录,所以需要开启远程访问权限

mysql> select host,user,authentication_string from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
| host      | user             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | $A$005$Tc)b!)aj%HZV1:twbDcq4KjMid9yAsFiak98MK7ncCUzb4/d67iiRF/pns4 |
+-----------+------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

默认都是localhost,如果想要远程登录, 则需要进行update更新下root账号的权限

mysql> update mysql.user set host='%' where user="root";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,authentication_string from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
| host      | user             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| %         | root             | $A$005$Tc)b!)aj%HZV1:twbDcq4KjMid9yAsFiak98MK7ncCUzb4/d67iiRF/pns4 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+-----------+------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)
  • mysql8.0默认编码方式为utf8mb4,因此使用时不需要修改,可使用如下命令查看
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------------------+
| 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/ |
| collation_connection     | utf8mb4_0900_ai_ci             |
| collation_database       | utf8mb4_0900_ai_ci             |
| collation_server         | utf8mb4_0900_ai_ci             |
+--------------------------+--------------------------------+
11 rows in set (0.00 sec)
  • 远程登陆验证

yum安装mysql8.0

部分参数配置查询命令

  • 查询mysql最大连接数设置
mysql> show global variables like 'max_conn%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT @@MAX_CONNECTIONS AS 'Max Connections';
+-----------------+
| Max Connections |
+-----------------+
|             151 |
+-----------------+
1 row in set (0.00 sec)
  • 查看最大链接数
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 2     |
+----------------------+-------+
1 row in set (0.00 sec)
  • 查看慢查询日志是否开启以及日志位置
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | OFF                            |
| slow_query_log_file | /var/lib/mysql/gitlab-slow.log |
+---------------------+--------------------------------+
2 rows in set (0.01 sec)
  • 查看慢查询日志超时记录时间
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
  •  查看链接创建以及现在正在链接数
mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 2     |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.00 sec)
  • 查看数据库当前链接
mysql> show processlist;
+----+-----------------+----------------------+------+---------+------+------------------------+------------------+
| Id | User            | Host                 | db   | Command | Time | State                  | Info             |
+----+-----------------+----------------------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost            | NULL | Daemon  | 1096 | Waiting on empty queue | NULL             |
| 14 | root            | 192.168.31.149:54340 | NULL | Sleep   |  465 |                        | NULL             |
| 15 | root            | localhost            | NULL | Query   |    0 | starting               | show processlist |
+----+-----------------+----------------------+------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)
  • 查看数据库配置
mysql> show variables like '%quer%'; 
+----------------------------------------+--------------------------------+
| Variable_name                          | Value                          |
+----------------------------------------+--------------------------------+
| binlog_rows_query_log_events           | OFF                            |
| ft_query_expansion_limit               | 20                             |
| have_query_cache                       | NO                             |
| log_queries_not_using_indexes          | OFF                            |
| log_throttle_queries_not_using_indexes | 0                              |
| long_query_time                        | 10.000000                      |
| query_alloc_block_size                 | 8192                           |
| query_prealloc_size                    | 8192                           |
| slow_query_log                         | OFF                            |
| slow_query_log_file                    | /var/lib/mysql/gitlab-slow.log |
+----------------------------------------+--------------------------------+
10 rows in set (0.00 sec)
继续阅读
  • 我的QQ
  • QQ扫一扫
  • weinxin
  • 我的头条
  • 头条扫一扫
  • weinxin
Mysql 最后更新:2020-7-17
IT
  • 本文由 发表于 2020年5月15日13:51:27
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
二进制安装mysql8.0 Mysql

二进制安装mysql8.0

MySQL下载地址如下 http://dev.mysql.com/downloads/mysql/ 卸载mariadb # yum -y remove mariadb-libs 创建目录和用户 # m...
XtraBackup Mysql

XtraBackup

XtraBackup介绍 Percona XtraBackup是一款基于MySQL的服务器的开源热备份实用程序,在备份过程中不会锁定数据库。它可以备份来自MySQL5.1,5.5,5.6和5.7服务器...
docker安装mysql8.0 Docker

docker安装mysql8.0

创建存储目录 # mkdir -p /opt/data/mysql/data /opt/data/mysql/conf 基本使用 创建区分大小写文件 # cd /opt/data/mysql/conf...
mysql不区分大小写 Mysql

mysql不区分大小写

配置很简单 # cat my.cnf lower_case_table_names = 1 有个前提是 修改配置文件之前,数据库里面的表不能为大写,想办法转换成小写即可。 配置完之后重启mysql即可...