Skip to content

Mysql Backup and Restore (Dump and Import)

Dump

MySQL
mysqldump -u username -p database_name > data-dump.sql

Dump from remote machine

MySQL
mysqldump -h host -u username -p database_name > data-dump.sql

Dump using different port (3306 is default)

MySQL
mysqldump -h host -u username --port=12345 -p database_name > data-dump.sql

Dump From AWS RDS

MySQL
mysqldump --hex-blob --skip-extended-insert --triggers --routines --events -h prod-clone.xxxxxxxxxxx.eu-west-1.rds.amazonaws.com schema_name > /opt/dump/schema-$(date +%F).sql

Dump Tables Only

MySQL
mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql

Import

First create database and then import *.sql dump

MySQL
mysql -u username -p new_database < data-dump.sql

Import to remote machine

MySQL
mysql -h host -u username -p new_database < data-dump.sql

-u user | (db user)

-p password | (ask for password)

-h host | (FQDN or IP)

--port=12345 or -P 12345 | change port

Import Tables

Dump Tables Only

Bash
mysql -h xxx  -u... -p... mydb t1 t2 t3 < mydb_tables.sql

Import from AWS RDS

  • Remove definer first
Bash
sed -i -e 's/DEFINER=`root`@`localhost`//g' dump.sql
  • Then simple import