A customer asked me to copy a whole database from one mysql server to another.
\nA few years ago, I would go with the classic mysqldump + import solution, but it is very slow, especially the import part (because MySQL insert buffer is monothread). One can also use mysqlimport (LOAD DATA INFILE), but it is still quite slow… When using a standard SQL dump, I measured a speed of 1MB\/sec for reimport … Quite long if you have gigabytes of data !<\/p>\n
So I tested xtrabackup for this. This tool is already in heavy tests internally but to my mind, is not quite ready for production. But let’s try this for this specific task of migrating a database.<\/p>\n
First, you need to install xtrabackup and all other binaries included (especially xbstream). You’ll also need at least version 5.5.25 on remote host (you’ll see why). And last but not least, InnoDB must run with<\/p>\n
innodb_file_per_table=1<\/pre>\nIn this blog post, I’ll name \u00ab\u00a0server A\u00a0\u00bb the source, and \u00ab\u00a0server B\u00a0\u00bb the destination.
\nMy SQL data is stored in \/srv\/mysql.
\nOn server B, create a destination folder, for example \/tmp\/test
\nThis is because you need to import data to a temporary directory, where there is .
\nOn server A, launch the following command :<\/p>\ntime innobackupex --export --databases 'mydb' --no-lock \\\r\n--stream=xbstream --tmpdir=\/tmp --use-memory=128MB \\\r\n--user=backup --password=XXXXX --parallel=4 \/srv\/mysql \\\r\n| ssh root@10.0.0.224 \"xbstream --directory=\/tmp\/test -x\"<\/pre>\nA few explanations :<\/p>\n
Dump is complete ! I achieved a rate of 11.3 MB\/sec with this : far better than mysqldump \/ mysqlimport ! I’m sure we can be faster by tuning a few things.
\nTake care of file owner : copy was done with root, but I’m sure your mysql data is owned by someone else (or it should be !).<\/p>\n
Then, prepare data for export :<\/p>\n
xtrabackup_55 --prepare --export --target-dir=\/tmp\/test<\/pre>\nThis will \u00ab\u00a0prepare\u00a0\u00bb data (applying innodb log …), and especially will create .exp files that contains table metadata. This is mandatory for import in the next step !<\/p>\n
We can see xtrabackup working :<\/p>\n
[...]\r\nxtrabackup: export option is specified.\r\nxtrabackup: export metadata of table 'mydb\/performer' to file `.\/mydb\/performer.exp` (1 indexes)\r\nxtrabackup: name=PRIMARY, id.low=443, page=3\r\n[...]<\/pre>\nOk, now we need to create the new database, and import scheme.
\nOn server B, do :<\/p>\nCREATE DATABASE mydb;<\/pre>\nWe also need a temporary database (you’ll see why later …)<\/p>\n
CREATE DATABASE test;<\/pre>\nThen, we have two choices :<\/p>\n
\n
- create all tables, discard all tablespaces, and import all<\/li>\n
- do the same operation, but for each table<\/li>\n<\/ol>\n
I first thought that there were a bug with method 1 (https:\/\/bugs.launchpad.net\/percona-server\/+bug\/1052960<\/a>) but it appears this bug is also hitting if you do method 2. Nevertheless, I’ve created a script that handle method 2 all by itself.<\/p>\n
NOTE THAT YOU NEED<\/strong> Percona Server 5.5.25 v27.1 if you don’t want to hit the bug I was talking about. This bug crashes MySQL, and leaves it in a state where it cannot start again … You’ve been warned.
\nFirst, let’s dump all tables schemas. On server A, do :<\/p>\nmysqldump --routines --triggers --single-transaction --no-data \\\r\n--host=serverA --user=root --password=xxx \\\r\n--result-file=schemas.sql mydb;<\/pre>\nAnd import it ON THE TEMPORARY DATABASE :<\/p>\n
cat schemas.sql |mysql -uroot -hSERVER_B -p test<\/pre>\nExecute following statement in MySQL (server B) to tell MySQL that we will import data :<\/p>\n
SET GLOBAL innodb_import_table_from_xtrabackup = 1;\r\n\/**\u00a0If you have a version < 5.5.10, despite what I just\r\n * said about minimum version required, query is : **\/\r\nSET GLOBAL innodb_expand_import = 1;<\/pre>\nThen, let’s use following bash script :\u00a0https:\/\/www.olivierdoucet.info\/blog\/wp-content\/uploads\/2012\/09\/expand_import.sh.txt<\/a><\/p>\n
A few explanations :<\/em><\/p>\n
For each table, we do the following :<\/p>\n
ALTER TABLE xxx DISCARD TABLESPACE;\r\nmv xxx.ibd xxx.exp \/srv\/mysql\/mydb;\r\nALTER TABLE xxx IMPORT TABLESPACE;<\/pre>\nThis bash script is using .my.cnf file in your user dir for credentials (or other default values). Please ensure you have access to the destination database with these credentials.
\nALL STEPS ARE REQUIRED<\/strong>. If you miss something (the set global, chown, chmod …) you’ll probably get an error (like ‘got error -1 from storage engine’). At this point, you’d better start the process over (and drop the destination database, which is incomplete).<\/p>\nIf import works, you can see lines like this in log :<\/p>\n
[...]\r\nInnoDB: Import: The extended import of mydb\/mytable is being started.\r\nInnoDB: Import: 2 indexes have been detected.\r\nInnoDB: Progress in %: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 done.\r\n[...]<\/pre>\nNote that when importing huge InnoDB tables, there is (was) a lock on the dictionary while scanning the ibd file (the data file), so it may lock the whole server … If operation took too long, server crashed. This is bug https:\/\/bugs.launchpad.net\/percona-server\/+bug\/684829<\/a>
\nThis bug has been fixed since, and as you need at least version 5.5.25, you should not hit this problem.<\/p>\n<\/p>\n
Conclusion<\/h2>\n
This method is 10 times faster than mysqldump \/ mysqlimport. But As you can see, there are huge risks and still bugs remaining. The dump part is really safe, so I would recommend you to first test the import on a dev server before doing this in production.<\/p>\n
Xtrabackup is really an amazing tool, but it still suffers somme nasty bugs. I’m sure I’ll use it in production in a few months, when it will be perfectly stable for all tasks.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
A customer asked me to copy a whole database from one mysql server to another. A few years ago, I would go with the classic mysqldump + import solution, but it is very slow, especially the import part (because MySQL insert buffer is monothread). One can also use mysqlimport (LOAD DATA INFILE), but it is … Continuer la lecture de Migrating a MySQL database to another server<\/span>