5

How to efficiently dump a huge MySQL innodb database?

view full story
linux-howto

http://serverfault.com – I got an Ubuntu 10.04 production MySQL database server where total size of database is 260 GB while size of root partition is itself 300 GB where DB is stored, essentially means around 96% of / is full and there's no space left for storing dump/backup etc. No other disk is attached to server as of now. My task is to migrate this database to other server sitting in different datacenter. Question is how to do that efficiently with minimum downtime? I'm thinking in line of: Request to attach an extra drive to server and take a dump in that drive. Transfer dump to new server, restore it and m (HowTos)

grtyyu's picture
Submitted by grtyyu on 11/05/2012 – Made popular on 11/05/2012

Stories similar to How to efficiently dump a huge MySQL innodb database?

Running an Ubuntu server with MySQL for a high traffic production database server. Nothing else is running on the machine except the MySQL instance.

We store daily database backups on the DB server, is there any performance hit or reason why we should keep the hard disk relatively empty?

What exactly does the default ibdata1 file store that does not exist in a mysqldump?

I've got a backup/rsync job that stores dump files of a MySQL 5.5 database on a remote server for rotating daily/weekly backups.

However, I am not currently backing up the ibdata1 file itself -- is this a nightmare waiting to happen or are dump files enough?

Thanks

Move Big Table from AWS MYSQL 12 weeks 6 days ago

I have a question about getting a huge table to local machine from mysql running in AWS. I just created a table which has a size of 2.3GB, however I have only 2 GB free disk space. This lead into a situation that I even can not dump my table into a dump file which would cause error 28. Then I have two choices.

Clean up the disk with 300+MB free space.

Import SQL with SSH 12 weeks 2 days ago

I wanted to import a dump sql file via ssh like:

mysql -hlocalhost -uUSER -pPASS DATABASE < dump.sql

But it actually overwrites and removes the old database. The dump is tagged with "DROP TABLE IF EXISTS" but why does it remove the old entries? This dump was supposed to add NEW (unique) entries into the database but it removes old database entries.

Is there a way to avoid this?

Thanks.

This MySQL Database Backup 1.2.1 is a free and open source software that will help us to automatically or manually backup the MySQL Database. I often confused on how to backup my existing MySQL database stored in my Windows XP box.

I have a 1 TB MySQL database that I'd like to dump and reload. Most of the data is in one table.

Is this possible without having to dump to all other slaves at the same time?

Basically I would create a blank database with the 'create database' function on the master and slave. Then I would setup the replication of the DB. I would then do a dump on the master.. would all the inserts during the dump be replicated to the slave?

TIA!

edit: sorry i didn't mean mysqldump..

Hi to everyone someone know how to get an estimate about the size of one mysql table? I mean I am planning to make a backup of all the mysql's table on all my server but I'd like to know how big should be every dump table without make the physical dump. There is some command which permit to do this one? I am using mysql 5.1 on debian 6 Cheers

I am running a AWS RDS large instance for the production. I am taking mysql every mid nite. But the thing is its taking too long to take the mysql dump and also the website will stop responding during the mysql dump process. What I am planning to do is, instead of taking the mysql dump, any how I am taking DB snapshot every day.