MySQL运维命令速查手册

国猫
1,445 阅读25分钟

1. MySQL常用查询语句

1.1. 库操作

1.1.1. 创建数据库

CREATE database dbname;

1.1.2. 删除库

DROP database db1;srment12052

1.1.3. 切换到库

USE dbname;

1.2. 表操作

1.2.1. 创建数据表

CREATE TABLE <表明>
  （
     列名1, 数据类型[约束][默认值],
     列名2, 数据类型[约束][默认值],
     列名n, 数据类型[约束][默认值],
     )[ENGINE=存储引擎][CHARACTER SET=字符集];

例：

CHARACTER TABLE t1
  (
    id INT PRIMARY KEY,   #设定该列为主键，主键内的值必须唯一，会自动创建主键索引
    id2 INT not null,     #设置id2不允许为空
    name varchar(10),
    sex ENUM('F','M','UN')
  	)ENGINE=MYISAM CHARACTER SET utf8;

# 创建表t1，并设置两个列，一个列叫id类型是整形INT长度10，第二列叫name类型是可变长字符串VARCHAR长度20
#其中的长度代表显示的长度，一般没效果
CREATE TABLE t1( id INT(10), name VARCHAR(20) );

设定主键的第二种方式，先设定好每一列，最后再声明主键，方便一次性给多列设置主键

CREATE TABLE t1
  (
  	uid INT(100),
  	certid INT(20),
  	name VARCHAR(10),
  	sex ENUM('F','M','UN'),
  	PRIMARY KEY(uid,certid)
  	)ENGINE=InnoDB CHARACTER SET uft8;

创建表并沿用另外一张表的数据结构

CREATE TABLE t2 LIKE t1;

1.2.2. 表约束

1.2.2.1. 非空约束

#name列设置为非空列
CREATE TABLE t1
  (
  	uid INT PRIMARY KEY,
  	name VARCHAR(10) NOT NULL,             #非空
  	)ENGINE=MYISAM CHARACTER SET utf8;

1.2.2.2. 唯一性约束

约束列中的值不能重复，可以有但只能有一个空值
CREATE TABLE t13
  (
  	certid VARCHAR(20) UNIQUE
  	)ENGINE=MYISAM CHARACTER SET utf8;

1.2.2.3. 默认默认约束（默认值约束）

约束列中的值的默认值。

除非默认值为空值，否则不允许插入空值

CREATE TABLE t1
  (
      sex ENUM('F', 'M', 'NU') DEFAULT 'UN'
  )ENGINE=MYISAM CHARACTER SET utf8;

1.2.2.4. 自增长约束

用于系统自动生成字段的主键值

用于数据量较大且数据又连续性增长的列

值不能为空

CREATE TABLES t1
(
    uid INT PRIMARY KEY AUTO_INCREMENT
)ENGINE=MYISAM CHARACTER SET utf8;

1.2.3. 创建表，并设定外键

#设定外键的名称为fk_t1，关联到当前表t2的fid列，关联到其他表t1点uid列
CREATE TABLE t2
  (
  	fid INT(100),
  	phone varchar(16),
  	location varchar(50),
  	CONSTRAINT fk_t1 FOREIGN KEY(fid) REFERENCES t1(uid)
  	);

1.2.4. 删除表

DROP TABLE t1;

1.2.5. 删除表,如果表存在就删除，如是不存在也不会报错

DROP TABLE IF EXISTS t1;

1.2.6. 显示库中的所有表

SHOW TABLES;

根据关键词查找表 SHOW TABLES LIKE '%flex%';

1.2.7. 查看表结构

DESC tablename;
DESCRIBE tablename;

1.2.8. 修改表数据

1.2.8.1. 对表插入单行数据，有SET关键字

INSERT INTO table_name SET <字段1>=值1, <字段2>=值2, <字段n>=值n;

例

INSERT INTO tablename SET id = 1, name = 'tube';

1.2.8.2. 对表插入多行数据，无SET关键字

字符串值必须用单引号引起来

如果要插入所有字段，则字段可省略

INSERT INTO table_name
  [(字段1, 字段2, 字段n)]
VALUES
  （值1, 值2, 值n), (值1n, 值2n, 值3n);

例

INSERT INTO tablename (id,name) VALUES (2, 'kevin'), (3, 'mark');

1.2.8.3. 对表插入查询结果

将select的查询结果插入到表中

INSERT INTO table_name1
  (字段1, 字段2, 字段n)
SELECT 字段a, 字段b, 字段c FROM table_name2 [WHERE condition];

例

INSERT INTO t2
  (id, name, location)
SELECT id, name, locaton FROM t3;

1.2.8.4. 更新数据 update

UPDATE table_name SET
  字段1=值1,
  字段2=值2,
  字段n=值n
[WHERE condition];

例

UPDATE t1 SET
  name='Tom'
WHERE id=1;

1.2.8.5. 删除数据 DELETE

如果不带上where语句的话，delete会直接清空整张表

DELETE FROM table_nam [WHERE <condition>];

例子

DELETE FROM t8 where id> 13;
DELETE FROM t8 where id> 13 AND id< 18;
DELETE FROM t8 where id> 13 OR id< 3;

1.2.8.6. 清空表 TRUNCATE

将表内的所有数据清空，注意表名不要加引号

TRUNCATE TABLE tablename;

1.2.9. 其他操作

1.2.9.1. 显示创建库时使用的sql命令

SHOW CREATE database db3;

1.2.9.2. 显示有关在当前 session 中执行语句所产生的条件(错误，警告和注释)的信息

