MySQL MariaDB Export and Import

Dec 26, 2017 / Updated on Dec 26, 2017

Export the database

Use the following command to export the database

mysqldump --user=root --password=mypassword \
    --host=myhost1 --port=3306 \
    --single-transaction --compress --routines \
    --result-file=dump.sql \
    --databases mydb;

Note:

  • Include –single-transaction –compress to export and import quickly. Check documentation for the use
  • --rountines will include procedures/functions in the dump (triggers included by default)
  • --result-file will be the output file (instead of this param, output redirection like mysqldump [options] > dump.sql can be used, however it may have charset issues in OS like windows
  • if you have any blob columns then include –hex-blob

Import the database

Use the following command to import the database

mysql --user=root --password=mypassword \
    --host=myhost2 --port=3306 < dump.sql

Export and Import using a single statement

Combine the above two command using pipe as a single statement

mysqldump --user=root --password=mypassword \
    --host=myhost1 --port=3306 \
    --single-transaction --compress \
    --databases mydb \
| \
mysql --user=root --password=mypassword \
    --host=myhost2 --port=3306

Create the user

The above steps only create the database if you have specific user to access the database then create the user in the new database.

Refer page: https://yottabrain.org/database/mysql/mysql-mariadb-create-user/

Note:

  • Replace \ with ^ if you are using non unix environment
  • Replace all the dynamic values like user/password/host etc. with appropriate value

Watch in Youtube

References

comments powered by Disqus
Categories:
Tags: