MySQL的备份和恢复、主从复制架构(第42天)

 MySQL备份和恢复：


逻辑备份工具介绍：mysqldump， mydumper， phpAdmin

Schema（结构：库，表等的结构）和数据是保存在一起的的巨大的SQL语句，单个的巨大的备份文件


mysqldump 客户端命令， 通过mysql协议连接到mysqld服务

命令的语法格式：

mysqldump [OPTIONS] database [tables]：备份单个库，或库指定的一个或多个表

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：备份一个或多个库

mysqldump [OPTIONS] --all-databases [OPTIONS]：备份所有库


mysqldump选项：

-A, --all-databases


MyISAM, InnoDB: 温备

    -x, --lock-all-tables：锁定所有库所有表

    -l, --lock-tables：对于每个单独的数据库，在备份之前锁定其所有表

InnoDB:

    --single-transaction：启动一个大的单一事务实现备份


-B, --databases db_name1 db_name2 ...：备份指定的数据库

-C, --compress：压缩传输；

-E, --events：备份指定库的事件调度器；

-R, --routines：备份存储过程和存储函数；

--triggers：备份表相关的触发器


--master-data[=#]： #表示数字，1或者2       记录备份到的时间点（然后就可以使用mysqlbinlog命令从这个时间点开始继续备份）

    1：记录CHANGE MASTER TO语句；此语句未被注释；

    2：记录为注释语句；


--flush-logs, -F：锁定表之后执行flush logs命令；

注意：二进制日志文件与数据文件不应该放置于同一磁盘；


例子：

mysqldump -uroot -p  --databases  php39db  --master-data=2  > /data/php39db.sql 

查看/data/php39db.sql 文件可以看到记录了CHANGE MASTER TO语句，该语句记录 备份到的位置，然后就可以使用mysqlbinlog命令继续备份从这个位置开始往后的内容（即是增量备份）。



作业：

写脚本, 用mysqldump实现对指定数据库的备份；

写脚本，实现利用binary logs完成增量备份；



问题：还原数据的时候是不是要关闭二进制日志？

必须关闭，还原数据都是写操作，这些写操作会写入二进制日志中

在所以：

mysql> SET SESSION sql_log_bin=0;             关闭二进制日志

mysql> SOURCE /path/from/somefile.sql;    还原数据

mysql> SET SESSION sql_log_bin=1;            打开二进制日志



基于LVM2的物理备份：前提是数据库的数据存放在LVM中

1、请求锁定所有表：

mysql> FLUSH TABLES WITH READ LOCK;

2、记录二进制日志文件及事件位置：

mysql> flush logs;  SHOW MASTER STATUS;

3、创建快照：for  /dev/VG_NAME/LV_NAME

lvcreate -L SIZE -s -p r -n NAME /dev/VG_NAME/LV_NAME

4、释放锁：

mysql> UNLOCK TABLES;  

5、挂载快照卷，从快照卷中复制数据进行备份；

cp, rsync, tar等命令复制数据；

6、备份完成之后，删除快照卷；

7、制定好策略，备份原卷的二进制日志


Xtrabackup：percona开发的备份工具  

https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/

支持完全备份，增量备份，备份单表等等等


使用该工具之前必须在mysql配置文件中的[mysqld]段里设置innodb_file_per_table=ON


innobackupex: 客户端工具, 以mysql协议连入mysqld，不支持离线备份

    --user=

    --password=


例子：

innobackupex --user=USERNAME --password=PASSWORD /path/to/backup/dir 备份数据到指定的目录中

innobackupex --apply-log  /path/to/backup/dir  对数据整理，对提交的事务写入，未提交的事务回滚

增量备份等查看帮助文档  man innobackupex  


完整备份策略：完整备份 + 增量备份 + 二进制日志


MySQL主从复制（Master/Slave）

mysql的扩展:

scale up：向上扩展，垂直扩展，增加硬件性能，增大内存容量，使用固态硬盘等，性价比低

scale out：向外扩展，水平扩展，增加数量


mysql的复制机制：每一个mysql节点都有相同的数据集，并且遵循主从复制结构，只有一个mysql主节点可以进行读写，其他的mysql从节点能从mysql主节点读取数据，一般是从节点的I/O thread是通过向mysql主节点的dump thread请求二进制日志来完成数据的复制，mysql从节点把从主节点获取到的二进制日志事件保存到自己的中继日志中，SQL thread通过中继日志时间完成数据的重放， 这个过程是单行进行的。

该复制的功能有如下：

负载均衡：负载均衡只针对读操作，写操作都是写在mysql主节点中

数据分布

备份

高可用性

MySQL升级测试


mysql复制的特性：

异步：从服务器的数据可能会落后于主服务器

导致主从数据不一致


mysql读写分离器


mysql的复制架构有：

主从复制架构

mysql双主模型架构：两个节点都启动二进制日志和中继日志，互为主从

多主环状复制架构

一主多从

一从多主


二进制日志中事件格式：

STATEMENT

ROW

MIXED


演示的模型：

主从模型，主主模型，半同步复制模型，复制过滤器



mysql主从复制步骤：

主节点的配置如下：

1、启用二进制日志    在配置文件中[mysqld]段里设置log_bin=mysql-bin

2、定义自己的server-id  在配置文件中[mysqld]段里设置server-id=1

3、创建有复制权限的账号，从节点使用该账号登录该主节点

REPLICATION SLAVE, REPLICATION CLIENT

MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repluser@'172.16.%.%' IDENTIFIED BY 'replp@ss';

Query OK, 0 rows affected (0.03 sec)


MariaDB [(none)]> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec) 


从节点的配置如下：  （SHOW SLAVE STATUS\G   查看从节点的状态）

1、启动中继日志   在配置文件中[mysqld]段里设置relay_log=relay-log

2、定义自己的server-id   在配置文件中[mysqld]段里设置server-id=7

3、设置自己为从节点并使用有复制权限的账号（上面主节点创建的账号）连接至主节点

change master to MASTER_HOST='master节点主机', MASTER_USER='repluser', MASTER_PASSWORD='replp@ss', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=245;  

4、启动io thread以及sql thread

START SLAVE; 



下面谈谈复制中应该注意的问题：

1、如何限制从节点只读？

设置从节点的全局服务器变量read_only=ON   注意：此限制对于拥有SUPER权限用户无效；

或者在配置文件中[mysqld]段里设置read_only=ON

或者是 flush tables with read lock;  这个可以阻止所有用户执行写操作

2、如何保证主从复制时的事务安全？

前提：mysql对二进制日志事件数据会有缓冲; 

在主节点上设置如下参数：

sync_binlog=ON  

innodb_flush_logs_at_trx_commit=ON

innodb_support_xa=ON     开启innodb分布式事务

sync_master_info

在从节点设置中设置如下参数

skip_slave_start=ON     手动start slave 

sync_relay_log

sync_relay_log_info



MySQL主主复制模式：

两台节点都开启二进制日志和中继日志，都把自己设置为从服务器

容易导致数据不一致，谨慎使用

具体配置步骤如下：

各节点使用唯一的server_id

各节点都启动log_bin和relay_log

各节点创建拥有复制权限的用户帐号

各节点均把自己指定为从节点，并启动io_thread和sql_thread


MySQL半同步复制模式：

半同步：master要等待一个从节点把数据完整复制过去；

由google贡献的补丁；以插件的方式存在


master节点操作如下：

MariaDB [testdb]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';  安装该插件

MariaDB [testdb]> SHOW GLOBAL VARIABLES LIKE '%semi%';   查看该插件的变量

+------------------------------------+-------+

| Variable_name                      | Value |

+------------------------------------+-------+

| rpl_semi_sync_master_enabled       | OFF   |

| rpl_semi_sync_master_timeout       | 10000 |

| rpl_semi_sync_master_trace_level   | 32    |

| rpl_semi_sync_master_wait_no_slave | ON    |

+------------------------------------+-------+

MariaDB [testdb]> SET GLOBAL rpl_semi_sync_master_enabled=1;  启动该插件

Query OK, 0 rows affected (0.01 sec)


MariaDB [testdb]> SET GLOBAL rpl_semi_sync_master_timeout=1000;

Query OK, 0 rows affected (0.00 sec)        设置等待从节点的超时时间


MariaDB [testdb]>  SHOW GLOBAL STATUS LIKE '%semi%';  查看关于semi的状态


slave节点操作如下：（如果有多个从节点，只需要配置其中一个为半同步节点）

MariaDB [mysql]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Query OK, 0 rows affected (0.06 sec)    安装插件


MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled     | OFF   |

| rpl_semi_sync_slave_trace_level | 32    |

+---------------------------------+-------+

2 rows in set (0.07 sec)


MariaDB [mysql]> SET GLOBAL rpl_semi_sync_slave_enabled = 1;   启动该插件

Query OK, 0 rows affected (0.00 sec)


MariaDB [mysql]> START SLAVE;  

主节点在联系从节点超时后，会自动降纸为异步模式；



复制过滤器

让slave仅复制有限的几个数据库，甚至于仅复制某数据库内有限的几张表的机制；

有两种方案：

1、在主节点上过滤  （不建议使用）

在向二进制日志记录事件时，仅记录指定数据库的相关操作；

binlog_do_db =     # 数据库白名单

binlog_ignore_db =   # 数据库黑名单


2、在从节点上过滤

仅从中继日志中读取指定的数据库或表的相关事件并应用于本地；

replicate_do_db = 

replicate_ignore_db = 

replicate_db_table = DB_NAME.TB_NAME

replicate_ignore_table = 

replicate_wild_do_table = 

replicate_wild_ignore_table = 


跟复制功能相关的文件：

master.info：保存slave连接master时所需要信息；纯文本文件；

relay-log.info：保存了当前slave节点上（主节点上的）二进制日志和当前节点中继日志的对应关系；


 清理二进制日志：PURGE命令


对复制操作的监控和维护：

SHOW MASTER STATUS;

SHOW BINLOG EVENTS;

SHOW BINARY LOGS;

SHOW SLAVE STATUS;

SHOW PROCESSLIST; 


查看比主节点落后多长时间：

show slave status\G   里查找Second_Behind_Master  


确定主从节点数据是否一致：

表自身的checksum

使用percona-tools中的pt-table-checksum

















评论

发表评论

此博客中的热门博文

OAuth 2教程

图片
原文： http://tutorials.jenkov.com/oauth2/index.html OAuth 2.0是一种开放式 授权 协议，该协议使得应用程序可以访问彼此的数据。例如：一个游戏应用可以访问Facebook中的用户数据，或者基于位置的应用程序可以访问Foursquare应用程序的用户数据（ user data ）等。 OAuth 2.0被用于通过应用程序共享数据的示例如上图。 首先用户进入游戏的web应用，该应用要求用户通过Facebook账户登录，并定向到Facebook的登录界面，用户登录Facebook后，会重定向到之前的游戏应用。此时该应用就访问到了用户在Facebook的用户数据以及授权信息，并代表用户在Facebook中调用功能（例如发布状态更新）。 OAuth 2.0 用例 OAuth 2.0既可以用于在某个应用内访问其它应用的用户信息（比如上例中的游戏应用），又可以提供用户授权服务供其它应用调用（比如上例中的Facebook）。 OAuth 2.0是OAuth 1.0的替代，因为OAuth 1.0太复杂了，比如OAuth 1.0要求使用证书等。OAuth 2.0更加简单，不要求使用证书，仅使用SSL/TLS。 OAuth 2.0 规范 这个教程的目的是提供一个OAuth 2.0协议的概览以帮助理解，而不是涵盖此协议的所有细节。 如果你计划实现一个OAuth 2.0协议，最好是去阅读规范的详细内容，规范的地址：http://tools.ietf.org/html/draft-ietf-oauth-v2-23 OAuth 2.0 概述 在之前的介绍中我们提到，OAuth 2.0是一个开放标准，其允许某个应用程序访问其它应用程序的用户授权的数据。现在我们来介绍这个协议是如何工作的，以及规范中提到的各种概念。 OAuth 2.0 提供了不同的方式使得客户端应用获得权限去访问存储在资源服务器中的资源。现在介绍其中最安全和最常用的使用方式： 一个web应用请求访问另一个web应用 的资源。 下面的示例图描述了整个认证过程： 首先，用户访问客户端Web应用程序，在这个应用程序里有“通过Facebook登录”（或谷歌或Twitter等其他系统）的按钮。 第二步，当用户点击这个按...
阅读全文

