摘《mysql5.5从0开始》2

• 10 存储过程和函数
  存储过程就是一条或则多条sql语句的集合，可视为批文件，但是起作用不仅限于批处理。
• 10.1 创建存储过程和函数
  存储程序可以分为存储过程和函数，mysql中创建存储过程和函数分别是：CREATE PROCEDURE和create function.使用call 语句来调用存储过程，只能输出变量返回值，函数可以从语句外调用，也能返回标量值。存储过程也可以调用其他存储过程。
• 10.1.1 创建存储过程
  create procedure语句:
  create procedure sp_name ([proc_parameter]) [characteristics ...] routine_body
  sp_name为存储过程的名称；proc_parameter为指定存储过程的参数列表。列表形式如下：
  [IN|OUT|INOUT] param_name type
  in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；param_name表示参数名称；type表示参数类型（mysql中任意类型）。
  characteristics指定存储过程的特性：

characteristics：LANGUAGE SQL characteristics:[NOT] DETERMINISTIC characteristics：{CONTAINS SQL| NO SQL| READS SQL DATA|MODIFIES SQL DATA} characteristics：SQL SECURITY{DEFINER|INVOKER} characteristics:COMMENT 'string'

routine_body是SQL代码的内容，可以用BEGIN...END 来表示SQL代码的开始和结束。
编写存储过程并不是件简单的事情，肯存储过程中需要复杂的sql语句，并且要有创建存储过程的权限：但是使用存储过程将简化操作，减少冗余的操作步骤，同时，还可以减少操作过程中的失误，提高效率。
AvgFruitPrice存储过程：
CREATE PROCEDURE AvgFruitPrice()
BEGIN
SELECT AVG(f_price) AS avgprice
FROM fruits;
END;

'DELIMITER' 修改MySQL的结束符

使用delimiter命令时，避免使用反斜杠‘\’；反斜杠是MySQL的转义字符。
SELECT INTO FROM语句：
SELECT vale1, value2 into Table2 from Table1;
要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中.

• 10.1.2 创建存储函数 CREATE FUNCTION
  CREATE FUNCTION func_name ([func_parameter])
  RETURMS type
  [characteristic ...] routine_body
  func_name表示存储函数的名称；func_parameter为存储过程的参数列表，参数列表形式：
  [IN|OUT|INOUT] param_name type
  in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；param_name表示参数名称；type表示参数类型（mysql中任意类型）。
  RETURNS type 表示函数返回数据的类型；characteristic：指定存储函数的特性，取值与创建存储过程时相同。
  创建存储函数NameByZip:
  CREATE FUNCTION NameByZip()
  RETURNS CHAR(50)
  RETURN (SELECT s_name FROM suppliers WHERE s_call='123456');

代码执行结果 提示：函数体必须包含一个RETURN value语句

• 10.1.3 变量的使用
  变量可以在子程序中声明并使用，作用范围是在BEGIN...END程序中。
  （1）定义变量DECLARE
  DECLARE var_name[,varname]... date_type [DEFAULT value];
  var_name为局部变量的名称。DEFAULT value子句给变量提供一个默认值。值可以被声明为一个常数、还可以被指定为一个表达式。如果没有DEFAULT 子句，初始值为NULL；
  定义myparam变量，类型为INT，默认值为100：
  DECLARE myparam INT DEFAULT 100;
  （2）为变量赋值SET
  SET var_name = expr [, var_name = expr]...;
  DECLARE var1,var2,var3 INT;
  SET var1=10,var2=20;
  SET var3=var1+var2;
  通过SELECT ...INTO 为一个或多个变量赋值：
  SELECT col_name[,...] INTO var_name[, ...] table_expr;
  col_name表示字段的名称；var_name表示变量的名称；table_expr表示查询条件表达式，包括表名称和WHERE子句。
  DECLARE fruitname CHAR(50);
  DECLARE fruitprice DECIMAL(8,2);
  SELECT f_name,f_price INTO fruitname,fruitprice FROM fruits WHERE f_id='a1';
• 10.1.4 定义条件和处理程序
  （1）定义条件
  DECLARE condition_name CONDITION FOR [condition_type]

[condition_type]:
SQLSTATE [VALUE] sqlstate_value | mysql_error_code

格式分析

定义"ERROR 1148(42000)"，名称为command_not_allowed:
使用sqlstate_value：
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
使用mysql_error_code：
DECLATE command_not_allowed CONDITION FOR 1148;
（2）定义处理程序

