MySQL 数据库管理

MySQL 数据库管理

一、实验介绍

1.1 实验内容

本次实验将学习 MySQL 权限管理、用户管理、备份与恢复以及日志文件的相关知识。

1.2 实验知识点

• 权限管理
• 用户管理
• 备份与恢复
• 日志文件

1.3 实验环境

课程使用的实验环境为 Ubuntu Linux 14.04 64 位版本。实验中会用到程序：

• Mysql 5.5.50
• Xfce终端

二、实验步骤

2.1 MySQL 权限管理

账户权限信息被存储在mysql数据库中的user、db、host、tables_priv、columns_priv和procs_priv表中。

GRANT和REVOKE语句所授予的权限的名称显示在下表，还有在授权表中每个权限的表列名称和每个权限应用的领域（上下文）。

当从早期的没有CREATE VIEW、SHOW VIEW、CREATE ROUTINE、ALTER ROUTINE``和EXECUTE权限的版本的MySQL中升级到较高版本时，要想使用这些权限，你必须运行MySQL提供的mysql_fix_privilege_tables脚本来升级授权表。

如果启用了二进制日志，要想创建或修改保存的程序，你还需要具有SUPER权限。

通过CREATE和DROP权限，你可以创建新数据库和表，或删除(移掉)已有数据库和表。如果你将mysql数据库中的DROP权限授予某用户，该用户就可以删掉MySQL访问权限保存的数据库。

SELECT、INSERT、UPDATE和DELETE权限允许你对数据库中已经存在的表实施操作。

SELECT语句只有在真正从一个表中检索行时才需要SELECT权限。一些SELECT语句可以不需要具有访问表的权限，甚至没有任何到服务器上的数据库里的存取任何东西的许可。例如，你可使用mysql客户端作为一个简单的计算器来评估未引用表的表达式：

mysql> SELECT 1+1;
mysql> SELECT PI()*2;

INDEX权限允许你创建或删除索引，INDEX适用已有表。如果你具有某个表的CREATE权限，你可以在CREATE TABLE语句中加入索引定义。

通过ALTER权限，你可以使用ALTER TABLE来更改表的结构或者重新命名表。

需要CREATE ROUTINE权限来创建需要被储存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存好的程序，EXECUTE执行保存好的程序。

GRANT权限允许你把你自己拥有的那些权限授给其他的用户。可以应用于数据库、表和储存程序。

FILE权限给予你使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句对服务器上的文件进行读写，任何被授予FILE权限的用户都能对MySQL服务器能读或写的任何文件进行读写操作。(说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。FILE权限允许用户在MySQL服务器具有写权限的目录下创建新文件，但是不能覆盖已有文件。

其余的权限用于管理性操作，它使用mysqladmin程序或SQL语句实现。下表显示每个管理性权限允许你执行的mysqladmin命令：

reload命令告诉服务器将授权表重新读入内存。flush-privileges等价于reload，refresh命令清空所有打开的表并关闭记录文件，其它flush-xxx命令执行类似refresh的功能，但是范围更有限，因此在某些情况下可能更好用；例如，如果你只是想清空记录文件，flush-logs相比refresh是更好的选择。

shutdown命令关掉服务器。只能从mysqladmin发出命令，而且没有相应的SQL语句。

processlist命令显示在服务器内执行的线程信息（即其它账户相关的客户端执行的语句）。kill命令用于杀死服务器线程。你总是能显示或杀死你自己的线程，但是你需要PROCESS权限来显示或杀死其他用户和SUPER权限启动的线程。

拥有CREATE TEMPORARY TABLES权限便可以使用CREATE TABLE语句中的关键字TEMPORARY。 拥有LOCK TABLES权限便可以直接使用LOCK TABLES语句来锁定你拥有SELECT权限的表。包括使用写锁定，可以防止他人读锁定的表。

拥有REPLICATION CLIENT权限便可以使用SHOW MASTER STATUS和SHOW SLAVE STATUS。

REPLICATION SLAVE权限授予子服务器以该账户连接主服务器后可以执行replicate操作。没有这个权限，子服务器不能对主服务器上的数据库发出更新请求。

拥有SHOW DATABASES权限便允许账户使用SHOW DATABASE语句来查看数据库名。没有该权限的账户只能看到他们所具有权限的部分数据库， 如果数据库用--skip-show-database选项启动，则根本不能使用这些语句。请注意全局权限指数据库的权限。

总的说来，授予权限给需要他们的那些用户是好主意，但是你应该在授予FILE和管理权限时给定特定的警告：

• FILE权限可以任意的用于将服务器主机上MySQL能读取的任何文件读入到数据库表中。包括任何人可读的文件和服务器数据目录中的文件。使用SELECT访问数据库表，然后将其内容传输到客户端上。
• GRANT权限允许用户将他们的权限给其他用户。拥有不同的权限但有GRANT权限的两个用户可以合并权限。 ALTER权限可以用于通过重新命名表来推翻权限系统。
• SHUTDOWN权限通过终止服务器可以随意的完全拒绝为其他用户服务。
• PROCESS权限能被用来察看当前执行的查询的明文文本，包括设定或改变密码的查询。 SUPER权限能用来终止其它用户或更改服务器的操作方式。
• 授给mysql数据库本身的权限能用来改变密码和其他访问权限信息。密码会被加密后存储，所以恶意的用户不能简单地读取他们以知道明文密码。然而，具有user表Password列写访问权限的用户可以更改账户的密码，并可以用该账户连接MySQL服务器。

你不能用MySQL权限系统做到的一些事情：

• 你不能确切地指定某个给定的用户应该被拒绝访问。即你不能确切地匹配用户然后拒绝连接。
• 你不能指定用户它有权创建或删除数据库中的表，但不能创建或删除数据库本身。

2.2 MySQL 用户账户管理

2.2.1 向MySQL增加新用户账户

可以用两种方式创建MySQL账户：

1. 使用GRANT语句
2. 直接操作MySQL授权表

最好的方法是使用GRANT语句，因为这样更精确，错误少。

创建账户的其它方法是使用MySQL账户管理功能的第三方程序。phpMyAdmin即是一个第三方程序。

下面的示例说明如何使用MySQL客户端程序来设置新用户。

首先，在启动mysql服务后，使用MySQL程序以MySQL的root用户来连接服务器：

shell> mysql -u root

如果你为root账户指定了密码，还需要为该MySQL命令和本节中的其它命令提供--password或-p选项（实验楼环境是免密码登陆）。

以root连接到服务器上后，可以添加新账户。下面的语句表示使用GRANT来设置四个新账户：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

GRANT命令说明：

ALL PRIVILEGES 是表示所有权限，你也可以使用select、update等权限。

ON 用来指定权限针对哪些库和表。

*.* 中前面的*号用来指定数据库名，后面的*号用来指定表名。

TO 表示将权限赋予某个用户。

'monty'@'localhost' 表示monty用户，@ 后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。（注意：这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。）

IDENTIFIED BY指定用户的登录密码。

WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。（注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其他用户授权。）

备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加了一个SELECT权限，然后又给用户添加了一个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限。

上述用GRANT语句创建的账户具有以下属性：

其中两个账户有相同的用户名monty和密码some_pass。两个账户均为超级用户账户，具有完全的权限可以做任何事情。一个账户 ('monty'@'localhost')只用于从本机连接。另一个账户('monty'@'%')可用于从其它主机连接。请注意monty的两个账户必须能从任何主机以monty连接。当不具有localhost账户的用户monty从本机连接时，mysql_install_db创建的localhost匿名用户账户将具有优先权限。结果是，monty将被视为匿名用户。原因是在user表中匿名用户账户的Host列值比monty'@'%账户更具体（%相当于空HOST），这样在user表中排序是排在前面。

第三个账户有用户名admin，但没有密码。该账户只能用于本机连接。上面第三条语句中授予admin用户RELOAD和PROCESS管理权限；这些权限允许admin用户执行mysqladmin reload、mysqladmin refresh和mysqladmin flush-xxx以及mysqladmin processlist命令；但是它未被授予访问数据库的权限；你可以通过GRANT语句添加此类权限。

第四个账户有用户名dummy，但是也没有密码，该账户只用于本机连接，通过GRANT语句中赋予的USAGE权限，你可以创建账户而不授予任何权限；它可以将所有全局权限设为'N'。假定你将在以后将具体权限授予该账户。

除了GRANT语句，你可以直接用INSERT语句创建相同的账户，然后使用FLUSH PRIVILEGES刷新权限来告诉服务器重载授权表：

# 以root账户连接到服务器上的mysql数据库
shell> mysql -u root mysql

mysql> INSERT INTO user
    -> VALUES('localhost','monty',PASSWORD('some_pass'),
    -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user
    -> VALUES('%','monty',PASSWORD('some_pass'),
    -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

注意：在实验楼环境里操作INSERT INTO语句插入用户会报错：

ERROR 1136 (21S01): Column count doesn't match value count at row 1

意思是给表user中插入的数据列数跟表的列数不匹配。由于本教程的版本是MySQL5.1而实验楼环境安装的版本是MySQL5.1，可能user表的字段随着版本的更新而更新。那么查看该表中字段信息：

mysql> SHOW FULL COLUMNS FROM user;

获取字段的以下信息：

• Field ：字段名
• Type：字段类型
• Collation：字符集（mysql 5.0以上有）
• Null ：是否可以为NULL
• Key：索引（PRI,unique,index)
• Default：缺省值
• Extra：额外（是否 auto_increment)
• Privileges：权限
• Comment：备注（mysql 5.0以上有)

（下面的图由于表user的字段过多，所有只截取了开头和结尾部分。）

我们可以看到user表一共有42个字段，那么字段如果是缺省（Default值存在）就意味插入数据的时候不需要考虑该字段，可以看到字段名为Host，User，Password，ssl_type以及plugin的字段是没有缺省以外，其他字段都允许有缺省，因此我们在插入user表的时候注意给不缺省的字段插入值即可。（后来证明只要INSERT语句在指明字段名的情况下无论怎么插入数据都行。）

综上所述，上面插入表操作出错的原因就是在没有指定变量名的时候插入数据必须得全部42个字段依次赋值，那么爱动脑经的人一般不会重复输入并插入某个数，因此我们不鼓励上述插入语句中的前两种INSERT方法。

尝试执行第三种INSERT方法：

mysql> INSERT INTO user SET Host='localhost',User='admin',
    -> Reload_priv='Y', Process_priv='Y';

mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('localhost','dummy','');

mysql> FLUSH PRIVILEGES;

完美插入数据！

继续学习~

当你用INSERT创建账户时使用FLUSH PRIVILEGES的原因是告诉服务器重读授权表。否则，只有在重启服务器后更新才会被注意到。使用 GRANT，则不需要使用FLUSH PRIVILEGES。