网格策略

图片
波段策略网格之一：写在前面，体系以及策略 壹：写在前面 踏踏实实的踏踏实实 在这个有些寒意又充满希望的春天，我决定将我使用了7年的一个波段策略——网格策略，与大家分享与交流。这次分享，会把目前已经升级到3.0的网格策略事无巨细说的非常清楚。这应该是你能见到最详细最有价值的网格策略文章，因为这几篇文章凝结了整整7年的思考和实践。 在此之前，我不是不想与大家分享。有三个原因导致我迟迟没有做这件事。 第一个原因很简单，对于这样一个凝聚心血的东西，我是有些私心的。这个各位也应该能够理解。 第二个原因，在于这件事是一件需要耗费极大精力，又有很大风险的事情。对于做事的信条，我的理念是要么不做，要做就做到极致。所以我如果讨论这个策略，就一定要把方方面面的东西全部说到。这个工作量非常大。 同时，风险很大。风险点在于无论我如何强调需要注意的问题，看这篇文章的几万十几万读者，也一定有人学习能力强，有人接受能力差一些。只要有一个关键点你没有融会贯通，就可能出现不好的结果。 第三个原因，我自己也需要对这个策略进行不断的实践，不断将细节升级。我不可能将一个自己没有做过3轮以上的策略与大家分享，这是对你负责。我必须深刻理解它的优点缺点，才能跟你交流。我会用我自己的钱去做实证，而不是用你的。 基于以上原因，我一直没有公开讨论过这个策略。但现在，我认为时机差不多成熟了。再加上一些外部因素刺激，我决定与你分享。 这一个系列的文章会分为3-4篇。第一篇很重要，会讨论投资体系的构建，各种投资策略互相搭配的重要性以及应该注意的问题。这一篇虽然还未涉及网格策略，但真的很重要，希望你能认真看一看。 第二篇会介绍网格策略的基本操作方式。即1.0版本。 第三篇会介绍网格策略2.0。这次升级意义非常重大，将网格1.0的一些固有缺陷在一定程度上弥补掉。到时候看情况，也有可能将3.0也放在第三篇里，有可能将3.0单独开一篇。 这就是全部这次的交流计划。也许这个策略不适合你，也许你不喜欢这种波段策略，但无论如何，希望你能感受到我的责任感与诚意。 贰：投资体系 踏踏实实的踏踏实实 每个人做投资，都应该有一套完整的投资体系。整套体系包括大致两个方面： 第一个方面，是观察金融世界的体系。 ...
阅读全文

apt-get详细使用

NAME apt-get - APT包处理程序--基于命令行 SYNOPSIS apt-get [-asqdyfmubV] [-o=config_string] [-c=config_file] [-t=target_release] [-a=architecture] {update | upgrade | dselect-upgrade | dist-upgrade | install pkg [{=pkg_version_number | /target_release}]...  | remove pkg...  | purge pkg...  | source pkg [{=pkg_version_number | /target_release}]...  | build-dep pkg [{=pkg_version_number | /target_release}]...  | download pkg [{=pkg_version_number | /target_release}]...  | check | clean | autoclean | autoremove | {-v | --version} | {-h | --help}} DESCRIPTION apt-get是一种基于命令行的包处理工具，被认为是相对于其它apt库的“后端”（指在命令行下）工具，同样也有一些“前端”（基于图形模式）下工具，如aptitude(8),synaptic(8)和wajig(1). 除了-h,或是--help这两个参数，下面的子命令之一必须使用。 update update是被用来与它们的源中的包索引文件进行同步的。 可用的软件包的索引是从/etc/apt/sources.list文件中指定的路径获取的。举个例子，当我们使用Debian时，这个命令就取出并扫描Packages.gz的文件，因为这样就能得到升级了的包和新的包的信息了。更新(update)应该在使用upgrade或是dist-upgrade这2个子命令之前使用。请注意：因爲无法预知包文件的大小，所以整體更新進度表將不正確。 （译者注：所以請注意，update並不真正升級安裝在系統上的軟件包，只是查...
阅读全文