定义处理程序，使用DECLATE语句 condition_value参数说明 handler_type错误处理方式 condition_value错误类型取值 sp_statement参数是程序语句段；表示在遇到定义的错误时，需要执行的存储过程或函数 如果遇到sqlstate_value值为‘42s02’，执行CONTINUE,并且输出“NO_SUCH_TABLE”信息 如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且输出“NO_SUCH_TABLE”信息 这里先定义no_such_table条件，遇到1146错误就只想CONTINUE操作 SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出“ERROR”信息 NOT FOUND 捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出“NO_SUCH_TABLE”信息 SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出“ERROR”信息 实例 调用存储过程 查看调用过程结果 实例解析说明 @var_name表示用户变量，使用SET语句为其赋值当前客户端定义变量不能被其他客户端看到或使用

• 10.1.5 光标的使用
  查询语句可能返回多条记录，如果数据了非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。
  （1）声明光标
  MySQL中使用DECLARE关键字声明光标：
  DECLARE cursor_name CURSOR FOR select_statement
  cursor_name表示光标的名称；select_statement表示SELECT 语句的内容，返回一个用于创建光标的结果集。
  声明cursor_fruit的光标：
  DECLARE cursor_fruit CURSOR FOR SELECT f_name,f_price FROM fruits;
  （2）打开光标
  打开先前声明的名称为cursor_name的光标：
  OPEN cursor_name{光标名称}
  示例：OPEN cursor_fruit;
  （3）使用光标
  FETCH cursor_name INTO var_name [, ...] ...{参数名称}
  cursor_name表示光标名称；var_name表示将光标中的SELECT 语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。
  使用cursor_fruit光标，将查询出来的数据存入fruit_name和fruit_price这两个边路中：
  FETCH cursor_fruit INTO fruit_name,fruit_price;
  （4）关闭光标
  CLOSE cursor_name;
  示例：CLOSE cursor_fruit;
• 提示：MySQL中光标只能在存储过程和函数中使用。
• 10.1.6 流程控制的使用 IF/CASE/LOOP/WHILE/LEAVE/ITERATE/REPEAT/WHILE
  （1）IF语句

IF ... THEN

MySQL中还有一个IF()函数，不同于这里的IF语句。
（2）CASE语句

第一种CASE语句 第二种CASE语句 提示

（3）LOOP语句

LOOP语法 示例：LOOP使用LEAVE子句挑出循环过程

（4）LEAVE语句
LEAVE label
label表示循环的标志。
（5）ITERATE语句
ITERATE将执行顺序转到语句段开头处（再次循环）：
ITERATE label

ITERATE使用约束说明 示例：ITERATE 示例说明

（6）REPEAT语句

REPEAT说明 REPEAT格式 REPEAT格式参数说明 示例：REPEAT

（7）WHILE语句

WHILE说明 WHILE格式 WHILE格式说明

• 10.2 调用存储过程和函数
• 10.2.1 调用存储过程

CALL语句 定义存储过程 调用存储过程

• 10.2.2 调用存储函数

定义存储函数 调用存储函数

• 10.3 查看存储过程和函数
• 10.3.1 SHOW STATUS语句查看存储过程和函数的状态

show status语法及说明

SHOW PROCEDURE STATUS LIKE 'C%'\G;

• 10.3.2 SHOW CREATE语句查看存储过程和函数的定义

SHOW CREATE语法及说明

SHOW CREATE FUNCTION test.CountProc \G;

• 10.3.3 从information_schema.Routines表中查看存储过程和函数的信息
  SELECT *FROM infromation_schema.Routines WHERE ROUTINE_NAME='sp_name';
  ROUTINE_NAME字段中存储的是存储过程和函数名称；sp_name表示存储过程和函数名称。

• 10.4修改存储过程和函数
  ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...]

参数说明 修改与创建的语句中的参数基本一样

ALTER PROCEDURE CountProc MODIFIES SQL DATA SQL SECURITY INVOKER;

修改结果

• 10.5 删除存储过程和函数
  DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name

IF EXISTS是MySQL的扩展，防止发生错误，产生一个SHOW WARNINGS查看的警告

DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;

• 10.7 专家点拨

MySQL存储过程和函数有什么区别 存储过程中的代码不可以改变 存储过程中可以调用其他存储过程 存储过程的参数不要与数据表字段名相同 存储过程中的参数使用中文时，需character set gbk

定义userinfo存储过程：
CREATE PROCEDURE userinfo(IN u_name VARCHAR(55) character set gbk, OUT u_age INT);

创建一个存储过程add_id，计算出表中所有的id之和
CREATE PROCEDURE add_id(out count INT)
BEGIN
DECLARE itmp INT;设置变量
DECLARE cur_id CURSOR FOR SELECT id FROM sch;声明光标
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;捕获所有以02开头的sqlstate_value值，然后执行exit操作，并且关闭光标
SELECT count_sch() INTO count;为变量赋值
SET @sum=0;设置用户变量
OPEN cur_id;打开光标
REPEAT
FETCH cur_id INTO itmp;使用光标
IF itmp<10
THEN SET @sum= @sum+itmp;
END IF;
UNTIL 0 END REPEAT;直到0时才退出循环
CLOSE cur_id;关闭光标
END;

• 11.2 创建视图
  CREATE [OR REPLACE] [ALGORITHM ={UNDEFINED|MERGE|TEMPTABLE}]
  VIEW view_name [column_list]
  AS SELECT_statement [WITH [CASCADED|LOCAL] CHECK OPTION]

语法格式说明 ALGORITHM的取值 CASCADED与LOCAL参数 create view权限 创建视图名称指定为：db_name.view_name

• 11.2.2 在单表上创建视图
  MySQL可以在单个数据表上创建视图：
  CREATE VIEW view_t AS SELECT quantity, price, quantity * price FROM t;
  查看view_t2视图中的数据：
  SELECT * FROM view_t2;
  使用视图的时候，可能用户根本就不需要了解基本表的结构，更接触不到实际表的数据，从而保证了数据库的安全。
• 11.2.3 在多表上创建视图
  MySQL中也可以在两个或者两个以上的表上创建视图，可以使用CREATE VIEW 语句实现：
  CREATE VIEW stu_glass (id,name,glass) AS SELECT student.s_id,student.name,stu_info.glass FROM student,stu_info WHERE student.s_id=stu_info.s_id;
• 11.3 查看视图
  MySQL数据库下的user表中保存着权限信息；查看视图：
  DESCRIBE/SHOW TABLE STATUS/SHOW CREATE VIEW
• 11.3.1 DESCRIBE 语句查看视图基本信息
  DESCRIBE/DESC view_t;

DESC view_t;

• 11.3.2 SHOW TABLE STATUS 语句查看视图基本信息
  SHOW TABLE STATUS LIKE 'view_t\G;
  comment的值为view 说明该表为视图，其他的信息为NULL说明这个是一个虚表。

• 11.3.3 SHOW CREATE VIEW 语句查看视图详细信息
  SHOW CREATE VIEW view_name;

• 11.3.4 在views表中查看视图详细信息
  SELECT * FROM information_schema.views;

• 11.4 修改视图

• 11.4.1 CREATE OR REPLACE VIEW 语句修改视图

修改视图语法

当视图已经存在时，修改语句对视图进行修改；当视图不存在时，创建视图。
实例：修改视图view_t:
CREATE OR REPLACE VIEW view_t AS SELECT * FROM t;

• 11.4.2 ALTER 语句修改视图

ALTER修改视图语法

实例：使用ALTER 语句修改视图view_t:
ALTER VIEW view_t AS SELECT quantity FROM t;

• 11.5 更新视图
  INSERT / UPDATE / DELETE 更新视图。
  （1）UPDATE view_t SET quantity=5;
  对视图view_t更新后，基本表的内容也更新了，同样对基本表t更新后，另外一个视图view_t2中的内容也会更新。
  （2）INSERT INTO t VALUES(3,5);
  对基表t插入一条数据，视图也会跟着更新。
  （3）DELETE FROM view_t2 WHERE price =5;
  删除视图里的记录，实际表中所依赖的数据也被删除。

更新视图的限制

• 11.6 删除视图
  DROP VIEW [IF EXISTS]
  view_name[, ...]
  [RESTRICT|CASCADE]
  drop view if exists stu_glass;
  实例：

创建考上Tsinghua University的学生的视图

视图一句基本表的存在而存在；是基本表的抽象和在逻辑意义上建立的新关系。