前面第一个INSERT操作中，使用PASSWORD()函数是为了加密密码。GRANT语句已经为你加密密码，因此不需要PASSWORD()。

'Y'值启用账户权限。对于admin账户，还可以使用更加可读的INSERT扩充的语法（使用SET）。

在为添加dummy账户的INSERT语句中，只给该用户插入Host、User和Password数据。那些没有被指定插入的字段就会被默认值'N'填充。

请注意要设置超级用户账户，只需要创建一个权限列设置为'Y'的user表记录。user表权限为全局权限，因此其它授权表不再需要记录。

下面的例子创建3个账户，允许它们访问专用数据库。每个账户的用户名为custom，密码为obscure。

要想用GRANT创建账户，使用下面的语句：

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON bankaccount.*
    -> TO 'custom'@'localhost'
    -> IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON expenses.*
    -> TO 'custom'@'whitehouse.gov'
    -> IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON customer.*
    -> TO 'custom'@'server.domain'
    -> IDENTIFIED BY 'obscure';

这3个账户分别可以用于：

• 第1个账户可以访问bankaccount数据库，但只能本机访问。

• 第2个账户可以访问expenses数据库，但只能从主机访问whitehouse.gov。

• 第3个账户可以访问customer数据库，但只能从主机访问server.domain。

要想不用GRANT设置custom账户，使用INSERT语句直接修改授权表：

# 插入用户信息到`user`表
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('localhost','custom',PASSWORD('obscure'));

mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('whitehouse.gov','custom',PASSWORD('obscure'));

mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('server.domain','custom',PASSWORD('obscure'));

