Mysql 初始化设置密码

Mysql5.7，我是宝塔linux面板，所以文件夹在/www/server下

1. 备份data文件，然后删除数据data文件夹

[root@jaofuan ~]# cp -R /www/server/data /www/server/data_bak
[root@jaofuan ~]# rm -rf /www/server/data

2. 执行初始化命令，初始化mysql

[root@jaofuan server]# cd /www/server
[root@jaofuan server]# /www/server/mysql/bin/mysqld --initialize --user=mysql --basedir=/www/server/mysql --datadir=/www/server/data
[root@jaofuan server]# ll data
total 141352
-rw-r----- 1 mysql mysql       56 Jun 27 15:50 auto.cnf
-rw-r----- 1 mysql mysql      424 Jun 27 15:50 ib_buffer_pool
-rw-r----- 1 mysql mysql 77594624 Jun 27 15:50 ibdata1
-rw-r----- 1 mysql mysql 33554432 Jun 27 15:50 ib_logfile0
-rw-r----- 1 mysql mysql 33554432 Jun 27 15:50 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Jun 27 15:50 mysql
-rw-r----- 1 mysql mysql      177 Jun 27 15:50 mysql-bin.000001
-rw-r----- 1 mysql mysql       19 Jun 27 15:50 mysql-bin.index
-rw-r----- 1 mysql mysql      176 Jun 27 15:50 mysql-slow.log
drwxr-x--- 2 mysql mysql     4096 Jun 27 15:50 performance_schema
drwxr-x--- 2 mysql mysql    12288 Jun 27 15:50 sys

3. 编辑 /etc/my.cnf 文件，给mysqld项增加 skip-grant-tables 跳过登录验证

[root@jaofuan server]# vim /etc/my.cnf

[mysqld]
skip-grant-tables

4. 启动mysql service mysqld start

[root@jaofuan server]# service mysqld start

5. 连接mysql，不用输入密码，直接回车

[root@jaofuan server]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27-log Source distribution

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>

6. 找到 mysql 库下的 user 数据表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql> select * from user where User='root';
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *2354E7274DCCB7FFB22A48DD7A92663788E7BBBF | Y                | 2020-06-27 15:50:32   |              NULL | N              |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
1 row in set (0.00 sec)

7. 执行修改sql，退出mysql
从 mysql5.7 开始，不再使用 password 字段存储密码，更改为 authentication_string 字段。
执行密码修改必须使用 PASSWORD() 进行密码加密。

mysql> UPDATE user SET authentication_string=PASSWORD('123456') WHERE User='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> exit;

8. 编辑/etc/my.cnf，删除 skip-grant-tables

[root@jaofuan server]# vim /etc/my.cnf

9. 重启mysql

[root@jaofuan server]# service mysqld restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]

---

重置后，如果连接mysql出现下面报错，需要执行下 SET PASSWORD=PASSWORD('123456');

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SET PASSWORD=PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.01 sec)