There can be multiple ways for MySQL/MariaDB database export and import during database server migration or database backup. The quickest seems to be the command-line one. It seems much faster than PHPMyAdmin or SequelPro clients. I am sharing Linux commands for MySQL/MariaDB export and import.
Export MySQL Database
MySQL data backup program mysqldump creates a dump file of SQL statements for table data and database schema.
Log into MySQL on Linux shell. Depending on your MySQL server settings, use
mysqldump -u username -p database_name > db_dump.sql
mysqldump does not show a success message. Do ls in the directory where you ran the command to find DB-dump.sql. You can include or exclude specific content into a dump and have the gzipped compression. For details visit MySQL dump: Database backup program resources.
Moving MySQL dump to a new server
In terminal use scp to move database backup.
scp user@oldhost:/home/user/db_dump.sql user@newhost:/home/user
For more details, check this SCP command post to move the file from a server to another using the terminal.
Depending on your convenience and database size you can move SQL dump via FTP, SCP, or simply move the dump to a public_html directory and access it on HTTP. On your destination server run a WGET command to copy the dump file.
You will need to create a new database and a new database user account aside from the root user.
Import MySQL Database
On the Linux shell of the destination server, move to the directory where you have the database SQL dump file and run this command.
mysqlimport -u username -p database_name < db_dump.sql
you can also import in MySQL command line, log in to MySQL command line and run
mysql> use database_name;
mysql > source db_dump.sql
or run this one command in Linux shell
mysql -u username -p database_name < db_dump.sql
mysqldump | A database backup program
Do not run mysqldump inside the MySQL console. The mysqldump is not a MySQL command. It is a separate program to be run in a Linux shell.
The mysqldump command can also generate output in CSV, other delimited text, or XML format. mysqldump is convenient and flexible as you can view and edit the output before restoring. However, restoring data can be slow because executing SQL statements involves disk I/O operations. The best use cases for mysqldump are small sites, WordPress blog database or during the development phase of a project. For large-scale databases using physical backup and restore is more appropriate.
mysqldump can also populate databases by copying data from one MySQL server to another:
mysqldump --opt db_name | mysql --host=remote_host -C db_name
mysqldump options
--opt Enabled by default, --opt is shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
-
--skip-add-locksDo not add locks -
--skip-optTurn off options set by--opt -
--verboseVerbose mode -
--whereDump only rows selected by given WHERE condition -
--xmlProduce XML output
mysqldump filter options
-
--tablesadd table name arguments following the option to dump only those tables -
--ignore-tableexclude tables from dump--ignore-table=db_name.tbl_name -
--no-data, -ddump only the CREATE TABLE statement for the table
I am also on Twitter. You can follow me there as I regularly tweet about my journey.
Top comments (1)
@waqar This is OK for small dataset. Does not apply for production environment where you have very large data sets.
msyqldumplocks the database, which means, no other query will be executed. The right way to decommision a node in production is to create a replication setup.