1、对于码农来说,有时候可能会需要实时查看自己的程序执行的sql语句以方便排查问题调试程序,实时监控查看mysql执行的sql语句,下面主要介绍两种方法:

1)启用记录sql的日志文件:

查看是否开启日志记录sql, general_log的值为off,则没有开启
mysql> SHOW VARIABLES LIKE "general_log%";
+------------------+----------------------------------+
| Variable_name    | Value                            |
+------------------+----------------------------------+
| general_log      | OFF                              |
| general_log_file | /var/lib/mysql/VM_0_8_centos.log |
+------------------+----------------------------------+
2 rows in set (0.06 sec)

执行下面命令可临时开启sql记录功能
mysql> SET GLOBAL general_log = 'ON';
mysql> SET GLOBAL general_log_file = '/topath/sql.log';

如果想永久开启记录sql功能,则需要修改mysql的配置文件my.cnf,重启mysql使之生效。

general_log = 1
general_log_file = /topath/sql.log

2)在客户端服务器上使用抓包命令实时监控sql语句(推荐),127.0.0.1和3306分别替换成自己的mysql地址和端口号,-i参数指定网络接口名称,换成自己的。

[root@VM_0_8_centos ~]# tcpdump -s 0 -l -w - dst 127.0.0.1 and port 3306 -i eth0 |strings
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 262144 bytes

2、修改mysql密码策略,mysql8默认开启了密码安全检查插件,目前很多客户端都不支持此插件,需要修改密码验证策略,它要求要求密码必须包含:数字、大小写字母和特殊符号,且长度不能少于8个字符,否则会提示错误:ERROR 1819 (HY000): Your password does not satisfy the current policy requirements,可按照下面步骤修改密码验证策略:

修改mysql配置文件my.cnf
[root@VM_0_8_centos ~]# vim /etc/my.cnf
找到关键字default_authentication_plugin这一行,修改它的值为mysql_native_password

default_authentication_plugin = mysql_native_password

登录mysql终端,执行sql语句ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘YourNewPassword’。

[root@VM_0_8_centos ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15516
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewPassword';
#刷新权限
mysql> FLUSH PRIVILEGES;

如果报错误ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@’localhost’,说明@后面的登录地址不正确,然后执行进行下面操作:

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> select Host,User from user where User='root';
+------+------+
| Host | User |
+------+------+
| %    | root |
+------+------+
1 row in set (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'YourNewPassword';
#刷新权限
mysql> FLUSH PRIVILEGES;

3、对于很多情况我们需要远程连接mysql进行操作,而不是每次登录到mysql所在的服务器上去操作,下面介绍下远程连接的配置:

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
#为了安全起见不要设置root用户远程连接
mysql> CREATE USER 'user'@'%' IDENTIFIED BY 'userPassword';
#授予user用户dbname数据库的所有权限
mysql> grant all on dbname.* to 'user'@'%';

4、msyql8.0忘记密码以后如何修改密码步骤如下:

编辑mysql配置文件:

[root@VM_0_8_centos ~]# vim /etc/my.cnf

最末尾添加一行skip-grant-tables,保存退出

重启mysql:

[root@VM_0_8_centos ~]# systemctl restart mysqld.service

如果重启失败执行下面命令强制杀死所有mysql进程:

[root@VM_0_8_centos ~]# ps -ef | grep mysql | awk '{print $2}' | xargs kill -9

执行命令mysql -u root -p直接回车进入mysql

[root@VM_0_8_centos ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16306
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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>

修改root密码为空

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> update user set authentication_string='' where user='root';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

退出mysql,修改mysql配置文件/etc/my.cnf,删掉skip-grant-tables,再次用命令systemctl restart mysqld.service重启mysql

登录mysql:

#直接回车即可进入mysql
[root@VM_0_8_centos ~]# mysql -u root -p

修改密码:

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' identified by 'yourPassword';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;

大功告成!

发表评论

电子邮件地址不会被公开。 必填项已用*标注