13.1 设置更改root密码
13.1 设置更改root密码
脑洞老湿_ 发表于6个月前
13.1 设置更改root密码
  • 发表于 6个月前
  • 阅读 2
  • 收藏 0
  • 点赞 0
  • 评论 0

新睿云服务器60天免费使用,快来体验!>>>   

13.1 mysql设置更改root密码

 /usr/local/mysql/bin/mysql -uroot
 更改环境变量PATH,增加mysql绝对路径
 mysqladmin -uroot password '123456'
 mysql -uroot -p123456
 密码重置
 vi /etc/my.cnf//增加skip-grant
 重启mysql服务 /etc/init.d/mysqld restart
 mysql -uroot
 use mysql;
 update user set password=password('aminglinux') where user='root';

1、修改root密码

  • 将mysql加入到PATH
[root@DasonCheng ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@DasonCheng ~]# export PATH=$PATH:/usr/local/mysql/bin/    ///临时添加
……
[root@DasonCheng ~]# echo 'export PATH=$PATH:/usr/local/mysql/bin/' >>/etc/profile
[root@DasonCheng ~]# source /etc/profile    //永久添加,并使其生效!
  • 修改root密码
[root@DasonCheng ~]# mysqladmin -uroot password
New password: 
Confirm new password:     //修改密码成功
//or
[root@DasonCheng ~]# mysqladmin -uroot password 'p@sswr0d'    //这样直接修改密码就可以用于shell脚本;
  • 登录
[root@DasonCheng ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
//or
[root@DasonCheng ~]# mysql -uroot -pp@ssw0rd    //这种直接用户密码登录就可以用于shell脚本;

2、重置root密码

  • 编辑配置文件my.cnf
[root@DasonCheng ~]# vim /etc/my.cnf
[mysqld]
skip-grant
……  
  • 重启mysql并登录
[root@DasonCheng ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.... SUCCESS! 
[root@DasonCheng ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
……
  • 切换mysql库
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
mysql>
  • 存放密码信息的表user
mysql> select * from user;
  • 查看user表里面root的密码
mysql> select password from user where user='root';

mark

  • 修改user表里面root的密码
mysql> update user set password=password('aminglinux') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> 
  • 还原my.cnf配置文件并重启mysql
[root@DasonCheng ~]# vim /etc/my.cnf
[mysqld]
#skip-grant
……
[root@DasonCheng ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

13.2 连接mysql

mysql -uroot -p123456
 mysql -uroot -p123456 -h127.0.0.1 -P3306
 mysql -uroot -p123456 -S/tmp/mysql.sock
 mysql -uroot -p123456 -e “show databases”

连接方式分析:

[root@DasonCheng ~]# mysql -uroot -paminglinux    
mysql> 
//直接输入账号密码,相当于默认sock通信!
[root@DasonCheng ~]# mysql -uroot -paminglinux -S/tmp/mysql.sock
mysql> 
//指定sock文件,进行通信!(只适合本机)
[root@DasonCheng ~]# mysql -uroot -paminglinux -h127.0.0.1 -P3306
mysql> 
//指定远程 主机和对应 端口进行通信!
[root@DasonCheng ~]# mysql -uroot -paminglinux -e "show databases"  
//连接mysql,并操作一些命令!
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |  
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@DasonCheng ~]# 

13.3 mysql常用命令

 查询库 show databases;
 切换库 use mysql;
 查看库里的表 show tables;
 查看表里的字段 desc tb_name;
 查看建表语句 show create table tb_name\G;
 查看当前用户 select user();
 查看当前使用的数据库 select databsase();

 创建库 create database db1;
 创建表 use db1; create table t1(`id` int(4), `name` char(40));
 查看当前数据库版本 select version();
 查看数据库状态 show status;
 查看各参数 show variables; show variables like 'max_connect%';
 修改参数 set global max_connect_errors=1000;
 查看队列 show processlist; show full processlist;

#### mysql命令分析1:

mysql> show databases;    //查看所有库;  

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)
mysql> use 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> show tables;    
//查看库里面的所有表;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |  


mysql> desc user;    
//查看表里的字段;(库由表组成,表由字段组成;其中Field就是字段名-知道这个就好)
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(16)                          | NO   | PRI |   

mysql> show create table user\G;
//查看建表语句;\G是竖行显示,比较工整,里面有建表的一些信息!
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',

mysql> select user();
//查看当前用户;
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select database();
//查看当前库;
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)
[root@DasonCheng ~]# ll .mysql_history     //mysql命令历史文件;
-rw-------. 1 root root 514 8月  22 09:23 .mysql_history
[root@DasonCheng ~]# tail .mysql_history 
show\040databases;
use\040mysql;
show\040databases;
use\040mysql;
show\040tables;
……

#### mysql命令分析2:

mysql> create database db1;    //创建库db1;
Query OK, 1 row affected (0.00 sec)
mysql> use db1;    //进入db1库里面;
Database changed
mysql> create table tb1(`id` int(4), `name` char(40));    //创建表tb1;
mysql> show create table tb1\G;    //查看tb1的创建信息;
       Table: tb1
Create Table: CREATE TABLE `tb1` (
  `id` int(4) DEFAULT NULL,
  `name` char(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> drop table tb1;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table tb1;    //删除掉表tb1;
Query OK, 0 rows affected (0.01 sec)
mysql> create table tb1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
//创建表的同时,指定其字符集为utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> create table tb1(id int(4), name (40)); //创建表tb1;
创建一个表,表名tb1;共两个字段:
第一个字段为id,格式为int,最长为4;
第二个字段为name,格式为char,最长为40个字符; int代表数字number,char代表字符串;

mysql> select version();   
//查看当前数据库版本;
+-----------+
| version() |
+-----------+
| 5.6.35    |
+-----------+
1 row in set (0.00 sec)   

mysql> show status;  
//查看数据库状态;
+-----------------------------------------------+-------------+
| Variable_name                                 | Value       |
+-----------------------------------------------+-------------+
| Aborted_clients                               | 0           |
| Aborted_connects                              | 1           |  

mysql> show variables;   
 //查看配置my.cnf参数  

mysql> show variables like 'max_connect%';    
//匹配'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;    
//临时修改配置文件最大错误为1000;永久修改需要编辑/etc/my.cnf;
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)

mysql> show processlist;    
//查看队列信息;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 12 | 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                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 12 | root | localhost | db1  | Query   |    0 | init  | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 1
博文 109
码字总数 102141
×
脑洞老湿_
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: