一个增量备份关系数据库(MySQL, PostgreSQL, SQL Server, SQLite, Oracle等)到hive的php脚本工具
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
databases
tools
.gitignore
Increment_Backup_To_Hive.php
README.md

README.md

Increment_Backup_To_Hive

一个增量备份关系数据库(MySQL, PostgreSQL, SQL Server, SQLite, Oracle等)到hive的php脚本工具

原理

由于sqoop可定制性太差,本工具针对增量备份场景,备份某张表时只需要用户填写几个关键参数,就能自动化生成hive表,把脚本加入cron就能实现每天增量备份了。增量备份时,脚本主要根据表的自增主键来查询新增数据,然后把新增的数据按照hive表的格式导出成文本,最后调用hive命令直接把文本导入hive内部。支持压缩表+多分区+字段增减。

环境

  1. 脚本内部会调用hive命令,所以必须运行在安装hive的Linux主机上。你需要安装PHP5.4以上的版本,推荐安装PHP7.x。在centos7上安装php70参考:
yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
yum -y install http://rpms.remirepo.net/enterprise/remi-release-7.rpm
yum --enablerepo=remi -y install php70 php70-php-pdo php70-php-mysqlnd php70-php-mbstring php70-php-xml
ln -s /bin/php70 /bin/php
  1. 使用PDO扩展来查询关系数据库,你需要确认你的PHP安装了PDO扩展以及对应数据库适配器。MySQL需要PDO+pdo_mysql+mysqlnd扩展,PostgreSQL需要PDO+pdo_pgsql+pgsql扩展, SQL Server需要PDO+pdo_dblib等。用php -m来查看你是否安装了对应扩展

用法

  • 下载本repo到安装hive和php的linux主机上,进入databases目录,可以看到有一个test_database的样例,里面有一个备份MySQL表的例子test_table1.php(对应mysql数据库文件为databases/test_database/test_table1.sql)。假如你要备份一个名叫my_database数据库中的my_table1表,那么就在databases目录下新建一个名为my_database的目录,复制databases/test_database/config.ini过来到databases/my_database目录下并且修改PDO数据库连接参数。然后复制/databases/test_database/test_table1.phpdatabases/my_database目录下并且改名为my_table1.php,打开文件my_table1.php并且按照你的需要修改参数,这些参数的意义见“参数意义”内容。
  • my_table1.php参数修改好了以后就执行php my_table1.php create,这个操作会根据数据源自动生成创建hive表的sql文件databases/my_database/data/my_table1-schema.sql,你可以根据需要修改之,最后同意完成hive建表。一旦执行php my_table1.php create完成后,那么你就不能再修改my_table1.php的参数了。如果你想从新来过就执行php my_table1.php create,这会删除旧的$HIVE_TABLE表以及databases/my_database/data目录下该$HIVE_TABLE表有关的数据,然后开始全新的建表。
  • 接下来执行php my_table1.php backup就能进行备份了测试了,如果没有错误的话你可以按回车键就能使备份安全停止,程序需要花一段时间才能退出(最好不要使用Ctrl+C的方式来打断备份),为了能每天定时备份你需要复制databases/test_database/cron.shdatabases/my_database目录下,并且修改cron.sh中对应的内容为my_table1.php,这样只需要把cron.sh加入系统cron就能每天增量备份了。比如你希望每天凌晨1点运行cron.sh,那么在crontab中加入0 1 * * * /bin/bash /path/to/cron.sh即可。要检查cron备份是否出错只需要查看databases/my_database/cron_error.log的内容即可,更详细的log在databases/my_database/log目录下。

