修改MySQL数据库root密码流程讲析

发布时间：2023-09-01 15:28:44 所属栏目：MySql教程来源：

导读：下文内容主要给大家带来更改MySQL数据库root密码流程讲析，这里所讲到的知识，与书籍略有不同

更改MySQL数据库root密码

1. 首次进入数据库是不用密码的，如下所示：

[root@gary-tao ~]# /usr/local/mysq

下文内容主要给大家带来更改MySQL数据库root密码流程讲析，这里所讲到的知识，与书籍略有不同

更改MySQL数据库root密码

1. 首次进入数据库是不用密码的，如下所示：

[root@gary-tao ~]# /usr/local/mysql/bin/mysql -uroot //使用绝对路径进入mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.35 MySQL Community Server (GPL)

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

说明：退出时直接输入quit或者exit即可，上面我们是使用绝对路径进入mysql的,这样很不方便，由于/usr/local/mysql/bin不在PATH这个环境变量里，所以不能直接使用mysql这条命令，那我们就需要把它加入到环境变量PATH中，方法如下：

[root@gary-tao ~]# ls /usr/local/mysql/bin/mysql

/usr/local/mysql/bin/mysql

[root@gary-tao ~]# echo $PATH

/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

[root@gary-tao ~]# export PATH=$PATH:/usr/local/mysql/bin/ //加入PATH,但重启后会失效

[root@gary-tao ~]# mysql -uroot //-u是指定要登录的用户，后面有无空格均可。

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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@gary-tao ~]# vi /etc/profile //添加后重启会开机加载

把以下命令增加到最后一行：

export PATH=$PATH:/usr/local/mysql/bin/

[root@gary-tao ~]# source /etc/profile //即刻生效

2.设置mysql的root密码

[root@gary-tao ~]# mysqladmin -uroot password 'szyino-123' //设置密码

Warning: Using a password on the command line interface can be insecure.

警告信息：在命令行下面暴露了密码，这样不安全。

3.使用密码登录mysql

[root@gary-tao ~]# mysql -uroot //报错，提示需要密码登录

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@gary-tao ~]# mysql -uroot -p //交互的方式输入密码登录

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.6.35 MySQL Community Server (GPL)

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

Bye

[root@gary-tao ~]# mysql -uroot -p'szyino-123' //直接-p后面跟密码登录，-P后面不需要有空格。

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.6.35 MySQL Community Server (GPL)

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

4.更改mysql的root密码

[root@gary-tao ~]# mysqladmin -uroot -p'szyino-123' password 'Szyino-123' //更改密码

Warning: Using a password on the command line interface can be insecure.

[root@gary-tao ~]# mysql -uroot -p'Szyino-123' //使用新密码登录

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.6.35 MySQL Community Server (GPL)

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

5.重置密码

更改配置文件

[root@gary-tao ~]# vim /etc/my.cnf

增加如下内容：

skip-grant

如图：

更改MySQL数据库root密码流程讲析

重启mysql

[root@gary-tao ~]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

[root@gary-tao ~]# mysql -uroot

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.35 MySQL Community Server (GPL)

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

进入密码表更改密码

mysql> use mysql; //用户名密码存在user表里，而user表存在mysql这个库里，进入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 * from user; //查看user表

mysql> select password from user where user='root' ; //查询语句查询密码表。加密的字符串是password这个函数生成

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

| password                                  |

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

| *EBBC0E0C643D4DC86D226068E9C5A6693BB555A6 |

|                                           |

|                                           |

|                                           |

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

4 rows in set (0.01 sec)

mysql> update user set password=password('szyino-123') where user='root'; //更改密码命令

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0

把vi /etc/my.cnf增加的skip-grant去掉，否则所有的用户登录都不需要密码，不安全。

[root@gary-tao ~]# vi /etc/my.cnf //去掉skip-grant

[root@gary-tao ~]# /etc/init.d/mysqld restart //重启mysql

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

[root@gary-tao ~]# mysql -uroot -pszyino-123 //使用新密码测试登录mysql

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.35 MySQL Community Server (GPL)

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

Bye

连接mysql

1.连接本机数据库

[root@gary-tao ~]# mysql -uroot -p'123456'

2.远程连接登录mysql，A机器连接B云服务器的mysql，就需要加上IP和端口,如下：

[root@gary-tao ~]# mysql -uroot -pszyino-123 -h227.0.0.1 -P3306 //-h用来指定远程主机的IP -P指定端口

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.35 MySQL Community Server (GPL)

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

3.使用sock远程连接

[root@gary-tao ~]# mysql -uroot -pszyino-123 -S/tmp/mysql.sock

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.6.35 MySQL Community Server (GPL)

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

说明：mysql我们本机不止监听了3306也监听了sock，所以就可以使用sock登陆，但这个时候不是使用TCP/IP连接，是使用sock,只适合在本机。