SHOW WARNINGS;

1.2.9.3. 显示错误总数

SHOW COUNT(*) WARNINGS;

1.2.9.4. 显示创建该表的指令

SHOW CREATE TABLE tablename; 
# \G将输出的结果旋转90度变成纵向
SHOW CREATE TABLE tablename\G;

1.2.9.5. 统计表中的行数

#统计t2表的行数
SELECT COUNT(*) FROM t2;

#通过WHERE指定一个条件来计数;
SELECT COUNT（*） FROM t2 WHERE id = 2；

#加上DISTINCT，统计表中的唯一行
SELECT COUNT(DISTINCT id) FROM t2;

#以一列作为基准，统计列中每个值出现的次数
SELECT id, count(*) FROM t2 GROUP BY id;

#以一列为基准，统计列中每个值出现的次数，并只列出出现9次以上的结果
SELECT id, count(*) FROM t2 GROUP BY id HAVING count(*) >= 9;

1.2.9.6. 分析查询语句执行的过程

使用explain命令可以分析查询语句执行的过程

EXPLAIN SELECT * FROM t2 WHERE id>1 AND age<25;

1.2.9.7. 刷新查询缓存

清空查询缓存

reset query cache;

1.2.10. 修改表结构

1.2.10.1. 修改表名

ALTER TABLE <旧表名> RENAME <新表名>;

例：

ALTER TABLE ti RENAME t4;

1.2.10.2. 修改字段的数据类型

当表内已经有数据，一定要谨慎修改

ALTER TABLE <表名> MODIFY <字段名> <数据类型>;

例：

ALTER TABLE t1 MODIFY name VARCHAR(20);

1.2.10.3. 修改字段名

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

例：

ALTER TABLE t1 CHANGE name username VARCHAR(20);

1.2.10.4. 添加字段（添加列）

当表中数据量很大时，会严重影响性能

ALTER TABLE <表名> ADD <新字段名> <新数据类型> [约束条件] [FIRST|AFTER 已存在当字段名];

例：

ALTER TABLE t1 ADD location VARCHAR(20) AFTER uname;
ALTER TABLE t1 ADD location VARCHAR(20) NOT NULL AFTER uname;
#创建在第一列
ALTER TABLE t1 ADD location VARCHAR(20) FIRST;

1.2.10.5. 删除字段（删除列）

ALTER TABLE <表名> DROP <字段名>;

例

ALTER TABLES t1 DROP location;

1.2.10.6. 修改字段排列位置

ALTER TABLE <表名> MODIFY <字段名1> <数据类型> FIRST|AFTER <字段2>;

例:

ALTER TABLE t1 MODIFY name VARCHAR(20) AFTER uid;

1.2.10.7. 修改表存储引擎

ALTER TABLE <表名> ENGINE=<新引擎名称>;

例

ALTER TABLE t1 ENGINE=InnoDB;

1.2.10.8. 删除表的外键约束

在删除所有对应的外键之前，主键对应的表是无法被删掉的

ALTER TABLE <表名> DROP FOREIGN KEY <外键名>;

例

ALTER TABLE t2 DROP FOREIGN KEY fk_1;

1.2.11. 事务操作

使用InnoDB数据引擎的表支持事务操作

默认情况Mysql开启了自动提交，每提交一条sql语句会自动作为一个事务处理

  • BEGIN开启一个事务
  • ROLLBACK 回滚一个事务
  • COMMIT 提交一个事务

例子：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t8 VALUES (1, 'simon' ,'10');
Query OK, 1 row affected (0.01 sec)

#此时，数据只插入到了当前事务内（redolog），还未提交到物理存储中（binlog）
#所以该条目目前只能在当前事务内（session内）看到

mysql> select * from t8;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | simon |   10 |
+------+-------+------+
1 row in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
#commit后数据才提交到了物理存储中

如果事务添加时，后悔了，在commit之前可以使用rollback回滚操作

mysql> select * from t8;
Empty set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t8 VALUES (2, 'simon2' ,'10');
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t8;
Empty set (0.00 sec)

查看与关闭自动提交

在做大量单行提交时，关闭自动提交能提高效率，减少mysql commit的次数

#查看mysql环境配置
mysql> show variables like '%commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | ON    |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

#关闭自动提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | OFF   |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
2 rows in set (0.01 sec)

1.2.11. 表查询操作

1.2.11.1. 单表查询操作

  • select指定需要查询的列名
  • from 指定需要查询的表
  • where过滤值条件
SELECT * FROM city;
SELECT name, population FROM city WHERE id=1;
  • IN关键字查询

IN相当于where or条件，相当于例子中查询id为100或101的条目

SELECT id, name, population FROM city WHERE id IN (100,101);
  • AND多条件查询，代表必须同时符合多个条件才会显示
SELECT name,district,population FROM city WHERE district LIKE 'Nord' AND id< 5;
  • OR多条件查询，代表只需要满足多个条件中的任意一个即可
SELECT name,district,population FROM city WHERE district LIKE 'Nord' OR id< 5;
  • AND与OR可以一起使用，但是AND优先生效优先级高于OR
SELECT name,district,population FROM city WHERE district LIKE 'Nord' OR district LIKE 'shanghai' AND id< 5;
  • NOT排除条件，一般需要组合使用例如NOT LIKE
SELECT * FROM city WHERE id< 5 AND district NOT LIKE 'N%d';
  • WHERE BETWEEN AND 范围查询

例子查询10～20的数据

