3

Well I'm not much of a good developer or a database expert. But I have a little understanding of these things. I'm trying to dump a database on a VPS using "mysqldump" command which works perfectly. But when I tried to restore locally after downloading the dump, gives me a time out error.

Can anyone advise me how to dump a database by splitting it into tables separately. The database I'm referring to is pretty large (6 - 7 GB). I actually tried searching and it confuses me.. even this link here confuses me as where to start.

Any help is highly appreciated.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
AzkerM
  • 173
  • 1
  • 11
  • This shows how to backup a singe table and also how to restore : [how to take backup of a single table in the mysql database](http://stackoverflow.com/questions/6682916/how-to-take-backup-of-a-single-table-in-the-mysql-database) – Uours Nov 27 '13 at 11:38

3 Answers3

0

Are you restoring with phpmyadmin? If you try to upload the import it is probably too large. You can set a directory where the backup files are stored, then you can select the file in phpmyadmin without uploading it.

For the timeout with importing you can increase the timeout settings, or use something like "BigDump"

NLZ
  • 935
  • 6
  • 12
  • I have the dump locally download from the remote location. And yes! I'm trying restore that .sql dump file. Where should I locate the dump if I'm to select in phpmyadmin without uploading. And to make it more clear, I'm using xampp as my client in windows. – AzkerM Nov 27 '13 at 11:37
0

If you're using mysqldump I'll assume you're familiar with the command line.

On your local machine, use

mysql -u [root] -p [database_name] < [database_dump.sql] -v
enter password: ********

The empty database needs to be created your local machine first before you can import the structure and data to it (as simple as doing CREATE DATABASE [database_name];)

The -v flag will do it in 'verbose' mode so you can see the queries as they run. Omitting '-v' will stop it filling your window with the queries but will also give you that 'is it working or not?' nervous feeling after a few minutes.

This will work on Windows as well as it works on Linux / Mac / anything else

Replace my [placeholders] with your own values.

nealio82
  • 2,611
  • 1
  • 17
  • 19
0

Thank you so much for all your answers! Well, what I was looking for is a dumping method or a similar script to dump the database table by table. Finally I tried the dumping the output file with a .txt extension which returned me with success.

Below is the command I used (I know its pretty long proceess, but I finally got all tables dumped);

mysqldump -u users -p database_name table_name > table_name.txt

I used the current directory to output the file assuming I'm already in the directory where I need to dump. If you need to dump the output file to a specific dir, then use /path/to/the/dump/table_name.txt instead of just mentioning the table name. Ans make sure you don't enter password after -p. I don't know why, but I left it blank and it prompts for the password. Then when I type password it dumps to a text file.

I hope this helps.!

Once again thank you so much for the users who came in the first place to help me. :)

AzkerM
  • 173
  • 1
  • 11