• 12 MySQL函数
  MySQL的触发器和存储过程一样，都是嵌入到MySQL的一段程序。触发器是由事件来触发某个操作，事件包括：insert，update，delete语句。如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执行相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。
• 12.1 创建触发器
  触发器是个特殊的存储过程，不同的是存储过程需要call语句来调用，触发器是当一个预定义的时间发生的时候，就会被MySQL自动调用。
• 12.1.1 创建只有一个执行语句的触发器
  CREATE TRIGGER trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
  trigger_name:标识触发器名称；trigger_time：标识触发时机，可以指定为before或after；trigger_event：标识触发事件(insert/update/delete)；tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；trigger_stmt：触发器程序体。触发器程序可以使用begin和end作为开始、结束。
  实例：创建一个单执行语句的触发器：
  CREATE TABLE account(acct_num INT, amount DECIMAL(10,2));
  CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;创建名为ins_sum的触发器，触发条件是向表account插入数据之前，对新插入的amount字段值进行求和计算。

单执行语句触发器 执行

• 12.1.2 创建有多个执行语句的触发器
  CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

实例：多个执行语句的触发器

• 12.2 查看触发器
  查看数据库中已存在的触发器的定义、状态和语法信息。
  show triggers或者在triggers表中查看。
• 12.2.1 show triggers 查看触发器信息
  show triggers\G;
• 12.2.2 在triggers表中查看触发器信息
  语法：SELECT *FROM INFOMATION_SCHEMA.TRIGGERS WHERE conditions;
  SELECT *FROM INFORMATION_SHEMA.TRIGGERS WHERE TRIGGER_NAME='trig_update'\G;
• 12.4 删除触发器
  DROP TRIGGER [schema_name.]trigger_name;
  shema_name:数据库名称，是可选的；没有就从当前数据库中舍弃触发器程序；trigger_name：要删除的触发器名称。

相同的表，事件只能创建一个触发器 及时删除不再需要的触发器

• 13 MySQL用户管理
• 13.1 权限表
  权限表存放在MySQL数据库中，由mysql_install_db脚本初始化。存储账户权限信息表主要有：user、db、host、table_priv、columns_priv、procs_priv
• 13.1.1 user表
  记录允许连接到服务器的账号信息，里面的权限是全局的。
  修改权限，可以使用GRANT语句或UPDATE语句更改user表的这些字段。
• MySQL使用ssl功能？
  user表字段分为4类：用户列、权限列、安全列、资源控制列
  资源控制列：包含4个字段：

1. max_questions：用户每小时允许执行的查询操作次数。
2. max_updates：用户每小时允许执行的更新操作数。
3. max_connections：用户每小时允许执行的连接操作次数。
4. max_user_connnections：用户允许同时建立的连接次数。

• 13.1.2 db表和host表
  db表存储了用户对某个数据库的操作权限，host表存储了某个主机对数据库的操作权限，配合db表对给定主机上的数据库级操作权限做更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。
  db表和host表表结构相似，字段可以分为两类：用户列和权限列。

用户列 权限列

• 13.1.3 tables_priv表和columns_priv表
  tables_priv表用来对表设置操作权限，columns_priv表用来对表的某一列设置权限。

• 13.1.4 procs_priv表
  procs_priv表可以对存储过程和存储函数设置操作权限。

• 13.2 账户管理
  mysql -hlocalhost -uxxxx -pxxxx -P3306 db_name -e'sql_command'
  mysql -hlocalhost -uxxxx -pxxxx -P3306 db_name -e"sql_command"

  
  实例

• 13.2.2 新建普通用户
  （1）使用CREATE USER语句创建新用户
  CREATE USER user_specification
  [, user_specification] ...
  user_specification:
  user@host
  [
  IDENTIFIED BY [PASSWORD] 'password'
  | IDENTIFIED WITH auth_plugin [AS 'auth_string']
  ]

CREATE USER 语法格式参数说明

实例：CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
若没有指定localhost则默认为‘%’（即对所有的主机开放）
密码的哈希值可以使用password()函数获取：

password()函数 实例

CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH my_auth_plugin;

IDENTIFIED BY 与 IDENTIFIED WITH 互斥

（2） 使用GRANT 语句创建新用户
GRANT privileges ON db.table TO user@host [IDENTIFIED BY 'password'] [, user[IDENTIFIED BY 'password']] [WITH GRANT OPTION];

GRANT语句格式参数说明 GRANT实例 SELECT 查询用户的权限
user表中的user和host字段区分大小写，在查询的时候要指定正确的用户名称和主机名。

