How to make database backup in php shared hosting server

 

Some time anyhow my hosting database was deleted by unfortunately using any script or any mistake.

If you are using backup based server then maybe the previous backup was restored by the hosting provider but if the provider has the previous week's data backup then how to get back up in the current week's data.

For sorting this problem I have written the PHP script to take backup as you wish like 1 day or 1-week data backup.

This code requires a database username and password for all database access and the server have functionality for cron job setup.

Here is the PHP script for getting all database lists in the hosting providers.

<?php

$dbhost = 'localhost';
$dbuser = ''; // database username for access all database in the server
$dbpass = ''; // passwrod of the database username access

// Create connection
$conn = new mysqli($dbhost, $dbuser, $dbpass);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$result = $conn->query("SHOW DATABASES");  // query for list of all database
if ($result === false) {
	throw new Exception("Could not execute query: " . $conn->error);
}

$db_names = array();
while($row = $result->fetch_array(MYSQLI_NUM)) { // for each row of the resultset
	if(!in_array($row[0], ['mysql','information_schema','performance_schema','sys'])){ //skip for the system and mysql default database
		$db_names[] = $row[0]; // Add db name to $db_names array
	}
}

echo "<pre>";
print_r($db_names); // print list of all database
echo "</pre>"
?>

This script is to list all databases in the server and skip system, info, and all MySQL default databases.

Also, you can set the array to skip the database for the list of databases.

The above script passes into the shell script to make the backup file of every database in the server.

The shell script makes the gzip file of the database and you can use it to take a single database file to get back up according to the choice.

<?php
$dbhost = 'localhost';
$dbuser = ''; // database username for access all database in the server
$dbpass = ''; // passwrod of the database username access

$dbname = '' // database name

$backup_file = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass ". "$dbname | gzip > $backup_file";

system($command);
?>

This script makes database files in the server using gzip encryption method.

You can use this script to the first script using the loop to create every database backup.

If you are using this shell script to combine both codes to get all database list backup files, then you must understand some points:-

1. You must be set the $dbname variable in the loop database name
2. $backup_file is the reliable database backup file name and path
3. After setup all things you can use this file in the cron job for automatic getting backup according to the cron job running time.

In some servers PHP shell functions are disabled then you can not run the above shell script to make the backup.

You can use another PHP library using composer alexivashchenko/php-mysql-backup.

Using composer to install using this command

composer require alexivashchenko/php-mysql-backup:dev-master

Or you can refer to these documents in GitHub click here.

<?php
require 'vendor/autoload.php';
$Config = [
	'project_name' => 'backup', // project name and also it is prefix of the file name
	'storage_path' => 'storage/', // database backup directory
	'db' => [
		'host' => 'localhost',
		'name' => '', // database name
		'user' => '', // database username
		'pass' => '', // database username password
	],
	'total_archives' => 5, // collecting last n bakcup of every database file
];

$backup_files_all_db = [];
foreach($db_names as $db){
	$Config['db']['name'] = $db;
	$MysqlBackup = new Ali\MysqlBackup($Config); // initialize library
	
	$backup_files_all_db = $MysqlBackup->init(); // creating backup file and return all backup files in the directory
}
echo "<pre>";
print_r($backup_files_all_db); // print list of all database
echo "</pre>"
?>

In this code usage first PHP script database list variable $db_names.

The above script integrate and make a backup as your choice and also you can write code to upload this backup in the cloud storage like:-

1. Google drive
2. AWS storage
3. Firebase
4. and other cloud storage.