linux中怎么增量增量傳輸mysqlbinlog日志

這篇文章將為大家詳細(xì)講解有關(guān)linux中怎么增量增量傳輸MySQL binlog日志，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

創(chuàng)新互聯(lián)公司是一家專業(yè)提供沭陽(yáng)企業(yè)網(wǎng)站建設(shè),專注與做網(wǎng)站、網(wǎng)站建設(shè)、H5場(chǎng)景定制、小程序制作等業(yè)務(wù)。10年已為沭陽(yáng)眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站建設(shè)公司優(yōu)惠進(jìn)行中。

在實(shí)際的工作中我們遇到了一個(gè)問(wèn)題，MYSQL 5.5 我們備份使用的mysqldump，然后每天傳輸?shù)膫浞莸絺浞輽C(jī)器，但是binlog卻不好傳輸主要要考慮幾個(gè)問(wèn)題：
1、binlog 沒(méi)法取時(shí)間，按照什么規(guī)則來(lái)傳？
2、binlog的傳輸并不是每次都全部傳輸過(guò)去，而是需要增量的。
3、binlog的最后一個(gè)日志是在使用的是不能傳輸?shù)摹?br/>4、為了更通用需要使用一個(gè)前綴，因?yàn)槊總€(gè)數(shù)據(jù)庫(kù)的binlog前綴是可以自己指定的
如 mysqlbin.000001

那么有了上面的問(wèn)題，如何解決？也許shell腳本能夠完成，但是我選擇使用C/C++ 和LINUX系統(tǒng)函數(shù) opendir readdir lstat來(lái)完成
這是我最后掛載的腳本
#!/bin/sh
TIME_TAG=`date +%Y%m%d`
/root/ftptool/tool /dbdata/data/ /root/ftptool/ mysql-bin > tool_${TIME_TAG}.log
if [ $? != 0 ]; then
echo "tool failed"
exit
else
echo "tool sucess!"
fi
cat /root/ftptool/ftphead > /root/ftptool/ftp_${TIME_TAG}.sh
cat /root/ftptool/${TIME_TAG} >> /root/ftptool/ftp_${TIME_TAG}.sh
cat /root/ftptool/ftptail >> /root/ftptool/ftp_${TIME_TAG}.sh

chmod 755 /root/ftptool/ftp_${TIME_TAG}.sh
sh /root/ftptool/ftp_${TIME_TAG}.sh
實(shí)際上ftp_${TIME_TAG}.sh來(lái)自3個(gè)部分
1、ftphead --這個(gè)很簡(jiǎn)單自己寫明白ip 用戶名密碼就好了，如下：
#!/bin/sh

ftp -i -v -n<< EOF
open 192.168.*.*
user mysql ****
cd /backup/mysql
bin
2、 ftptail 這個(gè)更簡(jiǎn)單
quit
EOF
就這點(diǎn)
3、cat ${TIME_TAG}
這部分是我自己寫的工具tool 生成的
工具使用如下：
Usage:./tool binlogdir ftpdir binlogprefix
需要一個(gè)binlog目錄一個(gè)ftp目錄和binlong的前綴
binlog目錄不用多說(shuō)是binlog的所在目錄，需要以/結(jié)尾比如 /mysql/data/不能是/mysql/data 否則拋錯(cuò)error 20 binlogdir Must End With /

ftp目錄下存放兩個(gè)東西一個(gè)tmp文件和生成的ftp腳本，ftp腳本生成和當(dāng)前時(shí)間一致比如20160713
tmp文件用于存放當(dāng)前取到的最大的binlog序號(hào),如果不存在可以新建一個(gè)，如果不新建拋錯(cuò)error 15 tmp file not found
如果想再次全部傳輸刪除tmp文件新建一個(gè)空的就可以比如touch tmp
這個(gè)ftp目錄同樣必須使用/結(jié)尾否則拋錯(cuò)error 21 ftpdir Must End With /

binlogprefix 就是你的前綴比如mysql-bin

