MySQL/MariaDB重置root密码、配置慢查询日志
2019-10-22 20:12:11 Asn_Fy 阅读数 1436更多
知道密码的情况下修改root密码：
[root@linux ~]# mysqladmin -uroot -p123456 password "test123"
#将原密码123456修改为test123
不知道root密码的情况下修改密码：
1.编辑/etc/my.cnf：
[root@linux ~]# vi /etc/my.cnf
2.在[mysqld]下添加skip-grant：
3.重启mysql后即可通过mysql -uroot方式登录：
[root@linux ~]# service mysqld restart
Restarting mysqld (via systemctl): [ 确定 ]
[root@linux ~]# mysql -uroot
MariaDB [(none)]>
4.进入mysql库修改user表的authentication_string字段即可：
MariaDB [(none)]> use mysql;
Database changed
MariaDB [mysql]> update user set authentication_string=password("123456") where user='root';
Query OK, 4 rows affected (0.001 sec)
Rows matched: 4 Changed: 4 Warnings: 0
#将root用户的密码修改为123456，如需指定host，加上and host='xxx’即可
注意：
在较高版本的mysql/mariaDB中，root用户密码存放于authentication_string字段，普通用户存放于Password字段
较老版本的mysql/mariaDB中，所有密码存放于Password字段
5.退出mysql，删除/etc/my.cnf中添加的skip-grant重启mysql服务即可登录：
[root@linux ~]# mysql -uroot -p123456
MariaDB [(none)]>
MySQL/MariaDB慢查询日志配置：
#慢查询日志用于分析数据库的瓶颈点
查看慢查询日志开关：
MariaDB [(none)]> show variables like 'slow_query%';
+---------------------+----------------+
| Variable_name | Value |
+---------------------+----------------+
| slow_query_log | OFF |
| slow_query_log_file | linux-slow.log |
+---------------------+----------------+
2 rows in set (0.001 sec)
#慢查询日志状态为关，慢查询日志文件名：linux-slow.log
查看慢查询日志路径：
MariaDB [(none)]> show variables like 'datadir';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| datadir | /data/mysql/ |
+---------------+--------------+
1 row in set (0.001 sec)
查看慢查询日志超时时间：
MariaDB [(none)]> show variables like 'long%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.001 sec)
#执行超过10秒是sql语句将记录到慢查询日志中
编辑etc/my.cnf添加如下内容配置mysql慢查询日志：
slow_query_log = ON
slow_query_log_file = /data/mysql/linux-slow.log
long_query_time = 3
#定义慢查询日志状态为ON以及日志路径，超时时间3秒
重启mysql服务：
[root@linux ~]# service mysqld restart
Restarting mysqld (via systemctl): [ 确定 ]
测试：
MariaDB [(none)]> select sleep(6);
+----------+
| sleep(6) |
+----------+
| 0 |
+----------+
1 row in set (6.008 sec)
查看慢查询日志：
[root@linux ~]# cat /data/mysql/linux-slow.log
/usr/local/mysql/bin/mysqld, Version: 10.3.18-MariaDB-log (MariaDB Server). started with:
Tcp port: 0 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 191022 21:27:04
# User@Host: root[root] @ localhost []
# Thread_id: 10 Schema: QC_hit: No
# Query_time: 6.007324 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
# Rows_affected: 0 Bytes_sent: 63
SET timestamp=1571750824;
select sleep(6);
#日志会记录执行时间Query_time：6秒和执行的语句：select sleep(6);
查看mysql队列：
MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 11 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
6 rows in set (0.000 sec)
#查看正在执行的sql队列，类似在操作系统中查看进程，info列显示具体sql语句，如显示不完整可使用 show full processlist;
不登录数据库查看sql队列加上-e参数：
mysql -uroot -p123456 -e "show processlist";
