失眠网 > mysql的设置更改root密码连接常用命令

mysql的设置更改root密码连接常用命令

时间：2023-05-25 11:16:38

13.1 设置更改root密码

更改环境变量PATH ，增加mysql绝对路径

首次直接使用mysql会提示‘该命令不存在’，原因是还没有将该命令加入环境变量，如果要使用该命令，需要使用其绝对路径：/usr/local/mysql/bin/mysql，为了方便，先将其加入系统环境变量：

[root@taoyuan ~]# export PATH=$PATH:/usr/local/mysql/bin/

mysql命令路径暂时加入环境变量，系统重启后该变量会失效，若要永久生效，需要将其加入环境变量配置文件：

vi /etc/profile#在配置文件最后把上面的命令加#执行source命令生效[root@taoyuan ~]# source /etc/profile

首次登陆

[root@taoyuan ~]# mysql -uroot

注：-p=passwd，使用密码登录，在此可以将密码直接输入在命令行（跟在-p后面，不加空格：-p'123456'<此处单引号可以不加，但是当密码中有特殊符号时必须加，所以在命令行输入密码时养成习惯：加单引号>），也可以不在命令行输入，只跟-p选项，然后根据提示信息：“Enter password”，输入密码进行登录（此方法不会暴露用户密码，安全）。

设置mysql 的root密码 && 更改

[root@taoyuan ~]# mysqladmin -uroot password '123456'Warning: Using a password on the command line interface can be insecure.[root@taoyuan ~]# mysql -uroot -pEnter password: #输入密码#更改新的密码[root@taoyuan ~]# mysqladmin -uroot -p'123456' password 'taoyuan'Warning: Using a password on the command line interface can be insecure.[root@taoyuan ~]# mysql -uroot -p'taoyuan'Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.

密码重置

[root@taoyuan ~]# vi /etc/f#f 配置文件内容[mysqld]skip-grantdatadir=/data/mysql #增加skip-grant#忽略授权，意思是不用密码登陆#重启mysql服务[root@taoyuan ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! #登陆mysql 修改一个表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#查看表mysql> select * from user;#查看user表mysql> select password from user;+-------------------------------------------+| password |+-------------------------------------------+| *758ABA8398EF87C993D2C4420DACD8946907C873 || || || || || |+-------------------------------------------+6 rows in set (0.00 sec)#修改密码mysql> update user set password=password('Aa123456') where user='root';Query OK, 4 rows affected (0.00 sec)Rows matched: 4 Changed: 4 Warnings: 0#quit ，把/etc/f 配置文件修改回去，重启mysql服务[root@taoyuan ~]# vi /etc/f[root@taoyuan ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@taoyuan ~]# mysql -uroot -p'Aa123456'Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.

注：完成该操作之后就可以任意登录mysql了（无需密码），所以此时mysql安全性很差，平时配置文件中一定不要添加该参数！！

13.2 连接mysql

本机直接登陆

[root@taoyuan ~]# mysql -uroot -pAa123456

通过端口连接（TCP/IP）

[root@taoyuan ~]# mysql -uroot -pAa123456 -h127.0.0.1 -P3306# -P 指定端口

使用sock的连接

[root@taoyuan ~]# mysql -uroot -pAa123456 -S/tmp/mysql.sock#适用于本机连接

连接mysql操作一个命令

[root@taoyuan ~]# mysql -uroot -pAa123456 -e "show databases"Warning: Using a password on the command line interface can be insecure.+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test|+--------------------+#shell脚本使用中比较方便，可以直接获取数据

13.3 mysql常用命令

#查询库mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test|+--------------------+4 rows in set (0.00 sec)#切换库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#查看库里的表mysql> show tables;#查看字段mysql> desc user;#查看建表语句mysql> show create table user\G;#\G 表示：竖排显示#查看当前用户mysql> select user();+----------------+| user() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)#查看当前使用的数据库mysql> select database();+------------+| database() |+------------+| NULL |+------------+1 row in set (0.00 sec)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 changedmysql> select database();+------------+| database() |+------------+| mysql|+------------+1 row in set (0.00 sec)#创建库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)#创建表mysql> use db1;Database changedmysql> create table t1(`id` int(4), `name` char(40));Query 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=latin11 row in set (0.00 sec)ERROR: No query specified#ENGINE=InnoDB DEFAULT CHARSET=latin1 可以自定义#如下mysql> drop table t1; #删除t1表Query OK, 0 rows affected (0.01 sec)mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.01 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#查看当前数据库版本mysql> select version();+-----------+| version() |+-----------+| 5.6.35 |+-----------+1 row in set (0.00 sec)#查看数据库状态mysql> show status;#查看各参数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)#修改参数mysql> set global max_connect_errors=1000;Query OK, 0 rows affected (0.00 sec)#只在内存中生效#查看队列mysql> show processlist;+----+------+-----------+------+---------+------+-------+------------------+| Id | User | Host| db | Command | Time | State | Info |+----+------+-----------+------+---------+------+-------+------------------+| 9 | root | localhost | db1 | Query | 0 | init | show processlist |+----+------+-----------+------+---------+------+-------+------------------+1 row in set (0.00 sec)mysql> show full processlist;+----+------+-----------+------+---------+------+-------+-----------------------+| Id | User | Host| db | Command | Time | State | Info |+----+------+-----------+------+---------+------+-------+-----------------------+| 9 | root | localhost | db1 | Query | 0 | init | show full processlist |+----+------+-----------+------+---------+------+-------+-----------------------+1 row in set (0.00 sec)#full 查看完整的队列

小常识：

敲过的命令历史在mysql_history 文件里

如果觉得《mysql的设置更改root密码连接常用命令》对你有帮助，请点赞、收藏，并留下你的观点哦！

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。