SELECT name,population FROM city WHERE id BETWEEN 10 AND 20;
  • WHERE LIKE搜索字符查询
SELECT name,district,population FROM city WHERE district LIKE 'Nord';
  • 模糊查询，模糊匹配，模糊搜索字符

使用百分号%代表任意个任意字符，_代表一个任意字符

SELECT name,district,population FROM city WHERE district LIKE 'N%d';
SELECT name,district,population FROM city WHERE district LIKE 'No_d';
  • LIMIT限制显示行数

显示头20行

SELECT * FROM city LIMIT 20;

从第10行开始显示之后10行，显示特定范围

SELECT * FROM city LIMIT 10,10;
  • DISTINCT查询结果不重复
SELECT DISTINCT * FROM city WHERE district id< 20;
  • ORDER BY查询结果排序

Mysql 默认采用升序（ASC），也可以指定采用降序（DESC）

例子里根据population列排序

如果使用order by排序的时候不起作用，原因是order by的字段是varchar类型的，在字段后加上'+0' 则转化为int类型，例如: select * from ORDER BY 字段+0 即可

SELECT * FROM city WHERE id < 10 ORDER BY population;
SELECT * FROM city WHERE id < 10 ORDER BY population DESC;

多列排序，按照先后进行排序，中间用逗号分开

SELECT * FROM city WHERE id <10 ORDER BY countrycode,name;
  • GROUP BY分组查询，将相应的结果组织到一起回显

count(*)是一个统计函数，统计行数

AS给列起一个别名

例子里先用GROUP BY将结果分组，再使用count(*)统计每一个列的行数

SELECT CountryCode,count(*) AS Total FROM city WHERE ID<10 GROUP BY countrycode;

多字段分组，用逗号分隔列名，会先分组一个，再分组第二个

SELECT * FROM city GROUP BY countrycode,district;
  • HAVING过滤分组

用于数据输出前的最后一次筛选，二次过滤使用

SELECT CountryCode,count(*) AS Total FROM city 
WHERE id <101 
GROUP BY CountryCode
HAVING count(*) >10;
  • WITH ROLLUP 统计求和

需要配合GROUP BY使用，输出结果的末尾会增加一个总和

mysql> select name,count(*) as total from t group by name with rollup;
+-------+-------+
| name  | total |
+-------+-------+
| qq    |     2 |
| simon |     2 |
| NULL  |     4 |
+-------+-------+
3 rows in set (0.00 sec)

mysql>

1.2.11.2. 多表查询操作

1.2.11.2.1. 内连接查询INNER JOIN

内连接查询使用比较运算符进行表间列数据的比较操作。

并输出符合条件的结果。

FROM后面跟着要查询的多个表，用逗号分隔

SELECT后面查询的列需要声明从哪个表查，例如City.Name查询的是City表的Name列，

如果某一列只存在于其中一个表，这个列可以不需要声明表名，例如ID

如果不加上WHERE会出现显示异常，称为笛卡尔积的现象

#简单写法
SELECT ID, City.Name, Population, LifeExpectancy
FROM City, Coutry
WHERE ID< 10 and City.CountryCode = Country.Code;

#标准写法
SELECT ID, City.Name, Population, LifeExpectancy
FROM City INNER JOIN Coutry
WHERE ID< 10 and City.CountryCode = Country.Code;
1.2.11.2.2. 外连接查询

在内连接的基础上，还包含左表中所有不符合条件的数据行，并在其中的右表列填写NULL

OUTER JOIN,LEFT and RIGHT ON

外连接分为左连接与右连接

左连接代表以左表作为基准LEFT OUTER JOIN ON

右连接代表以右表作为基准RIGHT OUTER JOIN ON

SELECT ID, City.Name, City.Population, LifeExpectancy
FROM City LEFT OUTER JOIN Country
ON ID<10 and City.CountryCode=Country.Code LIMIT 10;

1.2.11.2.3. 子查询

  • ANY与SOME子查询

通过创建表达式，对返回对结果进行比较，并输出符合条件的结果

下面例子，查询t2表的name与age列，并过滤出age大于t1表中任何age的结果

SELECT name, age FROM t2
WHERE age > ANY (SELECT age FROM t1);
  • EXISTS与NOT EXISTS子查询

这是一种判断子查询

EXISTS判断子查询是否有返回行，如果有则执行外层语句中的查询，如果没有则不执行。

NOT EXISTS相反

例子中，查询state表，过滤出Nginx等于Fail的结果，如果结果存在，则再查询log表过滤出category='Nginx'

SELECT * FROM log
WHERE category='Nginx'
AND EXISTS (SELECT * from state WHERE Nginx='Fail');
  • IN子查询

判断子查询是否有返回行，如果有则执行外层语句中的查询，如果没有则不执行。

且有返回行的情况下，比对查询结果，输出值相同的行

例子查询blacklist表的Name列，如果有数据，则与People比对，输出People中Name存在于Blacklist的行

SELECT * FROM People
WHERE name IN (SELECT Name FROM blacklist);
  • UNION子查询

用于合并查询结果，可以将多条select结果组合成单个结果。

要求被组合的表列数必须相同。数据类型也必须相同。

默认组合会去掉相同的结果，只留下一条

SELECT * FROM t1
UNION SELECT * FROM t2;

而加上ALL语句，则会将重复的行都显示出来

SELECT * FROM t1
UNION ALL SELECT * FROM t2;

1.2.11.3. 使用函数查询

  • COUNT()函数，统计行数

