Mysql lvm备份和恢复

临时关闭二进制文件
mysql> show global variables like ‘%log%’;
mysql> show master status;
mysql> show binlog events in ‘mysql.000005’;
mysql> set sql_log_bin=0;
mysql> create database hellodb;
mysql> show binlog events in ‘mysql.000005’;
mysql> set sql_log_bin=1;
mysql> drop database hellodb;
mysql> show binlog events in ‘mysql.000005’;
逻辑备份：
1.浮点数丢失精度
2.备份出的数据更占用存储空间，压缩后可大大节省空间
3.不适合大数据库做完全备份
1.对Innodb
mysql> flush tables with read lock;
mysql> show engine innodb status;
MVCC：repeatable-read
–single-transaction
2.select备份恢复（通常用作单张表备份，不用作完全备份）
备份：
SELECT * INTO OUTFILE ‘/path/to/somefile.txt’ FROM tb_name [WHERE clause];
mysql> select * into outfile ‘/tmp/tutors.txt’ from tutors;
还原：
LOAD DATA INFILE ‘/path/to/somefile.txt’ INTO TABLE tb_name;
mysql> use jiaowu
Database changed
mysql> select * from tutors;
+—–+————–+——–+——+
| TID | Tname | Gender | Age |
+—–+————–+——–+——+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfeng | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
+—–+————–+——–+——+
9 rows in set (0.00 sec)

        mysql> select * into outfile '/tmp/tutors.txt' from tutors;        Query OK, 9 rows affected (0.02 sec)        mysql> create table tutor like tutors;        Query OK, 0 rows affected (0.30 sec)        mysql> desc tutor;        +--------+----------------------+------+-----+---------+----------------+        | Field  | Type                 | Null | Key | Default | Extra          |        +--------+----------------------+------+-----+---------+----------------+        | TID    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |        | Tname  | varchar(50)          | NO   |     | NULL    |                |        | Gender | enum('F','M')        | YES  |     | M       |                |        | Age    | tinyint(3) unsigned  | YES  |     | NULL    |                |        +--------+----------------------+------+-----+---------+----------------+        4 rows in set (0.07 sec)        mysql> desc tutors;        +--------+----------------------+------+-----+---------+----------------+        | Field  | Type                 | Null | Key | Default | Extra          |        +--------+----------------------+------+-----+---------+----------------+        | TID    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |        | Tname  | varchar(50)          | NO   |     | NULL    |                |        | Gender | enum('F','M')        | YES  |     | M       |                |        | Age    | tinyint(3) unsigned  | YES  |     | NULL    |                |        +--------+----------------------+------+-----+---------+----------------+        4 rows in set (0.00 sec)        mysql> drop table tutors;        Query OK, 0 rows affected (0.86 sec)        mysql> select * from tutor;        Empty set (0.00 sec)        mysql> load data infile '/tmp/tutors.txt' into table tutor;        Query OK, 9 rows affected (0.11 sec)        Records: 9  Deleted: 0  Skipped: 0  Warnings: 0        mysql> select * from tutor;        +-----+--------------+--------+------+        | TID | Tname        | Gender | Age  |        +-----+--------------+--------+------+        |   1 | HongQigong   | M      |   93 |        |   2 | HuangYaoshi  | M      |   63 |        |   3 | Miejueshitai | F      |   72 |        |   4 | OuYangfeng   | M      |   76 |        |   5 | YiDeng       | M      |   90 |        |   6 | YuCanghai    | M      |   56 |        |   7 | Jinlunfawang | M      |   67 |        |   8 | HuYidao      | M      |   42 |        |   9 | NingZhongze  | F      |   49 |        +-----+--------------+--------+------+        9 rows in set (0.00 sec)

几乎热备：LVM
snapshot:

前提：    1、数据文件要在逻辑卷上；    2、此逻辑卷所在卷组必须有足够空间使用快照卷；    3、数据文件和事务日志要在同一个逻辑卷上；步骤：    1、打开会话，施加读锁，锁定所有表；        mysql> FLUSH TABLES WITH READ LOCK;        mysql> FLUSH LOGS;    2、通过另一个终端，保存二进制日志文件及相关位置信息；        mysql> select @@tx_isolation;        mysql> show master status;        mysql> flush logs;        mysql> commit;        $ mysql -uroot -p -e 'SHOW MASTER STATUS\G' > /path/to/master.info        [root@node1 ~]# mysql -e 'show master status\G' > /backup/master-`date +%F`.info     3、创建快照卷        # lvcreate -L # -s -p r -n LV_NAME /path/to/source_lv    4、释放锁        mysql> UNLOCK TABLES;    5、挂载快照卷，备份        [root@node1 ~]# mount /dev/mydata/mydata-snap /media -o ro        [root@node1 data]# cp -a /backup/full-back-2015-06-14/* /mydata/data/    6、删除快照卷；        [root@node1 ~]# umount /media/        [root@node1 ~]# lvremove --force /dev/mydata/mydata-snap    7、增量备份二进制日志；    [root@node1 ~]# mysqlbinlog --start-datetime='2015-06-14 15:49:45' binlog.000009 binlog.000010 > /backup/increment-`data +%F-%H-%M-%S`.sql    mysql> set sql_log_bin=0;    mysql> source /backup/increment-2015-06-14-16-55-22.sql;    mysql> set sql_log_bin=1;

二进制日志相关的几个选项：

innodb_support_xa={TRUE|FLASE}
存储引擎事务在存储引擎内部被赋予了ACID属性，分布式(XA)事务是一种高层次的事务，它利用“准备”然后“提交”(prepare-then-commit)两段式的方式将ACID属性扩展到存储引擎外部，甚至是数据库外部。然而，“准备”阶段会导致额外的磁盘刷写操作。XA需要事务协调员，它会通知所有的参与者准备提交事务(阶段1)。当协调员从所有参与者那里收到“就绪”信息时，它会指示所有参与者进行真正的“提交”操作。
此变量正是用于定义InnoDB是否支持两段式提交的分布式事务，默认为启用。事实上，所有启用了二进制日志的并支持多个线程同时向二进制日志写入数据的MySQL服务器都需要启用分布式事务，否则，多个线程对二进制日志的写入操作可能会以与原始次序不同的方式完成，这将会在基于二进制日志的恢复操作中或者是从服务器上创建出不同原始数据的结果。因此，除了仅有一个线程可以改变数据以外的其它应用场景都不应该禁用此功能。而在仅有一个线程可以修改数据的应用中，禁用此功能是安全的并可以提升InnoDB表的性能。作用范围为全局和会话级别，可用于选项文件，属动态变量。
sync_binlog = 1

mysql> LOCK TABLES mydb.tb1 READ, mydb.tb2 READ, …
mysql> FLUSH TABLES mydb.tb1, mydb.tb2, …

mysq> SET SQL_LOG_BIN=0;
mysql> SOURCE somefile.sql;
mysql> SET SQL_LOG_BIN=1;