backup mysql with php

Question

I use this function to make mysql backup it work fine but my problem is i have some data with utf-8 encode in my database and when use this function data change to "?????" how can i fix it ?

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);
    }
    foreach($tables as $table)
    {
        $result = mysql_query('SELECT * FROM '.$table);
        $num_fields = mysql_num_fields($result);

        $return.= 'DROP TABLE '.$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] = ereg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                    if ($j<($num_fields-1)) { $return.= ','; }
                }
                $return.= ");\n";
            }
        }
        $return.="\n\n\n";
    }
    $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
}

Lion4H · Accepted Answer · 2015-10-17T17:32:07.247

2

Set charset to UTF8 for db connection

mysql_query("SET NAMES utf8");

right after selecting the db (before any other queries).

it is also recommended to abandon using mysql_* functions family in PHP since it is deprecated in PHP 5.5.0.

for fast switching, use mysqli_* functions family, you mostly need to add one parameter (db connection $link), a global replace should be enough.

edited Oct 17 '15 at 17:32

answered Oct 17 '15 at 17:17

Lion4H

166
3

score 0 · Answer 2 · answered Oct 17 '15 at 16:43

0

Even more easy approach would be:

<?php
    shell_exec("mysqldump -u <user> -ppassword1234 <db_name> > db.sql");
?>

Here, <user> is to be replaced by your username, and -ppassword1234 is password, example: if the password is password1234, you'll pass -ppassword1234, I hope I'm clear, to be more elaborative, if your password is pass then you'll pass -ppass For example, my password is hello and user is root, and I'm backing up mydb database, I'd execute:

<php
    shell_exec("mysqldump -u root -phello mydb > db.sql");
?>

answered Oct 17 '15 at 16:43

This should handle all UTF-8 characterset as well. – Oct 17 '15 at 16:44
as it turns out, you could just use: ``` db.sql` ?> ``` This is something called backtick operator. – Oct 17 '15 at 16:46

backup mysql with php

2 Answers2