1.mysqldump：
shell> mysqldump [options] db_name [tbl_name ...] #可以备份单个数据库的单个表，不会自动创建数据库，恢复时需要先创建数据库shell> mysqldump [options] --databases db_name ... shell> mysqldump [options] --all-databases例如：mysqldump -uroot --databases hellodb > /root/hellodbbak.sql #仅备份一个数据库mysqldump -uroot --databases hellodb mysql > /root/dbbak.sql #同时备份多个库
对于MyIsam引擎的数据库：
MyISAM：支持温备；锁定备份库，而后启动备份操作；
锁定方法： --lock-all-tables：锁定所有库的所有表； --lock-tables：对于每个单独的数据库，在启动备份之前锁定其所有表上面两个参数对InnoDB表一样生效，实现温备；
InnoDB：支持热备； --single-transaction 备份数据前启动一个事务，达到数据一致效果其它选项：
-E, --events：备份指定数据库相关的所有event scheduler； -R, --routines：备份指定数据库相关的所有存储过程和存储函数； --triggers：备份表相关的触发器； --master-data[=#]: 1：记录为CHANGE MASTER TO语句，此语句不被注释； 2：记录为注释的CHANGE MASTER TO语句；CHANGE MASTER TO记录了启动备份前时间处于哪个二进制文件的哪个位置
--flush-logs：锁定表完成后，执行flush logs命令可以实现二进制日志滚动
注意：二进制日志文件不应该与数据文件放在同一磁盘；二进制日志文件记录了全库信息，不能执行单独库还原，除非编辑它只针对一个库作用后再还原。
应该经常对二进制文件做增量备份，例如每过半填，让二进制日志自动滚动一次，把之前的备份起来。
示例：[root@testserver ~]# mysqldump -uroot --databases hellodb --lock-tables --master-data=2 > /root/hellodbbak.sql[root@testserver ~]# less hellodbbak.sql | grep CHANGE-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=7655;#这样就得到了当前使用的二进制日志文件名和当前记录位置修改当前库里的某些数据：MariaDB [hellodb]> INSERT INTO students (Name,Age,Gender,ClassID,TeacherID) VALUES ('Chao Gai',47,'M',3,7);Query OK, 1 row affected (0.00 sec)DELETE FROM students WHERE StuID=3;Query OK, 1 row affected (0.00 sec)此时mysql服务器挂了，我们把数据还原到另一个服务器上，执行恢复操作：还原前要注意先要关掉二进制日志，否则产生大量IO set sql_log_bin=0，等还原完成再开启，还原时在mysql交互式模式下用source命令[root@node1 ~]# mysql < hellodbbak.sql查看恢复的状况：发现没有完全恢复：晁盖没进来，谢烟客还在MariaDB [hellodb]> SELECT * FROM students;+-------+---------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 || 4 | Ding Dian | 32 | M | 4 | 4 || 5 | Yu Yutong | 26 | M | 3 | 1 || 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 10 | Yue Lingshan | 19 | F | 3 | NULL || 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 15 | Duan Yu | 19 | M | 4 | NULL || 16 | Xu Zhu | 21 | M | 1 | NULL || 17 | Lin Chong | 25 | M | 4 | NULL || 18 | Hua Rong | 23 | M | 7 | NULL || 19 | Xue Baochai | 18 | F | 6 | NULL || 20 | Diao Chan | 19 | F | 7 | NULL || 21 | Huang Yueying | 22 | F | 6 | NULL || 22 | Xiao Qiao | 20 | F | 1 | NULL || 23 | Ma Chao | 23 | M | 4 | NULL || 24 | Xu Xian | 27 | M | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL |+-------+---------------+-----+--------+---------+-----------+25 rows in set (0.00 sec)继续使用二进制文件恢复：根据上面列出来的二进制日志文件和当前记录位置[root@testserver ~]# mysqlbinlog --start-position=7655 /mydata/data/mysql-bin.000011 > incre.sql看一下这个二进制日志文件：[root@testserver ~]# mysqlbinlog --start-position 7655 /mydata/data/mysql-bin.000011/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#151110 21:35:57 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.44-MariaDB-log created 151110 21:35:57 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;BINLOG 'vfJBVg8BAAAA8QAAAPUAAAABAAQANS41LjQ0LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAC98kFWEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA8nm5Lg=='/*!*/;# at 7655#151110 23:14:20 server id 1 end_log_pos 7726 Query thread_id=10 exec_time=0 error_code=0SET TIMESTAMP=1447168460/*!*/;SET @@session.pseudo_thread_id=10/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=0/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 7726#151110 23:14:20 server id 1 end_log_pos 7754 IntvarSET INSERT_ID=26/*!*/;# at 7754#151110 23:14:20 server id 1 end_log_pos 7907 Query thread_id=10 exec_time=0 error_code=0use `hellodb`/*!*/;SET TIMESTAMP=1447168460/*!*/;INSERT INTO students (Name,Age,Gender,ClassID,TeacherID) VALUES ('Chao Gai',47,'M',3,7)/*!*/;# at 7907#151110 23:14:20 server id 1 end_log_pos 7934 Xid = 780COMMIT/*!*/;# at 7934#151110 23:16:55 server id 1 end_log_pos 8005 Query thread_id=10 exec_time=0 error_code=0SET TIMESTAMP=1447168615/*!*/;BEGIN/*!*/;# at 8005#151110 23:16:55 server id 1 end_log_pos 8105 Query thread_id=10 exec_time=0 error_code=0SET TIMESTAMP=1447168615/*!*/;DELETE FROM students WHERE StuID=3/*!*/;# at 8105#151110 23:16:55 server id 1 end_log_pos 8132 Xid = 781COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;拷贝到那台好的服务器进行还原：[root@testserver ~]# scp incre.sql root@192.168.3.131:/root/root@192.168.3.131's password: incre.sql 100% 2373 2.3KB/s 00:00[root@node1 ~]# mysql < incre.sql查看：发现谢烟客没了，晁盖来了，数据被完整恢复了！+-------+---------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 4 | Ding Dian | 32 | M | 4 | 4 || 5 | Yu Yutong | 26 | M | 3 | 1 || 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 10 | Yue Lingshan | 19 | F | 3 | NULL || 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 15 | Duan Yu | 19 | M | 4 | NULL || 16 | Xu Zhu | 21 | M | 1 | NULL || 17 | Lin Chong | 25 | M | 4 | NULL || 18 | Hua Rong | 23 | M | 7 | NULL || 19 | Xue Baochai | 18 | F | 6 | NULL || 20 | Diao Chan | 19 | F | 7 | NULL || 21 | Huang Yueying | 22 | F | 6 | NULL || 22 | Xiao Qiao | 20 | F | 1 | NULL || 23 | Ma Chao | 23 | M | 4 | NULL || 24 | Xu Xian | 27 | M | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL || 26 | Chao Gai | 47 | M | 3 | 7 |+-------+---------------+-----+--------+---------+-----------+25 rows in set (0.00 sec) 2.基于LVM2进行热备：
创建一个mysql的数据目录，使用lvm：[root@testserver ~]# fdisk /dev/sdaWARNING: DOS-compatible mode is deprecated. It's strongly recommended to switch off the mode (command 'c') and change display units to sectors (command 'u'). Command (m for help): nCommand action e extended p primary partition (1-4)pPartition number (1-4): Value out of range.Partition number (1-4): 3First cylinder (7859-15665, default 7859): Using default value 7859Last cylinder, +cylinders or +size{K,M,G} (7859-15665, default 15665): +20G Command (m for help): tPartition number (1-4): 3Hex code (type L to list codes): 8eChanged system type of partition 3 to 8e (Linux LVM) Command (m for help): wThe partition table has been altered!做LVM：[root@testserver ~]# partx -a /dev/sdaBLKPG: Device or resource busyerror adding partition 1BLKPG: Device or resource busyerror adding partition 2BLKPG: Device or resource busyerror adding partition 3[root@testserver ~]# pvcreate /dev/sda3 Physical volume "/dev/sda3" successfully created[root@testserver ~]# vgcreate myvg /dev/sda3 Volume group "myvg" successfully created[root@testserver ~]# lvcreate -L 10G -n mydata myvg Logical volume "mydata" created[root@testserver ~]# lvs LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert mydata myvg -wi-a----- 10.00g root vg0 -wi-ao---- 20.00g swap vg0 -wi-ao---- 2.00g usr vg0 -wi-ao---- 10.00g var vg0 -wi-ao---- 20.00g格式化：mke2fs -t ext4 /dev/myvg/mydata 开机自动挂载：mkdir /mydata/在fstab文件中添加/dev/myvg/mydata /mydata ext4 defaults 0 0mount -amkdir /mydata/datachown mysql.mysql /mydata/data/ -R编辑my.cnf：在mysqld段加如下内容datadir=/mydata/datainnodb_file_per_table = ONskip_name_resolve = ONlog-bin=mysql-binbinlog_format=mixed启动mysql service mysqld start现在先导入一个数据库，就用前面的备份文件cp hellodbbak.sql incre.sql /tmpMariaDB [(none)]> set sql_log_bin=0 #临时关闭二进制日志 -> ;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> source /tmp/hellodbbak.sql; #导入MariaDB [hellodb]> SET sql_log_bin=1; #开启二进制日志Query OK, 0 rows affected (0.00 sec)查看二进制日志状态：MariaDB [(none)]> SHOW MASTER LOGS;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 245 || mysql-bin.000002 | 245 |+------------------+-----------+2 rows in set (0.00 sec)MariaDB [(none)]> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 | 245 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)因为导入时关闭了二进制日志功能，所以没有产生新的二进制日志，避免了大量IOMariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000002';+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+| mysql-bin.000002 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.44-MariaDB-log, Binlog ver: 4 |+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+(1) 请求锁定所有表； mysql> FLUSH TABLES WITH READ LOCK;(2) 记录二进制日志文件及事件位置；可以先滚动下日志 mysql> FLUSH LOGS;MariaDB [(none)]> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 | 245 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)MariaDB [(none)]> FLUSH LOGS;Query OK, 0 rows affected (0.49 sec)MariaDB [(none)]> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000003 | 245 | | |+------------------+----------+--------------+------------------+1 row in set (0.02 sec) mysql> SHOW MASTER STATUS; 好的方法： mysql -e 'flush logs;' mysql -e 'SHOW MASTER STATUS' > /tmp/pos.`date +%F` (3) 创建快照； [root@testserver ~]# lvcreate -L 500M -n mydata-snap -p r -s /dev/myvg/mydata Logical volume "mydata-snap" created(4) 释放锁 mysql> UNLOCK TABLES;(5) 挂载快照卷，执行数据备份；(6) 备份完成后，删除快照卷；(7) 制定好策略，通过原卷备份二进制日志；删除两行试试：MariaDB [(none)]> use hellodb;Database changedMariaDB [hellodb]> DELETE FROM students WHERE StuID=5 -> ;Query OK, 1 row affected (0.05 sec)MariaDB [hellodb]> DELETE FROM students WHERE StuID=11;Query OK, 1 row affected (0.08 sec)二进制日志position从245变到了642MariaDB [hellodb]> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000003 | 642 | | |+------------------+----------+--------------+------------------+1 row in set (0.01 sec)挂载快照卷：mount -r /dev/myvg/mydata-snap /mnt/查看快照里的内容[root@testserver data]# ls /mnt/data[root@testserver data]# ls /mnt/data/aria_log.00000001 ibdata1 mysql mysql-bin.indexaria_log_control ib_logfile0 mysql-bin.000001 testserver.errhellodb ib_logfile1 mysql-bin.000002 testserver.pid执行备份：[root@testserver data]# cp -a /mnt/data/ /tmp/[root@testserver mysql]# rm -rf mysql-bin.*[root@testserver data]# cp -a /mydata/data/mysql-bin.000003 /tmp模拟故障：service mysqld stop[root@testserver data]# rm -rf /mydata/data/*还原：root@testserver data]# cp -a /tmp/data/* /mydata/data/重启查看：MariaDB [(none)]> SHOW MASTER LOGS;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 245 |+------------------+-----------+1 row in set (0.00 sec)MariaDB [hellodb]> SELECT * FROM students;+-------+---------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 || 4 | Ding Dian | 32 | M | 4 | 4 || 5 | Yu Yutong | 26 | M | 3 | 1 || 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 10 | Yue Lingshan | 19 | F | 3 | NULL || 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 15 | Duan Yu | 19 | M | 4 | NULL || 16 | Xu Zhu | 21 | M | 1 | NULL || 17 | Lin Chong | 25 | M | 4 | NULL || 18 | Hua Rong | 23 | M | 7 | NULL || 19 | Xue Baochai | 18 | F | 6 | NULL || 20 | Diao Chan | 19 | F | 7 | NULL || 21 | Huang Yueying | 22 | F | 6 | NULL || 22 | Xiao Qiao | 20 | F | 1 | NULL || 23 | Ma Chao | 23 | M | 4 | NULL || 24 | Xu Xian | 27 | M | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL |+-------+---------------+-----+--------+---------+-----------+25 rows in set (0.01 sec)使用bin-log继续继续恢复：查看位置：less /tmp/pos.2015-11-11 mysqlbinlog --start-position=245 /tmp/mysql-bin.000003 > /tmp/incre.sql mysql < /tmp/incre.sql（这里应该使用source命令导入，临时关闭sql_log_bin）恢复完成： MariaDB [hellodb]> SELECT * FROM students;+-------+---------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 || 4 | Ding Dian | 32 | M | 4 | 4 || 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 10 | Yue Lingshan | 19 | F | 3 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 15 | Duan Yu | 19 | M | 4 | NULL || 16 | Xu Zhu | 21 | M | 1 | NULL || 17 | Lin Chong | 25 | M | 4 | NULL || 18 | Hua Rong | 23 | M | 7 | NULL || 19 | Xue Baochai | 18 | F | 6 | NULL || 20 | Diao Chan | 19 | F | 7 | NULL || 21 | Huang Yueying | 22 | F | 6 | NULL || 22 | Xiao Qiao | 20 | F | 1 | NULL || 23 | Ma Chao | 23 | M | 4 | NULL || 24 | Xu Xian | 27 | M | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL |+-------+---------------+-----+--------+---------+-----------+23 rows in set (0.00 sec) 3.xtrabackup实现备份：
使用日志序列号lsn来识别时增量备份还是完全备份，以及保证增量备份可以在线进行