侧边栏壁纸
博主头像
WinJay博主等级

博观而约取 厚积而薄发

  • 累计撰写 333 篇文章
  • 累计创建 168 个标签
  • 累计收到 2 条评论

目 录CONTENT

文章目录

Docker MySQL8修改root密码方法及配置远程连接

WinJay
2022-11-18 / 0 评论 / 0 点赞 / 4 阅读 / 1,558 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-11-18,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

Docker MySQL8修改root密码方法

image-20221118170737637

img

1. 启动脚本信息

[root@mysql]# cat docker_start_Mysql_WinJay.sh 
docker rm -f MySQL8.0_DB
docker container run -d \
    --volume /etc/localtime:/etc/localtime:ro \
    --volume `pwd`/data:/var/lib/mysql \
    --volume `pwd`/conf:/etc/mysql/conf.d \
    --user root \
    --name MySQL8.0_DB \
    --restart always \
    -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=Qwe!1@2#3 \
    mysql \
    --character-set-server=utf8mb4 \
    --collation-server=utf8mb4_unicode_ci \
    --explicit_defaults_for_timestamp=true \
    --lower_case_table_names=1 \
    --max_allowed_packet=128M \
    --default-authentication-plugin=mysql_native_password

2. 持久化数据信息

image-20220920091129150

重置密码

1. 修改配置文件

# 二进制安装的是修改my.cnf文件,在/etc/my.cnf下,具体根据自己系统查找;
# 如果跟我一样是Docker版的MySQL,需要修改对应容器内的/etc/mysql/conf.d/docker.cnf这个文件;
# 由于我已经将‘--volume `pwd`/conf:/etc/mysql/conf.d’持久化出来了,所以在宿主机上直接编辑修改
# 注:配置后只可单点登录,远程无法连接。

vim conf/docker.cnf
  • 在最后新增 skip_grant_tables,完成后如下:
[root@mysql]# cat conf/docker.cnf 
[mysqld]
skip-host-cache
skip-name-resolve
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUT

skip_grant_tables

2. 重启服务

  • 修改完配置文件后需要重新启动MySQL容器。
[root@StorageClass mysql]# ./docker_start_Mysql_WinJay.sh 
MySQL8.0_DB
62c539c2b129d122e7b29366358213e8f1b06ec935a1a3fabbd8151853538960

image-20220920091957542

3. 进入容器重置密码

[root@StorageClass mysql]# docker exec -it MySQL8.0_DB /bin/bash
root@305636cc3283:/# mysql -u root -p
Enter password: 		#此处可以直接回车即可进入;跳过mysql权限验证
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> 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'@'localhost' IDENTIFIED BY '123456';  
	# 由于权限认证没有重新加载,执行修改密码语句后会报错。
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;	#重新加载权限
Query OK, 0 rows affected (0.03 sec)


mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
	# 再次修改root账户密码
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> exit # 重置完成,退出MySQL
Bye
root@62c539c2b129:/# exit # 重置完成,退出容器
exit

image-20220920092102151

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

# 将root的密码修改为:123456

image-20220920095029275

4.恢复配置文件

  • 将步骤1中新增的 skip_grant_tables字段注释或删除掉,然后再次重启容器。
[root@StorageClass mysql]# ./docker_start_Mysql_WinJay.sh 
MySQL8.0_DB
305636cc3283a45d8b6791cd0fd23e0701b93f68b33f447ef40d5ffd044929d9

image-20220920093951441

5. 再次登录验证测试

[root@StorageClass mysql]# docker exec -it MySQL8.0_DB /bin/bash
root@305636cc3283:/# mysql -u root -p
Enter password: 		# 此处仍然使用空密码登录测试,下面给出报错。
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@305636cc3283:/# mysql -u root -p
Enter password: 		# 此处使用修改的密码'123456'登录测试成功
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| ams                |
| information_schema |
| lit_inf_sys_db     |
| mysql              |
| nacos              |
| performance_schema |
| sys                |
| xxl_job            |
+--------------------+
8 rows in set (0.01 sec)

mysql> 

image-20220920094250050

6. 总结

本文在mysql8和mysql5.7两个版本均成功测试,相应的mysql安装目录和my.ini配置文件目录请务必根据自己安装情况进行更改。例如在跳过权限认证时,如果是在mysql5.7版本的默认安装下,应该修改为“mysqld --defaults-file=“C:\ProgramData\MySQL\MySQL Server 5.7\my.ini” --console --skip-grant-tables --shared-memory”。

配置远程连接

1. 查看用户信息

mysql> select host, user, authentication_string, plugin from user;

image-20220920103727602

2. 删除root用户远程连接

  • 由于当前配置的root用户的远程连接密码并不清楚,所以只能重新授权开放,需要先删除现有的信息;
  • 运行以下 SQL 脚本,以删除“root”用户从远程主机的所有访问权限
mysql> DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
  • 删除后重新对比,看到已经删除成功了。

image-20220920111019964

3. 更新授权

mysql> update user set host = '%' where user = 'root';

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
  • 再次对比查看:条数不变,但root用户已经授权完成。

image-20220920111221453

4. 远程连接验证

image-20220920112657383

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Qwe!1@2#3' WITH GRANT OPTION;

更新表

select id,uid,job_num,account_name,card_no,bank_name from f_bank_account;

update f_bank_account set card_no=8888888888888888;



select id,username,email,id_card_no,phone,real_name from user;
#  更新多个字段,使用','号分隔,并非使用and语句。
update user set email='test@mail.com',id_card_no=110108888888888888,phone=18888888888;




show table status from confluence;

SELECT TABLE_NAME,TABLE_COLLATION FROM information_schema.TABLES;

show create database mysql;

show full columns from user;


---
改邮箱、电话、身份证号 密码

select id, username, job_num, email, phone,id_card_no  from user;

select id, username, email from user;



UPDATE user SET email='api_admin@baidu.com' WHERE email='Uat_api_admin@baidu.com';

# mysql:批量更新某个列值,再原列值上统一增加固定文本值
UPDATE user SET email=CONCAT('Uat_',email ) WHERE email='api_admin@baidu.com';


select id, username, email from user;




UPDATE user SET phone=18888888888;
select id, username, phone from user;

UPDATE user SET id_card_no=101101200001008888;
select id, username, id_card_no from user order by id limit 1, 50;

select id,username,password from user order by id limit 1, 50;
UPDATE user SET password='fcea920f7412b5da7be0cf42b8c93759';



select id,username,password from user where username='admin';
----



根据ID排序取前500行数据
select job_num, username, id, email from user order by id limit 1, 500;



改银行卡号
---

desc f_bank_account;

select id,uid,bank_name,card_no from f_bank_account;


UPDATE f_bank_account SET card_no=8888888888888888;
0

评论区