（3） 直接操作MySQL用户表

直接insert into mysql.user 参数说明
FLUSH PRIVILEGES; 刷新授权表
使用grant并不需要flush privileges;

• 13.2.3删除普通用户
  （1）使用DROP USER 语句删除用户
  DROP USER 'user'@'localhost';
  （2）使用DELETE语句删除用户
  DELETE FROM MYSQL.user WHERE host='hostname' and user='username'

实例

• 13.2.4 root用户修改自己的密码
  （1）使用mysqladmin命令在命令行指定新密码（mysqladmin... password...）
  语法：mysqladmin -u username -h localhost -p password "newpwd"

  
  语法格式参数说明

实例

（2）修改MySQL数据库的user表(update ... set .. where)
语法：UPDATE mysql.user SET Password=password("newpwd") WHERE User="root" and Host="localhost";
注意：这个需要使用FLUSH PRIVILEGES重新加载权限。
（3）使用SET语句修改root用户的密码
语法：SET PASSWORD=PASSWORD("nerpwd");
注意：需要FLUSH PRIVILEGES重新加载权限。

• 13.2.5 root用户修改普通用户密码
  （1）使用SET语句修改普通用户的密码
  语法：SET PASSWORD FOR 'user'@'host' = PASSWORD('newpwd');
  同时，可以使用普通用户（非root）修改自己的密码（省略FOR子句）：
  语法：SET PASSWORD = PASSWORD('newpwd');
  （2）使用UPDATE语句修改普通用户的密码
  语法：UPDATE mysql.user SET Password=PASSWORD("newpwd") WHERE User="username" and Host="hostname";
  注意：需要FLUSH PRIVILEGES重新加载权限。
  （3）使用GRANT语句修改普通用户密码
  语法：GRANT USAGE ON . TO 'user'@'%' IDENTIFIED BY 'newpwd';
  

  实例

• 13.2.6 普通用户修改密码
  普通用户修改自己的密码：SET PQSSWORD = PQSSWORD('newpwd');

• 13.2.7 root密码丢失的解决办法
  先启动mysqld服务在用root密码登录:
  第（1）步：使用--skip-grant-tables选项启动MySQL服务器
  linux：

1. mysqld_safe --skip-grant-tables user=mysql
2. /etc/init.d/mysql start-mysqld --skip-grant-tables
   windows:
3. mysqld --skip-grant-tables
4. mysqld-nt --skip-grant-tables
   第（2）步：使用root用户登录，重新设置密码
   windows平台：

5. net stop mysql
   2.>mysqld --skip-grant-tables
   3.>mysql -u root
   4.>set password=password('newpwd');
   第（3）步：加载权限表
   FLUSH PRIVILEGES;

• 13.3 权限管理
• 13.3.1 mysql的各种权限
  kill命令能用来终止其他用户或更改服务器的操作方式。

kill id来杀掉连接，解决死锁

• 13.3.2 授权
  合理的授权可以保证数据库安全。
  授权分为多个层级：
  （1）全局层级：
  全局权限适用于一个给定服务器中的所有数据库；这些权限存储在mysql.user表中
  GRANT ALL ON . 和 REVOKE ALL ON . 只授予和撤销全局权限。
  （2）数据库层级：
  数据库权限适用于一个给定数据库中的所有目标；这些权限存储在mysql.db和mysql.host表中。
  GRANT ALL ON db_name.和REVOKE ALL ON db_name.*只授予和撤销数据库权限。
  （3）表层级：
  表权限适用于一个给定表中的所有列；这些权限存储在mysql.tables_priv表中。
  GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
  （4）列层级：
  列权限适用于一个给定表中的单一列；这些权限存储在mysql.columns_priv表中。REVOKE,必须指定与被授权列相同的列。
  （5）子程序层级：
  CREATE ROUTINE/ALTER ROUTINE/EXECUTE/GRANT权限适用于已存储的子程序。除了CREATE ROUTINE外，这些权限可以被授予子程序层级，并存储在mysql.procs_priv表中。
  要使用GRANT或REVOKE，必须拥有GRANT OPTION权限，并且必须用于正在授予或撤销的权限。

GRANT语法 GRANT OPTION取值 GRANT格式参数说明 GRANT OPTION取值说明

