MySQL常用操作

一、设置更改root密码

第一次进入数据库不需要密码：

[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot //-u 指定要登录的用户，后面有无空格都行;root为mysql自带的管理员账号，默认没有密码

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 2

Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> quit //退出时直接输入quit或exit即可

Bye

上面命令使用了绝对路径，不是很方便，为了更方便，可以修改/etc/profile把/usr/local/mysql/bin加入到环境变量中：

[root@localhost ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile

[root@localhost ~]# source /etc/profile

[root@localhost ~]# mysql -uroot

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 4

Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

这样就可以不用每次使用绝对路径了。

给root用户设定密码：

[root@localhost ~]# mysqladmin -uroot password '123456' //设定密码为123456

Warning: Using a password on the command line interface can be insecure. //这行为警告信息，意思是在命令行下面暴露了密码，不安全

[root@localhost ~]# mysql -uroot

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) //再次登录，提示错误，因为没有密码

重新输入密码登录：

[root@localhost ~]# mysql -uroot -p //-p 后面不可以有空格，可以直接跟密码，也可以不跟，不跟密码就是以交互形式输入密码

Enter password:

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 8

Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

更改root用户密码：

mysql> SET PASSWORD FOR 'root'@localhost =PASSWORD('1234567'); //之前密码为123456，现在改为1234567

Query OK, 0 rows affected (0.00 sec)

[root@localhost ~]# mysql -uroot -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) //输入123456，提示错误

[root@localhost ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 4

Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. //输入1234567，成功登陆

忘记root用户密码时修改密码：

[root@localhost ~]# vim /etc/my.cnf

skip-grant //在[mysqld]下面增加这一行

[root@localhost ~]# /etc/init.d/mysqld restart //重启服务

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

[root@localhost ~]# mysql -uroot //进入mysql

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 ('1234567') where user='root'; //修改root密码

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0

[root@localhost ~]# vim /etc/my.cnf //去掉skip-grant这行

[root@localhost ~]# /etc/init.d/mysqld restart //重启服务

[root@localhost ~]# mysql -uroot -p //使用新密码登陆

二、连接MySQL

上面我们使用mysql -uroot -p命令来连接数据库，但是连接的只是本地数据库的localhost。而很多时候，我们需要连接网络中某一主机上的mysql。

[root@localhost ~]# mysql -uroot -p -h192.168.33.128 -P3306 //-h 指定远程主机的IP，-P(大写)用来指定远程主机mysql的绑定端口

Enter password:

三、MySQL常用命令

在日常工作中，难免会遇到一些与mysql相关的操作，如建库、建表、查询mysql状态等，我们需要掌握关于这些常用的命令。

查询当前库

mysql> show databases; //注意每条命令的最后面都需要跟一个分号作为结束符号

+——————–+

| Database |

+——————–+

| information_schema |

| mysql |

| performance_schema |

| test |

+——————–+

4 rows in set (0.01 sec)

查询某个库的表

先要切换到某个库里：

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 //提示会把当前库里的所有表的字段全部读一段，可以在启动mysql时加上-A关闭这个提示，不关闭也无影响

Database changed

然后再把表列出来：

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)

查看某个表的全部字段

mysql> desc db; //查看db表的全部字段

+———————–+—————+——+—–+———+——-+

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

+———————–+—————+——+—–+———+——-+

| Host | char(60) | NO | PRI | | |

| Db | char(64) | NO | PRI | | |

| User | char(16) | NO | PRI | | |

| 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 | |

| 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 | |

| Create_tmp_table_priv | enum('N','Y') | NO | | N | |

| Lock_tables_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 | |

| Execute_priv | enum('N','Y') | NO | | N | |

| Event_priv | enum('N','Y') | NO | | N | |

| Trigger_priv | enum('N','Y') | NO | | N | |

+———————–+—————+——+—–+———+——-+

22 rows in set (0.00 sec)

另外，还可以使用这条命令：

mysql> show create table dbG //这个命令显示信息更详细，且会把建表语句全部列出来; G让列出来的结果竖排显示，这样看起来更清晰,用了G就不用加分号了。

*************************** 1. row ***************************

Table: db

Create Table: CREATE TABLE `db` (

`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',

`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

PRIMARY KEY (`Host`,`Db`,`User`),

KEY `User` (`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'

1 row in set (0.00 sec)

查看当前是哪个用户

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)

创建一个新库

mysql> create database db1; //新建一个库db1

Query OK, 1 row affected (0.00 sec)

创建一个新表

mysql> use db1 //切换到库db1

Database changed

mysql> create table t1 (`id` int(4),`name` char(40)); //新建表t1，并且写入数据，字段名id和name用反引号括起来

Query OK, 0 rows affected (0.01 sec)

查看当前数据库的版本

mysql> select version(); //查看当前mysql版本

+———–+

| version() |

+———–+

| 5.6.36 |

+———–+

1 row in set (0.00 sec)

查看MySql的当前状态

mysql> show status; //查看当前mysql状态

+———————————————–+————-+

| Variable_name | Value |

+———————————————–+————-+

| Aborted_clients | 0 |

| Aborted_connects | 2 |

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 0 |

| Binlog_stmt_cache_disk_use | 0 |

| Binlog_stmt_cache_use | 0 |

| Bytes_received | 1124 |

| Bytes_sent | 25602 |

| Com_admin_commands | 0 |

| Com_assign_to_keycache | 0 |

…

查看MySql的参数

mysql> show variables; //查看mysql各参数

| innodb_stats_sample_pages | 8 |

| innodb_stats_transient_sample_pages | 8 |

| innodb_status_output | OFF |

| innodb_status_output_locks | OFF |

| innodb_strict_mode | OFF |

| innodb_support_xa | ON |

| innodb_sync_array_size | 1 |

| innodb_sync_spin_loops | 30 |

| innodb_table_locks | ON |

| innodb_thread_concurrency | 0 |

| innodb_thread_sleep_delay | 10000 |

| innodb_tmpdir |

…

修改MySql的参数：

上面列出的很多参数都是可以在/etc/my.cnf中定义的。

以参数 max_connect_errors为例，修改它：

mysql> show variables like 'max_connect%'; //mysql中，符号%类似于shell下的*，表示通配

+——————–+——-+

| Variable_name | Value |

+——————–+——-+

| max_connect_errors | 100 |

| max_connections | 151 |

+——————–+——-+

2 rows in set (0.00 sec)

mysql> set global max_connect_errors = 1000; //修改max_connect_errors的值为1000，set global可以临时修改一些参数，重启mysql服务失效，修改配置文件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 |

+——————–+——-+ //max_connect_errors的值为1000

2 rows in set (0.00 sec)

查看当前MySql服务器的队列

查看服务器队列在日常工作中用的最多，使用它可以查看mysql当前在干什么，也可以发现是否有锁表。

查看服务器队列：

mysql> show processlist; //查看服务器队列

+—-+——+———–+——+———+——+——-+——————+

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

+—-+——+———–+——+———+——+——-+——————+

| 11 | root | localhost | db1 | Sleep | 9373 | | NULL |

| 12 | root | localhost | NULL | Query | 0 | init | show processlist |

+—-+——+———–+——+———+——+——-+——————+

2 rows in set (0.00 sec)

四、MySQL用户管理

创建一个普通用户并授权

mysql> grant all on *.* to user1 identified by '123456';

Query OK, 0 rows affected (0.00 sec)

1. all表示所有的权限(如读、写、查询、删除等操作);

2. . 旁边有两个匹配条件，前面的*表示所有的数据库，后面的*表示所有的表;

3. identified by后面跟密码，用单引号括起来，这里user1指的是localhost上的user1

给网络上其他机器上的某用户授权：

mysql> grant all on db1.* to 'user2'@'192.168.33.128' identified by '111222';

Query OK, 0 rows affected (0.00 sec)

用户和主机的IP都用单引号括起来，两者之间有@符号，IP可以用%代替，表示所有主机

五、常用SQL语句

查询语句select

第一种形式：

mysql> select count(*) from mysql.user;

+———-+

| count(*) |

+———-+

| 8 |

+———-+

1 row in set (0.00 sec)

//mysql.user 表示mysql库的user表，count(*)表示表中共有多少行

第二种形式：

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.33.128 | db1 | user2 | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |

+—————-+———+——-+————-+————-+————-+————-+————-+———–+————+—————–+————+————+———————–+——————+——————+—————-+———————+——————–+————–+————+————–+

3 rows in set (0.00 sec)

//上面表示查询mysql库的db表中的所有数据，谨慎使用该命令，因为需要耗费很大的cpu资源

查询单个字段或多个字段：

mysql> select db from mysql.db //查询单个字段

mysql> select db,user from mysql.db //查询多个字段

使用%查询：

mysql> select * from mysql.db where host like '192.168.%' //查询IP为192.168网段的mysql库的db表中的所有数据

插入一行insert

插入操作在mysql中也很常见。

– 插入：

mysql> insert into db1.t1 values (1,'abc'); //向db1库的t1表中插入1,abc

Query OK, 1 row affected (0.01 sec)

mysql> select * from db1.t1;

+——+——+

| id | name |

+——+——+

| 1 | abc |

+——+——+

1 row in set (0.00 sec)

更改表的某一行update

mysql表里存放的数据支持更改某个字段。

– 更改：

mysql> update db1.t1 set name='aaa' where id=1; //更改id=1的name为aaa

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)

清空某个表的数据truncate

有时候我们不想删除表，只想清空数据。

– 清空：

mysql> truncate table db1.t1; //清空db1库的t1表

Query OK, 0 rows affected (0.01 sec)

mysql> select * from db1.t1;

Empty set (0.00 sec)

删除表drop table

如果某个表不需要了，那就直接删除。

– 删除表：

mysql> drop table db1.t1; //删除db1库的t1表

Query OK, 0 rows affected (0.01 sec)

六、MySql数据库的备份与恢复

MySql备份mysqldump

[root@localhost ~]# mysqldump -uroot -p'123456' mysql > /tmp/mysql.sql

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

//-u 和 -p 的作用和前面一样，后面的mysql指的是库名，然后重定向到一个文档里

MySql恢复

[root@localhost ~]# mysql -uroot -p'123456' mysql < /tmp/mysql.sql //恢复时使用的是mysql命令而不是mysqldump。

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

扩展：

mysql5.7 root密码更改

myisam 和innodb引擎对比

mysql 配置详解

mysql调优

同学分享的亲身mysql调优经历

SQL语句教程

什么是事务?事务的特性有哪些

根据binlog恢复指定时间段的数据

mysql字符集调整