large db backup

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

large db backup

Post by s.dot »

Currently i'm using phpmyadmin to backup my database. But with a 75 mb gzipped database... the file always seems to get corrupted! About 25% of it is seen as "garbage" so i've been downloading the raw sql file uncompressed. When I do that, the server gets too stressed to even load my website (and downloading a 400 mb sql file takes around 20 minutes or so).

I don't really need to download the file. Just store it somewhere on my server. Is there a way to do a backup without putting so much stress on the server?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

Why not shell in and simply:

Code: Select all

mysqldump > backup-20060914.sql
or:

Code: Select all

cp -a /var/lib/mysql /some/backup/path/20060914/
When backing up, if you want to make sure you have a consistant backup you may want to:

Code: Select all

mysql> flush tables with read lock;
backup data in either two ways above then:

Code: Select all

mysql> unlock tables;
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Can you hit your database with a MySQL Administrator utility or SQLYog or something like that. Those client side apps tend to dump large databases quickly and easily.
User avatar
ok
Forum Contributor
Posts: 393
Joined: Wed May 31, 2006 9:20 am
Location: The Holy Land

Post by ok »

If you don't have what Everah or ody mentioned, you can make a PHP script that backup only one table at a time.
If the table is too large, then you split the table to 3M-5M and like that you continue to backup your DB.

When you want to import it, you also import one table each time and then insert the data.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I am trying out SQLyog, it looks nifty!
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

That is my tool of choice for MySQL DB admin work. The enterprise version kicks tail.
Post Reply