• 13.3.3 收回权限
  取消对于命名的用户的所有全局层级、数据库层级、表层级和列层级的权限：
  REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host' [, 'user'@'host' ...]

REVOKE ... ON ... FROM...

要使用REVOKE语句，必须拥有mysql数据库的全局CREATE UASER 权限或UPDATE权限。

实例：REVOKE UPDATE ON . FROM 'testuser'@'localhost';
删除账户信息：用DROP USER 语句：
DROP USER 'user'@'host';

• 13.3.4 查看权限
  SHOW GRANTS FOR 'user'@'host';
  SHOW GRANTS;
  查看用户的权限信息：
  SELECT privileges_list FROM user WHERE user='username',host='hostname';

查看用户权限信息

• 13.4 访问控制
  MySQL的访问可控制分为两个阶段：连接核实阶段和请求核实阶段。
• 13.4.1 连接核实阶段

连接核实阶段、密码身份验证、

• 13.4.2 请求核实阶段

请求核实、连接之后，检查权限 请求核实、确认权限 通过向下层级的顺序检查权限表，并不是所有的权限都要执行所有该过程

• 13.6 专家点拨

1. 已经讲一个账户信息从数据库中完全删除，为什么该用户还能登陆数据库?
   user表中存在匿名账户，且密码相同。匿名账户的User字段值为空字符串。
   SELECT * FROM user WHERE User='';
   DROP USER ''@'host';

• 14 数据库备份与还原
• 14.1.1 使用mysqldump命令备份
  mysqldump -u user -h host -ppassword dbname [tbname, [tbname ...]] > filename.sql
  备份文件内容：
  开头是备份信息；
  接着是将一些系统变量值赋值给用户定义变量，以确保被回复的数据库的系统变量和原来备份时的变量相同
  系统变量赋值给用户定义变量
  最后几行使用set语句恢复服务器系统变量原来的值
  将用户定义的变量保存的值赋值给实际的系统变量
  备份文件中的“--”字符开通的行是注释语句；以“/!”开头、“/”结尾的语句是可执行的mysql注释，这些语句可以被MySQL执行，但在其他数据库管理系统被作为注释忽略，提高了数据库的可移植性。

以数据开头的备份文件注意了

• 备份某个表
  mysqldump -u user -h host -p dbname [tblname, [tblname ...]] > filename.sql
  多个表名之间用空格隔开。

• 备份多个数据库
  mysqldump -u user -h host -o -B(或者--databases) [dbname, [dbname ...]] > filename.sql
  使用--databases参数之后，必须指定至少一个数据库的名称，多个库名之间用空格隔开。不使用--databases备份数据库，sql文件不含create database语句，就不会创建数据库。

若表都为MyISAM，应考虑用mysqlhotcopy，这样可以更快的备份和恢复
mysql --help 获得特定版本的完整选项列表
--opt/--quick/--add-locks/--extended-insert/--add-drop-database/--add-drop-tables/--add-locking --all-database(-A)/--comments[=0|1]/--compact/--skip-add-drop-tables/--no-set-names/--skip-disable-keys/--skip-add-locking --compatible=name/--complete-insert(-c)/--debug[=debug_options]/--delete(-D)/--default-character-set=charset -- delete-master-logs--extended-insert(-e)/--flush-logs(-F)/--force(-f) --lock-all-tables(-x)/--single-transaction/--lock-tables(-l)/--no-create-db(-n)/--no-create-info(-t)/--no-data(-d) --opt/--add-drop-tables--add-locking/--create-option/--disable-leys--extended-insert/--lock-tables-quick/--ser-charset/--skip/--skip-add-drop-tables/--skip-quick --password[=password]/-p[password]/--port=port_num/-p port_num --protocol={TCP|SOKET|PIPE|MEMORY}/--replace(-r)/--ignore/--silent(-s)/--socket=path/-S path --user=user_name/-u user_name/--verbose(-v)/--version(-V)/--xml(-X) mysqldump参数使用提示

• 14.1.2 直接复制整个数据库目录
  这样操作前需要将表LOCK TABLES，然后执行FLUSH TABLES（确保开始备份前将所有激活的索引页写入磁盘）
  这种方法不适用InnoDB，不同系统可能不兼容。

提示：MySQL主版本号相同的数据库文件格式相同

• 14.1.3 使用mysqlhotcopy工具快速备份

mysqlhotcopy

语法：
mysqlhotcopy db_name_1, ... db_name_n /path/to/new_directory