参数意义

  • $TABLE:要备份的表名

  • $TABLE_AUTO_INCREMENT_ID :表中用来进行增量备份的自增INT列,由于会使用类似SELECT * FROM table WHERE id>=M AND id<M+1000这种遍历方式,所以自增INT列必须加上索引。如果该表没有自增INT列,设置$TABLE_AUTO_INCREMENT_COLUMN = null;即可,此时会使用SELECT * FROM table LIMIT M,1000这种遍历方式,如果记录数太大性能会急剧下降,而且数据只能插入不能删除

  • $TABLE_BATCH:每次从数据源读多少行数据

  • $HIVE_DB:导入hive数据库名,没有则自动创建

  • $HIVE_TABLE:要创建的hive表名

  • $HIVE_FORMAT:创建hive表的格式,如果本身表体积就不大可以直接使用默认的TEXTFILE纯文本格式,此时设置$HIVE_FORMAT = null;对于占用磁盘太大的表使用RCFILE格式压缩,此时设置$HIVE_FORMAT = "RCFILE";即可;使用RCFILE格式时,脚本在创建了名为table的RCFILE格式的hive表之后会再创建一个名为table__tmp的TEXTFILE的临时hive表,从数据源把数据导入了table__tmp表之后再转存到table表,最后清空table__tmp

  • $ROW_CALLBACK_PARTITIONS:hive表的分区策略,有2种情况。 第一:不要分区,此时设置$ROW_CALLBACK_PARTITIONS = null;即可 第二:根据数据源读到的每行字段来确定分区,此时自己设置一个以表的行数据为参数的回调函数的数组即可,数组键为分区名(分区类型只能为STRING),如果某个回调函数返回了false备份就会在此停止,比如:

 (a),假如created_date字段代表插入时间,类型为TIMESTAMP,按照天分区
 $ROW_CALLBACK_PARTITIONS = [
 'partition_day' => function(Array $row)
 {
	 $created_date = empty($row['created_date'])?'0000-00-00 00:00:00':$row['created_date'];
	 $partition = substr($created_date, 0, 10);
	 return $partition;
 }
 ];
 
 (b),多分区情况下,假如created_date字段代表插入时间,类型为INT,按照月分区;假如province字段代表省,按照省分区
 $ROW_CALLBACK_PARTITIONS = [
 
 'partition_month' => function(Array $row)
 {
	 $created_date = empty($row['created_date'])? 0:$row['created_date'];
	 $created_date_str = date('Y-m-d H:i:s', $created_date);
	 $partition = substr($created_date_str, 0, 7);
	 return $partition;
 },
 'partition_province' => function(Array $row)
 {
    $province = empty($row['province'])? "default":$row['province'];
	return $province;
 }
 ];
 
 (c),表中没有分区字段,现在按照备份时间进行按天分区
 $ROW_CALLBACK_PARTITIONS = [
 'partition_day' => function(Array $row)
 {
	 $date = empty($row['date'])?date("Y-m-d H:i:s" , time()):$row['date'];
	 $partition = substr($date, 0, 10);
	 return $partition;
 }
 ];
 
  (d),假如created_date字段代表插入时间,类型为TIMESTAMP,由于表中的数据可能更新,所以延迟7天备份
 $ROW_CALLBACK_PARTITIONS = [
 'partition_day' => function(Array $row)
 {
	 $today = date("Y-m-d" , time());
	 $today_ts = strtotime($today);
	 $seven_day_ago_ts = $today_ts - 7*24*3600;

	 $date = empty($row['created_date'])?'0000-00-00 00:00:00':$row['created_date'];
	 $date_ts = strtotime($date);
	 if($date_ts<$seven_day_ago_ts)
	 {
	 	$partition = substr($date, 0, 10);
	 	return $partition;
	 }else
	 {
	 	//这里返回false以后备份就会在此停止
	 	return false;
	 }
 }
 ];
  • $ROW_CALLBACK_CHANGE:如果从数据源读到的行数据和hive中不一样,比如你对自动生成的hive表增减了一些字段,此时你需要对每一行的数据进行处理满足hive表的格式,返回的数组$row的字段顺序必须和对应的hive表一致,如果不一致程序会检测到错误并退出,比如:
(a),假如数据源表有`id, tel, birthday`这3个字段,你修改了自动生成的hive建表文件,把`tel`字段进行加密,把birthday改成birth_year字段,你的hive字段为`id, tel, birth_year`。
$ROW_CALLBACK_CHANGE=function (Array $row)
 {
    	//$row数组为:['id'=>1, 'tel'=>'15888888888', 'birthday'=>'1990-01-01'];
 	$row['tel'] = my_encrypt_fun($row['tel']);
 	$row['birth_year']= substr($row['birthday'], 0, 4);
 	unset($row['birthday'];
 	//$row数组为:['id'=>1, 'tel'=>'encrypted content', 'birth_year'=>'1990'];和hive表结构一致
 	return $row;
 }
  • $EXPORTED_FILE_BUFFER:文本文件缓存大小(Byte),脚本会把数据缓存到本地文件中,最后再统一导入hive,默认的null为8G
  • $ALARM:当stderr输出错误的时候报警回掉函数,默认null为不需要,使用时类似:
$ALARM = function($str)
{
	$config_path = __DIR__ . "/config.ini";
	$config_arr = parse_ini_file($config_path);
            
	$curl = curl_init();
	curl_setopt($curl, CURLOPT_URL, $config_arr['ALARM_URL']);
	curl_setopt($curl, CURLOPT_HEADER, 1);
	curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
	curl_setopt($curl, CURLOPT_POST, 1);
	$post_data = array(
		"name" => $config_arr['ALARM_NAME'],
		"msg" => $str);
	curl_setopt($curl, CURLOPT_POSTFIELDS, $post_data);
	$res = curl_exec($curl);
	if($res===false)
	{
		$fh = fopen('php://stderr', 'a');
		fwrite($fh, "alarm failed to send message:{$str}" . PHP_EOL);
		fclose($fh);
	}
}
  • $WORK_DIR:设置工作目录,必须为__DIR__

注意

  • 自动生成的hive表的所有字段类型都是STRING,用STRING保存关系数据库的INT,FLOAT,DECIMAL等并没有问题,但是如果是二进制BLOB的话需要用$ROW_CALLBACK_CHANGE参数来base64编码