查看系统环境
[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)
- 远程登陆验证
部分参数配置查询命令
- 查询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扫一扫
-
- 我的头条
- 头条扫一扫
-
评论