MySQL数据备份恢复mysqldump -uroot -p--databases test >/tmp/test.sql
该语句不仅备份了所有数据库下的表结构，而且包括创建数据库的语句
数据备份
方法一：使用mysqldump命令备份
mysqldump命令可以将数据库中的数据备份成一个文本文件
原理：先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句，然后，将表中的所有记录转换成一条INSERT语句，这些CREATE语句和INSERT语句都是在还原时使用，还原时，可以使用其中的CREATE语句来创建表，使用INSERT语句来还原数据
备份数据库：
mysqldump -u username-h host -ppassword dbname [table1 table2 .....]>backupname.sql
mysqldump-uroot -pwei test -l -F >'/tmp/test.sql'
-l: 读锁，可读不可写
-F 即flush logs,可以重新生成新的日志文件(从恢复起，日志是空的)，当然包括bin-log日志
查看binlog日志用mysql>show master status
[root@node201 ~]#mysqldump -u root -p mysql >/mysql.sql
mysql.sql必须还原到一个已经存在的数据库中
备份多个数据库
mysqldump -u username-p --databases dbname1 dbname2 ....>backupname.sql
[root@node201 /]#mysqldump -u root -p --databases test mysql >/test_mysql.sql
备份所有数据库
mysqldump -u username -p--all-databases >backupname.sql
[root@node201 /]#mysqldump -u root -p --all-database >/all_database.sql
只导出创建数据表的指令：
mysqldump -d test>mysql.sql
只导出插入的数据,不导出创建数据表的命令
mysqldump -t test>mysql.sql
只导出数据，不要sql命令：
mysqldump -T test>mysql.sql
方法二：使用select intooutfile 'filename'语句备份
select * frommysql.user into outfile 'test.txt'; //将mysql数据库的user表中的数据导出到文件中
//输出的数据只有数据，没有表结构,一次只能处理一个表
方法二：直接复制整个数据库目录
可以复制数据库文件，以达到备份的目的，但要停止服务器，对lnnoDB不适用，对myisam适用
1.lock tables
2.flush tables
3.flush tables withread lock;
使用mysqlhotcopy工具快速备份,只能备份myisam类型的表，不能备份InnoDB类型的表,只能运行在数据库目录所在的机器上,只能在Linux上运行
#mysqlhotcopy[option] dbname1 dbname2 ..... /backupdir/
[root@node201 /]#mysqlhotcopy --h
Option h requires anargument
Invalid option
/usr/bin/mysqlhotcopyVer 1.22
Usage:/usr/bin/mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]
-?, --help display this helpscreen and exit
-u, --user=# user for database login if not currentuser
-p, --password=# password to use when connecting to server(if not set
in my.cnf, which isrecommended)
-h, --host=# Hostname for local server whenconnecting over TCP/IP
-P, --port=# port to use when connecting to localserver with TCP/IP
-S, --socket=# socket to use when connecting to localserver
--allowold don't abort if target dir alreadyexists (rename it _old)//如存在相同的文件，在旧的备份文件名加上_old
--addtodest don't rename target dir if it exists,just add files to it
--keepold don't delete previous (now renamed)target when done //如存在相同的文件，不删除旧的备份文件，更改旧的文件名
--noindices don't include full index files incopy//只备份数据文件，不备份索引文件
--method=# method for copy (only "cp"currently supported)
-q, --quiet be silent except for errors
--debug enable debug
-n, --dryrun report actions without doing them
--regexp=# copy all databases with namesmatching regexp
--suffix=# suffix for names of copied databases
--checkpoint=# insert checkpoint entry into specifieddb.table
--flushlog flush logs once all tables arelocked //将备份日志记录到日志中
--resetmaster reset the binlog once all tables arelocked
--resetslave reset the master.info once all tablesare locked
--tmpdir=# temporary directory (instead of/tmp)
--record_log_pos=# record slave and master status in specifieddb.table
--chroot=# base directory of chroot jail inwhich mysqld operates
Try 'perldoc /usr/bin/mysqlhotcopy' for morecomplete documentation
数据还原
使用mysql命令还原
先在数据库下创建数据库
mysql -u root -p[dbname] <back.sql
直接复制到数据库目录
数据库目录：/var/lib/mysql /usr/local/mysql/data /usr/local/mysql/var
复制数据库目录后，一定要将数据库的用户和组变成mysql
chown -R mysql.mysqldatadir
恢复以文件名备份的数据库：
[root@node203 var]#/usr/local/mysql/bin/mysql -uroot -p123456 -v -f test</tmp/test.sql
-v ：查看导入的详细信息
-f :是当中间遇到错误时，可以skip过去，继续执行下面的语句
恢复以bin-log日志来恢复数据库：
[root@node203 var]#/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 |/usr/local/mysql/bin/mysql-uroot -p123456 test
登录MYSQL后用source还原数据库：
mysql> source/test.sql;
mysqlhotcopy快速恢复：
mysqlhotcopy备份后的文件也可以用来恢复数据库，将备份文件复制到mysql存放数据的位置(mysql的data文件夹)
cp -r/usr/backup/test /usr/local/mysql/data
chown -R mysql.mysql/var/lib/mysql/dbname
数据库迁移：
1.相同版本的MYSQL数据库之间的迁移：
相同版本就是主版本号相同的MYSQL数据库
方法：
mysqldump 备份导出数据->mysql命导入数据
eg:将www.abc.com主机的数据库迁移到www.bcd.com的主机数据库上
mysqldump -h www.abc.com -uroot-ppassword dbname | mysql -h www.bcd.com-uroot -ppassword
2.不同版本的MYSQL数据库之间的迁移:
MYSQL升级时，先停止服务，然后卸载旧版本，并安装新版的MYSQL
旧->新
MYISAM：使用mysqlhotcopy,mysqldump,
InnoDB:mysqldump导出 mysql导入
新->旧
MYISAM:mysqldump
InnoDB:mysqldump
工具：
windows:MYODBC实现mysql和sql server 之间的迁移
mysql:mysql MigrationToolkit 也可在不同数据库间进行数据迁移
表的导入和导出
导出:
用select .... into outfile导出文本文件(需要有file权限)
selectcolumnlist from table wherecondition into outfile 'filename'[options]
options:
fields terminated by 'value'
fields [optionally] enclosed by 'value'
fields escapedby 'value'
linesstartingby 'value'
linesterminatedby 'value'
eg:
select * fromtest.person into outfile '/tmp/person.sql'
使用mysqldump命令导出文本文件
mysqldump -T path-uroot -p dbname [tables] [options]
options:
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
eg:
mysqldump -T /tmpperson -uroot -p
执行后将产生：person.sql person.txt两个文件
person.sql为创建person表的create语句
person.txt:数据文件
用mysql命令导出文本文件
mysql -uroot -p --vertical --execute=''select 语句" dbname > filename.txt
--vertical:将每条记录分为多行显示
eg:
mysql -uroot -p --execute="select * fromperson;" test>/tmp/person.txt//导出文本文件
mysql -uroot -p --html -execute="select * from person;" test >/tmp/person.html //导出html文件
mysql -uroot -p --xml -execute="select * from person;" test >/tmp/person.html //导出xml文件
导入：
使用load data infile方式导入文本文件
load data infile 'filename.txt' into table tablename[options][ignore number lines]
eg:
load data infile '/tmp/person.txt' into table test.person;
使用mysqlimport命令导入文本文件
mysqlimport -uroot -p dbname filename.txt [options]
dbname导入的表所在的数据库名称，注意：mysqlimport 命令不指定导入数据库的表名称，数据表的名称由导入文件名称确定，文件名作为表名,导入数据前该表必须存在
eg:
#mysqlimport -uroot-p test /var/test.txt
++++++++++++++++++++++++++++++++++++++++++++
备份数据库
mysqldump:(在命令行模式下)
1.[root@node203 ~]#mysqldump -l -F[options] db_name [tables] //备份数据库中的一些表 (-l参数可以保证数据库备份的一致性,-F表示生成一个新的日志文件)
[root@node203 /]# mysqldump-uroot -p123456 test t >/t.sql //备份test数据库下的表t
[root@node203 /]# mysqldump-uroot -p123456 test t t1 >/t2.sql //备份test数据库下的表t,t1表
2.[root@node203 ~]#mysqldump [options] --database db1,db2 //备份指定数据库
3.[root@node203 ~]#mysqldump [options] --all-database //备份所有数据库
eg:[root@node203 ~]# mysqldump-uroot -p123456 --all-database >/2012.sql
热备份：
1.mysqlhotcopy db_name[/path/to/new_directory]
2.手动锁表copy
mysql>flush tables for read;/将所有表加读锁
再CP数据库文件到目录
恢复数据库：
[root@node203 /]# mysqldump-uroot -p123456 -l test >test.sql //备份test数据库
[root@node203 /]# mysql -uroot-p123456 test <test.sql //恢复test数据库
mysqlbinlog工具恢复自备份以来的所有BINLOG
使用二进制日志进行数据恢复
二进制日志记录一台服务器上所有SQL执行事务
提取二进制工具：mysqlbinlog
开启二进制日志功能:
vim my.cnf
[mysqld]
log-bin=/var/log/mysql/bing.log
将二进制日志与备份进行同步
#mysqladmin -u root -pmypwd flush-logs
简单的数据恢复(全部恢复)
1.停止mysql服务器
用以下命令启动mysql服务器
#mysqld--socket=/tmp/mysql_restore.sock --skip-networking
重新创建一个socket文件 阻止用户通过TCP/IP,socket文件或管道来访问服务器
本服务器只能从本地进行连接
2.恢复备份好的dump文件
#mysql-u root -p mypwd --socket=/tmp/mysql_restore.sock </var/backup/20122232.sql
3.恢复数据库事务处理，使用mysqlbinlog
#mysqlbinlog/var/log/mysql/bin.123456 | mysql -u root -pmypwd --socket=/tmp/mysql_restore.sock
手动恢复数据(部分恢复)
1.将二进制日志文件导出一个文本文件中
#mysqlbinlog /var/log/mysql/bin.123456 >/tmp/mysql_restore.sql
2.删除一些不要的SQL语句并保存
vim mysql_restore.sql
...
3.用修改后的mysql_restore.sql恢复文件
#mysql -u root -pmypwd--socket=/tmp/mysql_restore.sock </tmp/mysql-restore.sql
针对一个时间点恢复数据
mysql 5.1以上版本
在2010.1.20上午10点执行SQL删除了一个表，现在要恢复
#mysqlbinlog --stop-date="2012-01-209:59:59" /var/log/mysql/bin.123456 | mysql -u root -pmypwd--socket=/tmp/mysql_restore.sock
以上将恢复--stop-date日期之前的数据
如果在执行SQL语句数小时之后才发现执行了错误操作
#mysqlbinlog --start-date="2012-01-2010:01:00" /var/log/mysql/bin.123456 | mysql -u root -pmypwd--socket=/tmp/mysql_restore.sock
SQL语句记录的10:01:00以后的内容将被恢复
使用position参数恢复
通过日志文件中的数据来标识恢复的开始位置和结束位置
1.获取一个时间段里执行的SQL语句
#mysqlbinlog --start-date="2010-01-209:55:00" --stop-date="2010-01-22 10:04:00"/var/log/mysql/bin.123456 >/tmp/mysql_restore.sql
--stop-position="100"
--start-position="50"
--stop-date="2012-01-04 21:17:50"
--start-date="2012-01-04 19:10:10"
2.删除这个时间段内不想执行的SQL语句,记下位置数据，位置标识是log_pos后面跟随一个数字eg:log_pos123456
#mysqlbinlog --stop-postion="1314314"/var/log/mysql/bin.123456 | mysql -u root -pmypwd--socket=/tmp/mysql_restore.sock
恢复停止位置前的所有事务
#mysqlbinlog --start-postion="345245"/var/log/mysql/bin.123456 | mysql -u root -pmypwd--socket=/tmp/mysql_restore.sock
恢复从开始位置到二进制日志结束的所有内容
/mysql_bin_dir/mysqldump-u user -password dataname
> $(date+%Y%m%d)-dataname.sql
eg:
/usr/bin/mysqldump -u root -p mysql > -dataname.sql
说明：
(1)mysql_bin_dir：mysqldump所在的路径，
一般默认为为/usr/bin
(2)user：数据库用户名；
(3)dataname：数据库名；
(4)password：用户密码；
（5）$(date +%Y%m%d)：系统用来获取
当前时间的命令，这个时间作为前缀用来标
识备份文件。
定期备份数据库文件
如何将定期备份的数据库文件自动上传到另一台FTP服务器，是实现数据库自动备份
的关键。在上传数据前，需要FTP 服务器提供具有上传数据权限的用户，以及正常的FTP服务。这里通过Linux的shell 脚本程序来实现，具体的脚本命令如下：
ftp -n FtpHost<<END //FtpHost 为上传数据库本份文件的Ftp 服务器地址
user Uer passwd //User为Ftp服务器提供的具有写权限的用户名，passwd为Ftp 登录密码
binary //以binary模式传输
cd /home/test //转到FTP服务器上的备份文件夹
lcd /root //转到MySQL数据库服务器的备份数据文件所在的文件夹
put $(date+%Y%m%d)-mysql.sql // 上传备份文件
bye
END
删除mysql临时文件
为了节省MySQL 数据库上的空间，可以用以下命令删除在MySQL服务器生成的
临时文件。
rm -f $(date +%Y%m%d)-mysql.sql
创建文件backup.sh,将其保存在/root
目录下，具体内容如下：
#!/bin/bash
/mysql_bin_dir /mysqldump -u root– p password
dataname > $(date+%Y%m%d)-mysql.sql
ftp -n Ftphost <<END
