MySQL常见命令
MySQL常见命令
更改root密码
1、配置环境变量
[root@antong ~]# vim /etc/profile //在最后一行加入下面内容
export PATH=$PATH:/usr/local/mysql/bin/ //MySQL的绝对路径
[root@antong ~]# source /etc/profile //重新加载环境变量
2、创建MySQL密码
[root@antong ~]# mysqladmin -uroot password '000000' //刚安装初始化的时候使用
Warning: Using a password on the command line interface can be insecure.
//可以忽略warning内容，指的是明码输入屏幕不安全。
其他mysqladmin的命令
mysqladmin password 000000 //设置密码
mysqladmin -uroot -p000000 password antong //修改密码
mysqladmin -uroot -pantong status //查看状态，相当于show status。
mysqladmin -uroot -pantong -i 1 status //每秒查看一次状态。
mysqladmin -uroot -pantong extended-status //等同show global status;。
mysqladmin -uroot -pantong flush-logs //切割日志。
mysqladmin -uroot -pantong processlist //查看执行的SQL语句信息。
mysqladmin -uroot -pantong processlist -i 1 //每秒查看一次执行的SQL语句。
mysqladmin -uroot -p'antong' shutdown //关闭mysql服务，前文用过的。
mysqladmin -uroot -p'antong' variables //相当于show variables。
3、密码重置
修改配置文件/etc/my.cnf，在mysqld配置段，增加字段skip-grant
[root@antong ~]# vim /etc/my.cnf
[mysqld]
skip-grant
[root@antong ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@antong ~]# mysql -uroot //不需要密码就可以登陆进去
Welcome to the MySQL monitor. Commands end with ; or \g.
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> update user set password=password('000000') where user='root'; //修改表信息
Query OK, 0 rows affected (0.00 sec)
Rows matched: 5 Changed: 0 Warnings: 0
修改完成后，确认新密码登录有效。把/etc/my.cnf改回原有状态，并重启MySQL服务。
连接MySQL
1、mysql -uroot -p123456
[root@antong ~]# mysql -uroot -p000000
Warning: Using a password on the command line interface can be insecure.
mysql>
2、mysql -uroot -p123456 -h127.0.0.1 -P3306.
[root@antong ~]# mysql -uroot -p000000 -h127.0.0.1 -P3306
Warning: Using a password on the command line interface can be insecure.
mysql>
3、mysql -uroot -p123456 -S/tmp/mysql.sock
[root@antong ~]# mysql -uroot -p000000 -S/tmp/mysql.sock
Warning: Using a password on the command line interface can be insecure.
mysql>
4、mysql -uroot -p123456 -e “show databases”
[root@antong ~]# mysql -uroot -p000000 -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wordpress |
+--------------------+
MySQL常用命令
查询库show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wordpress |
+--------------------+
5 rows in set (0.00 sec)
切换库 use 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
查看库里的表 show tables;
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)
查看表里的字段 desc tb_name;
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
查看建表语句 show create table tb_name\G;
mysql> show create table user\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 '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
···省略
查看当前用户 select user();
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
查看当前使用的数据库 select database();
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
创建库 create database db1;
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
创建表 use db1; create table t1(id int(4), name char(40));
mysql> use db1; create table t1(`id` int(4), `name` char(40));
Database changed
Query OK, 0 rows affected (0.00 sec)
查看当前数据库版本 select version();
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.47 |
+-----------+
1 row in set (0.00 sec)
查看数据库状态 show status;
mysql> show status;
+-----------------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 5 |
···省略
查看各参数 show variables; show variables like 'max_connect%';
mysql> show variables; show variables like 'max_connect%';
456 rows in set (0.00 sec)
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
修改参数 set global max_connect_errors=1000;
mysql> set global max_connect_errors=1000;
Query OK, 0 rows affected (0.00 sec)
查看队列 show processlist; show full processlist;
mysql> show processlist; show full processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 11 | root | localhost | db1 | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
+----+------+-----------+------+---------+------+-------+-----------------------+
| 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创建用户以及授权
创建用户
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';
username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
例如：
CREATE USER 'antong'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'antong'@'192.168.200.10' IDENDIFIED BY '123456';
CREATE USER 'antong'@'%' IDENTIFIED BY '123456';
CREATE USER 'antong'@'%' IDENTIFIED BY '';
CREATE USER 'antong'@'%';
进行授权
mysql> grant all on *.* to 'user1' identified by 'passwd';
Query OK, 0 rows affected (0.00 sec)
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on db1.* to 'user3'@'%' identified by 'passwd';
Query OK, 0 rows affected (0.00 sec)
查看授权表
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for user2@192.168.133.1;
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.133.1 |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.1' |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MySQL常用SQL语句
查看表内行数select count(*) from mysql.user;
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
查看db表内的内容 select * from mysql.db;
mysql> select * from mysql.db;
+---------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+---------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N | Y | Y |
| % | test\_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N | Y | Y |
| 192.168.133.1 | db1 | user2 | Y | Y | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
| % | db1 | user3 | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
+---------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
4 rows in set (0.00 sec)
查看db表内含有db字段的内容 select db from mysql.db;
mysql> select db from mysql.db;
+---------+
| db |
+---------+
| db1 |
| test |
| test\_% |
| db1 |
+---------+
4 rows in set (0.00 sec)
搜索查看多个字段 select db,user from mysql.db;
mysql> select db,user from mysql.db;
+---------+-------+
| db | user |
+---------+-------+
| db1 | user3 |
| test | |
| test\_% | |
| db1 | user2 |
+---------+-------+
4 rows in set (0.00 sec)
查询host为127.0的内容 select * from mysql.db where host like '192.168.%';
mysql> select * from mysql.db where host like '192.168.%'\G;
*************************** 1. row ***************************
Host: 192.168.133.1
Db: db1
User: user2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
1 row in set (0.00 sec)
向db1.t1中插入内容 insert into db1.t1 values (1, ‘abc’);
mysql> insert into db1.t1 values (1, 'abc');
Query OK, 1 row affected (0.00 sec)
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.01 sec)
把id=1的字段内容更新成aaa update db1.t1 set name='aaa' where id=1;
mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from db1.t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
+------+------+
1 row in set (0.00 sec)
清空db1.t1表内的内容 truncate table db1.t1;
mysql> truncate table db1.t1; //清空后表的结构依然存在
Query OK, 0 rows affected (0.00 sec)
mysql> desc db1.t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
删除db1.t1表内的内容 drop table db1.t1;
mysql> drop table db1.t1;
Query OK, 0 rows affected (0.00 sec)
mysql> desc db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
清空db1.t1数据库 drop database db1;
mysql> drop database db1;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wordpress |
+--------------------+
5 rows in set (0.00 sec)
MySQL数据库的备份与恢复
备份库和恢复库
[root@antong ~]# mysqldump -uroot -p000000 mysql > /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
[root@antong ~]# ls /tmp/
mysql.sql
[root@antong ~]# mysql -uroot -p000000 < /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
备份表和恢复表
[root@antong ~]# mysqldump -uroot -p000000 mysql user > /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.
[root@antong ~]# mysql -uroot -p000000 < /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.
备份所有库
[root@antong ~]# mysqldump -uroot -p000000 -A > /tmp/123.sql
只备份表结构
[root@antong ~]# mysqldump -uroot -p000000 -d mysql > /tmp/mysql.sql
