mysql 本地密码_设置更改root密码（远程，本地）、连接mysql、mysql常用命令

设置更改root密码

1、将mysql加入环境变量中

[root@centos7 ~]# grep mysql /etc/profile

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

2、直接登录，无密码

[root@centos7 ~]# mysql -uroot

3、方式一：设置密码

[root@centos7 ~]# mysqladmin -uroot password '123456'

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

You have new mail in /var/spool/mail/root

方式二：设置远程密码和本地密码

远程密码：远程主机登录使用

本地密码：只能本机登录使用

mysql> set password for 'root'@'localhost'=password('root');                             #设置本地密码

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT USAGE ON *.* TO 'root'@'%'  IDENTIFIED BY  '123456';               #设置远程密码

Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'root'@'%'=password('g123');                                        修改远程密码

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;  #远程授权访问

iptables -I INPUT -p tcp --dport 3306-j ACCEPT      #开放端口

查看：

mysql> select Host,User from mysql.user;

备注：(直接输入mysql就可以登录)

vi /etc/my.cnf

[client]

port            = 3306

user=root

password=root

4、登录

[root@centos7 ~]# mysql -uroot -p'123456' -h127.0.0.1 -P3306

-h：指定主机

-P：指定端口

5、更改密码为1234

[root@centos7 ~]# mysqladmin -uroot -p'123456' password '1234'

忘记密码后，怎么更改？

1、[root@centos7 ~]# vi /etc/my.cnf

[mysqld]

skip-grant#跳过授权登录

2、重启mysql服务

[root@centos7 ~]# /etc/init.d/mysqld restart

3、[root@centos7 ~]# mysql -uroot

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> update user set password=password('123456') where user='root'; #更改密码

4、将skip-grant配置删除后重启mysql服务

/etc/init.d/mysqld restart

5、已更改密码

连接mysql

1、[root@centos7 ~]# mysql -uroot -p123456 -h127.0.0.1 -P3306

2、通过socket连接

[root@centos7 ~]# mysql -uroot -p123456 -S/tmp/mysql.sock

mysql常用命令

1、显示所有数据库

show databases;

2、进入哪个数据库

user db

3、显示数据库中所有的表

mysql> show tables;

4、显示表结构(由什么字段组成)

desc user;

5、查询

select host,user from user;

6、显示建表语句过程

show create table user\G;

7、显示哪个用户登录

select user();

8、显示表有多少行

select count(*) from user ;

9、显示变量

show variables;

10、查找

show variables like '%error%';

11、设置临时变量

mysql> set global max_connect_errors = 1000;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connect_errors';

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

| Variable_name      | Value |

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

| max_connect_errors | 1000  |

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

11、显示mysql的进程队列

mysql> show processlist;

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

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

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

|  3 | root | localhost | mysql | Query   |    0 | init  | show processlist |

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

1 row in set (0.01 sec)

mysql> show full  processlist;  #完整情况

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

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

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

|  3 | root | localhost | mysql | Query   |    0 | init  | show full  processlist |

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

12、新建表，字段

mysql> create table tb(`id` int,`num` varchar(10));

Query OK, 0 rows affected (0.02 sec)

mysql> show tables

-> ;

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

| Tables_in_test |

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

| tb             |

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

1 row in set (0.00 sec)

mysql> show create table tb;

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

| Table | Create Table                                                                                                             |

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

| tb    | CREATE TABLE `tb` (

`id` int(11) DEFAULT NULL,

`num` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

mysql> desc tb;

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

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

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

| id    | int(11)     | YES  |     | NULL    |       |

| num   | varchar(10) | YES  |     | NULL    |       |

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

13、删除库及表

drop database test;

drop table tb;

14、插入数据

insert tb value(1,4);