二进制日志：“修改”
也叫复制日志
position：位置
time：时间
滚动：
1、大小
2、时间
二进制日志的功用：
即时点恢复：
复制：
mysql>SHOW MASTER STAUS;
mysql>FLUSH LOGS;
mysql>SHOW BINARY LOGS;
mysql>SHOW BINLOG EVENTS IN 'log_file';
#mysqlbinlog
--start-time
--stop-time
--start-position
--stop-position
server-id ：服务器身份标识
MYSQL记录二进制日志的格式：
基于语句：statement
基于行： row
UPDATE tb1 SET salary=salary+1000
混合模式：mixed
二进制日志文件内容格式：
时间发生的日期和时间
服务器ID
时间的结束位置
事件的类型
原服务器生成此时间的线程ID
语句的时间戳和写入二进制日志文件的时间差
错误代码
事件内容
事件位置，相当于下一事件的开始位置
服务器参数：
log-bin = {ON|OFF},还可以是个文件路径
log-bin-trust-function-creators
sql_log_bin = {ON|OFF}
sync_binlog
binlog_format = {statement|row|mixed}
max_binlog_cache_size =
二进制日志缓冲空间大小，仅用于缓冲事务类的语句：
max_binlog_stmt_cache_size
max_binlog_size
二进制日志文件上限
建议：切勿将二进制日志与数据文件放在同一设备上：
中继日志：
relay_log_purge={ON|OFF}
是否自动清理不再需要的中继日志
备份和恢复：
1、灾难恢复：
2、审计：
3、测试：
备份：目的用于恢复：对于备份数据做恢复测试：
备份类型：
根据备份时，数据库服务器是否在线：
冷备：cold backup
温备：warm backup
热备：hot backup
根据备份的数据集：
完全备份：full
部分备份：partial backup
根据备份时的接口（直接备份数据文件还是通过mysql服务器导出数据）：
物理备份：直接复制（归档）数据文件的备份方式：physical backup
逻辑备份：把数据从库中提出来保存为文本文件：logical backup
mysqldump
根据备份时是备份整个数据还是仅备份变化的数据：
完全备份：full backup
增量备份：incremental backup
差异备份：different backup
备份策略：
选择备份方式
选择备份时间
考虑到恢复成本
恢复时长
备份成本：
锁时间
备份时长
备份负载
备份对象：
数据
配置文件：
代码：存储过程，存储函数，触发器
OS相关的配置文件，如crontab配置计划及相关的脚本
跟复制相关的配置：
二进制日志文件
备份工具：
mysqldump：逻辑备份工具
InnoDB热备、MyISAM温备、Aria温备
备份和恢复过程比较慢
mysqldumper：多线程备份工具
很难实现差异或增量备份：
lvm-snapshot：
接近于热备的工具：因为要先请求全局锁，而后创建快照，并在创建快照完成后释放全局锁：
使用cp、tar等工具进行物理备份：
备份和恢复速度较快：
很难实现增量备份，并且请求全局需要等待一段时间，在繁忙的服务器上油漆如此：
SELECT clause INTO OUTFILE '/path/to/somefile'
LOAD DATA INFILE '/path/from/somefile'
部分备份工具，不会备份关系定义，仅备份表中的数据：
逻辑备份工具，快于mysqldump
Innobase：商业备份工具，innobackup
Xtrabackup：由Percona提供的开源备份工具
InnoDB热备，增量备份：
MyISAM温备，不支持增量：
物理备份，速度快：
mysqlhotcopy：几乎冷备
mysqldump：
mysqldump [option] [tb1_name...]
备份单个库：mysqldump [option] db_name
恢复时：如果目标不存在，需要事先手动创建
--all-databases: 备份所有库
--databases db1 db2 ... 备份前加锁
--lock-all-tables：请求锁定所有表之后再备份，对MyISAM、InnoDB、Aria做温备
--single-transaction：能够对InnoDB存储引擎实现热备：
备份代码：
--events：备份事件调度器代码
--routines：备份存储过程和存储函数
--triggers：备份触发器
备份时滚动日志：
--flush-logs：备份前、请求到锁之后滚动日志
复制时的同步位置标记：
--master-data=【0|1|2】
0:不记录
1：记录为CHANGE MASTER语句
2：记录为注释的CHANGE MASTER语句
使用mysqldump备份：
请求锁： --lock-all-tables或使用--single-trasaction进行innodb热备：
滚动日志： --flush-logs
选定要备份的库： --databases
记录二进制日志文件及位置
恢复：
建议：关闭二进制日志，关闭其它用户连接：
备份策略：基于mysqldump
备份： mysqldump+二进制日志文件：
周日做一次完全备份：备份的同事滚动日志
周一至周六：备份二进制日志
恢复：
完全备份+二进制日志文件中至此刻的事件
对于MySQL配置文件，以及与MySQL相关的OS配置文件在每次修改后都应该直接进行备份：
lvm-snapshot：基于LVM快照的备份
1、事务日志跟数据文件必须在同一个卷上：
2、创建快照卷之前，要请求MySQL的全局锁，在快照创建完成之后释放锁：
3、请求全局锁之后，做一次日志滚动：做二进制日志文件及位置标记（手动进行）：
步骤：
1、请求全局锁，并滚动日志
mysql>FLUSH TABLES WITH READ LOCK;
mysql> FLUSH LOGS;
2、做二进制日志文件及位置标记（手动进行）；
#mysql -e 'show master status' > /path/to/somefile
3、创建快照卷
lvcreate -L -s -n -p r /path/to/some_lv
4、释放全局锁
msyql > UNLOCK TABLES;
5、卸载快照卷并备份
# cp
6、备份完成之后，删除快照卷
恢复：
1、二进制日志保存好：
提取备份之后所有事件至某sql脚本中；
2、还原数据，修改权限及属主属组等，并启动mysql
3、做即时还原
1、 查看所有的数据库
MariaDB [hellodb]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
2、备份单个数据库，这里是备份hellodb，把hellodb备份到/tmp下命名为hellodb.sql
[root@localhost ~]# mysqldump hellodb > /tmp/hellodb.sql
3、查看刚才备份的数据库hellodb.sql
[root@localhost tmp]# cat hellodb.sql
-- MySQL dump 10.14 Distrib 5.5.41-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: hellodb
-- ------------------------------------------------------
-- Server version 5.5.41-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `classes`
--
DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
`CLASSID` tinyint(4) NOT NULL,
`Class` varchar(30) NOT NULL,
`NumOfStu` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `classes`
--
LOCK TABLES `classes` WRITE;
/*!40000 ALTER TABLE `classes` DISABLE KEYS */;
INSERT INTO `classes` VALUES (1,'Shaolin Pai',10),(2,'Emei Pai',7),(3,'QingCheng Pai',11),(4,'Wudang Pai',12),(5,'Riyue Shenjiao',31),(6,'Lianshan Pai',27),(7,'Ming Jiao',27),(8,'Xiaoyao Pai',15),(9,'Liangshan',22);
/*!40000 ALTER TABLE `classes` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2017-03-26 15:56:56
4、备份指定的多个库，这里备份hellodb test 两个库，需要加--databases
[root@localhost tmp]# mysqldump --databases hellodb test > /tmp/test.sql
5、查看备份的库test.sql的库
[root@localhost tmp]# cat test.sql
-- MySQL dump 10.14 Distrib 5.5.41-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: hellodb
-- ------------------------------------------------------
-- Server version 5.5.41-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `hellodb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `hellodb`;
--
-- Table structure for table `classes`
--
DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
`CLASSID` tinyint(4) NOT NULL,
`Class` varchar(30) NOT NULL,
`NumOfStu` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `classes`
--
LOCK TABLES `classes` WRITE;
/*!40000 ALTER TABLE `classes` DISABLE KEYS */;
INSERT INTO `classes` VALUES (1,'Shaolin Pai',10),(2,'Emei Pai',7),(3,'QingCheng Pai',11),(4,'Wudang Pai',12),(5,'Riyue Shenjiao',31),(6,'Lianshan Pai',27),(7,'Ming Jiao',27),(8,'Xiaoyao Pai',15),(9,'Liangshan',22);
/*!40000 ALTER TABLE `classes` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Current Database: `test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2017-03-26 16:01:30
6、事实上，真正的现网操作需要先锁定所有的表，再进行备份。需要使用mysqldump 后面跟上--lock-all-tables参数进行备份。
[root@localhost tmp]# mysqldump --databases hellodb --lock-all-tables --flush-logs > /tmp/hellodb.sql
7、在mysql库中演示一个完整的备份，手动备份
（1）、对所有表加锁，手动请求全局锁
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
（2）、刷新日志
MariaDB [(none)]> FLUSH LOGS;
Query OK, 0 rows affected (0.02 sec)
（3）、查看二进制文件位置
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 245 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.07 sec)
（4）重新备份hellodb数据库，把数据库备份到/tmp下并命名为hellodb1.sql
[root@localhost tmp]# mysqldump --databases hellodb > /tmp/hellodb1.sql
（5）备份完释放表锁定
MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)
8、对Innodb的库进行热备
[root@localhost tmp]# mysqldump --databases hellodb --single-transaction --flush-logs > /tmp/hello2.sql
阅读(2135) | 评论(0) | 转发(0) |