默认用于统计所有数据行的总行数，不包括空行

SELECT COUNT(*) FROM city;

统计特定列的行数

SELECT COUNT(name) FROM city;

  • SUM()函数，数据求和

用于列求和，在数字类型的数据使用可用，在字符类型列使用则会返回0

SELECT SUM(population) from city;

  • AVG()函数，统计平均

SELECT AVG(population) from city;

  • MAX()函数，取出列最大值

SELECT name,MAX(population) from city;

  • MIN()函数，取出最小值

SELECT name,MIN(population) from city;

1.2.11.4. 正则表达式查询REGEXP

  • 以特定字符开头 REGEXP ^

SELECT * FROM city WHERE contrycode like 'CHN' and District REGEXP '^z';

  • 以特定字符结尾 REGEXP $

SELECT * FROM city WHERE contrycode like 'CHN' and District REGEXP 'g$';

  • 匹配任意单个字符REGEXP .

SELECT * FROM city WHERE contrycode like 'CHN' and District REGEXP 'C.N';

  • 匹配前面的字符0个或多个REGEXP *

例子中可匹配开头包含S，或开头包含Sh的结果，等价于{0,}

例如可匹配到Shanghai、S、Hongkong

SELECT * FROM city WHERE district REGEXP '^Sh*';

  • 匹配前面的字符1个或多个REGEXP +

例子中可匹配开头包含Sh的结果，例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}

SELECT * FROM city WHERE district REGEXP '^Sh+';

  • 匹配一个字符串或另外一个或多个字符串|
SELECT * from city
WHERE district REGEXP 'Shan｜Guang'
  • 匹配任意一个字符[Sh]

如果写 [^Sh]则是不包含这两个字母的结果

SELECT * from city
WHERE district REGEXP '[Sh]'
  • 匹配指定字符连续出现的次数h{1,2}

h{1,}与h{1}相当于匹配h连续出现1次或以上

SELECT * from city
WHERE district REGEXP 'h{1,5}'

1.2.12. 数据库视图

视图是一个虚拟表，是从数据库中一个或多个表导出的表。

视图是一个编译好的sql语句，而表不是

视图保存在内存中，所以速度更快

当建视图的SQL语句中包含以下子句时，无法使用MERGE算法：

  • 聚集函数
  • DISTINCT
  • GROUP BY
  • HAVING
  • 集合操作（UNION，UNION ALL）
  • 子查询

视图的特点：

  • 视图用于提高安全性
  • 简化工作
  • 逻辑独立

1.2.12.1. 创建单表视图

语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED ] MERGE | TEMPTABLE}]
# REPLACE 重新设置视图时使用
# ALGORITHM 定义视图算法，默认 undefined 会自动选择合适的视图
# MERGE 合并视图的语意定义，如果能使用底层表的索引则会自动使用这个算法
# TEMPTABLE 如果底层数据表没有索引，则使用这个算法，该算法会创建一个临时表，效率更低
VIEW view_name 【（column_list)】
# 设置视图的名称，可选性加上列名称
AS SELECT_statement
# 视图的查询语句
[WITH [ CASCADED | LOCAL ] CHECK OPTION]
# CASCADED 默认值，更新时必须满足底层表的条件，例如非空约束等等
# LOCAL 更新时仅满足该视图本身定义的条件即可，忽视底层表的数据结构约束

# 创建名为view_user的视图，其值包含从表user查出的列name, age
CREATE VIEW view_user
AS SELECT name, age FROM user;

1.2.12.2. 查看视图

# 查询视图
SELECT * FROM view_user;

#查看视图结构
DESC view_user;

#查看创建视图的sql语句（不能加引号）
SHOW CREATE VIEW view_user;

#查看表/视图的属性信息
SHOW TABLE STATUS LIKE 'view_userinfo' \G;

#在infomation_schema表view列中查看视图
SELECT * FROM infomation_schema.views \G;

1.2.12.3. 修改视图

因为视图是一个虚拟表，其中显示的数据是视图指向的基本表的数据

修改或删除视图的内容就相当于修改或删除了视图所指向表的内容

# 修改视图的表内容
UPDATE view_user SET age = 20 
WHERE name='simon';

# 修改视图的结构
CREATE OR REPLACE VIEW view_user
AS SELECT id, name FROM user;

# 使用ALTER语句修改视图结构
ALTER [ALGORITHM = {UNDEFINED ] MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS SELECT_statement [WITH [ CASCADED | LOCAL ] CHECK OPTION]

ALTER VIEW view_user
AS SELECT name from user;

1.2.12.4. 创建多表视图

CREATE VIEW view_userinfo(new_name, new_phone)
# 视图名称后括号里设置里视图里列的名称
AS SELECT user.name, userinfo.phone FROM user, userinfo
WHERE id= fid;

1.2.12.5. 删除视图

# 如果被删除的视图原本就不存在，会报错
DROP VIEW view_user;
# 就算视图不存在也不会报错
DROP VIEW IF EXISTS view_user;

1.2.12.6. 设置视图定义人

某些情况下非视图定义者访问视图时，会报错没有权限，所以需要将视图的定义者改为需要访问视图的mysql账户

创建视图时设置视图定义者

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`platform`@`%` SQL SECURITY DEFINER VIEW `v_i18n_resourcekey` AS SELECT 
 1 AS `resourcekey`,
 1 AS `resourcetype`,
 1 AS `appcode`,
 1 AS `createtime`,
 1 AS `createusername`*/;
 
 
 # 主要关注其中的：
 DEFINER=`platform`@`%` SQL SECURITY DEFINER

已存在的视图修改定义者

# 先查询视图的创建语句
mysql> SHOW CREATE VIEW ACT_ID_GROUP \G
*************************** 1. row ***************************
                View: ACT_ID_GROUP
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`srment`@`%` SQL SECURITY DEFINER VIEW `ACT_ID_GROUP` AS select `t`.`roleid` AS `id_`,1 AS `rev_`,`t`.`rolename` AS `name_` from `s_role` `t` where (`t`.`stopflag` = 0)
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.00 sec)