使用就是這么簡(jiǎn)單，有了這3部分就能夠通過(guò)shell
cat ftphead > ftp_${TIME_TAG}.sh
cat ${TIME_TAG} >> ftp_${TIME_TAG}.sh
cat ftptail >> ftp_${TIME_TAG}.sh
生成一個(gè)全部的腳本。然后運(yùn)行了。
另外tool工具會(huì)輸出一些信息，當(dāng)然可以關(guān)閉，這些輸出信息我重定向到了 tool_${TIME_TAG}.log
可以查看下面就是一份：
WARNING:Ftpdir Less Than 1000 Char!
PROCESS:Create Chain Now:
INFO:Current Binog Dir Is :/dbdata/
PROCESS:Checkbin Binlogprefix: mysql-bin
................. 這里省略很多
PROCESS:Print chain info
................. 這里省略很多
PROCESS:Now Acc Ftpdir:
INFO:Ftp_scirpt fun time:
Wed Jul 13 14:44:13 2016
20160713
INFO:Ftp_scirpt Fun Tmpfile :/root/ftptool/tmp
INFO:Tmpfile Write Mode Succ!
INFO:Ftp_Scirpt Fun Tmp_max_st:0 Max_st_1:2028:
INFO:Scan Put Counter :163
INFO:Current Max Binlog-1 is:2028
PROCESS:Free Node Is:164

可以清楚看到將會(huì)生成163個(gè)put腳本信息，最高的傳輸是2028，實(shí)際最大是2029，我在編碼的時(shí)候做了-1操作,目的在于不要傳輸當(dāng)前的binlog，
因?yàn)楫?dāng)前的binlog正在使用
最后釋放了鏈表節(jié)點(diǎn)164個(gè).同事你的tmp文件目錄也會(huì)輸出,binglog 的目錄也會(huì)輸出.

下面是全部分出錯(cuò)碼：
error 10 open bindbr error
error 11 argv parameter error
eroor 12 not find binlog
error 14 fptdir open error
error 15 tmp file not found
error 16 lstat tmp file error
error 18 tmp file write error
error 20 binlogdir Must End With /
error 21 ftpdir Must End With /
當(dāng)然成功放回 0

編譯好的我放到了百度云盤
http://pan.baidu.com/s/1i4WuBB3
下面是全部的源代碼
編譯使用 g++ main.cpp mybin.cpp -o tool 即可注意本工具使用了LINUX系統(tǒng)函數(shù)所以必須是LINUX系統(tǒng)或者其他滿足posix標(biāo)準(zhǔn)的系統(tǒng)不包含windows
在LINUX下編譯沒(méi)有問(wèn)題。有什么問(wèn)題可以聯(lián)系QQ 22389860.

點(diǎn)擊(此處)折疊或打開(kāi)

/*************************************************************************
> File Name: mbin.h
> Author: gaopeng
> Mail: gaopp_200217@163.com
> Created Time: Tue 02 Aug 2016 05:57:27 PM CST
************************************************************************/
#include<iostream>
#define _DEG
using namespace std;
typedef unsigned char uchar;
typedef unsigned long ulong;
typedef unsigned int uint;
typedef unsigned short ushort;
typedef struct data
{
char* file_name;
char* path_name;
char* full_name;
ulong file_st;
uint nuse;
} DATA;
typedef struct f_node
{
struct f_node *next;
DATA* data;
} F_NODE;
typedef struct quence
{
F_NODE* first;
F_NODE* last;
int items;
} QUE;
uint checkbin(const char* file_name,const char* binprefix);
F_NODE* create_que(const char *bindir,const char* binprefix);
QUE* ini_que(void);
void show( F_NODE* const frs_nd);
void ins_chan(const char* f_name,const char* p_name,const char* ful_name,QUE * que);
ulong char_to_num(const char* file_name);
void time_to_time(char* ltime,char* mytime);
void ftp_script(const char* ftp_dir,F_NODE* frs_nd);
int get_max_st(F_NODE* const frs_nd);
ulong readtmp(FILE *fd,const char* tmpdir);
int myfree(F_NODE* const frs_nd);
uint checkdir(const char* mychar);

點(diǎn)擊(此處)折疊或打開(kāi)

/*************************************************************************
> File Name: main.cpp
> Author: gaopeng
> Mail: gaopp_200217@163.com
> Created Time: Tue 02 Aug 2016 05:58:15 PM CST
************************************************************************/
#include<iostream>
#include<iostream>
#include<stdio.h>
#include <sys/types.h>
#include <dirent.h>
#include <string.h>
#include"mbin.h"
#include <stdlib.h>
using namespace std;
int main(int argc,char *argv[])
{
F_NODE* frs_nd=0;
int freeno =0;
printf("%s\n","Usage:./tool binlogdir ftpdir binlogprefix");
if(argc<4)
{
cout<<"ERROR(9):Parameter Error Like:"<<endl;
printf("%s\n","Usage:./tool binlogdir ftpdir binlogprefix");
exit(9);
}
if(checkdir(argv[1]) == 1)
{
cout<<"ERROR(20):binlogdir Must End With /"<<endl;
exit(20);
}
if(checkdir(argv[2]) == 1)
{
cout<<"ERROR(21):ftpdir Must End With /"<<endl;
exit(21);
}
printf("WARNING:Ftpdir Less Than 1000 Char!\n");
printf("PROCESS:Create Chain Now:\n");
frs_nd = create_que(argv[1],argv[3]);
cout<<"PROCESS:Print chain info"<<endl;
show(frs_nd);
cout<<"PROCESS:Now Acc Ftpdir:"<<endl;
ftp_script(argv[2], frs_nd);
freeno = myfree(frs_nd);
cout<<"PROCESS:Free Node Is:"<<freeno<<endl;
return 0;
}

