In this tutorial we will explain couple of ways how to export one or all MySQL databases from one server and import to another server. We will also explain how to export MySQL database in a file for backup.
To export database we will using mysqldump command.
In this example we will export one database with name mydatabase. You need to run following command:
|
1 |
mysqldump -u mysqluser -p mydatabase > mysqldatabase.sql |
If you want to export all databases from one server:
|
1 |
mysqldump -u root -p --all-databases > all-databases.sql |
To compress exported database:
|
1 |
mysqldump -u root -p --all-databases | gzip > all-databases.sql.gz |
If your database use events and routines, to export use following command:
|
1 |
mysqldump -AER -u root -p > all-databases.sql |
Very useful command if you need to export all databases separately (every database in separate file) except databases information_schema, performance_schema and mysql:
|
1 |
mysql -s -r -u root -pRootPassword -e 'show databases' | grep -v information_schema | grep -v performance_schema | grep -v mysql | while read db; do mysqldump -u root -pRootPassword $db -r /root/${db}.sql; [[ $? -eq 0 ]] && gzip /root/${db}.sql; done |
Importing one database to another server:
|
1 |
mysql -u root -p mydatabase < mysqldatabase.sql |
To import all databases:
|
1 |
mysql -u root -p < all-databases.sql |
That’s it. You now know how to export and import MySQL databases.