根据创建视图的语句修改，将create命令改为ALTER
ALTER ALGORITHM=UNDEFINED DEFINER=`srment`@`%` SQL SECURITY DEFINER VIEW `ACT_ID_GROUP` AS select `t`.`roleid` AS `id_`,1 AS `rev_`,`t`.`rolename` AS `name_` from `s_role` `t` where (`t`.`stopflag` = 0)

1.2.13. 数据库锁

1.2.13.1. MyISAM数据库锁

#给表加写锁
LOCK TABLES 'table' write;
#给表解锁
UNLOCK TABES;

2. Shell命令

2.1. 初始化mysql，创建默认库

./mysql_install_db --basedir=/usr/local/mysql/(mysql的安装路径) \
--datadir=/var/lib/mysql(数据库的存放路径) \
--no-defaults --user=mysql

2.2. 在shell环境里执行sql语句

使用-e参数在shell环境里执行sql语句

其中i是外部变量

md5()函数用于生成值的md5值

i=10
mysql db1 -e "insert into test1 value ($i, md5($i));"

3. 关键说明

3.1. 内置库

3.1.1. infomation_schema

这个库并不存在磁盘，而是mysql启动时临时创建出来的

这个数据库中保存了MySQL服务器所有数据库的信息。

如数据库名，数据库的表，表栏的数据类型与访问权限等。

再简单点，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，

每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在information_schema里面。

  • information_schema的表schemata中的列schema_name记录了所有数据库的名字
  • information_schema的表tables中的列table_schema记录了所有数据库的名字
  • information_schema的表tables中的列table_name记录了所有数据库的表的名字
  • information_schema的表columns中的列table_schema记录了所有数据库的名字
  • information_schema的表columns中的列table_name记录了所有数据库的表的名字
  • information_schema的表columns中的列column_name记录了所有数据库的表的列的名字

3.1.2. performance_schema

这个库并不存在磁盘，而是mysql启动时临时创建出来的

主要用于保存性能收集信息

3.2. 索引

  • 普通索引和唯一索引
    • 普通索引是MySQL的基本索引类型
    • 唯一索引对应列的值必须唯一，但允许空值。如果是组合索引，则列值但组合必须唯一。
    • 主键索引是一种特殊的唯一索引，不允许空值。给列添加主键约束时，会自动添加主键索引。
  • 单列索引和组合索引
    • 单列索引指只包含一列的索引。一个表可以有多个单列索引。
    • 组合索引指表的多个字段组合上创建的索引。遵循做前缀组合。
  • 全文索引
    • FULLTEXT类型索引。可以在CHAR，VARCHAR或者TEXT类型的列上创建。
    • 仅MyISAM支持
  • 空间索引
    • 对空间数据类型对字段建立的索引。

索引原则

  • 索引并非越多越好。每次插入数据，就会触发重新计算索引，如果索引多将造成很大压力。
  • 数据量不多不需要键索引。
  • 列中的值变化不多也不需要建索引，因为查询时数据会存入缓存，缓存速度很快。
  • 经常排序和分组的数据列要建立索引。
  • 唯一性约束对应使用唯一性索引。

3.2.1. 创建表的时候创建索引

3.2.1.2. 创建普通索引

定义好每个列后使用INDEX语句声明要创建索引的列，例子中针对name列创建索引

CREATE TABLE t1
  (
    id INT PRIMARY KEY,
    name VARCHAR(10),
    sex ENUM('F', 'M', 'UN'),
    INDEX(name) 
  )ENGINE=InnoDB CHARACTER SET utf8;

3.2.1.3. 创建唯一索引

UNIQUE INDEX关键词创建唯一索引，其后必须跟着可自定义的索引名称id_in，最后声明对id列创建索引

CREATE TABLE t1
  (
    id INT NOT NULL,
    name VARCHAR(10),
    sex ENUM('F', 'M', 'UN'),
    UNIQUE INDEX 'id_in' ('id')
  )ENGINE=InnoDB CHARACTER SET utf8;

3.2.1.4. 创建单列索引

用INDEX关键词创建单列索引，其后跟上自定义的索引名称'name_in'，之后声明对name列创建索引，并且指定索引长度为10个字符

CREATE TABLE t1
  (
    name VARCHAR(10),
    sex ENUM('F', 'M', 'UN'),
    INDEX 'name_in' ('name'(10))
  )ENGINE=InnoDB CHARACTER SET utf8;

3.2.1.5. 创建组合索引

组合索引与单列所以的区别就在于，创建索引事声明的列为多个

使用了组合索引，查询语句的查询条件必须包含了索引声明的第一个列（如id）才会触发索引查询

例如where name like 'Tom' and age <25 无法触发索引查询

例如where name like 'Tom' and id <25 能触发索引查询

CREATE TABLE t1
  (
    id INT NOT NULL,
    name VARCHAR(20),
    age INT NOT NULL,
    INDEX muti_in (id,name) 
  )ENGINE=InnoDB CHARACTER SET utf8;

