1

I'm need to merge two mysql instances (both mysql V5.6) onto one server.

The binaries of 12 databases are approx. 40GB.

I read other posts about mysqldump but as I've previously had many issues dumping and importing multi GByte dumps, I don't dare try this method again.

Can anyone offer some starting points or from experience working with this size of data, some instructions.

Many internet posts I read are on <2GB website databases and point to mysqldump.

Many thanks.

Chris (DBA in training)

  • I guess this would be one way to do it but replicating 40GB might take a while.. mysqlhighavailability.com/… – Chris May 8 '16 at 18:52
  • 1
    I guess with "binaries" you mean the database data. But "binaries" is commonly used for executable files, not for data files. – ypercubeᵀᴹ May 8 '16 at 19:19
  • Hi - my bad terminology - yes to database data file - the ibd files. – Chris May 8 '16 at 19:29
0

The right answer depend from - what is Your full task description?

  • is it 12 different databases, which just need to have on same server?
  • is it the versions of same databases and You need merge data in each table?

in first - You still can use mysqldump, and it will be fasted method without additional steps and/or packages

if size of single dump is big - make dump by single database, table

user#> mysqldump db_name table_name > table_name.sql

Single huge file now must not be a problem with most of OS, but could be difficult for edit.

In second case, need analyse merging logic simples way - mysqldump with --no-create-info (not CREATE TABLE), but could be problems with duplicate keys.

If some duplicate keys (indexes):

or edit dump (for big files - use sed) and change insert to INSERT IGNORE or UPDATE ON DUPLICATE

or create ETL Job with any of available package and manage merge logic.

  • Thanks for your comments. In reply: 4 database on one server need moving to the instance on a second server containing 8 databases so total 12 independent databases. No table merging. Some dumps are +2GB per table and I could not import successfully. Just looking for config changes I tried in my.ini to try to achieve this (-max_allowed_packet I think). Reading this on importing large dumps: ozerov.de/bigdump – Chris May 8 '16 at 19:43
  • if You have trouble with file size, it for 99% - not a MySQL but OS problem. which OS are You use for MySQL? In this case, I personally use Talend Open Studio - create simple Job which do it or as single flow, or incrementally, For example I use it for sync 15G (60M+) tables - MySQl and Redshift – a_vlad May 8 '16 at 20:00
  • @Chris could you share some actual errors or problems you had with big dumps? We "moved" 40GB database by a way of single dump and I once restored 120GB table from a dump too so there should be no inherent problem with it. – jkavalik May 9 '16 at 6:00
  • I can confirm - I have many time dump file 15-20Gb, one time transfer 140Gb table. It could be some restriction of OS with limit of 2Gb per file, but better of course too see errors – a_vlad May 9 '16 at 7:15
  • Hi all. A courteous note of thanks. I was distracted until last week on this task and persisted with your encouragement. Bigger server, more RAM, faster hard drives. Success. – Chris Jul 25 '16 at 16:10
0

If two different instances of MySQL have the same schema and all keys are distinct, you could use mysqldump in three steps.

For this example, we'll merge mydb from ServerA and ServerB

STEP 01

mysqldump the schema only into a text file call schema.sql

mysqldump -h... -u... -p... --no-data --databases mydb > schema.sql

STEP 02

mysqldump the data from ServerA

mysqldump -u... -p... --no-create-info --databases mydb > data1.sql

STEP 03 : DECISION TIME

If you want to insert only new data from ServerB, use --insert-ignore

mysqldump -u... -p... --insert-ignore --no-create-info --databases mydb > data2.sql

If you want to overwrite ServerA data with ServerB data , use --replace

mysqldump -u... -p... --replace --no-create-info --databases mydb > data2.sql

STEP 04

Load the new server in the following order

mysql -u... -p... < schema.sql
mysql -u... -p... < data1.sql
mysql -u... -p... < data2.sql

FINAL THOUGHTS

This is a rather oversimplified answer but should provide a starting point in the merge process. I will leave the messier details of constraints, referential integrity, and all unique corners to your technical imagination.

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