溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

更改MySQL數(shù)據(jù)庫root密碼流程講析

發(fā)布時間：2020-04-28 10:29:12 來源：億速云 閱讀：254 作者：三月 欄目：MySQL數(shù)據(jù)庫

下文內(nèi)容主要給大家?guī)砀?a title="MySQL" target="_blank" href="http://kemok4.com/mysql/">MySQL數(shù)據(jù)庫root密碼流程講析，這里所講到的知識，與書籍略有不同，都是億速云專業(yè)技術(shù)人員在與用戶接觸過程中，總結(jié)出來的，具有一定的經(jīng)驗(yàn)分享價值，希望給廣大讀者帶來幫助。

更改MySQL數(shù)據(jù)庫root密碼

1. 首次進(jìn)入數(shù)據(jù)庫是不用密碼的，如下所示：
[root@gary-tao ~]# /usr/local/mysql/bin/mysql -uroot //使用絕對路徑進(jìn)入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即可，上面我們是使用絕對路徑進(jìn)入mysql的,這樣很不方便，由于/usr/local/mysql/bin不在PATH這個環(huán)境變量里，所以不能直接使用mysql這條命令，那我們就需要把它加入到環(huán)境變量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  //添加后重啟會開機(jī)加載

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

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

[root@gary-tao ~]# source /etc/profile  //即刻生效
2.設(shè)置mysql的root密碼
[root@gary-tao ~]# mysqladmin -uroot password 'szyino-123'  //設(shè)置密碼
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

增加如下內(nèi)容：

skip-grant
如圖：

更改MySQL數(shù)據(jù)庫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>
  • 進(jìn)入密碼表更改密碼
mysql> use mysql;  //用戶名密碼存在user表里，而user表存在mysql這個庫里，進(jìn)入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這個函數(shù)生成
+-------------------------------------------+
| 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.連接本機(jī)數(shù)據(jù)庫
[root@gary-tao ~]# mysql -uroot -p'123456'
2.遠(yuǎn)程連接登錄mysql，A機(jī)器連接B云服務(wù)器的mysql，就需要加上IP和端口,如下：
[root@gary-tao ~]# mysql -uroot -pszyino-123 -h227.0.0.1 -P3306 //-h用來指定遠(yuǎn)程主機(jī)的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遠(yuǎn)程連接
[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我們本機(jī)不止監(jiān)聽了3306也監(jiān)聽了sock，所以就可以使用sock登陸，但這個時候不是使用TCP/IP連接，是使用sock,只適合在本機(jī)。
4. 連接mysql后，把數(shù)據(jù)庫表列出來，這種情況只適用在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相關(guān)的操作，比如建庫、建表、查詢MySQL狀態(tài)等，掌握最基本的操作。

注意：使用mysql命令的結(jié)尾處都需要加一個分號。
1.查詢當(dāng)前庫
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.查看當(dāng)前用戶
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
7.查看當(dāng)前使用的數(shù)據(jù)庫
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)
8.創(chuàng)建庫
mysql> create database db1;  //創(chuàng)建庫
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.創(chuàng)建表
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;  //查看創(chuàng)建的表
*************************** 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;  //創(chuàng)建表時指定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.查看當(dāng)前數(shù)據(jù)庫版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.35    |
+-----------+
1 row in set (0.00 sec)
10.查看數(shù)據(jù)庫狀態(tài)
mysql> show status;
11.查看各參數(shù)
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.修改參數(shù)
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.查看數(shù)據(jù)庫隊(duì)列
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)
對于以上關(guān)于更改MySQL數(shù)據(jù)庫root密碼流程講析，如果大家還有更多需要了解的可以持續(xù)關(guān)注我們億速云的行業(yè)推新，如需獲取專業(yè)解答，可在官網(wǎng)聯(lián)系售前售后的，希望該文章可給大家?guī)硪欢ǖ闹R更新。

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手