Comments - pre-seeding databases from remote servers for multi-source replication

9 years, 11 months ago Ives Stoddard

Hi Sergii,

my previous response had been blocked as spam, so reposting...

did you mean copying just tablespace files, or copying the entire innodb database directory (each tablespace is an individual file)?

copying all mysql data files will work only for one server. this has to do with tablespace metadata information stored in transaction logs (ibdata*) and master mysql database (table ids, etc.). for more info about that error, see the following:

"... 14:21:02 InnoDB: Error: tablespace id in file './db_name/new_innodb_table.ibd' is 26, but in the InnoDB InnoDB: data dictionary it is 28."

http://mysqlhints.blogspot.com/2008/10/fixing-innodb-import-tablespace-error.html http://dba.stackexchange.com/questions/6268/how-to-recover-an-innodb-table-whose-files-were-moved-around/6269#6269

so i can restore a full file-based backup (or AWS EBS snapshot restore) for the first server, server A, and all of the databases/tables from the first server will work. the problem is when i try and copy files from additional servers. if I copy in just the database files, then the mysql database doesn't contain the necessary metadata and tablespace IDs that match between mysql master database and the copied tablespace files. the database will appear in "show databases", but i get an error when trying to query from the tables (mismatch tablespace id).

overwriting the restored mysql master database with a second server (server B) will break access to server A's tablespaces (since the metadata in master mysql database would contain server B's info and not server A's info, again running into the mismatching tablespace ids).

i know of no way to merge information from multiple master mysql tablespaces, unless i upgrade to 5.6 and use transportable tablespaces:

https://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html

i'm guessing my only option is to use an intermediary server to upgrade the data restored from a snapshot from 5.5 to 5.6. this way i can then use the transportable tablespaces. the new process will look like:

(assumes multiple 5.5 source servers and a new multi-source replication server running 5.6+ / mariadb 10.0+)

1) create a new 5.5 DB instance 2) restore data directory from a snapshot of a 5.5 server 3) start and stop 5.5 server to cleanly commit transaction logs to tablespaces 4) upgrade from 5.5 to 5.6+ 5) export desired database/tablespace files with transportable tablespace options 6) move database/tablespace files to new multi-source replication server 7) import tablespace files into 5.6+ (or mariadb 10.0+) multi-source replication server 8) grant privileges to imported database/tables. 9) stop / start server and verify no errors. 10) delete the temporary 5.5 >> 5.6+ database instance

(repeat 1-10 for each source 5.5 server)

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.