PHP: Backup Your Website’s MySQL Database

It is always good to backup your MySQL database regularly. You should also create a backup before making any major changes, in case something goes wrong and you need to revert to the unmodified version, then you will found yourself in safe hand. Database backups can also be used to move your database from one server to another if you change hosting company.

If you want to take backup of your complete database using the command prompt then follow the below command.

mysqldump -u yourusername -p yourpassword yourdatabasename > yourbackupfilename.sql

Please replace yourusername, yourpassword, yourdatabasename, yourbackupfilename according to your real credentials.

You can also do the same job using ‘php script for mysql database backup’.
Follow the below script to have backup of your complete database or some tables.

You just need to provide database credentials to the function, as well as an array of tables you’d like to backup leave it if you want to take complete database backup.

<?php
    backup_tables('HOSTNAME','USERNAME','PASSWORD','DATABASE');

    /* Function to create backup of a database */
    function backup_tables($host,$user,$pass,$name,$tables = '*')
    {
		$link = mysql_connect($host,$user,$pass);
		mysql_select_db($name,$link);
	
		if($tables == '*'){
			$tables = array();
			$result = mysql_query('SHOW TABLES');
			while($row = mysql_fetch_row($result)){
				$tables[] = $row[0];
			}
		}else{
			$tables = is_array($tables) ? $tables : explode(',',$tables);
		}
	
		//Looping of tables
		foreach($tables as $table){
			$result = mysql_query('SELECT * FROM '.$table);
			$num_fields = mysql_num_fields($result);
		
			$return.= 'DROP TABLE  IF EXISTS '.$table.';';
			$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
			$return.= "\n\n".$row2[1].";\n\n";
		
			for ($i = 0; $i < $num_fields; $i++)
			{
				while($row = mysql_fetch_row($result))
				{
					$return.= 'INSERT INTO '.$table.' VALUES(';
					for($j=0; $j<$num_fields; $j++)
					{
						$row[$j] = addslashes($row[$j]);
						$row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]);
						if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
						if ($j<($num_fields-1)) { $return.= ','; }
					}
					$return.= ");\n";
				}
			}
			$return.="\n\n\n";
		}
	
		//Save .sql file
		$handle = fopen($name.'-backup-'.date('d-m-y-H-i-s').'.sql','w+');
		fwrite($handle,$return);
		fclose($handle);
	
		/*
		@ Use this code if you want to create zipped file
		*/
		/*
		$gzdata = gzencode($return, 9);
		$handle = fopen($name.'-backup-'.date('d-m-y-H-i-s').'.sql.gz','w+');
		fwrite($handle, $gzdata);
		fclose($handle);
		*/
    }
?>

We all know how important the database backup is, so don’t hesitate or waste your time in thinking else, have backup your database at a regular interval

Have backup, keep yourself in safe side, Have fun, Enjoy Scriptarticle!!