MySQL is an relation database management system to storing data in table format. It is an opensource database server available to install on variety of operating systems
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:
mysqldump -u root -p database_name > db_backup.sql
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
-- MySQL dump 10.13 Distrib 8.0.23, for Linux (x86_64) -- -- Host: localhost Database: mysql -- ------------------------------------------------------ -- Server version 8.0.23 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */;
Next, Run the following command to view the last line from the backup file.
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */; /*!40101 SET [email protected]_COLLATION_CONNECTION */; /*!40111 SET SQL_NOT[email protected]_SQL_NOTES */; -- Dump completed on 2021-02-02 17:07:24
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:
mysqladmin -u root -p create new_database_name
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:
mysql -u root -p new_database_name < db_backup.sql
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.
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.