Exporting Data From MemSQL¶
Using mysqldump to Extract Data From MemSQL¶
MemSQL supports the same query-level inputs and outputs as MySQL, so getting data out of MemSQL is as simple as using mysqldump.
mysqldump creates a .sql file containing the queries necessary to recreate a database. You can run the output of mysqldump against an instance of MemSQL or MySQL, so you can use this strategy to move data between the two.
If you have a database named “foo”, you can create a backup with the following command:
$ mysqldump -h 127.0.0.1 -u root -P 3306 foo > foo.sql
If you want to back up every database in an instance of MemSQL, use
$ mysqldump -h 127.0.0.1 -u root -P 3306 --all-databases > full_backup.sql
Warning
The string "localhost" will not work in place of the IP address 127.0.0.1 in the commands above, because the MySQL client defaults to connecting to localhost via Unix-domain sockets rather than via the specified TCP port. This also means the host must always be explicitly given on the command line, because otherwise it defaults to localhost.
Note
Mysqldump uses LOCK TABLES to help ensure that it reads a consistent view of the database as it queries across tables. This means you cannot run write queries against the database while the backup is being performed. See Lock and Unlock Syntax for details on locking.
MemSQL-specific Constructs¶
If you use a hash table with an explicitly set BUCKET_COUNT (see Hash Table Indexes), then the BUCKET_COUNT will show up in the file produced by mysqldump. MySQL does not support this syntax, so this file will produce an error if you try to consume it with MySQL.
An easy workaround is to remove the USING HASH BUCKET_COUNT = X part of the index declaration. MySQL will revert to using a BTREE instead.