1：local&Remote backups
mysqldump can connect to local or remote servers
mysqlhotcopy performs only local backup
select ..... into outfile
snapshot
lvm
logical & physical backup
physical backup :cp scp tar rsync 工具
logical backup: mysqldump 工具，但恢复需要时间，logical backup are performed with the mysql running ,The server is not taken offline
online backup & offline backup :一般生产环境下不会用到offline backup
2:full & incremental backups
A full backup includes all data managed by a mysql server at a given point time
An incremental backup consists of the change made to the data during a given the span
3:full & point-in-time(incremental) recovery
4:for innodb tables, It is possible to perform an online backup that takes no locks on tables using the
--single-transaction option to mysqldump ##备份innodb时该参数异常重要
5:select * into outfile 'file_name' from tbl_name;
导入用load data infile 或mysqlimport
导入sql文件：
mysql < a.sql
source a.sql
mysqldump -uroot -p'password' test > test.sql #test 为库名
mysqldump -uroot -p'password' test t1 t2 t3 > d.sql #test 为库名,t1,t2,t3为表名
mysqldump 执行的时候默认是导出trigger的，但procedure和functionse 不会导出，需要导出procedure和functions 需要加上-r(--routines)
mysqldump --no-data --routines --events test > test.sql ##这个方式更稳妥，考虑到了procedure和functions
mysqldump --all-databases --no-create-info > dump-all.sql ## 不导入表结构，只导出数据
6：point-in-time incremental recovery using the binary log
>show binary logs; ##列出所有的二进制日志文件
>show master status; ##列出现在使用的二进制日志文件
如果有多个binary logs 例如有两个，
如果使用如下的恢复方式是不正确的！
mysqlbinlog binlog.00001 | mysql -uroot -p'passowrd'
mysqlbinlog binlog.00002 | mysql -uroot -p'passowrd'
##上述的方式是错误的
正确的方式应该为：
mysqlbinlog binlog.00001 > 1.sql
mysqlbinlog binlog.00002 >> 1.sql
或者：
mysqlbinlog binlog.00001 binlog.00002 | mysql -uroot -p'password'
摘抄之文档：
或event：
mysqldump -uroot -p'password' --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > backup.sql ##master/slave 机器常用备份，--master-data=2 表示写二进制日志信息
如果已经开启gtid，则需要使用：
mysqlbinlog --skip-gtid binlog.00001 > /tmp/dump.sql
阅读(887) | 评论(0) | 转发(0) |
