Hi team, we would like to migrate our memsql db from one cloud to another with different configuration (with less leaves). what is the best way to dump all databases including tables, view, users? I read about mysqldump tool and BACKUP & RESTORE commands and i’m not sure which one to use. Thanks !!
BACKUP will save all the data/metadata within a database and is likely the easiest to use for larger databases (Its creates files in a compressed binary format. mysqldump dumps out a file with a series of insert statements which will be slow to reload for a large database). The downside of a backup, is you need to place the backup files somewhere that the nodes in your new cluster can access it (S3, NFS, Azure blob store are all options).
In summary, if your database is small mysqldump is easier to use (one text file), but will be very slow for larger databases. For larger databases your going to need to use BACKUP/RESTORE.
We don’t currently have a utility to dump out all users, roles, groups. You’ll need to write a script that runs over all users in SHOW USERS and runs SHOW GRANTS FOR .
We’ll be bringing some new tools online shortly to make this type of migration easier.