{"id":818,"date":"2018-02-19T16:51:42","date_gmt":"2018-02-19T08:51:42","guid":{"rendered":"https:\/\/www.techcoil.com\/blog\/?p=818"},"modified":"2018-09-05T11:11:14","modified_gmt":"2018-09-05T03:11:14","slug":"how-to-migrate-your-mysql-mariadb-database-with-mysqldump-tar-and-scp","status":"publish","type":"post","link":"https:\/\/www.techcoil.com\/blog\/how-to-migrate-your-mysql-mariadb-database-with-mysqldump-tar-and-scp\/","title":{"rendered":"How to migrate your MySQL \/ MariaDB database with mysqldump, tar and scp"},"content":{"rendered":"

If you had been building the content for your new WordPress site on a Raspberry Pi 3<\/a>, there will come a time when you need to migrate the underlying MySQL database to another machine, for eg. a DigitalOcean<\/a> droplet, for production usage.<\/p>\n

MySQL provides us with the mongodump client utility to produce a set of SQL statements that can be executed to reproduce the original database object definitions and table data. <\/p>\n

A simple MySQL database migration can be performed with the following steps:<\/p>\n

    \n
  1. Use mysqldump<\/a> to get the set of SQL statements that can be executed to reproduce the original database object definitions and table data.<\/li>\n
  2. If the mysqldump output is too big, use the tar<\/code> command to compress it.<\/li>\n
  3. Use the scp<\/code> command to send the mysqldump output from the source server to the destination server.<\/li>\n
  4. If you had used the tar<\/code> command to compress the mysqldump output, use the tar command at the destination server to decompress it.<\/li>\n
  5. Execute the set of SQL commands in the mysqldump<\/code> output at the destination server.<\/li>\n<\/ol>\n

    This post documents how you can migrate your MySQL \/ MariaDB database with utilities provided by MySQL and most Linux servers.<\/p>\n

    Exporting data from MySQL \/ MariaDB database with mysqldump<\/h2>\n

    The way you export data from a MySQL \/ MariaDB database depends on whether you have a default user that is set to use the Authentication Plugin<\/a>.<\/p>\n

    Exporting data from MySQL \/ MariaDB server with a regular user without the Authentication Plugin<\/h3>\n

    To export data from a MySQL \/ MariaDB database instance with a regular user without the Authentication Plugin, run the following command:<\/p>\n

    \r\nsudo mysqldump -u root -p db_to_migrate > db_to_migrate.sql\r\n<\/pre>\n
    The command will prompt for the password for the root database user. Once the command completes, you will find a text file, db_to_migrate.sql<\/code>, in your current directory. This file will contain all the SQL statements that can be run to reconstruct the db_to_migrate database instance.<\/p>\n
    Exporting data from MySQL \/ MariaDB server with a default user set to use the Authentication Plugin<\/h3>\n
    To export data from a MySQL \/ MariaDB database instance with a default user set to use the Authentication Plugin, run the following command:<\/p>\n
    \r\nsudo mysqldump db_to_migrate > db_to_migrate.sql\r\n<\/pre>\n
    If you had not supplied your system user password via previous sudo<\/code> command executions, the command will prompt for the password of your current system user. Once the command completes, you will find a text file, db_to_migrate.sql<\/code>, in your current directory. This file will contain all the SQL statements that can be run to reconstruct the db_to_migrate database instance.<\/p>\n
    Using the tar command to compress the output from mysqldump<\/h2>\n
    If your database instance is large, you can use the tar command to compress the output from mysqldump<\/code> to reduce the time needed to send the output file from the source server to a destination server.<\/p>\n
    To compress the output from the mysqldump<\/code> command mentioned earlier, we can run the following command:<\/p>\n
    \r\ntar -cvzf db_to_migrate.tar.gz db_to_migrate.sql \r\n<\/pre>\n
    When the command complete, you should get the file, db_to_migrate.tar.gz<\/code> in your current working directory. This file should be significantly smaller than the original output file that we got from running the mysqldump<\/code> command mentioned earlier.<\/p>\n
    Sending the output from mysqldump from the source server to the destination server<\/h2>\n
    If there is a SSH server running at your destination server, you can use the scp<\/code> command to send the db_to_migrate.tar.gz<\/code> file from your current working directory to the destination server. <\/p>\n
    Suppose your destination server is reachable by the domain example.com<\/code>, has a system user account with the username root<\/code> and has a \/var\/receiving<\/code> directory, you can run the following command to send your db_to_migrate.tar.gz<\/code> file to the destination server:<\/p>\n
    \r\nscp db_to_migrate.tar.gz root@example.com:\/var\/receiving\/db_to_migrate.tar.gz\r\n<\/pre>\n
    Using the tar command to decompress the output from mysqldump<\/h2>\n
    Once the file transfer is done, you can use the ssh<\/code> command to get into your destination server to extract the contents of db_to_migrate.tar.gz<\/code>:<\/p>\n
    \r\nssh root@example.com\r\n<\/pre>\n
    Once you got into your destination server, run the following commands to extract the original output file from running mysqldump<\/code> at the source server:<\/p>\n
    \r\ncd \/var\/receiving\r\ntar -zxvf db_to_migrate.tar.gz\r\n<\/pre>\n
    Once the commands complete, you should be able to get back the original output file from running mysqldump<\/code> at the source server.<\/p>\n
    Importing data into the destination MySQL \/ MariaDB server<\/h2>\n
    The last step to migrating your MySQL \/ MariaDB database is to import the data to the destination MySQL \/ MariaDB server.<\/p>\n
    As with the case of exporting data from a MySQL \/ MariaDB database, importing data into the destination MySQL \/ MariaDB server depends on whether you have a default user that is set to use the Authentication Plugin.<\/p>\n
    Importing data into MySQL \/ MariaDB server with a regular user without the Authentication Plugin<\/h3>\n
    Assuming that the a_database<\/code> database instance was created in the destination MySQL \/ MariaDB server, run the following command to import the data from the source server:<\/p>\n
    \r\nsudo mysql -u root -p a_database < db_to_migrate.sql\r\n<\/pre>\n
    The command will prompt for the password for the root user. Once the command completes, the database content from the source MySQL \/ MariaDB server will be reconstructed at the destination server.<\/p>\n
    Importing data into MySQL \/ MariaDB server with a default user set to use the Authentication Plugin<\/h3>\n
    To import data into a MySQL \/ MariaDB database instance with a default user set to use the Authentication Plugin, run the following command:<\/p>\n
    \r\nsudo mysql a_database < db_to_migrate.sql \r\n<\/pre>\n
    If you had not supplied your system user password via previous sudo<\/code> command executions, the command will prompt for the password of your current system user. Once the command completes, the database contents from the source MySQL \/ MariaDB server will be reconstructed at the destination server.<\/p>\n\n