In case of mysql database migration, you can easily create a dump of database and restore it on target database server. MySQL server provides console utilities to export and import databases. This tutorial help you to export MySQL database using system console. Also helped you to restore database from dump file.
Step 1 — Export MySQL Database
Use mysqldump command line utility to perform a database backp. Which makes the process more easier to transfer database to other system. In order to export database, you need database’s name and login credentials with at least read-only privileges to the databases. Let’s export your database using mysqldump: Here:
root – is the username to login to database server database_name – is the name of the database to export db_backup.sql -is the text file name, that will stores the output
The above command will run silently with no output on screen. If any errors occur during the export process, mysqldump will print them to the screen.
Step 2 — Verify Backup File
Let’s, verify the database dump file created in above step. First make sure there is no error displayed on screen with mysqldump command. Next, Run the following command: This will show you the database details like below Next, Run the following command to view the last line from the backup file. The last line must contain a message like “Dump completed” followed by the date time.
Step 3 — Import MySQL Database
Before importing the data from backup file, make sure to create database on database server. You can use “mysqladmin” console command to create a new database. To create a new database, execute: Enter mysql user password to complete process. This will create a database in mysql server. Next, you can import the dump file using “mysql” console command. The command will be like below: Here:
root - is the username to access database server database_name - is the name of the newly created database db_backup.sql -is the dump file name taken from source database server.
On successfuly command execution, you will get the command prompt back without any message. In case of any error occurs with restore process, the error message will be printed on terminal screen. Now, you can connect your database and access database tables from mysql shell.
Conclusion
In this tutorial, you have learned to create dump of mysql database. Then create a new database on destination and restore from backup file. You can visit mysqldump official documentation page to read more about this command.
