8-MySQL用户管理[归.pdf

飞越电脑求实教育中心MySQL 数据库之八主讲老师：李霞-1-数据备份与还原尽管采取了一些管理措施来保证数据库的安全，但是不确定的意外情况总是有可能造成数据的损失，例如意外的停电、管理员不小心的操作失误都可能会造成数据的丢失。保证数据安全的最重要的措施是确保对数据进行定期备份。如果数据库中的数据丢失或者出一了错误，可以使用备份的数据进行还原，这样就尽可能的降低了意外原因导致的损失。MySQL提供了多种方法对数据进行备份和还原，本章将介绍数据备份、数据还原、数据迁移和数据导入导出的相关知识。一、数据备份数据备份是数据库管理员非常重要的工作。系统意外崩溃或者硬件的损坏都可以能导致数据库的丢失，因此MySQL 管理员应该定期地备份数据库，使得在意外情况发生时，尽可能减少损失。本节将介绍数据备份的3 种方法。A、使用 mysqldump 命令备份myslqdump 是 MySQL 提供的一个非常有用数据库备份工具。mysqldump 命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包含了多个CREATE 和 INSERT语句，使用这些语句可以重新创建表和插入数据。mysqldump 备份数据库语句的基本语法格式如下：mysqldump u user-h host ppassword dbnametbname,tbnamefilename.sql user表示用户名称；host 表示登录用户的主机名称；password为登录密码；dbname为需要备份的数据库名称；tbname 为 dbname数据库中需要备份的数据表，可以指定多个需要备份的表；右箭头符号“”告诉 mysqldump 将备份数据表的定义和数据写入备份文件；filename.sql 为备份文件的名称。A、使用 mysqldump 命令备份单个数据库中的所有表【例 1】使用 mysqldump 命令备份数据库中的所有表mysqldump-u root-p booksdb C:/backup/booksdb_20110101.sql 输入密码之后，MySQL 便对数据进行了备份。可以看到，备份文件包含了一些信息，文件开头首先表明了备份文件使用r mysldump工具的版本号；然后是备份帐户的名称和主机信息，以及备份的数据库的名称，最后是MySQL 服务器的版本号。备份文件接下来的是一些SET语句，这些语句将一些系统变量值赋给用户定义变量，以确保被恢复的数据库的系统变量和原来备份时的变量相同。例如：/*!40101 SET OLD_CHARACTER_SET_CLIENT=CHARACTER_SET_CLIENT*/;该SET语 句 将 当 前 系 统 系 统character_set_client的 值 给 用 户 定 义 变 量old_character_set_client。其它变量与此类似。备份文件的最后几行MySQL 使用 ASET 语句恢复服务器系统变量原来的值，例如：/*!40101 SET COLLATION_CONNECTION=OLD_COLLATION_CONNECTION*/;该语句将用户的变量old_character_set_client中保存的值赋给实际的系统变量character_set_client。飞越电脑求实教育中心MySQL 数据库之八主讲老师：李霞-2-备份文件中的“”字符开头的行为注释语句，以“/*！”开关不、“*/”结尾的语句为可执行的MySQL 注释，这些语句可以被MySQL 执行，但在其他数据库管理系统将被作为注释忽略，这可以提高数据库的可移植性。另外注意到，备份文件开始的一些语句以数字开头，这些数字代表MySQL 版本号，该数字告诉我们，这些语句只有在指定的MySQL 版本或者比该版本高的情况下才能执行。例如：40101，表明这些语句只有在MySQL 版本号为4.01.01 或者更高的条件下可以执行。B、使用 mysqldump 备份数据库中的某个表在前面 myaqldump 语法中介绍过，mysqldump 还可以备份数据中的某个表，其语法格式为：mysqldump u root h host p dbname tbname,tbname filename.sql tbname 表示数据库中的表名，多个表名之间的空格隔开。备份表和备份数据库中所有表的语句中不同的地方在于，要在数据库名称dbname之后指定需要备份的表名称。【例 2】备份 booksDB 数据库中的books 表mysqldump-u root-p booksDB books C:/backup/books_20110101.sql 该语句创建名称为books_20110101.sql 的备份文件，文件中包含了前面介绍的SET语句等内容，该文件只包含books 表的 CREATE 和 INSERT 语句。C、使用 mysqldump 备份多个数据库如果要使用mysqldump 备份多个数据库，需要使用databases参数，备份从个数据库的名称，多个数据库名称之间用空格隔开。【例 3】使用 mysqldump 备份 booksDB 和 test 数据库mysqldump-u root-p-databases booksDB test C:backupbooks_testDB_20110101.sql 该语句创建名称为book_testDB_20110101.sql的备份文件，文件中包含了创建两个数据库 bookDB 和 test_db所必须的所有语句。另外，使用 all-databases参数可以备份系统中所有的数据库，语句如下：mysqldump u user h host p all-databases filename.sql 使用参数 alldatabases参数时，不需要指定数据库名称。【例 4】使用 mysqldump 备份服务器中的所有数据库mysqldump-u root-p-all-databases C:/backup/alldbinMySQL.sql 该语句创建名称为alldbinMySQL.sql的备份文件，文件中包含了对系统中所有数据库的备份信息。提示：如果在服务器上进行备份，并且表均为MyISAM表，应考虑使用mysqlhotcopy，因为可以更快地进行备份和恢复。二、数据还原管理人员操作的失误解、计算机故障以及其他意外情况，都会导致数据的丢失和破坏。当数据丢失或意外破坏后，可以通过已经备份的数据尽量减少数据丢失和破坏造成的损失。本节将介绍数据还原的方法。使用 mysql 命令还原对于已经备份的包含CREATE、INSERT 语句（有时也会有DROP）语句。mysql 命令可以直接执行文件中的这些语句。其语法格式如下：飞越电脑求实教育中心MySQL 数据库之八主讲老师：李霞-3-mysql u user p dbname filename.sql user是执行 backup.sql 中语句的用户名；-P 表示输入用户密码；dbname 是数据库名。如果 filname.sql 文件为 mysqldump 工具创建的包含数据库语句的文件，执行的时候不需要指定数据库名。【例 5】使用 mysql 命令将 C:backupbooksdb_20110101.sql 文件中的备份导入到数据库中mysql-u root-p booksDB ”开始，以“”字符串结尾SELECT*FROM test.person INTO OUTFILE C:/person2.txt LINES STARTING BY TERMINA TED BY;SELECT*FROM test.person INTO OUTFILE C:/person2.txt LINES STARTING BY TERMINA TED BY rn;可以看到，虽然将所有的字段值导出到文本文件中，但是所有的记录滑有分行区分，出现这种情况因为TERMINATED BY选项替换了系统默认的“n”换行符，如果希望换行显示。则需要修改导出语句。2、用 mysqldump 命令导出文本文件除 了 使 用SELECT INTO OUTFILE语 句 导 出 文 本 文 件 之 外，还 可 以 使 用mysqldump。本章开始介绍了使用mysqldump 备份数据库，该工具不仅可以将数据导出为包含 CREATE、INSERT 的 sql 文件，也可以导出为纯文本文件。mysqldump 创建一个包含创建表的CREATE TABLE 语句的 tablename.sql 文件，和一个包含其数据的tablename.txt 文件，mysqldump 导出文本文件的基本语法格式如下：mysqldump T path-u root p dbname tables OPTIONS【例 13】使用 mysqldump 将 test 数据库中的person表中的记录导出到文本文件mysqldump-T C:/text person-u root-p【例 14】使用 mysqldump 命令将 test数据库中的person表中的记录导出到文本文件，使用 FIELDS 选项，要求字段之间使用逗号,间隔，所有字符类型字段值用双引号括起来，定义转义字符定义为问号?，每行记录以回车换行符“rn”结尾mysqldump-T C:backup text person-u root-p-fields-terminated-by=,飞越电脑求实教育中心MySQL 数据库之八主讲老师：李霞-5-fields-optionally-enclosed-by=-fields-escaped-by=?-lines-terminated-by=rn 上面语句要在一行中输入，语句执行成功，系统 c:backup 目录下面将会有两个文件，分别为 person.sql 和 person.txt。person.sql 包含创建person表的 create 语句，其内容与前面例子中的相同，person.txt 文件与上一个例子不同。可以看到，只有字符类型的值被双引号括了起来，而数值类型的值没有；第5 行记录中的 NULL 值表示为“？u。相比 mysqldump，mysql 工具导出的结果可读性更强。如果 MySQL 服务器是单独的机器，用户是在一个client 上进行操作，用户要把数据结果导入client 机器上，可以使用mysql e 语句。使用 mysql 导出数据文本文件语句的基本格式如下：mysql u root p execute=SELECT 语句 dbname filename.txt【例 115】使用 mysql 语句导出test 数据库中person表中的记录到文本文件mysql-u root-p-execute=SELECT*FROM person;text C:person3.txt 可以看到，person3.txt 文件中包含了每个字段的名称的名称记录，该显示格式与MySQL 命令行下SELECT 查询结果显示相同。使用 mysql 命令还可以指定查询结果的显示格式，如果某行记录字段很多，可能一行不能完全显示，可以使用vartical 参数，将每条记录分为多行显示。【例 16】使用mysql 命令导出test 数据库中person 表中的记录到文本文件，使用-vertical 参数显示结果mysql-u root-p-vertical-execute=SELECT*FROM person;test C:person4.txt 可以看到，SELECT 的查询结果导出到文本文件之后，显示格式发生了变化，如果person 表中记录内容很长，这样显示将会更加容易阅读。【例 17】使用 mysql 命令导出test数据库中 person 表中的记录到html 文件mysql-u root-p-html-execute=SELECT*FROM person;test C:person5.html【例 18】使用 mysql 命令导出test数据库中 person 表中的记录到xml 文件mysql-u root-p-xml-execute=SELECT*FROM person;test C:person6.xml 4、使用 LOAD DA TA INFILE 方式导入文本文件MySQL 允许数据导出到外部文件，也可以从外部文件导入数据。MySQL 提供了一些导入数据的工具，这些工具有LOAD DATA 语句、source命令和 mysql 命令。LOAD DATA INFILE 语句用于高速地从一个文本文件夹中读取行，并装入一个表中。文件名称必须为文字字符串。本节将介绍LOAD DA TA 语句的用法。LOAD DA TA 语句的基本语法如下：LOAD DATA INFILE filename.txt INTO TABLE tablename OPTIONS IGNORE numberLINES 可以看到LOAD DA TA 语句中，关键字 INFILE 后面的 filename 文件为导入数据的来源；tablname 表示待入的数据表名称；OPTIONS 为可选参数选项，OPTIONS 部分的语法包括FIELDS 和 LINES 子句。【例 19】使用 LOAD DA TA 命令将 C:person0.txt 文件中的数据导入到test 数据库中的 person 表LOAD DA TA INFILE C:/person0.txt INTO TABLE text.person;可以看到，语句执行成功后，原来的数据重新恢复到了person 表中。【例 20】使用 LOAD DA TA 命令将 C:person1.txt 文件中的数据导入到test 数据库中的 person 表，使用FIELDS 选项和 LINES 选项，要求字段之间使用逗号,间隔，所有字段值用双引号括起来，定义转义字符定义为单引号 飞越电脑求实教育中心MySQL 数据库之八主讲老师：李霞-6-LOAD DA TA INFILE C:/person1.txt INTO TABLE test.person FIELDS TERMINA TED BY,ENCLOSED BY ESCAPED BY LINES TERMINA TED BY rn;5、使用 mysqlimport 命令导入文本文件使用 mysqlmport 可以导入文本文件。并且不需要登录MySQL 客户端。mysqlmport命令提供许多与LOAD DATA INFILE语句相同的功能，大多数选项直接对应LOAD DATA INFILE 子句。使用mysqlmport 语句需要指定所需的选项、导入的数据库名称以及导入的数据文件的路径和名称。mysqlmport 命令的基本语法格式如下：mysqlmport u root-p dbname filename.txt OPTIONS【例 21】使用 mysqlimport 命令将 C:backup 目录下的person.txt 文件内容导入到test数据库中，字段之间使用逗号,间隔，字符类型字段值用双引号括起来，定义转义字符定义为问号?，每行记录以回车换行符“rn”结尾mysqlimport-u root-p test C:/backup/person.txt-fields-terminated-by=,-fields-optionally-enclosed-by=-fields-escaped-by=?-lines-terminated-by=rnp