4. 连接mysql后，把数据库表列出来，这种情况只适用在shell脚本里。

[root@gary-tao ~]# mysql -uroot -pszyino-123 -e "show databases"

Warning: Using a password on the command line interface can be insecure.

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

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

mysql常用命令

在日常工作中，难免会遇到一些与Mysql相关的操作，比如建库、建表、查询MySQL状态等，掌握最基本的操作。

注意：使用mysql命令的结尾处都需要加一个分号。

1.查询当前库

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

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

4 rows in set (0.00 sec)

2.切换库

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

3.查询库的表

mysql> show tables;

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

| Tables_in_mysql           |

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

| columns_priv              |

| db                        |

| event                     |

| func                      |

| general_log               |

| help_category             |

| help_keyword              |

| help_relation             |

| help_topic                |

| innodb_index_stats        |

| innodb_table_stats        |

| ndb_binlog_index          |

| plugin                    |

| proc                      |

| procs_priv                |

| proxies_priv              |

| 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                      |

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

28 rows in set (0.00 sec)

4.查看表里的字段

mysql> desc user;

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

| Field                  | Type                              | Null | Key | Default               | Extra |

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

| Host                   | char(60)                          | NO   | PRI |                       |       |

| User                   | char(16)                          | NO   | PRI |                       |       |

| Password               | char(41)                          | NO   |     |                       |       |

| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |

| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |

| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |

| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |

| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |

| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |

| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |

| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |

| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |

| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |

| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |

| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |

| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |

| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |

| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |

| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |

| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N                     |       |

| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |

| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |

| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |

| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |

| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |

| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |

| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |

| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |

| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |

| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |

| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |

| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |

| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |

| ssl_cipher             | blob                              | NO   |     | NULL                  |       |

| x509_issuer            | blob                              | NO   |     | NULL                  |       |

| x509_subject           | blob                              | NO   |     | NULL                  |       |

| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |

| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |

| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |

| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |

| plugin                 | char(64)                          | YES |     | mysql_native_password |       |

| authentication_string | text                              | YES |     | NULL                  |       |

| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |

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

43 rows in set (0.01 sec)

5.查看建表语句

mysql> show create table user\G;

6.查看当前用户

mysql> select user();

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

| user()         |

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

| root@localhost |

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

1 row in set (0.00 sec)

7.查看当前使用的数据库

mysql> select database();

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

| database() |

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

| mysql      |

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

1 row in set (0.00 sec)

8.创建库

mysql> create database db1; //创建库

Query OK, 1 row affected (0.00 sec)

mysql> show databases;    //查看库

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

| Database           |

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

| information_schema |

| db1                |

| mysql              |

| performance_schema |

| test               |

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

5 rows in set (0.00 sec)

9.创建表

mysql> use db1; create table t1(`id` int(4), `name` char(40));

Database changed

Query OK, 0 rows affected (0.03 sec)

mysql> show create table t1\G; //查看创建的表

*************************** 1. row ***************************

       Table: t1

Create Table: CREATE TABLE `t1` (

  `id` int(4) DEFAULT NULL,

  `name` char(40) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.01 sec)

ERROR:

No query specified

mysql> drop table t1; //删除表

Query OK, 0 rows affected (0.01 sec)

mysql> use db1; create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8; //创建表时指定CHARSET=utf8

Database changed

Query OK, 0 rows affected (0.02 sec)

mysql> show create table t1\G;

*************************** 1. row ***************************

       Table: t1

Create Table: CREATE TABLE `t1` (

  `id` int(4) DEFAULT NULL,

  `name` char(40) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR:

No query specified

9.查看当前数据库版本

mysql> select version();

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

| version() |

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

| 5.6.35    |

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

1 row in set (0.00 sec)

10.查看数据库状态

mysql> show status;

11.查看各参数

mysql> show variables;

mysql> show variables like 'max_connect%';

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

| Variable_name      | Value |

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

| max_connect_errors | 100   |

| max_connections    | 151   |

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

2 rows in set (0.00 sec)

12.修改参数

mysql> set global max_connect_errors=1000;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connect%';

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

| Variable_name      | Value |

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

| max_connect_errors | 1000 |

| max_connections    | 151   |

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

2 rows in set (0.00 sec)

13.查看数据库队列

mysql> show processlist;

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

| Id | User | Host      | db   | Command | Time | State | Info             |

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

| 11 | root | localhost | db1 | Query   |    0 | init | show processlist |

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

1 row in set (0.01 sec)

mysql> show full processlist;

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

| Id | User | Host      | db   | Command | Time | State | Info                  |

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

| 11 | root | localhost | db1 | Query   |    0 | init | show full processlist |

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

1 row in set (0.00 sec)

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!