3.2.1.6. 创建全文索引（FULLTEXT索引支持MyISAM，不支持InnoDB）

使用FULLTEXT INDEX关键词创建全文索引

CREATE TABLE t1
  (
    id INT NOT NULL,
    name VARCHAR(20),
    age INT NOT NULL,
    info TEXT,
    FULLTEXT INDEX 'info_in' (info)
  )ENGINE=InnoDB CHARACTER SET utf8;

3.2.2. 对已有表创建或删除索引

使用ALTER创建索引

ALTER TABLE t1 ADD INDEX nameIdx (name(20));

使用CREATE INDEX创建索引，对t1表的name列创建索引

CREATE INDEX nameIdx ON t1(name);

使用ALTER删除索引

ALTER TABLE t1 DROP INDEX nameIdx;

使用DROP INDEX删除索引

DROP INDEX nameIdx ON t1;

3.2.3. 查看表拥有哪些索引

show create table t1;

4. Mysql的权限管理

Mysql使用逐级下查的方式确认权限，使用以下的顺序查询用户权限，

当匹配到有权限则不再继续下查

  • mysql先查询mysql库的user表，user表是全局生效的，当用户对库有权限则对所有库都有权限
  • 之后再查询db表，db表内描述的是用户对某一个库的权限
  • 之后再查询host表，用户对应用户主机的权限
  • 之后查询tables_priv表的权限，或procs_priv，用户对表级别的权限
  • 之后是columns_priv表，用户对某个列的权限
  • 如果以上的查询结果都是no，则返回用户无权限

4.1. 创建用户

4.1.1. CREATE USER语句创建

# 这样创建的用户没有任何权限
CREATE USER 'username'@'host' [ IDENTIFIED BY 'PASSWORD' ];

# 创建用户允许从任意主机访问过来
CREATE USER 'simon'@'%' IDENTIFIED BY 'PASSWORD' ;

# 创建用户只允许从本地访问
CREATE USER 'simon'@localhost ;

4.1.2. GRANT语句创建用户并授权，如果用户不存在则会自动创建

# 语法
GRANT <ALL|priv1, priv2, ...privn> 
# 设置授予的数据操作权限，all就是所有权限
ON [object]
# object可以是表、函数、存储过程
[WITH GRANT OPTION];

WITH GRANT OPTION]附加设定，附加设定有以下可选：

1、GRANT OPTION代表给这个用户的授权允许下发，允许把自己权限下发给其他人

2、MAX_QUERIES_PER_HOUR设定每小时能发起几次查询

3、MAX_UPDATES_PER_HOUR设定每小时能发起几次数据更新操作

4、MAX_CONNECTIONS_PER_HOUR允许每小时发起多少次连接

5、MAX_USER_CONNECTIONS允许该用户发起总连接多少个

#授权simon用户允许从192.168.1.1发起连接，并允许操作db1库的所有表，允许操作表操作
GRANT ALL ON db1.* to 'simon'@'192.168.1.1' ;

4.2. FLUSH PRIVILEGES刷新授权表

将内存中的缓存信息写入磁盘

MySQL [wms_im]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MySQL [wms_im]>

4.3. 删除用户

删除用户并不是删除一个用户所有的授权

而是删除某一个用户从某个来源地址的授权

DROP USER 'username'@'host';

4.4. 查看用户权限

查询时正常会看到一个用户存在两条GRANT

其中一条GRANT USAGE代表创建用户，这句没有赋予任何权限

SHOW GRANTS FOR 'username'@'host';
SHOW GRANTS FOR 'username'@'host' \G;

mysql> show grants for 'simon';
+-------------------------------------------------+
| Grants for simon@%                              |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `simon`@`%`               |
| GRANT ALL PRIVILEGES ON `test`.* TO `simon`@`%` |
+-------------------------------------------------+
2 rows in set (0.00 sec)

4.5. 回收权限

语法

REVOKE <ALL|priv1, priv2, ...privn>
[ON table1, table2, ...tableN]
FROM 'username'@'host' [, 'username'@'host'];

例

REVOKE ALL ON db1.* FROM 'simon'@'192.168.1.1';

4.6. 修改密码

4.6.1. SET PASSWORD修改密码

# 修改自己的密码
SET PASSWORD=PASSWORD('yourpassword');

SET PASSWORD=PASSWORD('1234');

# 修改其他用户密码
SET PASSWORD FOR 'user'@'host' =PASSWORD('newpassword');

4.6.2. 直接修改user表来修改密码

UPDATE mysql.user SET
PASSWORD=PASSWORD('newpassword')
WHERE User='simon' AND Host= 'host';

4.6.3. 使用mysqladmin命令修改密码

mysqladmin -u username -p'oldpassword' password "newpassword"

4.7. 忘记root密码怎么处理

1、关闭数据库

2、使用这个命令启动mysqlmysqld_safe --skip-grant-tables &

3、使用空密码进入数据库（mysql命令后直接回车）

4、使用UPDATE语句修改root密码

update user set password=password('newpass') where user='root';

5、关闭数据库并重新以正常方式启动

5. 参数配置

5.1. 查看参数

mysql的参数，mysql官方叫变量（Varialbes）

MySQL的变量分为以下两种：

  • 系统变量：配置MySQL服务器的运行环境，可以用show variables查看

  • 状态变量：监控MySQL服务器的运行状态，可以用show status查看

5.1.1. 系统变量

