更改MySQL数据库root密码流程讲析
发表于:2024-09-22 作者:千家信息网编辑
千家信息网最后更新 2024年09月22日,下文内容主要给大家带来更改MySQL数据库root密码流程讲析,这里所讲到的知识,与书籍略有不同,都是专业技术人员在与用户接触过程中,总结出来的,具有一定的经验分享价值,希望给广大读者带来帮助。更改M
千家信息网最后更新 2024年09月22日更改MySQL数据库root密码流程讲析
下文内容主要给大家带来更改MySQL数据库root密码流程讲析,这里所讲到的知识,与书籍略有不同,都是专业技术人员在与用户接触过程中,总结出来的,具有一定的经验分享价值,希望给广大读者带来帮助。
更改MySQL数据库root密码
1. 首次进入数据库是不用密码的,如下所示:
[root@gary-tao ~]# /usr/local/mysql/bin/mysql -uroot //使用绝对路径进入mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 2Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 5Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> exitBye[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 6Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 8Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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@gary-tao ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! [root@gary-tao ~]# mysql -urootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 //重启mysqlShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@gary-tao ~]# mysql -uroot -pszyino-123 //使用新密码测试登录mysqlWarning: 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 1Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> quitBye
连接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 4Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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.sockWarning: 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 6Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase 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 changedQuery OK, 0 rows affected (0.03 sec)mysql> show create table t1\G; //查看创建的表*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` int(4) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.01 sec)ERROR: No query specifiedmysql> 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=utf8Database changedQuery OK, 0 rows affected (0.02 sec)mysql> show create table t1\G;*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` int(4) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 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)
对于以上关于更改MySQL数据库root密码流程讲析,如果大家还有更多需要了解的可以持续关注我们的行业推新,如需获取专业解答,可在官网联系售前售后的,希望该文章可给大家带来一定的知识更新。
密码
数据
数据库
登录
命令
用户
查询
本机
流程
安全
专业
内容
分号
参数
变量
状态
环境
知识
端口
表里
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
东软软件开发工程
全国网络软件开发公司
手机服务器搭建
那些不属于关系数据库
团务百科服务器返回状态403
明源服务器怎么清理内存
类似小皮的数据库
nbu数据库怎么恢复
k3服务器要求高吗
税务网络安全宣传周方案
crm软件开发团队
中国数据库域名
服务器整个数据备份
惠普服务器只有ide和r
地下城跨8有哪些服务器
服务器电脑版微信
联想服务器维修调试哪家便宜
广州工行软件开发中心怎么样
双路服务器主板可以用3820吗
aws小型服务器
阿里网络技术面试
户外直播软件开发
锄浆网络技术有限公司
软件开发前期准备费包括
大型网络不采用哪种服务器
网络安全实施办法 党委
请简述什么是服务器集群
网络安全教育作业好多
沃土 数据库
泰兴网络技术供应商家