How to manage large Mysql databases?

[ridesign1]

Hi,
I have a large 14mb database but i am having problem in trying to put it into phpmyadmin on my localhost.

I downloaded the database from the backup in cpanel, but i keep on getting 300 seconds timeout execution errors when i try to input via phpmyadmin.

I am using Xampp, how can I create the database,

I tried to use a script:

<?PHP 
    $TheFile = "/dbs/mydb.gz"; 
    $User = "root"; // NOTE: Either use a MySQL user or your 
    $Password = ""; // There is no password
    $DatabaseName = "mydb"; // NOTE: The database is prefixed with 

your CPanel/Netadmin user 

    $Results = shell_exec( "mysql -u$User -p$Password -hlocalhost 

$DatabaseName < $TheFile" ); 

    echo "Results from MySQL import of $DatabaseName with User $User 

with file $TheFile:\r\n $Results\r\n"; 
?>

But the database doesn't get created.

location of this script: http://localhost/xampp/mysqlimport.php
locations of database: http://localhost/xampp/dbs/mydb.gz

hope someone can help.

 

[SlorNet]

Do you happen to have shell access to your hosting account so you could run a script from there? If so, that might be your best bet. I've run into the PHP timeout thing before as well.

 

[ridesign1]

sorry, i forgot to add that I am trying to run the php on my windows xp pc

thanks

 

[death001]

The best way would be through ssh, but your more then likely dont so your best bet would be maybe to contact your host and ask them if they will upload it for you, maybe

 

[ridesign1]

thanks for my reply, but there is no host as I am trying to put it on my pc, I have installed apache, my sql etc.

thanks

 

[Mihewa]

You wouldn't run a script to transfer the database. You'd probably want to go to the same "Back-Up" section in the cPanel of the new site, but instead of creating/downloading a back-up, upload one.

 

[Peter]

phpmyadmin has a maximum size of 2MB that it will allow to be transfered (probably based on the php restriction)

You should use a command line.

 

[ridesign1]

thanks for your replies, I think I wasn't clear in my first messsage, I have successfully downloaded the databse from my website via cpanel "BACKUP", and I am trying to put it on to my computer windows xp on which I have installed the program XAMPP which installs apache and php and phpmyadmin,

The problem I am having is that when i try to upload the databse on to my computer on windows xp i get the error as above, as the database is big.

So what method can i use to install the database onto my home pc?

hope that's slightly clearer.

thanks

 

[Peter]

my response still stands, phpmyadmin will not allow the restore because of the file size. You will need to use a command line ie a dos window.

The reason the script did not work is because i beleive it is a different command for windows.

The file you have unzip it using winzip or winrar (they work with .gz files).

in dos travers to your /mysql/bin folder then execute the following command:-

mysql -uUSERNAME -pPASSWORD DATABSE_NAME < c:\PATH\TO\BACKUPbackup.sql

replace USERNAME with the database username, PASSWORD with the password for the database, DATABSE_NAME with the real name of the database and of course change the path to where the backup file is on your computor.

 

[abdulmueid]

Adding to filth@flexiwebhost's post: Make sure that the database already exists. If not, create one with phpMyAdmin before running the command.

Is the command "mysql" added to your Windows PATH? If not then you will have to browse to the /bin folder in your MySQL directory and then execute the above command as given by filth@flexiwebhost. Good Luck

 

[dongan]

If you want to transfer, best thing to use a MySQL GUI Tool simply. Download a trial version from here...

http://www.mysqlfront.de/download.html

Install it in local machine. Create a database. Go to SQL query Window and Browse and select the SQL dump file. just click Execute Query.

If you want to bulk transfer, login to both localhost and the remote host, just do copy(transfer) database from one host to another host.