Mysql 1（6月19日）

2018-06-19

13.1 设置更改root密码
13.2 连接mysql
13.3 mysql常用命令

13.1 设置更改root密码

Mysql也有超级管理员用户root，类似于系统的root。但是他们并不是同一个用户。它默认是没有密码的。使用MySQL命令要使用/usr/local/mysql/bin/mysql 由于命令路径太长了因此可以把他放到环境变量中这样就可以直接使用mysql来敲命令。

临时添加

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

export可以让子shell也生效

永久添加

1
2
3

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

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

在文件最后添加此行并使用source来加载配置

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

进入MySQL

1	[root@localhost ~]# mysql -uroot

-u指定用户 root用户默认没有密码

设置root用户密码

1 2	[root@localhost ~]# mysqladmin -uroot password '123456' Warning: Using a password on the command line interface can be insecure.

mysqladminMySQL的管理工具 password指定新密码密码由于可能带特殊符号，因此最好带上用单引号

知道旧密码来更改密码

1 2	[root@localhost ~]# mysqladmin -uroot -p'123456' password 'avarice/.34' Warning: Using a password on the command line interface can be insecure.

-p指定密码这里指定旧密码换新密码,前提是知道旧密码

不知道旧密码来更改密码

1.在配置文件中添加skip-grant来忽略授权

1
2
3

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

skip-grant

2.重启MySQL服务

1	[root@localhost ~]# service mysqld restart

3.进入MySQL中的mysql库

1 2	[root@localhost ~]# mysql -uroot ##进入MySQL mysql> use mysql; ##切换mysql库中

4.更改user表中root的密码

1
2
3

mysql> update user set password=password('38003800') where user='root';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

左边的password是变量名右边的password是参数可以把密码用加密形式显示 where条件语句

5.去掉配置文件中的skip-grant，并重启MySQL服务

1 2	[root@localhost ~]# vim /etc/my.cnf [root@localhost ~]# service mysqld restart

成功用新密码登录MySQL

[root@localhost ~]# mysql -uroot -p'38003800'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
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.

13.2 连接MySQL

常用的连接MySQL方式有以下几种

1.日常连接

用法：mysql -u用户名 -p密码

1	[root@localhost ~]# mysql -uroot -p'38003800'

-u表示用户 -p表示密码

2.使用sock方式连接

用法：mysql -u用户名 -p密码 -S指定sock

1	[root@localhost ~]# mysql -uroot -p'38003800' -S/tmp/mysql.sock

-S指定要连接的sock，此方式用于本机连接，即使不指定sock,默认也是使用sock来连接MySQL的

3.连接远程MySQL

用法：mysql -u用户名 -p密码 -h远程IP -P端口

1	[root@localhost ~]# mysql -uroot -p'380038000' -h127.0.0.1 -P3306

-h指定远程IP -P指定端口

4.连接并执行某个命令

用法：mysql -u用户 -p密码 -e命令

1	[root@localhost ~]# mysql -uroot -p'38003800' -e"show databases"

-e指定执行的命令此连接方式多用在脚本中

13.3 MySQL常用命令

查询所有的库

用法：show databases;

mysql> show databases;  
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

切换库

用法：use 库名字;

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

查看当前所进入的库

用法：select database();

mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

查看当前库中的所有表

用法：show tables;

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |

查看指定表中的所有字段

用法：desc 表名;
desc表示降序

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(16)                          | NO   | PRI |                       |       |
| Password               | char(41)                          | NO   |     |                       |       |
| 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                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |

Field表示字段的名字 Type表示字段的格式

查看指定表的创建语句

用法：show create table 表名\G;
\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_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',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

查看当前登录的用户

用法：select user();

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

新建库

用法：create database 库名;

1 2	mysql> create database test1; Query OK, 1 row affected (0.01 sec)

新建表

用法：create table 表名(字段格式,字段格式)
字段需要使用反引号

1 2	mysql> create table t1(`id` int(4),`name` char(40)); Query OK, 0 rows affected (0.02 sec)

指定创建表的字符集和引擎

1 2	mysql> create table t2(`id` int(4),`name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.02 sec)

删除表

用法：drop table 表名;

1 2	mysql> drop table t1; Query OK, 0 rows affected (0.00 sec)

查看当前数据的版本

用法：select version();

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.36    |
+-----------+
1 row in set (0.00 sec)

查看数据库的状态

用法：show status;

mysql> show status;
+-----------------------------------------------+-------------+
| 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                                | 1595        |
| Bytes_sent                                    | 26322       |
| Com_admin_commands                            | 0           |
| Com_assign_to_keycache                        | 0           |
| Com_alter_db                                  | 0           |

查看MySQL参数

用法：show variables;

1	mysql> show variables;

查看带某个关键字的参数

用法：show variables like '参数'
%表示通配

mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)

临时修改参数

用法：set global 参数;

1 2	mysql> set global max_connect_errors=1000; Query OK, 0 rows affected (0.00 sec)

这样修改指定只是临时修改参数，永久修改参数可以把参数放在/etc/my.cnf下

查看队列（哪些用户使用数据库，是否锁表）

用法：show processlist;或者 show full processlist;

mysql> show processlist;
+----+------+-----------+-------+---------+------+-------+------------------+
| Id | User | Host      | db    | Command | Time | State | Info             |
+----+------+-----------+-------+---------+------+-------+------------------+
| 12 | root | localhost | test1 | 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 | test1 | Query   |    0 | init  | show full processlist |
+----+------+-----------+-------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

show processlist;和show full processlist;区别在于Info显示是否完整

MySQL中的命令历史存放在root目录下有.mysql_history的文件中

[root@localhost ~]# tail .mysql_history
show\040database;
show\040databases;
use\040mysql;
select\040database();
show\040tables;
use\040mysql;
show\040tables;
desc\040user;
show\040create\040table\040user\134G;
select\040user();