参数说明

实例：mysqlhotcopy -u root -p test /usr/backup
要想执行mysqlhotcopy，必须可以访问备份的表文件，具有表的select权限，reload权限（以便执行flush tables）和lock tables权限。

mysqlhotcopy只是将表所在的目录复制到另一个位置，只适用于MyISAM和ARCHIVE

• 14.2 数据还原
• 14.2.1 使用mysql命令还原
  mysql -u user -p [dbname] < filename.sql

source命令导入sql文件：

use dbname;
source filename

• 14.2.2 直接复制到数据库目录

直接复制数据库目录

• 14.2.3 mysqlhotcopy快速恢复

mysqlhotcopy快速恢复、将备份文件复制到MySQL存放数据的位置

chown -R mysql.mysql /var/lib/mysql/dbname

实例： 提示：mysqlhotcopy备份文件恢复时，要恢复的数据库已存在，需drop存在的数据库

• 14.3 数据库迁移
  数据库迁移原因：

1. 需要安装新的数据库服务器。
2. MySQL版本更新。
3. 数据库管理系统的变更（如microsoft sql server 迁移到mysql）。

• 14.3.1 相同版本的MySQL数据库之间的迁移
  实例：
  mysqldump -h abc -uroot -ppassword dbname | mysql -h bcd -uroot -ppassword
  mysqldump导入的数据直接通过管道符“ | ”，传给mysql命令导入到主机bcd数据库中。
  如果要迁移全部数据库，可使用参数--all-databases。

• 14.3.2 不同版本的MySQL数据库之间的迁移
  若要保留旧版本的用户访问控制信息，则需要备份MySQL中的mysql数据库；有中文数据的，迁移过程中需要对默认字符集进行修改，不让可能无法正常显示结果。

• 14.3.3 不同数据库之间的迁移
  不同数据库中的架构不同，定义相同类型的数据的关键字也可能不同，，需要对不兼容的语句进行语句映射处理。
  当然也可以使用工具来进行不同数据库间的数据迁移。

• 14.4 表的导出和导入
  mysql数据库可以到处成sql文本文件、xml文件、html文件，导出文件也可以导入到数据库中。

• 14.4.1 用SELECT ... INTO OUTFILE 导出文本文件
  基本格式：
  SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [options]

--options选项
FIELDS TERMINATED BY 'value'
FIELDS [OPTIONALLY] ENCLOSED BY 'value'
FIELDS ESCAPED BY 'value'
LINES STARTING BY 'value'
LINES TERMINATED BY 'value'

格式说明 OPTIONS部分的语法包括FIELDS和LINES子句

FIELDS和LINES两个子句都是自选的，若是两个都被指定，FIELDS必须位于LINES的前面。

mysql客户主机转储表到服务器 LOAD DATA INFILE

实例：
select *from test.t1 into outfile '/var/lib/mysql/test.txt';

字段为NULL时，存储的值是“\N”

