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?
Use
mysqldumpto export the databases and then usemysqlto restore the dumps on the remote server.mysqldumpdumps 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.
If space is an important issue, maybe NFS can avoid this issue.
Combine with Partial Backups (needs
innodb-file-per-tableenabled, which you already did it), you can just copy these big InnoDB databases into another slave server.use
mysqldump -u root -p --all-databases > alldb.sqlto dump all db then copyalldb.sqlfile to the remote server. Again restore db through commandmysql -u root -p < alldb.sql.