0

I'm trying to set up a slave MySQL server on another continent. I don't have enough space on the new server for the entire database that the master contains. Therefore I am trying to bring up this server without the three largest databases. Once I get the server working, I intend to drop those three databases.

The DB heavily uses InnoDB, with the innodb-file-per-table option, so it's not a single ibdata1 tablespace file.

I tried copying over the data directory, minus the directories for the three extremely large databases. This didn't work, because it couldn't load the innodb tablespace for the tables in the databases that I didn't copy.

Then I copied the rest of the files, but used dd to copy only the first 100MB of each .ibd file in those three directories, instead of the many gigabytes that the files on the master contain. With the innodb recovery option, I can start MySQL, but I am unable to drop the tables or databases at that point.

How can I successfully bring up this slave without the three large databases, so I can get replication going on a subset of the remaining tables?

1

Use mysqldump to export the databases and then use mysql to restore the dumps on the remote server. mysqldump dumps individual databases, so you can freely select which databases to dump.

It is not a good idea trying to hack with the actual database files without knowing the exact internal implementation of the files.

0

If space is an important issue, maybe NFS can avoid this issue.

Combine with Partial Backups (needs innodb-file-per-table enabled, which you already did it), you can just copy these big InnoDB databases into another slave server.

-1

use mysqldump -u root -p --all-databases > alldb.sql to dump all db then copy alldb.sql file to the remote server. Again restore db through command mysql -u root -p < alldb.sql.

  • There's about 100GB of data that needs to be replicated. Locking the tables on the master so a dump can complete will result in an extremely long downtime for all the websites that rely on the database. I can't get permission for that. – elyograg Sep 29 '16 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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