mariadb重置密码
- 修改一般用户的密码
MariaDB [mysql]> use mysql;
MariaDB [mysql]> update user set authentication_string=password("bbs?DXG123") where User='bbs' and host='localhost';
- 修改root密码(知道原来root密码的情况)
mysqladmin -uroot -proot password "root"
- 修改root密码(不知道原来root密码的情况)
[root@linux2019 ~]# vi /etc/my.cnf
skip-grant
[root@linux2019 ~]# /etc/init.d/mysqld restart
[root@linux2019 ~]# mysql -uroot #此时不需要输入密码可以直接登陆mariadb
MariaDB [(none)]> use mysql;
MariaDB [mysql]> update user set authentication_string=password("root") where User='root' and host='localhost';
MariaDB [mysql]> exit;
[root@linux2019 ~]# vi /etc/my.cnf #删除之前增加的配置
#skip-grant
[root@linux2019 ~]# /etc/init.d/mysqld restart
PS:mysql/mariadb在新版本里面把密码字段存到了authentication_string字段里,老版本还是在password字段里
MariaDB慢查询日志
类似于php-fpm配置里面的slow日志,可以帮助分析mariadb的瓶颈
配置
#先查看当前配置情况
MariaDB [(none)]> show variables like 'slow%';
+---------------------+--------------------+
| Variable_name | Value |
+---------------------+--------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | linux2019-slow.log |
+---------------------+--------------------+
3 rows in set (0.002 sec)
MariaDB [(none)]> show variables like 'datadir%';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| datadir | /data/mysql/ |
+---------------+--------------+
1 row in set (0.002 sec)
MariaDB [(none)]> show variables like 'long%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.002 sec)
[root@linux2019 ~]# vi /etc/my.cnf
slow_query_log = ON
slow_query_log_file = /data/mysql/linux2019-slow.log
long_query_time = 2
[root@linux2019 ~]# /etc/init.d/mysqld restart
测试
MariaDB [(none)]> select sleep(5); #模拟慢查询
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.001 sec)
MariaDB [(none)]> exit
Bye
[root@linux2019 ~]# cat /data/mysql/linux2019
linux2019.pid linux2019-slow.log
[root@linux2019 ~]# cat /data/mysql/linux2019-slow.log
/usr/local/mysql/bin/mysqld, Version: 10.3.11-MariaDB-log (MariaDB Server). started with:
Tcp port: 0 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 190214 11:46:40
# User@Host: root[root] @ localhost []
# Thread_id: 9 Schema: QC_hit: No
# Query_time: 5.000585 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
# Rows_affected: 0 Bytes_sent: 63
SET timestamp=1550116000;
select sleep(5);
PS
- show processlist; #查看查询队列
- show full processlist; #详细显示
- mysql -uroot -proot -e “show processlist” #-e选项可以让sql语句直接在linux系统执行,不用登陆到数据库