【第九课】MriaDB密码重置和慢查询日志
1、如何进行修改MariaDB的密码
记得root密码的修改方式：
[root@localhost ~]# mysqladmin -uroot -p123456 password "123123"
[root@localhost ~]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.1.20-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> quit
Bye
忘记root密码的修改方式
[root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld] #在mysqld段中增加skip-grant-tables，跳过授权表
server_id=1111
log-bin=mysql-bin
skip-grant-tables
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
[root@localhost ~]# systemctl restart mariadb #重启mariadb
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.20-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> 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
MariaDB [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(80) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| is_role | enum('N','Y') | NO | | N | |
| default_role | char(80) | NO | | | |
| max_statement_time | decimal(12,6) | NO | | 0.000000 | |
+------------------------+-----------------------------------+------+-----+----------+-------+
46 rows in set (0.00 sec)
MariaDB [mysql]> update user set password=password("123456") where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 3 Changed: 1 Warnings: 0
MariaDB [mysql]> quit;
Bye
[root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf
删除/etc/my.cnf里面的skip-grant, 重启服务
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql -uroot -p
Enter password: 123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.20-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> quit;
Bye
2、Mariadb的慢查询日志
为什么要配置慢查询日志？
其目的是为了帮助我们分析MariaDB的瓶颈点，或者说是业务的瓶颈点。任何一个动态网站都会有读取和写入到数据库的操作，如果其中有一个脚本或SQL语句执行特别慢，那么带来的现象可能是用户访问站点时出现卡顿，或者响应较慢。那么为了去分析慢查询，就可以启用慢查询日志的方式。
如何配置慢查询？？
- （1）进入MariaDB里面进行执行：
show variables like "slow%";show variables like "datadir";show variables like "long%";- （2）编辑/etc/my.cnf进行配置
- （3）重启服务
- （4）模拟慢查询
- （5）查看慢查询日志
（1）进入MariaDB里面进行执行
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.1.20-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show variables like "slow%"; #查看慢查询相关的配置
+---------------------+--------------------+
| Variable_name | Value |
+---------------------+--------------------+
| slow_launch_time | 2 | #慢查询运行时间配置
| slow_query_log | OFF | #是否开启慢查询
| slow_query_log_file | localhost-slow.log | #慢查询日志文件名称，存在于datadir中
+---------------------+--------------------+
3 rows in set (0.01 sec)
MariaDB [(none)]> show variables like "datadir"; #查看datadir路径
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show variables like "long%"; #查询时间配置
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
（2）编辑/etc/my.cnf进行配置
[root@localhost ~]# vim /etc/my.cnf #修改my.cnf配置慢查询日志
[mysqld]
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
slow_query_log = ON #开启慢查询记录
slow_query_log_file = /var/lib/mysql/mysql-slow.log #配置慢查询日志文件路径
long_query_time = 2 #配置查询时间超过2s进行记录
（3）重启服务
[root@localhost ~]# systemctl restart mariadb
（4）模拟慢查询
[root@localhost ~]# mysql -uroot -p #重新登录，运行select进行验证记录
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.20-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select sleep(5);
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)
（5）查看慢查询日志
[root@localhost ~]# cat /var/lib/mysql/mysql-slow.log #查看慢查询日志
/usr/libexec/mysqld, Version: 10.1.20-MariaDB (MariaDB Server). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 190218 11:43:05
# User@Host: root[root] @ localhost []
# Thread_id: 3 Schema: QC_hit: No
# Query_time: 5.002032 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
# Rows_affected: 0
SET timestamp=1550461385;
select sleep(5); #提示慢查询的运行指令
同时，还可以使用
show processlist;和show full processlist;进行查看慢查询队列
show processlist;查看所有的队列；可以查看数据库中有些什么查询和查询量的多少，如果查询量超过100都会比较慢，此时就需要进行优化。
show full processlist; 和上面有什么区别呢？如果有1个语句特别长，info列，只会显示一部分，使用这个命令时，就可以显示完整的信息。
MariaDB [(none)]> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
| 3 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)
MariaDB [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------+-----------------------+----------+
| 3 | root | localhost | NULL | Query | 0 | init | show full processlist | 0.000 |
+----+------+-----------+------+---------+------+-------+-----------------------+----------+
1 row in set (0.00 sec)
Don't forget the beginner's mind
