4

Docker MySQL8修改root密码方法

 1 year ago
source link: https://blog.51cto.com/WinJay/5716429
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Docker MySQL8修改root密码方法

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. 持久化数据信息

Docker MySQL8修改root密码方法_mysql

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

Docker MySQL8修改root密码方法_mysql_02

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
Docker MySQL8修改root密码方法_bash_03
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

# 将root的密码修改为：123456
Docker MySQL8修改root密码方法_mysql_04

4.恢复配置文件

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

Docker MySQL8修改root密码方法_bash_05

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>
Docker MySQL8修改root密码方法_bash_06

本文在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;
Docker MySQL8修改root密码方法_docker_07

2. 删除root用户远程连接

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

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用户已经授权完成。
Docker MySQL8修改root密码方法_bash_09

4. 远程连接验证

Docker MySQL8修改root密码方法_mysql_10
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='[email protected]',id_card_no=110108888888888888,phone=18888888888;

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK