备份恢复MySQL数据库的命令

备份与恢复备份：1、系统运行中，增量备份与整体备份例：每周日整体备份一次，周一到周六备份当天如果周五出了问题可以用周日的整体备份加周一、二、三、四来恢复2、备份工具有第三方收费备份工具，系统也有自带的备份功能mysqldumpmysqldump可以导出表和库1）、导出指定表格式：mysqldump -u用户名 -p 库名 表1 表2 表3 >保存地址\\保存备份文件名例一、导出mugua库下的account表mysqldump -uroot -p mugua account >D:\\account.sql注：语句结尾不用加分号，用反斜杠需要再加一个用来转义。2）、导出库下的所有表格式：mysqldump -u用户名 -p 库名 >保存地址\\保存备份文件名3）、导出库格式：mysqldump -u用户名 -p -B 库名1 库名2 库名3 >保存地址\\保存备份文件名注：不加-B会把库2和库3当初表名，加了-B后就表示以库为单位备份4）、导出所有库格式：mysqldump -u用户名 -p -A >保存地址/保存备份文件名恢复：a、登录到mysql情况下：语句格式：source 保存地址/保存备份文件名;对于表级备份文件需要在指定的库下才能恢复b、不登录mysql情况下恢复库、表：1、恢复库：mysql -uroot -p <保存地址/保存备份文件名;2、恢复库下的表：mysql -uroot -p 库名 <保存地址/保存备份文件名;备份1、mysqldump备份1）、备份多个数据库，如：mysqldump -u username -p --databases dbname1 dbname2...>backupName.sql;//要加--databases选项2）、备份所有数据库mysqldump -u username -p --all-databases > backupName.sql;2、mysqlhostcopy工具快速备份--直接复制整个数据库目录注：该工具是perl脚本，主要在linux系统下使用。该工具原理是将数据库文件拷贝到目标目录。因此只能备份MyISAM类型的表，不能用来备份InnoDB类型的表。该工具使用lock tables、flush tables和cp进行快速备份。先将需要备份的数据库加上一个读操作锁，如拿回用flush tables将内存中的数据写回到硬盘上的数据库，最后，把需要备份的数据库文件复制到目标目录。   backipDir参数指出备份到哪个文件夹下。选项：--help：用来查看帮助 --allowold：如果备份目录下存在相同的备份文件，将旧的备份文件名加上_old；--keepold：如果备份目录下存在相同的备份文件，不删除旧的备份文件，而是将旧文件更名；--flushlog：本次备份之后，将对数据库的更新记录到日志中；--noindices：只备份数据文件，不备份索引文件；--user=用户名：用来指定用户名，可以用-u代替；--password=密码：用来指定密码，可以用-p代替。使用-p时，密码与-p紧挨着。或者只用-p，然后交换方式输入密码。--port=端口号：用来指定访问端口，可以用-P代替；--socket=socket文件：用来指定socket文件，可以用-S代替。如：[root@localhost~]#mysqlhostcopy [option] dbname1 dbname2 ... backupDir/数据还原直接复制到数据库目录1）、可以直接复制到mysql数据库目录下。需要mysql数据库主版本号相同。而且对MyISAM类型的表比较有效。winsows下数据库目录通常放在C:\mysql\date、C:\documents and settings\all users\application data\mysql\mysql server5.1\data或c:\program files\mysql\mysql server 5.1\data。linux下通常在/var/lib/mysql、/usr/local/mysql/data或/usr/local/mysql/var。具体位置根据安装时设置位置而定。2）、mysqlhostcopy命令也是通过这种方式还原的。在linux下，复制到数据库目录后一定要将数据库的用户和组变成mysql。如：chown -R mysql.mysql dataDir 注：两个mysql分别表示组和用户；"-R"参数可以改变文件夹下的所有子文件的用户和组；"dataDir"参数表示数据库目录。mysql数据库只有root用户和mysql用户组下的mysql用户可以访问。因此，将数据库目录复制到指定文件夹后，一定要用chown命令将文件夹用户组变为mysql，将用户变为mysql。数据库迁移1、相同版本mysql数据库间迁移备份和迁移可以同时进行，如host1机器备份出所有数据库迁移到host2机器上。如：mysqldump -h name1 -u root --password=password1 --all-databases | mysql -h host2 -u root --password=password2其中‘|’符号表示管道，作用是将mysqldump备份的文件送给mysql命令；"--password=password1"是name1主机上root用户的密码。同理，password2是name2主机上的root用户的密码。通过这种方式可以直接实现迁移。2、不同版本的mysql数据库间迁移也可用mysqldump3、不同数据库间迁移通常可以用MyODBC来实现Mysql与sql server间迁移。mysql迁移到oracle时，需要用mysqldump导出sql文件，然后手动更改sql文件中的create语句。详情可以看tech.ccidnet.com/zt/qianyi/表的导出导入mysql中可以将表导出成文本文件、xml文件或html文件。相应文本文件也可以导入mysql中。1、用select...into outfile导出文本文件如：select [列名] from table [where 语句] into outfile '目标文件'[option];注：a、fields terminated by '字符串'：设置字符串为字段的分隔符，默认值是"\t";b、fields enclosed by '字符'：设置字符来括上字段的值。默认情况下不使用任何符号；c、fields optionally enclosed by '字符'：设置字符来括上char、varchar和text等字符型字段。默认情况下不使用任何符号；d、fields escaped by '字符'：设置转义字符，默认值为“\”;e、lines starting by '字符串'：设置每行开头的字符，默认情况下无任何字符；f、lines terminated by '字符串'：设置每行的结束符，默认值是"\n"。如：select * from test.student into oufile 'C:/student1.txt' fields terminated by '\、' optionally enclosed by '\"' lines starting by '\>' terminated by '\r\n';student1.txt中内容如下：>901、"张老大"、"男"、1985、"计算机系"、"北京市"注：windows下"\r\n"才是回车换行。2、用mysqldump命令导出文本文件格式：mysqldump -uroot -ppassword -T 目标目录 dbname table [option];注：option表示附件选项：这些选项必须用双引号括起来--fields-terminated-by=字符串：设置字符串为字段的分隔符，默认值是"\t";--fields-enclosed-by=字符：设置字符来括上字段的值；--fields-optionally-enclosed-by=字符：设置字符括上char、varchar和text等字符型字段；--fields-escaped-by=字符：设置转义字符；--lines-terminated-by=字符串：设置每行的结束符。如：mysqldump -uroot -phuang -T C:/test student "--fields-terminated-by=," "--fields-optionally-enclosed-by=""student.txt中的内容如：901,"张老大","男",1985,"计算机系","北京市"3、导出xml格式文件：mysqldump -uroot -ppassword [--xml或 -X] dbname table > C:/name.xml;注：--xml或-X选项可以导出xml格式文件；4、用mysql命令导出文本文件如：mysql -uroot -ppassword -e "select 语句" dbname > C:/name.txt;如：mysql -uroot -ppassword [--xml|-X] -e "select 语句" dbname > C:/name.xml;//导出xml格式如：mysql -uroot -ppassword [--html|-H] -e "select 语句" dbname > C:/name.html;//导出html文件5、用load data infile方式导入文本文件LOAD DATA [LOCAL] INFILE file INTO TABLE table [OPTION];其中，"LOCAL"是在本地客户端计算机中查找文本文件时用的，如果不加local表示从服务器查找文件；"file"参数指定了文本文件的路径和名称；"table"参数指表的名称；"OPTION"参数是可以有常用的选项如：fields terminated by '字符串'：设置字符串为字段的分隔符，默认值是"\t";fields enclosed by '字符'：设置字符来括上字段的值。默认情况下不使用任何符号；fields optionally enclosed by '字符'：设置字符来括上char、varchar和text等字符型字段。默认情况下不使用任何符号；fields escaped by '字符':设置转义字符，默认值为"\";lines starting by '字符'：设置每行开头的字符，默认情况下无任何字符；lines terminated by '字符串'：设置每行的结束符，默认值是"\n"；ignore n lines：忽略文件的前n行记录；（字段列表）：根据字段列表中的字段和顺序来加载记录；set column=expr：将指定的列column进行相应地转换后再加载，使用expr表达式来进行转换。例：LOAD DATA INFILE 'C:/student.txt' into table student FIELDS TERMINATED BY ',' ENCLOSED BY "'" LINES TERMINATED BY '\r\n' (字段1,字段2);6、用mysqlimport命令导入文本文件格式如：mysqlimport -u -ppassword [--local] dbname file [OPTION]其中，"local"是在本地计算机中查找文本文件时用的；"file"参数指定了文本文件的路径和名称；如：--fields-terminated-by=字符串：设置字符串为字段的分隔符，默认值是"\t";--fields-enclosed-by=字符：设置字符来括上字段的值。--fields-optionally-enclosed-by=字符：设置字符括上char、varchar和text等字符型字段。--fields-escaped-by=字符:设置转义字符;--lines-terminated-by=字符串：设置每行的结束符；--ignore-lines=n：忽略文件的前n行；如：mysqlimport -uroot -phuang test C:/student.txt "--fields-terminated-by=," "--fields-optionally-enclosed-by="" 7、将查询结果集导出文件1）mysql> select * from city where a='c' into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/df.txt' FIELDS TERMINATED BY ',' ENCLOSED BY "'" LINES TERMINATED BY '\r\n'; //此文件需要放在安全目录下才行，别的目录不行，这文件是被系统盘隐藏的，也可以将mysql notifier中sql edit中的options file中的security将secure-file-priv复选框勾去掉后重启mysql57服务即可，然后文件可以随便放了。但此只能导出结果集，不能导出字段名。2）在批处理中执行：mysql -uroot -proot -e "select * from city where a='c'" skdata > D:/jdlz.xls 此可将字段名也一并导出