10 MySQL之数据备份与恢复
01-数据备份
# 1、使用 MySQLdump 命令备份 MySQLdump 是MySQL提供的一个非常有用的数据库备份工具。 MySQLdump 命令执行时,可以将数据库备份成一个文本文件,该文本实际上包含了多个CREATE和INSERT语句,使用这些语句可以重新创建表和插入数据。 基本语法格式如下: mysqldump -u user -h host -p password dbname[tbname, [tbname...]]>filename.sql user表示用户名称;host 表示登录用户的主机名称;password为登录密码; dbname为需要备份数据库名称;tbname为dbname数据库中需要备份的数据表,可以指定多个需要备份的表; 右箭头符号'>'告诉MySQLdump将备份数据表的定义和数据写入备份文件; filename.sql 为备份文件的名称。 # 2、使用 MySQLdump 备份单个数据库中的所有表 mysqldump -u root -p booksdb > /users/huangyanpeng/Desktop/db_test/booksdb_20190909.sql # 3、使用 MySQLdump 备份数据库中的某个表 语法格式如下: mysqldump -u user -h host -p dbname [tbname, [tbname...]] > filename.sql tbname表示数据库中的表名,多个表名之间用空格 隔开。 备份表和备份数据库中所有的表 的区别:要在数据库名称dbname之后指定需要备份的表名称。 # 4、使用MySQLdump备份多个数据库 mysqldump -u user -h host -p --databases [dbname, [dbname...]] > filename.sql 使用--databases 参数之后,必须指定至少一个数据库的名称,多个数据库名称之间用空格隔开。 另外,使用 --all-databases 参数可以备份系统中所有的数据库,语句如下: mysqldump -u user -h host -p --all-databases > filename.sql # 运行帮助命令 mysqldump --help # 5、使用 MySQLhotcopy 工具快速备份 备份数据库或单个表的最快途径,但它只能运行在数据库目录所在的机器上,并且只能备份MyISM类型的表。MySQLhotcopy 在Unix系统中运行。 语法格式如下: mysqlhotcopy db_name_1, ... db_name_n /path/to/new_directory db_name_1, ... db_name_n 分别为需要备份数据库的名称; /path/to/new_directory 指定备份文件目录。 使用 MySQLhotcopy 备份test数据库到 /usr/backup 目录下,输入语句如下: mysqlhotcopy -u root -p test /usr/backup 要想执行 MySQLhotcopy,必须可以访问备份的表文件,具有哪些表的SELECT权限、RELOAD权限 和 LOCK TABLES 权限。 注意: (1)MySQLhotcopy 只是将表所在的目录复制到另一个位置,只能用于备份MyISAM和ARCHIVE表。 (2)备份InnoDB类型的数据表时会出现错误信息。 (3)由于它复制本地格式的文件,故也不能移植到其他硬件或操作系统下。
02-数据恢复
# 1、使用MySQL命令恢复 对于已经备份的包含 CREATE、INSERT 语句的文本文件,可以使用MySQL命令导入到数据库中。 备份的sql文件中包含CREATE、INSERT 语句(有时也会有DROP语句)。 语法如下: mysql -u root -p [dbname] < filename.sql 参数: user是执行backup.sql 中语句的用户名;-p 表示输入用户名密码; dbname 是数据库名;如果filename.sql 文件为MySQLdump 工具创建的包含创建数据库语句的文件,执行的时候不需要指定数据库名。 例: mysql -u root -p booksDB < /.../booksdb_20190909.sql 执行该语句之前,必须先在MySQL服务器中创建 booksDB数据库。 如果已经登录服务器,还可以使用 source 命令导入 SQL 文件。 source filename.sql 要先切换到 相应的数据库下。 # 2、直接复制到数据库目录 # 3、MySQLhotcopy快速恢复 MySQLhotcopy 备份后的文件也可以用来恢复数据库,在MySQL服务器停止运行时, 将备份的数据库文件复制到MySQL存放数据的位置(MySQL的data文件夹),重新启动服务即可。 如果以根用户执行该操作,必须指定数据库文件的所有者,输入语句如下: chown -R mysql.mysql /var/lib/mysql/dbname 例: 从MySQLhotcopy 复制的备份恢复数据库,输入语句如下: cp -R /usr/backup/test usr/local/mysql/data 执行完该语句,重启服务器,MySQL将恢复到备份状态。 注意: (1)如果要恢复的数据库已经存在,则先删除DROP数据库之后,再恢复才能成功。 (2)另外MySQL不同版本之间必须兼容,恢复之后的数据才可以使用。
03-数据迁移
# 1、相同版本的MySQL数据库之间的迁移 最简单的方式是通过复制数据库文件目录,只试用于MyISAM引擎表。 对于InnoDB表,不能直接复制文件的方式备份数据库。 因此最常用和最安全的方式是使用MySQLdump命令导出数据,然后在目标数据库服务器使用MySQL命令导入。 例: 将www.abc.com 主机上的MySQL数据库全部迁移到www.bcd.com主机上。在www.abc.com 主机上执行的命令如下: mysqldump -h www.abc.com -uroot -p password dbname | mysql -h www.bcd.com -uroot -p password MySQLdump导入的数据直接通过管道符 | 传给MySQL命令导入的主机www.bcd.com 数据库中。 dbname 为需要迁移的数据库名称,如果要迁移全部数据库,使用参数 --all-databases。 # 2、不同版本的MySQL数据库之间的迁移 旧 --》新: (1)对于MyISAM引擎表,可以直接复制数据库文件,也可以使用 MySQLhotcopy 工具、MySQLdump工具。 (2)对于InnoDB引擎表,一般只能使用MySQLdump 将数据导出。然后使用MySQL命令导入到目标服务器上。 新 --》旧:最好使用 MySQLdump 名导出,然后导入目标数据库中。 # 3、不同数据库之间的迁移
04-表的导入和导出
# 1、使用 SELECT ... INTO OUTFILE 导出文本文件 (1)MySQL数据库导出数据时,允许使用包含导出定义的SELECT 语句今次那个数据的导出操作。 (2)该文件被创建到服务器主机上,一次必须拥有文件写入权限(FILE 权限),才能使用此语法。 (3)"SELECT ... INTO OUTFILE 'filename' "形式的SELECT语句可以把被选择的行写入一个文件中,filename 不能是一个已经存在的文件。 基本的语法格式如下: SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [OPTIONS] --OPTIONS 选项