MySQL备份与恢复

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