How do i import and export mysql database in ubuntu?
|
In this post, we will focus on the process to export and import databases in MySQL and MariaDB in order to create a backup. There is not the slightest doubt that backups are essential to be protected against incidents such as data losses or cyberattacks. Therefore, when backing up websites and applications, it is important to keep in mind databases (DB) as well. So that, in case of a contingency, databases can be restored any minute. Show
So, if you want to learn how to import and export MySQL/MariaDB databases using data dumps, keep reading. We will also explain the steps to restore backups, which is easier than it might seem. This tutorial applies both to MySQL and MariaDB databases, as the commands are interchangeable between both database management systems. Previous requirements to export and import databasesTo begin with, it is interesting to know the necessary requirements to import and export MySQL or MariaDB databases:
Exporting a MySQL or MariaDB databaseFor exporting the database, you can use the mysqldump -u username -p database_name > dump_filename.sql
That command will not generate any visual output. So, to verify the SQL copy has been done correctly, you can inspect the generated file in order to make sure it is a SQL copy. For doing so, you can use the following command: head -n 5 dump_filename.sql This command should return something like this: -- MySQL dump 10.13 Distrib 8.0.28, for Linux (x86_64) -- -- Host: localhost Database: database_name -- ------------------------------------------------------ -- Server version 8.0.28-0 ubuntu 0.20.04.1 It is also possible to export one or several tables instead of the whole database. For doing so, you must indicate in the command the selection you wish to do. mysqldump -u username -p database_name table_name_1 table_name_2 table_name_3 > dump_filename.sql In this case, it is important to be especially careful with the relationships between the different registers. After the import, only the selected tables will be overwritten. Importing a MySQL or MariaDB databaseTo import a MySQL or MariaDB dump, the first thing to do is to create the database where the import will take place. For doing so, if you do not have a database manager, you need to connect to the database server as a “root” user. mysql -u root –p This will open the Shell of MySQL or MariaDB. Then, you will be able to create the database. mysql> CREATE DATABASE new_database; If everything works correctly, you will see something similar to this: Query OK, 1 row affected (0.00 sec) Once it has been created, you need to exit that Shell; for doing so, use mysql -u username -p new_database < dump_filename.sql
If there is any type of error during the import process, it will be displayed on the screen. As you can see, the process of exporting and importing a MySQL or MariaDB database is very simple. February 27, 2019April 4, 20210 In this article of MySQL series, we’ll learn to import & export DBs. When you work with SQL, you will often be taking a backup of your databases. You will also be importing previously taken backups. All of this is an integral part of learning MySQL. Exporting a DatabaseWe first need a database that can be exported. You can see in the image below that I have a database named theitstuff. We will be exporting it here. To export the database we need to open our command line and type the following command. mysqldump -uroot -proot theitstuff > theitstuff.sql; This will export out database into a file called theitstuff.sql in the same folder where the command line is opened from. The file that was created can be opened in some text editor of your choice and it should look like this, You can see that the contents of this file are nothing but SQL codes. We will now see how to import a previously exported database.  Importing a databaseIn the previous example, we exported a database. Now we will try to import it. Firstly we need to delete all tables from our current database; You can see in the image below that there are no tables in our current database. mysql -uroot -proot theitstuff < theitstuff.sql; You can see that the arrow sign has changed since this time data is being copied from the file to the database. Once you are done with this command you can see that the import has successfully worked. ConclusionDatabase imports and exports are very important concepts in MySQL. We have learned how to import and export a given database using the MySQL command line client. We also saw the export file is basically a big list of SQL commands and nothing else. I hope you understood this topic. If you have any queries regarding this topic, do let me know in the comment section below. How import MySQL database in Ubuntu terminal?Command line MySQL import. Type: mysql -u username -p database_name < file.sql.. The username refers to your MySQL username.. database_name refers to the database you want to import.. file. sql is your file name.. If you've assigned a password, type it now and press Enter.. How do I import and export a MySQL database?Method #1: Use phpMyAdmin. Log in to cPanel. ... . In the DATABASES section of the cPanel home screen, click phpMyAdmin: ... . In the left pane of the phpMyAdmin page, click the database that you want to export.. Click the Export tab.. Under Export method, confirm that Quick is selected. ... . Under Format, confirm that SQL is selected.. How do I export a database in Ubuntu?Exporting from MySQL. $ mysqldump -u my_username -p database_name > output_file_path.. $ mysqldump -u book_admin -p books > ~/backup/database/books. sql Enter password:. $ mysqlimport -u book_admin -p books_production ~/backup/database/books. sql Enter password:. How do I copy MySQL database from one computer to another in Ubuntu?1 Answer. To backup/save databases as SQL files, run mysqldump -uroot -p database_name > dumpfilename.sql. ... . Copy the dumpfilename.sql file over to the other system like any other regular file and import the database using this command: mysql -uroot -p database_name < dumpfilename.sql.. |
