Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

All tables are InnoDb. I would rather not use mysqldump, because the shard sizes will be about 200 GB (about 700 million rows), and that will take too long.

I was hoping to just stop mysql for an hour, copy the data files to a new machine, and start back up. But you can't do this with InnoDb, as some data is in the shared tablespace. Even if I have the innodb_file_per_table option set.

This is not a website, but a custom application, used by tens of thousands right now, so uptime and performance are important. I suppose I could add logic into my server application to allow for gradual rebalancing / moving of a shard.

Does anyone have a better idea?

share|improve this question
    
@tim - you can take full backup of mysql with mysqlhotcopy or xtrabackup without affecting any operations. it will be atomic backup that you can use to bootstrap your slave [eg - it will contain binlog position you can use to continue replication]. –  pQd Apr 7 '10 at 22:13

5 Answers 5

You can try with innodb hotcopy from http://www.innodb.com/products/hot-backup/features/

A little expensive, but seems you can get an evaluation copy.

share|improve this answer
    
Thanks, I looked at this. I just need to find out if it's possible to restore individual tables to a different mysql instance. –  Tim Apr 7 '10 at 17:10
    
I see this thread is quite old, but it came up in a Google search so I thought I would add for reference that Percona XtraBackup does essentially the same thing as InnoDB Hot Backup (now MySQL Enterprise Backup). The difference is that XtraBackup is free and open source :) –  Michael Mior Dec 21 '10 at 21:24

If you create a table after enabling the innodb_file_per_table option, then all of the table content should be stored in files unique to the table.

Failing that...

If keeping binary logs around indefinitely isn't a problem, you can take a data dump before loading any data. That initial dump can be used to bootstrap replication. Then it's just a matter of waiting for replication to catch up before you try to make the switch.

Alternatively, if you are already using replication for backups, you can set up replication from the backup server instead. The obvious penalty being that backups would be offline during the database dump.

Third option, you can always copy the entire contents of MySQL to a new server, start it up, and then drop anything that you aren't using.

share|improve this answer
    
For first option, unfortunately, if innodb_file_per_table is set, not all data in new tables is stored in the .ibd files. You still need the shared tablespace ibdata file on the new machine. I don't think it's possible to just copy the .ibd files to a new server and import them somehow. –  Tim Apr 7 '10 at 17:08
    
The binary logs would be huge, and would be slow to create a full database. But I would want to do it for selected databases only, so this may not work. Eg. if I have 6 databases on a server, and I want to move two of them to another server. –  Tim Apr 7 '10 at 17:09

you possibly could put mysqlproxy in between appserv and your application servers to add some logic [warning - it has performance impact ].

you can also use mysql replication - but still you will need to bootstrap it from full backup.

ps.

is see others propose mysqlhotcopy. then look at xtrabackup - same but open source.

share|improve this answer
    
We already use replication. Getting the full backup without a mysqldump is still the problem. Too bad mysql doesn't have the ability to detach a database like MS SQL Server does. –  Tim Apr 7 '10 at 16:21
up vote 0 down vote accepted

The Percona xtrabackup appears to do what is required. Specifically, if you have inndb_table_per_file set, you can do a "hot" backup of innodb tables in a database, and import them into another mysql instance.

See these steps for how:

http://www.percona.com/docs/wiki/percona-xtrabackup:xtrabackup_manual#restoring_a_single_ibd_file

share|improve this answer

We do offer a solution to this using our dbShards technology. We can provide a hot set of secondary servers to your existing databases, with reliable replication. Then you can reshard the secondary servers without taking your primary (master) databases down, and smoothly transition to the additional shard servers.

For more info you can read about this at: http://www.dbshards.com

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.