mysqldump is a database backup program that performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server and can also generate output in CSV, other delimited text, or XML format.
- Create a MySQL deployment at ScaleGrid.
- Your existing database should be running on MySQL community versions 5.6.x or 5.7.x.
- If you do not have the mysqldump utility already installed, refer to these instructions to download and install it as part of the MySQL command line utilities.
- Ensure that the network connectivity is working fine between your source database server and your ScaleGrid deployment by following these instructions.
- The username you are using needs to have SELECT, LOCK TABLES, SHOW VIEW and TRIGGER grants.
1: Dump your existing database to a file using the command:
mysqldump -h <hostname> -u <username> -p<password> --databases <Your DB Name> --single-transaction > Your_DB_NAME.sql
--single_transaction option will ensure that your Innodb table data is consistent in the dump file. Also note that the username you are using above needs to have SELECT, LOCK TABLES, SHOW VIEW and TRIGGER grants.
2: Load the dump file onto your ScaleGrid deployment:
mysql -u <ScaleGrid Admin User> -h <ScaleGrid hostname> -p<ScaleGrid Admin Password> < Your_DB_NAME.sql
If your ScaleGrid deployment is SSL enabled, use the command:
mysql -u <ScaleGrid Admin User> -h <ScaleGrid hostname> -p<ScaleGrid Admin Password> --ssl-mode=REQUIRED < Your_DB_NAME.sql
Note: If your source cluster has GTID enabled, then you may see a warning like below when you execute the mysqldump command in step 1:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF.
In this case, rerun the command with following options:
mysqldump -h <hostname> -u <username> -p<password> --databases <Your DB Name> --single-transaction --set-gtid-purged=OFF > Your_DB_NAME.sql
Updated almost 3 years ago