主从服务器数据库同步(php版)

主服务器: master.com

从服务器: slave.com

 

1,定义数据库配置选项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$database = array(
'Username' =>'root',
'Password' => '123456',
'Database' => 'test',
'SiteUrl' => 'http://master.com',
'FilePath' => '/home/www/tmp/db_dump/',
'ReturnVar' => NULL,
'Output' => NULL,
'CN-Server' => 'admin@slave.com',
'CN-SSH-Host' => 'slave.com',
'CN-SSH-Username' => 'slave',
'CN-SSH-Password' => '123456',
'CN-SSH-Port' => '22',
'CN-SSH-Timeout' => 600,
'CN-Username' =>'root',
'CN-Password' => '123456',
'CN-Database' => 'test',
'CN-Port' => '3306',
'CN-SiteUrl' => 'http://slave.com',
'CN-FilePath' => '/home/www/tmp/db_dump/'
);

 

2,主服务器导出sql并scp到从服务器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//export sql to slave.com
$timeStamp = date("YmdHis");
$localDir = $database["FilePath"];
$remoteDir = $database["CN-FilePath"];
$remoteServer = $database["CN-Server"];
$filename = "live_{$timeStamp}.sql.gz";

echo "==========================\nEXPORT DB TO CN SERVER\n==========================\nDumping DB ... ";
exec("mysqldump --single-transaction --ignore-table={$database['Database']}.wp_options -u{$database['Username']} -p{$database['Password']} {$database['Database']}|gzip -9 > {$localDir}{$filename}");

echo "Done! \nCopying to CN server ... ";
exec("scp -r {$localDir}{$filename} {$remoteServer}:{$remoteDir}");

echo "Done! \nCleaning up ... ";
exec("rm {$localDir}{$filename}");

echo "ALL FINISHED!\n";

 

3,从服务器导入最新的sql.gz
(1)扫描 /home/www/tmp/db_dump/ 文件夹,查看是否存在 .gz 扩展名的文件
(2)如果发现多个,删除文件名内时间较旧的文件,只保留时间最新的一个
(3)解压文件/导入数据等
(4)将文件名内的时间记录到 /home/www/tmp/db_dump/import.log 文件中,放在第一排
(5)删除刚刚导入的数据库文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
//import db.sql
$dbDir = $database['CN-FilePath'];
$importLog = $dbDir.'import.log';
$dirFiles = scan_dir($dbDir,array('gz'));
if(!$dirFiles){
die('No sql file!');
}
$sqlName = pathinfo($dirFiles[0],PATHINFO_FILENAME);
$fileTime = explode('_', pathinfo($sqlName,PATHINFO_FILENAME));
$fileTime = $fileTime[2];
$filePath = $database['CN-FilePath'].$dirFiles[0];
if(count($dirFiles) > 1){
unset($dirFiles[0]);
foreach ($dirFiles as $_file){
unlink($database['CN-FilePath'].$_file);
}
}

if(file_exists($filePath)){
$newFilePath = $database['CN-FilePath'].$sqlName;
if(file_exists($newFilePath)){
@unlink($newFilePath);
}
echo "import '{$sqlName}' ... ";
exec("gunzip {$filePath} && mysql -u{$database['CN-Username']} -p{$database['CN-Password']} {$database['CN-Database']} < {$newFilePath}");
echo "ok! \r\n";
file_put_contents($importLog, $fileTime,FILE_APPEND);
@unlink($filePath);
@unlink($newFilePath);
}

 

坚持原创技术分享,您的支持将鼓励我继续创作!