# 插入用户信息到`db`表  
mysql> INSERT INTO db
    -> (Host,Db,User,Select_priv,Insert_priv,
    -> Update_priv,Delete_priv,Create_priv,Drop_priv)
    -> VALUES('localhost','bankaccount','custom',
    -> 'Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO db
    -> (Host,Db,User,Select_priv,Insert_priv,
    -> Update_priv,Delete_priv,Create_priv,Drop_priv)
    -> VALUES('whitehouse.gov','expenses','custom',
    -> 'Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO db
    -> (Host,Db,User,Select_priv,Insert_priv,
    -> Update_priv,Delete_priv,Create_priv,Drop_priv)
    -> VALUES('server.domain','customer','custom',
    -> 'Y','Y','Y','Y','Y','Y');

# 刷新授权表    
mysql> FLUSH PRIVILEGES;

前3个INSERT语句在user表中加入用户信息记录，允许用户custom从各种主机用给定的密码进行连接，但不授予全局权限(所有权限设置为 默认值'N')。后面3个INSERT语句在db表中加入记录，为custom授予bankaccount、expenses和customer数据库权限，但只能从指定的主机发出访问。通常若直接修改授权表，则应告诉服务器用FLUSH PRIVILEGES重载授权表，使权限更改生效。

如果你想要让某个用户从给定域内的所有机器访问(例如，mydomain.com)，你可以在账户名的主机部分使用含‘%’通配符的GRANT语句：

mysql> GRANT ...
    -> ON *.*
    -> TO 'myname'@'%.mydomain.com'
    -> IDENTIFIED BY 'mypass';

也可以通过直接修改授权表来实现：

mysql> INSERT INTO user (Host,User,Password,...)
    -> VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);
mysql> FLUSH PRIVILEGES;

2.2.2 从MySQL删除用户账户

要想移除账户，应使用DROP USER语句。

2.2.3 限制账户资源

限制MySQL服务器资源使用的一个方法是将max_user_connections系统变量设置为非零值。但是，该方法严格限于全局，不允许管理具体的账户。并且，它只限制使用单一账户同时连接的数量，而不是客户端连接后的操作。许多MySQL管理员对两种类型的控制均感兴趣，特别是Internet服务提供者。

在MySQL 5.1中,你可以为具体账户限制下面的服务器资源：

• 账户每小时可以发出的查询数

• 账户每小时可以发出的更新数

• 账户每小时可以连接服务器的次数

客户端可以执行的语句根据查询限制来记数。只有修改数据库或表的语句根据更新限制来记数。

还可以限制每个账户的同时连接服务器的连接数。

本文中的账户为user表中的单个记录。根据User和Host列值唯一识别每个账户。

作为使用该特性的先决条件，mysql数据库的user表必须包含资源相关的列。资源限制保存在max_questions、max_updates、max_connections和max_user_connections列内。如果user表没有这些列，则必须对它进行升级。

要想用GRANT语句设置资源限制，使WITH子句来命名每个要限制的资源和根据每小时记数的限制值。例如，要想只以限制方式创建可以访问customer数据库的新账户，执行该语句：

mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
    -> IDENTIFIED BY 'frank'
    -> WITH MAX_QUERIES_PER_HOUR 20
    -> MAX_UPDATES_PER_HOUR 10
    -> MAX_CONNECTIONS_PER_HOUR 5
    -> MAX_USER_CONNECTIONS 2;

限制类型不需要全部在WITH子句中命名，但已经命名的可以按任何顺序。每个每小时限制值均应为整数，代表每小时的记数。如果GRANT语句没有WITH子句，则每个限制值设置为默认值零（即没有限制）。对于MAX_USER_CONNECTIONS，限制为整数，表示账户一次可以同时连接的最大连接数。如果限制设置为 默认值零，则根据MAX_USER_CONNECTIONS系统变量确定该账户可以同时连接的数量。

要想设置或更改已有账户的限制，在全局级别使用GRANT USAGE语句（ON .）。下面的语句可以将francis的查询限制更改为100：

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
    -> WITH MAX_QUERIES_PER_HOUR 100;

该语句没有改变账户的已有权限，只修改了指定的限制值。

要想取消已有限制，将该值设置为零。例如，要想取消francis每小时可以连接的次数的限制，使用该语句：

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
    -> WITH MAX_CONNECTIONS_PER_HOUR 0;

在账户使用资源时如果有非零限制，则对资源使用进行记数。

服务器运行时，它统计每个账户使用资源的次数。如果账户在最后一个小时的连接次数达到限制，该账户的进一步的连接被拒绝。类似地，如果账户达到查询或更新次数的限制，进一步的查询或更新被拒绝。在这种情况下，会给出相关错误消息。

根据每个账户进行资源计算，而不是根据每个客户端。例如，如果你的账户的查询次数限制为50，你不能通过两个客户端同时连接服务器将连接次数限制增加到100。两个连接的查询会被一起计算。

可以为所有账户从全局重设当前的每小时资源使用记数，或单独重设给定的账户：

• 要想将所有账户当前的记数重设为零，可以执行FLUSH USER_RESOURCES语句。还可以通过重载授权表来重设记数(例如，使用FLUSH PRIVILEGES语句或mysqladmin reload命令)。

• 将具体账户的限制重新授予任何值，可以将它设置为零。要想实现，按照前面语句所述使用GRANT USAGE，并将限制值指定为该账户当前的限制值。

计数器重设不影响MAX_USER_CONNECTIONS限制。

当服务器启动时所有记数从零开始。

2.2.4 设置账户密码

可以用mysqladmin命令在命令行指定密码：

shell> mysqladmin -u user_name -h host_name password "newpwd"

该命令为Host为host_name，User为user_name账户添加密码newpwd。

为账户赋予密码的另一种方法是执行SET PASSWORD语句：

mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');

只有root等可以更新mysql数据库的用户可以更改其它用户的密码。如果你没有以匿名用户连接，省略FOR子句便可以更改自己的密码：

mysql> SET PASSWORD = PASSWORD('biscuit');

你还可以在全局级别使用GRANT USAGE语句(ON .)来指定某个账户的密码而不影响账户当前的权限：

mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';

一般情况下最好使用上述方法来指定密码，你还可以直接修改user表：

要想在创建新账户时建立密码，在Password列提供一个值：

shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
  -> VALUES('%','jeffrey',PASSWORD('biscuit'));
mysql> FLUSH PRIVILEGES;

要想更改已有账户的密码，使用UPDATE来设置Password列值：

mysql> UPDATE user SET Password = PASSWORD('bagel')
  -> WHERE Host = '%' AND User = 'francis';
mysql> FLUSH PRIVILEGES;

当你使用SET PASSWORD、INSERT或UPDATE指定账户的密码时，必须用PASSWORD()函数对它进行加密。（唯一的特例是如果密码为空，你不需要使用PASSWORD()）。需要使用PASSWORD()是因为user表以加密方式保存密码，而不是明文。如果你忘记了，你可能会这样设置密码：

mysql> INSERT INTO user (Host,User,Password)
  -> VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;

结果是密码'biscuit'保存到user表后没有被加密。当jeffrey使用该密码连接服务器时，值被加密并同保存在user表中的进行比较。但是，由于已经保存的Password值为字符串'biscuit'，因此比较将以失败告终，服务器拒绝连接：

shell> mysql -u jeffrey -p biscuit test

如果你使用GRANT ... IDENTIFIED BY语句或mysqladmin password命令设置密码，它们均会加密密码。在这种情况下，不需要使用 PASSWORD()函数。

注释：PASSWORD()加密不同于Unix密码加密。

2.3 备份与恢复

2.3.1 数据库备份

由于MySQL表保存为文件方式会很容易备份。要想保持备份的一致性，需要对相关表执行LOCK TABLES操作，然后对表执行FLUSH TABLES。你只需要读锁定；这样当你复制数据库目录中的文件时，允许其它客户继续查询表。需要FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。

如果你想要进行SQL级别的表备份，你可以使用SELECT INTO ...OUTFILE或BACKUP TABLE。对于SELECT INTO ...OUTFILE， 输出的文件不能先存在。对于BACKUP TABLE也如此，因为覆盖完整的文件会有安全风险。

对于InnoDB表，可以进行在线备份，不需要对表进行锁定。

MySQL支持增量备份：需要用--log-bin选项来启动服务器以便启用二进制日志。当想要进行增量备份时(包含上一次完全备份或增量备份之后的所有更改)，应使用FLUSH LOGS回滚二进制日志。然后，你需要将从最后的完全或增量备份的某个时刻到最后某个点的所有二进制日志复制到备份位置。这些二进制日志为增量备份；恢复时，按照下面的解释应用。下次进行完全备份时，还应使用FLUSH LOGS或mysqlhotcopy --flushlogs回滚二进制日志。

如果MySQL服务器为复制子服务器时，则无论选择什么备份方法，当备份子机数据时，还应备份master.info和relay-log.info文件。恢复了子机数据后，需要这些文件来继续复制。如果子机执行复制LOAD DATA INFILE命令，你应用--slave-load-tmpdir选项备份指定的目录中的SQL_LOAD-*文件。（如果未指定，该位置默认为tmpdir变量值）。子机需要这些文件来继续复制中断的LOAD DATA INFILE操作。

如果必须恢复MyISAM表，先使用REPAIR TABLE或myisamchk -r来恢复，99.9%的情况下该方法可以生效。如果myisamchk恢复失败，试试下面的方法：

请注意只有添加--log-bin选项启动MySQL服务器从而启用二进制日志它才生效。

如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始(例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。

1. 恢复原mysqldump备份，或二进制备份。

2. 执行下面的命令重新更新二进制日志：

   shell> mysqlbinlog hostname-bin.[0-9]* | mysql
   

在某些情况下，你可能只想要从某个位置重新运行某些二进制日志。（通常你想要根据恢复备份的日期重新运行所有二进制日志）。

还可以对具体文件进行选择备份：

• 要想复制表，使用SELECT * INTO OUTFILE 'file_name' FROM tbl_name语句。

• 要想重载表，使用LOAD DATA INFILE 'file_name' REPLACE ...载入并恢复表。要避免复制记录，表必须有PRIMARY KEY或一个UNIQUE索引。当新记录复制唯一键值的旧记录时，REPLACE关键字可以将旧记录替换为新记录。

如果备份时遇到服务器性能问题，有用的一个策略是在子服务器而不是主服务器上建立复制并执行备份。

如果使用Veritas文件系统，可以这样备份：

1. 从客户端程序执行FLUSH TABLES WITH READ LOCK语句。

2. 从另一个shell执行mount vxfs snapshot命令。

3. 从第一个客户端执行UNLOCK TABLES。

4. 从快照复制文件。

5. 卸载快照。

2.3.2 备份与恢复策略示例

1） 备份策略

我们都知道必须按计划定期进行备份。可以用一些工具(某个时间点的数据快照)完全备份MySQL。例如，InnoDB Hot Backup为InnoDB数据文件提供在线非数据块物理备份，mysqldump提供在线逻辑备份。

假定我们在星期日下午1点进行了备份，此时负荷较低。下面的命令可以完全备份所有数据库中的所有InnoDB表：

mysqldump -u root --single-transaction --all-databases > backup_sunday_1_PM.sql

以上方法是在线非数据块备份，不会干扰对表的读写。我们假定我们以前的表为InnoDB表，因此--single-transaction一致性地表，并且保证mysqldump所看见的数据不会更改。(其它客户端对InnoDB表进行的更改不会被mysqldump进程看见）。如果我们还有其它类型的表，我们必须假定在备份过程中它们不会更改。例如，对于mysql数据库中的MyISAM表，我们必须假定在备份过程中没有对MySQL账户进行管理更改。

mysqldump命令产生的.sql文件包含一系列SQL INSERT语句，可以用来重载转储的表。

进行完全备份的时候有时不方便，因为会产生大的备份文件并需要花时间来生成。从某个角度来看，完全备份并不理想，因为每个成功的完全备份都包括所有数据，甚至包括自从上一次完全备份以来没有被更改的部分。完成了初始完全备份后，进行增量备份会更有效。这样备份文件要小得多，备份时间也较短。缺点是，恢复时不能只重载完全备份来恢复数据。还必须要用增量备份来恢复增量更改。

要想进行增量备份，我们需要保存增量更改。应使用--log-bin选项启动MySQL服务器，以便更新数据时将这些更改保存到文件中。该选项启用二进制日志，因此服务器会将每个更新数据的SQL语句写入到MySQL二进制日志。让我们看看用--log-bin选项启动的已经运行多日的MySQL服务器的数据目录。找到以下MySQL二进制日志文件：

每次重启，MySQL服务器都会使用以上序列中的下一个编号创建一个新的二进制日志文件。当服务器运行时，你还可以通过执行FLUSH LOGS SQL语句或mysqladmin flush-logs命令，告诉服务器关闭当前的二进制日志文件并创建一个新文件。mysqldump也有一个选项来清空日志。数据目录中的.index文件包含该目录下所有MySQL二进制日志的清单，该文件用于复制。

恢复时MySQL二进制日志很重要，因为它们是增量备份。如果进行完全备份时确保清空了日志，则后面创建的二进制日志文件包含了备份后的所有数据更改。让我们稍稍修改前面的mysqldump命令，让它在完全备份时能够清空 MySQL二进制日志，以便转储文件包含包含新的当前二进制日志：

mysqldump -u root --single-transaction --flush-logs --master-data=2 --all-databases > backup_sunday_1_PM.sql

执行该命令后，数据目录则包含新的二进制日志文件。产生的.sql文件包含下列行：

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

因为mysqldump命令可以执行完全备份，以上行代表两件事情：

1. .sql文件包含所有写入gbichot2-bin.000007二进制日志文件或最新的文件之前的更改。

2. 备份后所记录的所有数据更改不出现在.sql中，但会出现在gbichot2-bin.000007二进制日志文件或最新的文件中。

在星期一下午1点，我们可以清空日志并开始根据新的二进制日志文件来创建增量备份。例如，执行mysqladmin flush-logs命令创建gbichot2-bin.000008。星期日下午1点的完全备份和星期一下午1点之间的所有更改为文件gbichot2-bin.000007。该增量备份很重要，因此最好将它复制到安全的地方。（例如，备份到磁带或DVD上，或复制到另一台机器上）。在星期二下午1点，执行另一个mysqladmin flush-logs命令，这样星期一下午1点和星期二下午1点之间的所有更改为文件gbichot2-bin.000008(也应复制到某个安全的地方)。

MySQL二进制日志占据硬盘空间。要想释放空间，应随时清空。操作方法是删掉不再使用的二进制日志，例如进行完全备份时输入以下命令：

shell> mysqldump --single-transaction --flush-logs --master-data=2
       --all-databases --delete-master-logs > backup_sunday_1_PM.sql

注释：如果你的服务器为复制主服务器，用mysqldump方法中的 --delete-master-logs选项删掉MySQL二进制日志很危险，因为从服务器可能还没有完全处理该二进制日志的内容。关于这一点，PURGE MASTER LOGS语句的描述中解释了为什么在删掉MySQL二进制日志之前应进行确认一下。

2） 为恢复进行备份

现在假设在星期三上午8点出现了灾难性崩溃，需要使用备份文件进行恢复。恢复时，我们首先恢复最后的完全备份(从星期日下午1点开始)。完全备份文件是一系列SQL语句，因此恢复它很容易：

shell> mysql < backup_sunday_1_PM.sql

接下来使得数据恢复到星期日下午1点的状态。要想恢复从那时起的更改，我们必须使用增量备份，也就是gbichot2-bin.000007和gbichot2-bin.000008这两个二进制日志文件。根据需要从备份处取得这些文件，然后按下述方式处理：

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

我们现在将数据恢复到星期二下午1点的状态，但是从该时刻到崩溃之间的数据仍然有丢失；要实现恢复，我们需要MySQL服务器将MySQL二进制日志保存到安全的位置(RAID disks, SAN, ...)，应为与数据文件的保存位置不同的地方，保证这些日志不在被毁坏的硬盘上。(也就是，我们可以用--log-bin选项启动服务器，指定一个其它物理设备上的与数据目录不同的位置。这样，即使包含该目录的设备丢失，日志也不会丢失）。如果我们执行了这些操作，我们手头上会有gbichot2-bin.000009文件，我们可以用它来恢复大部分最新的数据更改，而不会丢失星期二下午1点到崩溃时刻之间的数据。

3） 备份策略摘要

出现操作系统崩溃或电源故障时，InnoDB自己可以完成所有数据恢复工作。但为了确保你可以睡好觉，应遵从下面的指导：

• 一定用--log-bin或甚至--log-bin=log_name选项启动MySQL服务器，其中日志文件名位于某个安全媒介上，不同于数据目录所在驱动器。如果你有这样的安全媒介，最好进行硬盘负载均衡(这样能够提高性能)。

• 定期进行完全备份，使用mysqldump命令进行在线非数据块备份。

• 用FLUSH LOGS或mysqladmin flush-logs清空日志进行定期增量备份。

2.3.3 自动恢复

注意：由于实验楼环境中默认的mysql配置文件中把log_bin等日志设置选项给注释了，因此需要更改文件my.cnf：

# 登陆到root账户
shell> sudo -s
shell> vi /etc/mysql/my.cnf

修改后然后保存my.cnf文件，重启mysql服务器，并查看日志是否启动：

shell> service mysql restart --log-bin
shell> mysql -u root
sql> show variables like 'log_%';

继续~

要想确定当前的二进制日志文件的文件名，在命令行中加入下面的MySQL语句：

shell> mysql -u root -e 'SHOW BINLOG EVENTS \G'

1） 指定恢复时间

对于MySQL 5，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00(今天是2015年8月6日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前一晚上的备份，并从命令行输入以下命令：

shell> mysqlbinlog --stop-date="2015-8-6 10:01:00" /var/log/mysql/bin.123456 \
     | mysql -u root -p mypwd

该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。

在以上行中，从上午10:01登录的SQL语句将运行。结合执行前夜的转储文件和mysqlbinlog的两行命令可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。

2） 指定恢复位置

也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为：

shell> mysqlbinlog --start-date="2014-10-29 9:55:00" --stop-date="2014-10-29 10:05:00" \
      /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用`log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：

shell> mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \
    | mysql -u root -pmypwd 

shell> mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \
    | mysql -u root -pmypwd \

上面的第1行将恢复到停止位置为止的所有事务。第二行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反映事务执行的原时间。

2.4 日志文件

2.4.1 错误日志

错误日志文件包含了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。

如果mysqld莫名其妙地死掉并且需要mysqld_safe重新启动它，那么mysqld_safe在错误日志中写入一条restarted mysqld消息。如果mysqld注意到需要自动检查或着修复一个表，则错误日志中将写入这条消息。

在一些操作系统中，如果mysqld死掉，错误日志会包含堆栈跟踪信息。跟踪信息可以用来确定mysqld死掉的地方。

可以用--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值，mysqld会在数据目录中使用日志名host_name.err 写入日志文件，如果你执行FLUSH LOGS，日志会使用-old重新命名后缀并且mysqld创建一个新的空日志文件。(如果未给出--log-error选项，则不会重新命名）。

如果不指定--log-error，或者(在Windows中)如果你使用--console选项，错误被写入标准错误输出stderr。通常标准输出为你的终端。

在Windows中，如果未给出--console选项，错误输出总是写入.err文件。

2.4.2 通用查询日志

如果你想要知道mysqld内部发生了什么，你应该用--log[=file_name]或-l [file_name]选项启动服务器。如果没有给定file_name的值， 默认名是host_name.log。所有连接和语句都会被记录到日志文件。当你怀疑在客户端发生了错误并想确切地知道该客户端发送给mysqld的语句时，该日志可能非常有用。mysqld按照它接收的语句顺序记录查询日志，这可能与执行的顺序不同。这与更新日志和二进制日志不同，它们在执行后但是是在任何一个锁释放之前记录日志。(查询日志还包含所有语句，而二进制日志不包含只查询数据的语句）。

服务器重新启动和日志刷新不会产生新的通用查询日志文件(尽管刷新会关闭并重新打开一般查询日志文件)。在Unix中，你可以通过下面的命令重新命名文件并创建一个新的日志文件：

shell> mv hostname.log hostname-old.log
shell> mysqladmin flush-logs
shell> cp hostname-old.log to-backup-directory
shell> rm hostname-old.log

在Windows中，服务器打开日志文件期间你不能重新命名日志文件，你必须先停止服务器然后重新命名日志文件，然后重启服务器来创建新的日志文件。

2.4.3 二进制日志

二进制日志以一种更有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。

二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。

注释：二进制日志已经代替了老的更新日志，更新日志在MySQL 5.1中不再使用。

二进制日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果你想要记录所有语句（例如，为了识别有问题的查询），你应使用一般查询日志。

二进制日志的主要目的是在恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。

二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。

运行服务器时若启用二进制日志则性能大约慢1%。但是，二进制日志的好处即用于恢复并允许设置复制超过了这个小小的性能损失。

当用--log-bin[=file_name]选项启动服务器时，mysqld写入包含所有更新数据的SQL命令的日志文件。如果未给出file_name值， 默认名为-bin后面所跟的主机名。如果给出了文件名，但没有包含路径，则文件被写入数据目录。

如果你在日志名中提供了扩展名(例如，--log-bin=file_name.extension)，则扩展名被悄悄除掉并忽略。

mysqld在每个二进制日志名后面添加一个数字扩展名。每次你启动服务器或刷新日志时该数字则增加。如果当前的日志大小达到max_binlog_size时，还会自动创建新的二进制日志。如果你正使用大的事务，二进制日志大小还会超过max_binlog_size。（事务要全写入一个二进制日志中，绝对不要写入不同的二进制日志中。）

为了能够知道还使用了哪个不同的二进制日志文件，mysqld还创建一个二进制日志索引文件，包含所有使用的二进制日志文件的文件名。默认情况下与二进制日志文件的文件名相同，扩展名为'.index'。你可以用--log-bin-index[=file_name]选项更改二进制日志索引文件的文件名。当mysqld在运行时，不应手动编辑该文件；如果这样做将会使mysqld变得混乱。

可以用RESET MASTER语句删除所有二进制日志文件，或用PURGE MASTER LOGS只删除部分二进制文件。

二进制日志格式有一些已知限制，会影响从备份恢复。

可以使用下面的mysqld选项来影响记录到二进制日志内的内容：

• --binlog-do-db=db_name

  告诉主服务器，如果当前的数据库(即USE选定的数据库)是db_name，应将更新记录到二进制日志中。其它所有没有明显指定的数据库 被忽略。如果使用该选项，你应确保只对当前的数据库进行更新。

  对于CREATE DATABASE、ALTER DATABASE和DROP DATABASE语句，有一个例外，即通过操作的数据库来决定是否应记录语句，而不是用当前的数据库。

  一个不能按照期望执行的例子：如果用binlog-do-db=sales启动服务器，并且执行USE prices; UPDATE sales.january SET amount=amount+1000；，该语句不写入二进制日志。

• --binlog-ignore-db=db_name

  告诉主服务器，如果当前的数据库(即USE选定的数据库)是db_name，不应将更新保存到二进制日志中。如果你使用该选项，你应确保只对当前的数据库进行更新。

  一个不能按照你期望的执行的例子：如果服务器用binlog-ignore-db=sales选项启动，并且执行USE prices; UPDATE sales.january SET amount=amount+1000；，该语句不被写入二进制日志。

  类似于--binlog-do-db，对于CREATE DATABASE、ALTER DATABASE和DROP DATABASE语句，有一个例外，即通过操作的数据库来决定是否应记录语句，而不是用当前的数据库。

要想记录或忽视多个数据库，可以在启动服务器的时候使用多个选项，为每个数据库指定相应的选项。

服务器根据下面的规则对选项进行评估，以便将更新记录到二进制日志中或忽视。请注意对于CREATE/ALTER/DROP DATABASE语句有一个例外。在这些情况下，根据以下列出的不同情况，所创建、修改或删除的数据库将代替当前的数据库。

1. 是否有binlog-do-db或binlog-ignore-db规则?

   没有：将语句写入二进制日志并退出。

   有：执行下一步。

2. 有一些规则(binlog-do-db或binlog-ignore-db或二者都有)。当前有一个数据库(是否使用USE语句选择了数据库？)?

   没有：不要写入语句，并退出。

   有：执行下一步。

3. 有一些binlog-ignore-db规则。当前的数据库是否匹配binlog-ignore-db规则?

   有：不要写入语句，并退出。

   没有：写入查询并退出。

例如，只用binlog-do-db=sales运行的服务器只将当前数据库为sales的语句写入二进制日志(换句话说，binlog-do-db有时可以表示“忽视其它数据库”)。

如果你正进行复制，应确保没有子服务器在使用旧的二进制日志文件时，方可删除它们。一种方法是每天一次执行mysqladmin flush-logs并删除三天前的所有日志。可以手动删除，或最好使用PURGE MASTER LOGS语句删除日志。

具有SUPER权限的客户端可以通过SET SQL_LOG_BIN=0语句禁止将自己的语句记入二进制记录。

你可以用mysqlbinlog实用工具检查二进制日志文件。如果你想要重新处理日志止的语句，这很有用。例如，可以从二进制日志更新MySQL服务器，方法如下：

shell> mysqlbinlog log-file | mysql -h server_name

如果你正使用事务，必须使用MySQL二进制日志进行备份，而不能使用旧的更新日志。

查询结束后、锁定被释放前或提交完成后则立即记入二进制日志。这样可以确保按执行顺序记入日志。

对非事务表的更新执行完毕后立即保存到二进制日志中。对于事务表，例如BDB或InnoDB表，所有更改表的更新(UPDATE、DELETE或INSERT)都会 被缓存起来，直到服务器接收到COMMIT语句。在执行完COMMIT之前，mysqld将整个事务写入二进制日志。当处理事务的线程启动时，它为缓冲查询分配binlog_cache_size大小的内存。如果语句大于该值，线程则打开临时文件来保存事务。线程结束后临时文件被删除。

Binlog_cache_use状态变量显示了使用该缓冲区(也可能是临时文件)保存语句的事务的数量。Binlog_cache_disk_use状态变量显示了这些事务中实际上有多少必须使用临时文件。这两个变量可以用于将binlog_cache_size调节到足够大的值，以避免使用临时文件。

max_binlog_cache_size(默认4GB)可以用来限制用来缓存多语句事务的缓冲区总大小。如果某个事务大于该值，将会失败并回滚。

如果你正使用更新日志或二进制日志，当使用CREATE ... SELECT or INSERT ... SELECT时，并行插入被转换为普通插入。这样通过在备份时使用日志可以确保重新创建表的备份。

默认情况下，并不是每次写入时都将二进制日志与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能二进制日志中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量(设置该变量值为1是最安全的值，但也是最慢的)，使二进制日志在每N次二进制日志写入后与硬盘同步。

该选项可以提供更大程度的安全，还应对MySQL服务器进行配置，使每个事务的二进制日志(sync_binlog =1)和(默认情况为真)InnoDB日志与硬盘同步。该选项的效果是崩溃后重启时，在滚回事务后，MySQL服务器从二进制日志剪切 回滚的InnoDB事务。这样可以确保二进制日志反馈InnoDB表的确切数据等，并使从服务器保持与主服务器保持同步(不接收回滚的语句)。

请注意即使MySQL服务器更新其它存储引擎而不是InnoDB，也可以使用--innodb-safe-binlog选项启动服务。在InnoDB崩溃恢复时，只能从二进制日志中删除影响InnoDB表的语句或事务。如果崩溃恢复时MySQL服务器发现二进制日志变短了(即至少缺少一个成功提交的InnoDB事务)，如果sync_binlog =1并且硬盘或文件系统的确能根据需要进行同步(有些不需要)则不会发生，则输出错误消息 ("二进制日志<名>比期望的要小")。在这种情况下，二进制日志不准确，复制应从主服务器的数据快照开始。

写入二进制日志文件和二进制日志索引文件的方法与写入MyISAM表的相同。

2.4.4 慢速查询日志

用--log-slow-queries[=file_name]选项启动服务时，mysqld会写入一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。其中，获得初使表锁定的时间不算作执行时间。

如果没有给出file_name值，默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。

语句执行完并且所有锁释放后记入慢查询日志。记录顺序可以与执行顺序不相同。

慢查询日志可以用来找到执行时间长的查询，可以用于优化。但是，检查又长又慢的查询日志会很困难。要想容易些，你可以使用mysqldumpslow命令获得日志中显示的查询摘要来处理慢查询日志。

在MySQL 5.1的慢查询日志中，不使用索引的慢查询同使用索引的查询一样记录。要想防止不使用索引的慢查询记入慢查询日志，使用--log-short-format选项。

在MySQL 5.1中,通过--log-slow-admin-statements服务器选项，你可以请求将慢管理语句，例如将OPTIMIZE TABLE、ANALYZE TABLE和ALTER TABLE语句写入慢查询日志。

用查询缓存处理的查询不加到慢查询日志中，因为表有零行或一行而不能从索引中受益的查询也不写入慢查询日志。

2.4.5 日志文件维护

MySQL服务器可以创建各种不同的日志文件，从而可以很容易地看见所进行的操作。

当启用日志使用MySQL时，你可能想要不时地备份并删除旧的日志文件，并告诉MySQL开始记入新文件。

在 Linux (Redhat)的安装上，你可为此使用mysql-log-rotate脚本。如果你在RPM上分发安装MySQL，脚本应该已经自动被安装。

在其它系统上，你必须自己安装短脚本，你可从镜像网站获得处理日志文件。

你可以通过mysqladmin flush-logs或SQL语句FLUSH LOGS来强制MySQL开始使用新的日志文件。

日志清空操作主要完成下列事情：

• 如果使用标准日志(--log)或慢查询日志(--log-slow-queries)，关闭并重新打开日志文件。(默认为mysql.log和`hostname-slow.log`)。
• 如果使用更新日志(--log-update)或二进制日志(--log-bin)，关闭日志并且打开有更高序列号的新日志文件。

• 如果你只使用更新日志，你只需要重新命名日志文件，然后在备份前清空日志。例如，你可以这样做：

  shell> cd mysql-data-directory
  shell> mv mysql.log mysql.old
  shell> mysqladmin flush-logs
  

  然后备份并删除“mysql.old”。

三、实验总结

到这里 MySQL 的相关知识点就全部介绍完了，课程的基础篇是需要同学们熟练掌握的，而进阶篇也需要了解一些，在以后的学习工作中用到的时候再来查看。