Octalys
asked on
How to backup a MySQL database that is too big to mysqldump? Cant take server offline.
Hi,
How do I backup a MySQL database that is too big to mysqldump? Cant take server offline. The database is live and contains 20 million row of records.
I havent been able to backup it for a few months, the plan is to use mysql replication when we upgrade the code. But for now we just simple want a mysqldump, but when we try it, the whole servers stops doing everything else and only dumping the database. Which takes quite some time to finish, so how do people dump such big database on a live server?
Thank you.
How do I backup a MySQL database that is too big to mysqldump? Cant take server offline. The database is live and contains 20 million row of records.
I havent been able to backup it for a few months, the plan is to use mysql replication when we upgrade the code. But for now we just simple want a mysqldump, but when we try it, the whole servers stops doing everything else and only dumping the database. Which takes quite some time to finish, so how do people dump such big database on a live server?
Thank you.
ASKER
Hi,
Thanks for the answer, but I dont think bigdump is what I am looking for. I am not transferring data to other host or importing elsewhere. Just a local backup, the problem is, the second I run any dump tool using mysqldump everything gets locked for the dump.
Thanks for the answer, but I dont think bigdump is what I am looking for. I am not transferring data to other host or importing elsewhere. Just a local backup, the problem is, the second I run any dump tool using mysqldump everything gets locked for the dump.
ASKER
Bigdump description:
To restore the very large backup of your mySQL database (or a part of it) into the new or the same mySQL database. You can't access the server shell and you can't import the dump using phpMyAdmin or any other scripts due to hard memory resp. runtime limit of the web server.
I dont want to restore, just backup and I have access to the shell~
To restore the very large backup of your mySQL database (or a part of it) into the new or the same mySQL database. You can't access the server shell and you can't import the dump using phpMyAdmin or any other scripts due to hard memory resp. runtime limit of the web server.
I dont want to restore, just backup and I have access to the shell~
Hi,
You can use Replication also:
http://onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html?page=last&x-maxdepth=0
Take a look at this.
Regards.
You can use Replication also:
http://onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html?page=last&x-maxdepth=0
Take a look at this.
Regards.
What options are you currently using with mysqldump?
ASKER
No special arguments actually. I just mysqldump
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It would be good to go straight to mysql folder and looking for folder name data. All database name are same name with directory in this folder you can use software to backup that folder as well.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I dont think there's a good way to do this. Thats why always use cluster or replication before rolling a big live database.
You can use a tool named Bigdump.
Here are some links fro helping you.
http://www.techduke.com/2007/05/19/guide-to-moving-a-large-mysql-database-to-new-server-using-bigdump/
http://vavai.net/2009/09/05/tips-for-dumping-large-mysql-database/
Hope this will help you.
Regards.