一、Mysql root用户密码忘记

1.用命令编辑/etc/my.cnf配置文件，即：vim /etc/my.cnf 或者 vi /etc/my.cnf

2.在[mysqld]下添加skip-grant-tables，然后保存并退出

3.重启mysql服务：service mysqld restart

4.更改root用户名

重启以后，执行mysql命令进入mysql命令行

5.修改root用户密码

update mysql.user set authentication_string=password('root') where user='root'; --5.7版本

flush privileges;

6.把/etc/my.cnf中的skip-grant-tables注释掉，然后重启mysql，即：service mysqld restart

好了，下面就可以用root新的密码登录了！

mysql> prompt session1> --设置名字

prompt session1>show variables like '%autocommit%'; --自动提交

prompt session1>show variables like '%iso%';

prompt session1>set tx_isolation='repeatable-read'; -重复读

prompt session1>start transaction with consistent snapshot; --快照模式

二、MySQL添加新用户、创建数据库、为新用户分配权限

create user 'test1' @ 'localhost' identified by '123456' ; --只能本地登录

select a. User ,a.Host,a.password_expired,a.authentication_string from mysql. user a；

create user 'test2' @ '%' identified by '123456' ; --不限定，都可以登录

flush privileges ; --刷新授权

create database testdb DEFAULT CHARSET utf8 --创建数据库

grant all privileges on `testdb`. * to 'test1' @ 'localhost' identified by '123456' with grant option ;

grant all privileges on `testdb`. * to 'test2' @ '%' identified by '123456' with grant option ;

三、mysql drop数据库

mysql> DROP DATABASE wmp;

四、 mysql binlog日志清理

首先使用ps -ef|grep mysql命令查看

进入到/var/lib/mysql目录，该目录下面有很多mysql-bin文件

mysql>show binary logs;

删除1天前的binlog日志文件

mysql>PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);

还有一个方法就是关闭binlog

vi /etc/my.cnf

注释掉

再重启mysql服务即可

# service mysqld restart --root用户执行

五、在docker中使用yum安装MySQL后，启动MySQL卡住

[root@centos76mysql /]# systemctl status mysqld

● mysqld.service - MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

Active: activating (start) since Wed 2021-02-17 05:38:04 CST; 10min ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Process: 9902 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)

Process: 9887 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

CGroup: /docker/2dea36440adea3fb825010c99928f283526f3d1117fc6a2902f98033826839f5/system.slice/mysqld.service

└─9904 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Feb 17 05:38:04 centos76mysql systemd[1]: Stopped MySQL Server.

Feb 17 05:38:04 centos76mysql systemd[1]: Starting MySQL Server...

Feb 17 05:38:04 centos76mysql systemd[1]: New main PID 9904 does not belong to service, and PID file is not owned ...sing.

Feb 17 05:38:04 centos76mysql systemd[1]: New main PID 9904 does not belong to service, and PID file is not owned ...sing

解决：挂载宿主机 cgroup 目录，启动时加上 -v /sys/fs/cgroup:/sys/fs/cgroup

docker rm -f lhrc76mysql

docker run -d --name lhrc76mysql -h lhrc76mysql \

-p 3306-3406:3306-3406 -p 222:22 \

-v /sys/fs/cgroup:/sys/fs/cgroup \

--privileged=true \

lhrbest/lhrcentos76:5.0 /usr/sbin/init

docker exec -it lhrc76mysql bash

六、修改密码报错如下

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> SHOW VARIABLES LIKE 'validate_password%';

+--------------------------------------+--------+

| Variable_name | Value |

+--------------------------------------+--------+

| validate_password_check_user_name | OFF |

| validate_password_dictionary_file | |

| validate_password_length | 8 |

| validate_password_mixed_case_count | 1 |

| validate_password_number_count | 1 |

| validate_password_policy | MEDIUM |

| validate_password_special_char_count | 1 |

+--------------------------------------+--------+

7 rows in set (0.00 sec)

密码的长度是由validate_password_length决定的,但是可以通过以下命令修改

set global validate_password_length=1; --长度五限制

validate_password_policy决定密码的验证策略,默认等级为MEDIUM(中等),可通过以下命令修改为LOW(低)，即关闭密码复杂性策略

set global validate_password_policy=0; --取消负复杂度

七、密码修改

#mysql8

ALTER USER 'test'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '新密码';

其他

grant all privileges on *.* to 'root'@'localhost' identified by 'root' with grant option;

grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;

flush privileges;

ITPub博客

Mysql 常见故障处理

最新文章