點(diǎn)擊(此處)折疊或打開(kāi)

/*************************************************************************
> File Name: mybin.cpp
> Author: gaopeng
> Mail: gaopp_200217@163.com
> Created Time: Tue 02 Aug 2016 05:57:34 PM CST
************************************************************************/
#include<iostream>
#include "mbin.h"
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>
#include <time.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <dirent.h>
#include <math.h>
using namespace std;
QUE* ini_que(void)
{
QUE* que = new QUE;
que->first =0;
que->last =0;
que->items =0;
return que;
}
void ins_chan(const char* f_name,const char* p_name,const char* ful_name,QUE * que)
{
F_NODE *add = new F_NODE; //inital node pointer
DATA *name = new DATA; //inital data pointer
name->file_name = new char[strlen(f_name)+5];
name->path_name = new char[strlen(p_name)+5];
name->full_name = new char[strlen(ful_name)+5];
//init mem
memset(name->file_name,0,strlen(f_name)+5);
memset(name->path_name,0,strlen(p_name)+5);
memset(name->full_name,0,strlen(ful_name)+5);
name->file_st = char_to_num(f_name);
name->nuse = 0;
strcpy(name->file_name,f_name);
strcpy(name->path_name,p_name);
strcpy(name->full_name,ful_name);
add->next =0;
add->data = name;
if( que->items==0)
{
que->first =add;
que->last =add;
que->items++;
}
else
{
que->last->next = add;
que->last = add;
que->items++;
}
}
F_NODE* create_que(const char *bindir,const char* binprefix)
{
struct dirent *DirEntry=0;
DIR* Dir=0;
QUE* myq=0;
uint nob=0; //check if binlog find
myq = ini_que();
F_NODE* frs_nd =0;
if( !(Dir = opendir(bindir)))
{
perror("ERROR(10):Open Binlog Dir Error:");
exit(10);
}
else
{
printf("INFO:Current Binog Dir Is :%s\n",bindir);
while( DirEntry = readdir(Dir))
{
if(DirEntry->d_type == DT_REG && checkbin(DirEntry->d_name,binprefix) == 1)
{
nob++;
#ifdef _DEG
printf("%s%s\n",bindir,DirEntry->d_name);
#endif
char *tloop = new char[strlen(bindir) + strlen(DirEntry->d_name)+5];
strcat(strcat(tloop,bindir),DirEntry->d_name); //bindir like /test/test/test/ last / is must
ins_chan(DirEntry->d_name,bindir,tloop,myq);
delete [] tloop;
}
}
closedir(Dir);
if(nob == 0)
{
printf("WARNING:No Bin Log Find!!\n");
delete myq;
exit(12);
}
}
frs_nd = myq->first;
delete myq;
return frs_nd;
}
uint checkbin(const char* file_name,const char* binprefix)
{
uint lenprefix = strlen(binprefix); //exp 5
uint filelen = strlen(file_name);
uint i;
uint t;
char tmpfix[strlen(binprefix)+1]; //exp strlen(binprefix)+1 =6
memset(tmpfix,0,strlen(binprefix)+1);
for(i=0;i<=lenprefix-1;i++) //exp lenprefix-1=4
{
tmpfix[i]=file_name[i]; //5 char copy tmpfix[0-4]
}
tmpfix[lenprefix]='\0'; //tmpfix[5] = '\0'
#ifdef _DEG
cout<<"PROCESS:Checkbin Binlogprefix: "<<tmpfix<<endl;
#endif
if(!strcmp(tmpfix,binprefix)) //check prefix eq tmpfix
{
for(t=(filelen-1);t>=(filelen-6);t--) //exp filelen-1=10 filelen-6=5
{
if(file_name[t]<48 || file_name[t]>57 ) //check 000000
{
return 0;
}
}
if(file_name[filelen-7] !='.') //check . of .000000
{
return 0;
}
}
else
{
return 0;
}
return 1;
}
void show( F_NODE* const frs_nd)
{
F_NODE* scan_nd = frs_nd;
do {
if(!scan_nd->next)
{
break;
}
printf("INFO:%s,%s,%s,%lu,%u\n",scan_nd->data->file_name,scan_nd->data->path_name,scan_nd->data->full_name,scan_nd->data->file_st,scan_nd->data->nuse);
scan_nd = scan_nd->next;
} while(scan_nd->next);
printf("INFO:%s,%s,%s,%lu,%u\n",scan_nd->data->file_name,scan_nd->data->path_name,scan_nd->data->full_name,scan_nd->data->file_st,scan_nd->data->nuse);
}
ulong char_to_num(const char* file_name)
{
uint len = strlen(file_name);
uint i;
ulong num=0;
uint chk=0;
for(i=(len-1);i>0;i--)
{
if(file_name[i]>=48 && file_name[i]<=57)
{
num = (ulong)(file_name[i] - 48)*(ulong)pow(10,chk)+num;
chk++;
}
}
return num;
}
void time_to_time(char* ltime,char* mytime)
{
char buffer[10];
buffer[0] = ltime[4];
buffer[1] = ltime[5];
buffer[2] = ltime[6];
buffer[3] = '\0';
mytime[0]=ltime[20];
mytime[1]=ltime[21];
mytime[2]=ltime[22];
mytime[3]=ltime[23];
if(!strcmp(buffer,"Jan"))
{
mytime[4]='0';
mytime[5]='1';
}
if(!strcmp(buffer,"Feb"))
{
mytime[4]='0';
mytime[5]='2';
}
if(!strcmp(buffer,"Mar"))
{
mytime[4]='0';
mytime[5]='3';
}
if(!strcmp(buffer,"Apr"))
{
mytime[4]='0';
mytime[5]='4';
}
if(!strcmp(buffer,"May"))
{
mytime[4]='0';
mytime[5]='5';
}
if(!strcmp(buffer,"Jun"))
{
mytime[4]='0';
mytime[5]='6';
}
if(!strcmp(buffer,"Jul"))
{
mytime[4]='0';
mytime[5]='7';
}
if(!strcmp(buffer,"Aug"))
{
mytime[4]='0';
mytime[5]='8';
}
if(!strcmp(buffer,"Sep"))
{
mytime[4]='0';
mytime[5]='9';
}
if(!strcmp(buffer,"Oct"))
{
mytime[4]='1';
mytime[5]='0';
}
if(!strcmp(buffer,"Nov"))
{
mytime[4]='1';
mytime[5]='1';
}
if(!strcmp(buffer,"Dec"))
{
mytime[4]='1';
mytime[5]='2';
}
if(ltime[8]<48 ||ltime[8]>57)
{
mytime[6]=48;
}
else
{
mytime[6]=ltime[8];
}
mytime[7]=ltime[9];
mytime[8]='\0';
}
void ftp_script(const char* ftp_dir,F_NODE* frs_nd)
{
time_t now;
F_NODE* scan_nd = frs_nd;
struct tm* nowtm;
FILE* ftp=0;
FILE* tmp=0;
char timebuf[40];
char mytime[25];
char myftpdir[1000];
char tmpdir[1000];
ulong tmp_max_st;
ulong max_st_1;
int i=0;
int freeno=0;
memset(timebuf,0,40);
memset(mytime,0,40);
time(&now);
nowtm = localtime(&now);
asctime_r(nowtm,timebuf);
time_to_time(timebuf,mytime);
#ifdef _DEG
printf("INFO:Ftp_scirpt fun time:\n%s%s\n",timebuf,mytime);
#endif
memset(myftpdir,0,1000);
if((ftp=fopen(strcat(strcat(myftpdir,ftp_dir),mytime),"w"))==NULL)
{
perror("ERROR(14):Ftpdir open error:");
freeno=myfree(frs_nd);
cout<<"PROCESS:Free Mem:Free Node Is:"<<freeno<<endl;
exit(14);
}
memset(tmpdir,0,1000);
if((tmp=fopen(strcat(strcat(tmpdir,ftp_dir),"tmp"),"r"))==NULL)
{
printf("ERROR(15):No Tmp File Found,Please Create it frist!\n");
freeno=myfree(frs_nd);
cout<<"PROCESS:Free Mem:Free Node Is:"<<freeno<<endl;
exit(15);
}
else
{
#ifdef _DEG
printf("INFO:Ftp_scirpt Fun Tmpfile :%s\n",tmpdir);
#endif
tmp_max_st=readtmp(tmp,tmpdir);
}
memset(tmpdir,0,1000);
if((tmp=fopen(strcat(strcat(tmpdir,ftp_dir),"tmp"),"w"))==NULL)
{
printf("ERROR:Have No Write Privi In %s?\n",tmpdir);
perror("ERROR(18):Tmpfile Open W Error:");
freeno=myfree(frs_nd);
cout<<"PROCESS:Free Mem:Free Node Is:"<<freeno<<endl;
exit(18);
}
else
{
printf("INFO:Tmpfile Write Mode Succ!\n");
}
max_st_1 = get_max_st(frs_nd) - 1;
printf("INFO:Ftp_Scirpt Fun Tmp_max_st:%lu Max_st_1:%lu:\n",tmp_max_st,max_st_1);
do{
if(!scan_nd->next)
{
break;
}
if(scan_nd->data->file_st > tmp_max_st && scan_nd->data->file_st <= max_st_1)
{
i++;
fputs("put ",ftp);
fputs(scan_nd->data->full_name,ftp);
fputs(" ",ftp);
fputs(scan_nd->data->file_name,ftp);
fputs("\n",ftp);
}
scan_nd = scan_nd->next;
}while(scan_nd->next);
if(scan_nd->data->file_st > tmp_max_st && scan_nd->data->file_st <= max_st_1)
{
i++;
fputs("put ",ftp);
fputs(scan_nd->data->full_name,ftp);
fputs(" ",ftp);
fputs(scan_nd->data->file_name,ftp);
fputs("\n",ftp);
}
printf("INFO:Scan Put Counter :%d\n",i);
if(i == 0)
{
printf("INFO:Read Max Binlog From Temp is :%lu\n",tmp_max_st);
fprintf(tmp,"%lu",tmp_max_st);
fputs("\n",tmp);
}
else
{
printf("INFO:Current Max Binlog-1 is:%lu\n",max_st_1);
fprintf(tmp,"%lu",max_st_1);
fputs("\n",tmp);
}
fclose(ftp);
fclose(tmp);
}
int get_max_st(F_NODE* const frs_nd)
{
F_NODE* scan_nd = frs_nd;
ulong max_st=0;
do {
if(!scan_nd->next)
{
break;
}
if(scan_nd->data->file_st > max_st)
{
max_st = scan_nd->data->file_st;
}
scan_nd = scan_nd->next;
}while(scan_nd->next);
if(scan_nd->data->file_st > max_st)
{
max_st = scan_nd->data->file_st;
}
return max_st;
}
ulong readtmp(FILE *fd,const char* tmpdir)
{
struct stat buf;
ulong max_st = 0;
if(lstat(tmpdir, &buf) < 0)
{
perror("ERROR(16):Lstat Error:");
exit(16);
}
if(buf.st_size == 0)
{
max_st = 0;
}
else
{
fscanf(fd,"%lu",&max_st);
/*
if(max_st == 0)
{
printf("Tmp File Is Bad ?\n");
exit(17);
}
*/
}
return max_st;
}
int myfree(F_NODE* const frs_nd)
{
F_NODE* scan_nd = frs_nd;
F_NODE* cur_nd = 0;
int i = 0;
do{
cur_nd = scan_nd;
if(!scan_nd->next)
{
free(cur_nd->data->file_name);
free(cur_nd->data->path_name);
free(cur_nd->data->full_name);
free(cur_nd->data);
free(cur_nd);
i++;
return i;
}
scan_nd = scan_nd->next;
free(cur_nd->data->file_name);
free(cur_nd->data->path_name);
free(cur_nd->data->full_name);
free(cur_nd->data);
free(cur_nd);
i++;
}while(scan_nd->next);
free(scan_nd->data->file_name);
free(scan_nd->data->path_name);
free(scan_nd->data->full_name);
free(scan_nd->data);
free(scan_nd);
i++;
return i;
}
uint checkdir(const char* mychar)
{
uint len = strlen(mychar);
if(mychar[len-1] == '/' )
{
return 0;
}
else
{
return 1;
}
}

關(guān)于linux中怎么增量增量傳輸mysql binlog日志就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。

本文名稱：linux中怎么增量增量傳輸mysqlbinlog日志
標(biāo)題來(lái)源：http://m.newbst.com/article0/jpjgio.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、自適應(yīng)網(wǎng)站、網(wǎng)站改版、全網(wǎng)營(yíng)銷推廣、網(wǎng)站營(yíng)銷、移動(dòng)網(wǎng)站建設(shè)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

免费观看又色又爽又黄的小说免费_美女福利视频国产片_亚洲欧美精品_美国一级大黄大色毛片

linux中怎么增量增量傳輸mysqlbinlog日志