实例：
select *from test.t1 into outfile '/var/lib/mysql/test.txt'
fields terminated by ',' enclosed by '"' escaped by '''
lines terminated by '\r\n';

NULL变为“ 'N ” 实例1： 将实例1换行显示

• 14.4.2 用mysqldump命令倒出文本文件

mysqldump导出纯文本文件 参数说明1 参数说明2

options各个选项等号后面的value值不要用引号括起来。

mysqldump纯文本实例 mysqldump纯文本实例2

• 14.4.3 用mysql命令导出文本文件
  使用mysql在命令行下执行SQL命令，将查询结果导入到文本文件中。
  如果MySQL服务器是单独的机器，用户在client上操作，可以使用 mysql -e 语句将数据结果导入到client机器上。
  语法：
  mysql -r root -p --execute="SELECT语句" dbname > filename.txt
  mysql -r root -p -e "SELECT语句" dbname > filename.txt

mysql导出文本文件 使用--vertical 参数显示结果 使用参数 --html；mysql命令导出表到html文件 使用参数--xml；将表导出到xml文件

• 14.4.4 使用LOAD DATA INFILE 方式导入文本文件
  mysql提供了一些导入数据的工具，这些工具LOAD DATA语句、source命令和mysql命令。LOAD DATA INFILE 语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为文字字符串。

load data语句
--OPTIONS选项 load data参数说明 options部分的语法取值 options部分的语法取值 参数IGNORE number LINES说明 实例 实例14.20 实例14.20

• 14.4.5 使用mysqlimport命令导入文本文件
  语法：
  mysqlimport -u root -p dbname filename.txt [OPTIONS]

--OPTIONS选项取值 语法格式参数说明 options常见取值 mysqlimport实例

mysqlimport其他参数选项。

• 14.6 专家点拨

点拨1：MySQLdump备份文件不仅仅在MySQL中使用

• 15. MySQL日志
• 15.1 日志简介
  MySQL日志分4类：错误日志、查询日志、二进制日志、慢查询日志。

MySQL日志 flush logs语句、mysqladmin flush-logs、mysqladmin refresh命令

• 15.2 二进制日志
• 15.2.1 启动和设置二进制日志
  默认情况下，二进制日志是关闭的。
  windows下my.ini：
  [mysqld]
  log-bin [= path/[filename]]
  expire_logs_days = 10
  max_binlog_size=1024M

字段log-bin说明 字段expire_logs_days说明 字段max_binlog_size说明 数据库文件最好不要与日志文件放在同一磁盘上

• 15.2.2 查看二进制日志
  当MySQL创建二进制日志文件时，首先创建一个以“xx”为名称，以“.index”为后缀的文件；再创建“xx.000001”文件，MySQL服务重启会加1递增；若日志超过max_binlog_size的上限也为加1递增。
  查看二进制文件个数机器文件名：SHOW BINARY LOGS;
  查看二进制日志：mysqlbinlog命令
  mysqlbinlog --no-defaults --start-date='2016-09-21 00:00:00' --stop-date='2016-09-21 14:20:00' mysql-bin.000032 > 0921.txt

• 15.2.3 删除二进制日志
  reset master 删除所有的二进制日志文件；purge master logs只删除部分二进制日志文件。
  （1）reset master;
  （2）删除文件名编号比指定文件名编号小的所有日志文件：
  PURGE {MASTER | BINARY} LOGS TO 'log_name'
  删除指定日期以前的所有日志文件：
  PURGE {MASTER | BINARY} LOGS BEFORE 'date'

purge删除二进制日志文件实例 purge删除二进制日志文件实例2

• 15.2.4 使用二进制日志还原数据库
  mysqlbinlog恢复数据的语法：
  mysqlbinlog [option] filename | mysql -uuser -ppassword
  比较重要的option参数：--start-date、--stop-date(可以指定恢复数据库的起始时间和结束时间点)和--start-position、--stop-position(可以指定恢复数据库的起始位置和结束位置)。

mysqlbinlog恢复数据库实例

-15.2.5 暂时停止二进制日志功能
暂停记录二进制日志：
SET sql_log_bin = 0;
开启记录二进制日志：
SET sql_log_bin = 1;

• 15.3 错误日志

• 15.3.2 查看错误日志
  查看错误日志存储路径，SHOW VARIABLES LIKE 'log_error';

• 15.3.3 删除错误日志
  错误日志是以文本文件的形式存储，可以直接删除。
  命令形式：

1. mysqladmin -uroot -ppassword flush-logs
   2.mysql> flush logs;

• 15.4 通用查询日志

• 15.4.1 启动和设置通用查询日志
  MySQL服务器默认没有开启查询日志
  配置配置文件开启通用查询日志my.ini：
  [mysqld]
  log[=path/[filename]]
  默认日志名称为hostname.log

• 15.4.2 查看通用查询日志
  通用查询日志记录了用户的所有操作，通过日志，可以了解用户对MySQL进行的操作。

• 15.4.3 删除通用查询日志
  可以直接删除日志文件方式删除；
  使用语句重新建立日志文件：mysqladmin -uroot -ppassword flush-logs

• 15.5 慢查询日志
  15.5.1 启动和设置慢查询日志
  MySQL中慢查询日志默认是关闭的。
  配置配置文件打开慢查询my.ini：
  [mysqld]
  log-slow-queries[=path/[filename]]
  long_query_time=n
  默认日志名称hostname-slow.log；参数n默认为1-秒。

-15.5.2 查看慢查询日志

慢查询日志分析工具

• 15.5.3 删除慢查询日志
  可以直接删除日志文件；
  执行mysqladmin -uroot -ppassword flush-logs重新生成日志文件，或者执行flush logs重建日志文件。

15.6 综合案例
window启动mysql：net stop mysql/net start mysql