1

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?

Tim
  • 71
  • 4
  • @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 07 '10 at 22:13

5 Answers5

1

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.

sntg
  • 1,450
  • 11
  • 15
  • 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 07 '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
1

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.

Sam Rodgers
  • 81
  • 2
  • 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 07 '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 07 '10 at 17:09
0

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.

pQd
  • 29,981
  • 6
  • 66
  • 109
  • 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 07 '10 at 16:21
0

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

Tim
  • 71
  • 4
0

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