系统变量按其作用域的不同可以分为以下两种：

  • 分为全局（GLOBAL）级：对整个MySQL服务器有效

  • 会话（SESSION或LOCAL）级：只影响当前会话

有些变量同时拥有以上两个级别，MySQL将在建立连接时用全局级变量初始化会话级变量，但一旦连接建立之后，全局级变量的改变不会影响到会话级变量。

5.1.1.1. show vairables查看系统变量

mysql> show variables like 'log%';
mysql> show variables where Variable_name like 'log%' and value = 'NO';

show vairables优先显示会话级别变量的值，如果值不存在则显示全局级别变量的值。

查询的时候也可以加上GLOBAL或SESSION关键字区分查询的级别：

show global variables;
show session/local variables;

在存储过程中引用系统变量的值，可以这样引用：

@@GLOBAL.var_name
@@SESSION.var_name
@@LOCAL.var_name

如果变量名前没有级别限定符，将优先显示会话级别的值。

5.1.1.2. 设置和修改系统变量值

mysql启动时修改，有两种方法：

  • mysqld命令行参数，如：mysqld --max_connections
  • 将配置写入my.cnf启动时生效

mysql启动后修改的方法，通过SET语句：

# 全局参数

set global 参数名=参数值；
set @@global.参数名:=参数值；

# 会话参数

set [session] 参数名=参数值；
set @@session.参数名:=参数值；

注意，在运行时设置的值不允许使用后缀字母K、M等。所以需要用表达式来达到效果

例子：

SET GLOBAL read_buffer_size = 2*1024*1024

5.2. 常见参数配置

5.2.1. 最大连接数

max_connections=2000
#默认值：
max_connections=151

指定MySQL可能的连接数量。当MySQL主线程在很短的时间内得到非常多的连接请求，该参数就起作用，之后主线程花些时间（尽管很短）检查连接并且启动一个新线程。

show variables like 'back_log';

back_log=1024
#默认值：
back_log=80

back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。

也就是说，如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中以等待某一连接释放资源，该堆栈的数量即back_log

如果等待连接的数量超过back_log，将不被授予连接资源。将会报错：

unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.

back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效。

查看当前系统的TCP/IP连接的侦听队列的大小命令：

cat /proc/sys/net/ipv4/tcp_max_syn_backlog

对于Linux系统推荐设置为大于512的整数。

修改系统内核参数，可以编辑/etc/sysctl.conf去调整它。

如：net.ipv4.tcp_max_syn_backlog = 2048，改完后执行sysctl -p 让修改立即生效。

5.2.2. 索引块缓冲区大小

key_buffer_size = 32M
#默认值：
key_buffer_size=8M

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads /key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%'获得）。 key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。 对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）

6. Mysql数据库备份

6.1. 备份前的规划

  • 需要备份哪些库
  • 数据库的体积
  • 确认存储引擎
  • 选择备份工具以及备份方式
  • 锁和宕机带来的影响
  • 备份保存到什么地方
  • 数据变化的频率
  • 行业规范或者合规性

备份方式大致分为两种：

1、操作系统级别的备份，文件备份

2、逻辑方式备份，SQL语句方式备份

6.2. 操作系统级别备份，文件备份

特点：

  • 操作简单
  • 速度最快
  • 需要停服务操作
  • 需要结合其他手段共同使用

6.2.1. mysqlhotcopy

  • mysqlhotcopy是MySQL软件包自带的备份工具，企业版与社区版都包含
  • mysqlhotcopy是一个Perl脚本，需要安装相关软件包
  • 其本质是使用锁表语句后再使用cp或scp命令拷贝数据库文件
  • 备份前后执行FLUSH TABLE WITH READ LOCK与UNLOCK TABLES与mysqldump相似
  • 仅支持MyISAM

语法:

mysqlhotcopy -u root -p password mydb /mnt/backup

例：

mysqlhotcopy -u root -p 'password' mydb /backup
mysqlhotcopy -u root -p 'password' mydb1 mydb2 /backup

6.2.2. LVM卷快照备份

  • 几乎是热备，因操作之前需要锁表，操作后需要解锁，无法预计锁表与解锁消耗的时间
  • 支持所有存储引擎
  • 备份速度快
  • 虽然很快，但依然需要锁表，锁表后对磁盘LVM卷做一个快照
  • 数据如果分布在多个卷上，操作复杂度将翻倍

例：

#创建LVM
pvcreate /dev/sdb
vgcreate testvg /dev/sdb
lvcreate -L 200M -n testlv testvg
mkfs -t ext4 /dev/testvg/testlv
mount /dev/testvg/testlv /mnt

#导入数据库，这部分操作省略 。。。

#锁表
mysql> flush tables with read lock;
#记录一下二进制日志数字，留意File与Position字段
mysql> show master status \G;

#创建快照
#使用/dev/testvg/testlv产生一个快照来创建一个大小为50M的名称为snap1的卷
lvcreate -L 50m -s -n snap1 /dev/testvg/testlv 

#解锁表
mysql> unlock tables;

#之后将snap1卷拷贝走即可

6.2.3. mylvmbackup

  • mylvmbackup是一个开源工具，可以自动创建lvm卷或快照
  • 依赖perl

官网：www.lenzg.net/mylvmbackup…

6.3. 逻辑方式备份

特点：

  • 其实是用SQL语句描述数据库，或是输出所有的查询结果
  • 兼容性最好，跨版本、平台、产品
  • 执行效率最慢，影响较大

