• 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
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• 51
• 52
• 53
• 54
• 55
• 56
• 57
• 58
• 59
• 60
• 61
• 62
• 63
• 64
• 65
• 66
• 67
• 68
• 69
• 70
• 71
• 72
• 73
• 74
• 75
• 76
• 77
• 78
• 79
• 80
• 81
• 82
• 83

function __backup_mysql_database($params)
{
	$mtables = array(); $contents = "-- Database: `".$params['db_to_backup']."` --\n";
	
	$mysqli = new mysqli($params['db_host'], $params['db_uname'], $params['db_password'], $params['db_to_backup']);
	if ($mysqli->connect_error) {
		die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
	}
	
	$results = $mysqli->query("SHOW TABLES");
	
	while($row = $results->fetch_array()){
		if (!in_array($row[0], $params['db_exclude_tables'])){
			$mtables[] = $row[0];
		}
	}

	foreach($mtables as $table){
		$contents .= "-- Table `".$table."` --\n";
		
		$results = $mysqli->query("SHOW CREATE TABLE ".$table);
		while($row = $results->fetch_array()){
			$contents .= $row[1].";\n\n";
		}

		$results = $mysqli->query("SELECT * FROM ".$table);
		$row_count = $results->num_rows;
		$fields = $results->fetch_fields();
		$fields_count = count($fields);
		
		$insert_head = "INSERT INTO `".$table."` (";
		for($i=0; $i < $fields_count; $i++){
			$insert_head  .= "`".$fields[$i]->name."`";
				if($i < $fields_count-1){
						$insert_head  .= ', ';
					}
		}
		$insert_head .=  ")";
		$insert_head .= " VALUES\n";		
				
		if($row_count>0){
			$r = 0;
			while($row = $results->fetch_array()){
				if(($r % 400)  == 0){
					$contents .= $insert_head;
				}
				$contents .= "(";
				for($i=0; $i < $fields_count; $i++){
					$row_content = 	str_replace("\n","\\n",$mysqli->real_escape_string($row[$i]));
					
					switch($fields[$i]->type){
						case 8: case 3:
							$contents .=  $row_content;
							break;
						default:
							$contents .= "'". $row_content ."'";
					}
					if($i < $fields_count-1){
							$contents  .= ', ';
						}
				}
				if(($r+1) == $row_count || ($r % 400) == 399){
					$contents .= ");\n\n";
				}else{
					$contents .= "),\n";
				}
				$r++;
			}
		}
	}
	
	if (!is_dir ( $params['db_backup_path'] )) {
            mkdir ( $params['db_backup_path'], 0777, true );
     }
	
	$backup_file_name = "sql-backup-".date( "d-m-Y--h-i-s").".sql";
		 
	$fp = fopen($backup_file_name ,'w+');
	if (($result = fwrite($fp, $contents))) {
		echo "Backup file created '--$backup_file_name' ($result)"; 
	}
	fclose($fp);
}

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

$para = array(
	'db_host'=> 'localhost',  //mysql host
	'db_uname' => 'username',  //user
	'db_password' => 'password123', //pass
	'db_to_backup' => 'wordpress', //database name
	'db_backup_path' => '/home/my_wordpress/', //where to backup
	'db_exclude_tables' => array('wp_comments','wp_w3tc_cdn_queue') //tables to exclude
);
__backup_mysql_database($para);