Importing and Exporting in MySQL databases aids in guaranteeing knowledge safety as importing databases is used for transferring knowledge between servers, alternatively, exporting databases helps in knowledge backup and restoration.
This information will present the process for:
Prerequisite: Set up of MySQL Server in Linux
Updating your system earlier than any set up is preferable so kind:
Set up the MySQL server by typing:
$ sudo apt set up mysql-server
To examine the put in model of MySQL, kind:
For beginning the companies of MySQL use this command:
$ sudo systemctl begin mysql
Exporting MySQL Databases in Linux
Lets export a database in Linux, however earlier than that, that you must hook up with a MySQL Server. Use this syntax to connect with a Native MySQL Server:
For this publish the username is “mysql”:
As it’s seen within the output above, the MySQL server is linked efficiently.
Use this SHOW command to see all of the out there databases:
To export the “mysql” database from the out there databases, first let’s create a listing utilizing this command:
Navigate to this listing by typing:
To export the database, use this syntax:
$ sudo mysqldump -u <username> -p <db-name> > <filename>.sql
Present your username, database title that you simply need to export and the title for a file through which you need to export the database. Use this syntax and hit enter, and sort the ls command to see if the database is exported efficiently or not:
Right here you possibly can see that file is efficiently created.
To see some knowledge of this file, kind:
$ head -n 5 <filename>.sql
Right here the info of the exported database is seen.
Importing MySQL Databases in Linux
To import the database, let’s hook up with the native server through the use of the syntax:
You will have efficiently logged in to your native database server.
To create a database, use this syntax:
> CREATE DATABASE <db-name>;
Present the title for the database:
To see the out there databases, kind this command:
Right here it’s seen that the database you created is accessible.
Let’s import the already present file with “.sql” extension containing the backup knowledge of the database, utilizing this syntax:
$ mysql -u <username> -p <new-database-name> < <filename>.sql
Present the required values in syntax:
Your database is imported efficiently because it requested for the password and gave error-free output.
Conclusion
Importing and Exporting databases is a vital activity for knowledge restoration and backup, knowledge migration, collaboration, or testing and debugging. Exporting creates a backup file, to export the database use this syntax “sudo mysqldump -u <username> -p <db-name> > <filename>.sql” whereas importing lets you make the info switch simple between servers, you possibly can import database from a file through the use of this syntax “mysql -u <username> -p <new-database-name> < <filename>.sql”.