6.3.1. mysqldump

  • mysqldump是MySQL软件包自带的备份工具
  • 使用SQL语句描述数据库及数据并导出
  • 备份时，在MYISAM引擎锁表，InnoDB引擎锁行
  • 数据量很大时不推荐使用，消耗时间长

语法：

#备份单表
mysqldump [OPTIONS] databases [tables]
#备份多个或1个库
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3 ...]
#备份所有库
mysqldump [OPTIONS] --all-databases [OPTIONS]

例：

#备份单个表
mysqldump -uroot -p viewdb > backup.sql
#备份多个表
mysqldump -uroot -p viewdb,worlddb >backup.sql

6.3.2. Percona XtraBackup

XtraBackup是一个开源、免费的支持对InnoDB进行热备份对软件。

XtraBackup由Percona发布、支持。

XtraBackup特性：

  • 不暂停服务创建InnoDB热备份。
  • 为Mysql做增量备份。
  • 在Mysql服务器之间做在线表迁移。
  • 简化MySQL Replication创建。
  • 低负担备份数据。
  • Xtrabackup不会备份MyISAM表、.frm文件以及数据库其余部分，需要另外操作备份

安装XtraBackup，通过官网下载：www.percona.com/downloads/P…

使用XtraBackup做完整备份

#如果被备份的mysql版本为5.6，则使用xtrabackup_56命令备份
#备份表空间ibd文件
xtrabackup_56 --backup --target-dir=/var/lib/backup

# 处理事务一致性，将事务日志中已提交的事务补充到库文件内
# --use-memory=500M 用于设置备份时占用多少内存，内存足够多的时候可省略
# 需要执行2次才能达到一致性，数据与事务日志的一致性
xtrabackup_56 --use-memory=500M --prepare --target-dir=/var/lib/backup

使用XtraBackup 增量备份

# 模拟每日增量备份，创建备份目录
mkdir -p {mon.tue,wed}

#创建周一备份
xtrabackup_56 --backup --target-dir=/mon/

#参考周一的备份创建周二的差异备份，使用参数--incremental-basedir
xtrabackup_56 --backup --target-dir=/tue/ --incremental-basedir=/mon/

#参考周二创建周三的备份
xtrabackup_56 --backup --target-dir=/wed/ --incremental-basedir=/tue/

恢复增量备份 所有的备份恢复都应该设置 apply-log-only 参数（only 指的就是只回放 redo log 阶段，跳过 undo 阶段），避免未完成事务的回滚。

# 处理原始备份的一致性，将事务日志补充到备份使其一致
xtrabackup_56 --prepare --apply-log-only --target-dir=/var/lib/backup/

# 根据需要将增量合并至原始备份
# 这里举例将周一的增量备份追加到基础备份
xtrabackup_56 --prepare --apply-log-only --target-dir=/var/lib/backup/ --incremetal-dir=/mon/

# 然后将周二的增量备份追加
xtrabackup_56 --prepare --apply-log-only --target-dir=/var/lib/backup/ --incremetal-dir=/tue/

# 最后处理一次事务一致性，得到最终备份
xtrabackup_56 --prepare --target-dir=/var/lib/backup/

6.3.3. Percona innobackupex

innobackupex是一个对xtrabackup封装的perl脚本， 其提供了用于myisam和innodb引擎，及混合使用引擎备份的能力，主要是为了方便同时备份InnoDB和MyISAM引擎的表。在处理myisam表时需要加一个读锁。

其加入了一些使用的选项。如slave-info可以记录备份恢复后作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重建slave。

innobackupex比xtarbackup有更强的功能，它整合了xtrabackup和其他的一些功能，它不但可以全量备份/恢复，还可以基于时间的增量备份与恢复。

使用innobackupex做完整备份

# 生成一个完整备份，备份完成后会自动产生一个日期目录
innobackupex --user=root --password=passwd /var/lib/backup/

# 生成InnoDB日志，使备份可用
innobackupex --use-memory=512m --apply-log /var/lib/backup/2019-02-28_02-47-28/

使用innobackupex恢复备份

#这条命令根据my.cnf内的datadir设置将备份恢复至数据库
innobackupex --copy-back /var/lib/backup/2019-02-28_02-47-28/

#修改文件权限
chmod -R mysql.mysql /var/lib/mysql

使用innobackupex做增量备份

# 先做一个完整备份，假设当天为周一
innobackupex --user=root --password=passwd /var/lib/backup/mon/

# 创建增量备份，直接运行会报错目录不存在，目录需要手动创建
# 以周一为基准创建周二的增量备份
innobackupex --incremental /var/lib/backup/tue/ --incremental-basedir=/var/lib/backup/mon/ --user=root --password=password

恢复增量备份

#先处理原始备份的一致性
innobackupex --apply-log --redo-only /var/lib/backup/mon/--use-memory=500m --user=root --password=passwd

# 合并增量备份至原始备份，mon是完整备份，tue是增量备份
# 如果有多个增量备份，也一样按顺序合并到基础备份去
innobackupex --apply-log --redo-only /var/lib/backup/mon/日期/ --incremental-dir=/var/lib/backup/tue/日期/ --use-memory=500m --user=root --password=passwd

# 处理完整备份的最终一致性，将redo与undo都合并
innobackupex --apply-log /var/lib/backup/mon/日期/ --use-memory=500m --user=root --password=pass

# 恢复备份到生产库中
innobackupex --copy-back /var/lib/backup/mon/日期/

#修改文件权限
chmod -R mysql.mysql /var/lib/mysql
avatar
文章
阅读
粉丝