Move a MySQL Database from one host to another using Terminal

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-locks Do not add locks
  • --skip-opt Turn off options set by --opt
  • --verbose Verbose mode
  • --where Dump only rows selected by given WHERE condition
  • --xml Produce XML output

mysqldump filter options

  • --tables add table name arguments following the option to dump only those tables
  • --ignore-table exclude tables from dump --ignore-table=db_name.tbl_name
  • --no-data, -d dump only the CREATE TABLE statement for the table

19

Catégories:

Database

Tags:

TerminalMysqlDatabase