MySQL MariaDB Export and Import

Categories:

  2 minute read  

Export the database

Use the following command to export the database

mysqldump --user=<<db-user>> --password=<<db-password \
    --host=<<db-host>> --port=3306 \
    --single-transaction --compress --routines \
    --result-file=dump.sql \
    --databases <<db-name>>;

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=<<db-user>> --password=<<db-password>> \
    --host=<<db-host>> --port=3306 < dump.sql

Export and Import using a single statement

Combine the above two command using pipe as a single statement

mysqldump --user=<<db-user>> --password=<<db-password>> \
    --host=<<db-host>> --port=3306 \
    --single-transaction --compress \
    --databases <<db-name>> \
| \
mysql --user=<<db-user>> --password=<<db-password>> \
    --host=<<db-host>> --port=3306

If acecss not available for the host then provide access to the host using the following SQL

GRANT ALL PRIVILEGES ON *.* TO '<<db-user>>'@'<<hostname|ip>>' IDENTIFIED BY '<<password>>';

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/blog/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

Export single table in database

mysqldump --user=<<db-user>> --password=<<db-password> \
    --host=<<db-host> --port=3306 \
    --single-transaction --compress \
    --result-file=dumpt.sql \
    <<db-name> <<table-name>;

Add --no-create-info to ignore drop and create table statements Add --skip-add-locks to ignore lock the table before insert statements

to import:

mysql --user=<<db-user> --password=<<db-password> \
    --host=<<db-host> --port=3306 \
    <<db-name> < dump.sql;

Watch in Youtube

References