Mysql db restore

[gsaray101]

I am trying to set up one server with multiple mysql dbs. This script will get the mysqldump files from the ftp server and based on the name of the server in the file, it will create the corresponding db on the server.

this is the code

<?php
 
#ftp the most recent file to the ftp server
$dir1="/patrol_media/patadmin/sqlbak";
$dir2="d:\\cacti\\backup";
 
$ftp_server = "logsvr2.dowjones.net"; // Address of FTP server.
$ftp_user_name = "username";
$ftp_user_pass = "password";
$destination_file = "/patrol_media/patadmin/sqlbak";
 
$conn_id = ftp_connect($ftp_server);
 
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);
 
$contents = ftp_nlist($conn_id, $dir1);
 
foreach($contents as $key => $file)
{
    $extension = substr(strrchr($file, $dir1), 1);
    echo "KEY $key => $file $extension";
    // try to download $server_file and save to $local_file
    if (ftp_get($conn_id, "$dir2\\$extension", $file, FTP_BINARY))
        {
            echo "Successfully written to $dir2\\$extension";
            $BackupFile="$dir2\\$extension";
            // move file into OLD folder
 
        }
}
$username = "cactiuser";
$password = "cactiuser";
$dc = opendir($dir2);
while ($file = readdir($dc)) {
    $server_name = strtok($file, "_");
    $database=$server_name;
 
    $con = mysql_connect("localhost","cactiuser","cactiuser");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }if (mysql_query("DROP DATABASE $database",$con))
          {
              echo "Database dropped";
          }
      else
          {
              echo "Error dropping database: " . mysql_error();
          }
      if (mysql_query("CREATE DATABASE $database",$con))
          {
                      echo "Database created";
        }
             else
        {
                      echo "Error creating database: " . mysql_error();
          }
 
      mysql_close($con);
 
 
    echo $restoreCommand = 'mysql -u {$username} -p{$password} {$database} < {$file}';
 
    echo "{$file}\n";
 
    if (exec($restoreCommand)) {return true;}
        else {return false;}
 
}
 
?>
 

when I ran this, I am getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.' at line 1
Error creating database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax
 to use near '.' at line 1
mysql -u {$username} -p{$password} {$database} < {$file}.
 

Can anybody help?

[gsaray101]

I found one problem where $fn variable was not defined. I changed it to $file and I am getting a more meaningfull error now:

Access denied for user 'cactiuser'@'localhost' to database 'sbkesmgendev01'
Error creating database: Access denied for user 'cactiuser'@'localhost' to database 'sbkesmgendev01'
mysql -u {$username} -p{$password} {$database} < {$file}SBKESMGENDEV01_20080508.sql
 

[gsaray101]

the part that ftp gets all files into d:\cacti\backup works. I need to go throught each file and create a mysqldb based on the file name. I would like to do something like this in php. This piece of code works in perl. Can any body help?

opendir(DIR, "$dir2");
@files = readdir(DIR);
closedir(DIR);
 
foreach $file (@files) {
 

[gsaray101]

when I do:

$dir = opendir("$dir2");//List files in images directory
while (($file = readdir($dir)) !== false){
 
echo "file name is $file\n";
closedir($dir);
 

I am not getting all of the files in that directory. I have two actual files and it is only displaying the first one.

this is the output:

file name is .
file name is ..
file name is SBKESMGENDEV01_20080508.sql

I am missing SBKW2K3ESMPRD4_20080429.sql.

Can anybody tell me why?

[TheServant]

Use one post at a time. You have four! You can edit your posts so next to the reply button under each post you will see Edit/Delete. Please use it!

[Atli]

Hi.
In your $restoreCommand variable, I assume you are trying to insert the values of those variables into the string?
If so, you should use double-quotes, not single-quotes. When used like this, the variable names will be used, not their values.

# This will not work...
echo $restoreCommand = 'mysql -u {$username} -p{$password} {$database} < {$file}';
 
# It should be
echo $restoreCommand = "mysql -u {$username} -p{$password} {$database} < {$file}";
 

The code you posted in your last post shouldn't work. You should get a parse-error, due to the fact that there is a single { bracket with no matching } bracket.

After fixing that, all my tests work perfectly.
If it is not showing the second file, it must not be there.

<?php
$dir = opendir(".");
while ($file = readdir($dir)) {
    echo "file name is $file\n";
}
closedir($dir);
?>