免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 796 | 回复: 0

MySQL备份与恢复 [复制链接]

论坛徽章:
0
发表于 2011-12-20 09:46 |显示全部楼层
MySQL备份与恢复小结:
一、mysqldump -uusername -ppassword -hhostname --databases db_name > /path/db_name
常用选项:
 --databases db_name
 --all-databases
 --single-transaction(适用事务表)
 --master-data(热备份)
 --trigers(同时导出触发器,该选项默认启用,用--skip-trigers禁用他)
 --routines,-R(导出存储过程以及自定义函数)
 --opt(这只是一个快捷选项,等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset
选项。本选项能让 mysqldump 很快的导出数据,并且导出的数据能很快导回。该选项默认开启,但可以用 --skip-opt 禁用。注意,如果运行 mysqldump 没有指定 --quick 或 --opt 选项
,则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。)
 --no-data,-d(不导出任何数据,只导出数据库表结构)
 --no-create-info,-t(只导出数据,而不添加CREATE TABLE语句)
 --lock-tables(它和 --lock-all-tables 类似,不过是锁定当前导出的数据表,而不是一下子锁定全部库下的表。本选项只适用于 MyISAM 表,如果是 Innodb 表可以用 --single
-transaction 选项。)
 --hex-blob(使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 BINARY、VARBINARY、BLOB。)
 --extended-insert = true|false(默认情况下,mysqldump 开启 --complete-insert 模式,因此不想用它的的话,就使用本选项,设定它的值为 false 即可。)
 
例子:
MySQL备份
  1. 直接备份
     mysqldump -uusername -hhostname -ppassword --opt --default-character-set=utf8 --triggers --databases db_name > /path/db_name
  2. 压缩备份
     mysqldump -uusername -hhostname -ppassword db_name |gzip -9 >/path/db_name.gz
  3. 备份多个数据库
     mysqldump -uusername -hhostname -ppassword --databases db1_name db2_name db3_name >/path/multidb_name.sql
  4. 备份所有数据库
      mysqldump -uusername -hhostname -ppassword --all-databases >/path/alldb_name.sql
  5. 仅仅备份数据结构
     mysqldump -uusername -hhostname -ppassword --no-data db_name >/path/db_name.sql
  6. 基于事务的备份
       mysqldump -uuser -hhostname -ppassword --opt --single-transaction --default-character-set=utf8 --triggers db_name >/path/db_name.sql
  7. 在线备份
     mysqldump -uuser -ppassword -hhostname --opt --single-transaction --default-character-set=utf8 --master-data=1 --flust-logs db_name > /path/db_name.sql

MySQL还原

    mysql db_name </path/db_name.sql (此时的数据库必须事先手动创建好,然后在恢复)
    gunzip </path/db_name.gz | mysql -uusername -ppassword -hhostname db_name
    source /root/data.back
 将数据库转移到新服务器
    mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename


二、mysqlhotcopy -uusername -ppassword -hhostname db_name /tmp
注意,想要使用 mysqlhotcopy,必须要有 SELECT、RELOAD(要执行 FLUSH TABLES) 权限,并且还必须要能够有读取 datadir/db_name 目录的权限。


还原
mysqlhotcopy 备份出来的是整个数据库目录,使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /usr/local/mysql/data/)目录下即可,同时要注意权限的问题,如下例:
cp -rf db_name /data
chown -R nobody:nobody /data


三、select into outfile
如:select * into outfile /tmp/stu.sql from stu;
还原:load data infile /tmp/stu.sql into table stu;


四、基于快照备份(注:此时存放数据的目录必须位于lvm卷上)
创建快照lvmcreate -L 1G -s -n data.snap /dev/myvg/lvdata
备份dump -[0-9]u -f /home/data.back /dev/myvg/data.snap
备份出来数据以后就可以删除快照了lvmremove /dev/myvg/data.snap
恢复restore -rf /home/data.back


五、基于时间点mysqlbin --start-position n1 --stop-position n2 >/home/data.back
 --start-datatime
 --stop-datatime

   mysqlbinlog --start-position 824 --stop-position 1352 >/tmp/haha.sql


六、最直接,直接复制数据copy /data/*.sql /path/to/file

 

常用:MySQL备份与恢复实例:
一、基于逻辑备份与恢复,我们用MySQL自带的工具mysqldump来实现
我通常使用以下 SQL 来备份 MyISAM 表:
  1>mysqldump -uroot -p --events --triggers --flush-logs db_name >/tmp/mydb-`date +"%F-%H-%M-%S"`.sql
  2>mysqldump -uroot -p --events --triggers --flush-logs --routines --lock-all-tables db_name > /tmp/mydb-`date +"%F-%H-%M-%S"`.sql

使用以下 SQL 来备份 Innodb 表:
  1>mysqldump -uroot -p --events --triggers --routines --flush-logs --single-transaction --master-data=2 db_name > /tmp/mydb-`date +"%F-%H-%M-%S"`.sql
 
另外,如果想要实现在线备份,还可以使用 --master-data 参数来实现,如下:
  1>mysqldump -uroot -p --events --triggers --routines --flush-logs --master-data=1 --single-transaction db_name > /tmp/mydb-`date +"%F-%H-%M-%S"`.sql

 

MySQL的恢复
首先创建要恢复的数据库如:CREATE DATABASE mydb;
  1>mysql -uroot mydb < /tmp/mydb-`date +"%F-%H-%M-%S"`.sql
基于时间点的备份
  mysqlbinlog --start-position 106 --stop-position 513 > /tmp/mydbstore.sql
基于时间点的恢复
  mysql -uroot -p < /tmp/mydbstore.sql

 

二、基于快照的备份与恢复
LVM的实现
要保证datadir在LVM分区上。
FLUSH TABLES WITH READ LOCK;
创建快照
UNLOCK TABLES;
停止服务service mysqld stop
将创建的快照挂载到一个目录如:mount /dev/vg0/mysqldata /snap
cp -rp /data/* /snap/
之后就可以拷贝要备份的数据,拷贝完成后我们可以装饰快照删除
lvremove /dev/vg0/mysqldata

恢复数据可直接拷贝如:cp -rp /path/datadb /data/
基于时间点的恢复mysqlbinlog --start-position 206 --stop-position 445 mysql-bin.000005 >/tmp/mysql.sql
mysql -uroot -p </tmp/mysql.sql

 


 

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

DTCC2020中国数据库技术大会

【架构革新 高效可控】2020年12月21日-23日第十一届中国数据库技术大会将在北京隆重召开。

大会设置2大主会场,20+技术专场,将邀请超百位行业专家,重点围绕数据架构、AI与大数据、传统企业数据库实践和国产开源数据库等内容展开分享和探讨,为广大数据领域从业人士提供一场年度盛会和交流平台。

http://dtcc.it168.com


大会官网>>
  

北京盛拓优讯信息技术有限公司. 版权所有 16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122
中国互联网协会会员  联系我们:huangweiwei@it168.com
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP