Importing and exporting databases is a common task in software development. You can use data dumps to back up and restore your information. You can also use them to migrate data to a new server or development environment.
In this tutorial, you will work with database dumps in MySQL or MariaDB (the commands are interchangeable). Specifically, you will export a database and then import that database from the dump file.
Prerequisites
To import or export a MySQL or MariaDB database, you will need:
A sample database created in your database server. To create one, follow “Creating a Sample Database” in our tutorial, “An Introduction to Queries in MySQL”.
Note: As an alternative to manual installation, you can explore the DigitalOcean Marketplace’s MySQL One-Click Application.
Step 1 — Exporting a MySQL or MariaDB Database
The mysqldump console utility exports databases to SQL text files. This makes it easier to transfer and move databases. You will need your database’s name and credentials for an account whose privileges allow at least full read-only access to the database.
username is the username you can log in to the database with
database_name is the name of the database to export
data-dump.sql is the file in the current directory that stores the output.
The command will produce no visual output, but you can inspect the contents of data-dump.sql to check if it’s a legitimate SQL dump file.
Run the following command:
head -n 5data-dump.sql
The top of the file should look similar to this, showing a MySQL dump for a database named database_name.
SQL dump fragment
-- MySQL dump 10.13 Distrib 5.7.16, for Linux (x86_64)
--
-- Host: localhost Database: database_name
-- ------------------------------------------------------
-- Server version 5.7.16-0ubuntu0.16.04.1
If any errors occur during the export process, mysqldump will print them to the screen.
Step 2 — Importing a MySQL or MariaDB Database
To import an existing dump file into MySQL or MariaDB, you will have to create a new database. This database will hold the imported data.
First, log in to MySQL as root or another user with sufficient privileges to create new databases:
mysql -u root -p
This command will bring you into the MySQL shell prompt. Next, create a new database with the following command. In this example, the new database is called new_database:
CREATE DATABASE new_database;
You’ll see this output confirming the database creation.
Output
Query OK, 1 row affected (0.00 sec)
Then exit the MySQL shell by pressing CTRL+D. From the normal command line, you can import the dump file with the following command:
mysql -u username -p new_database<data-dump.sql
username is the username you can log in to the database with
newdatabase is the name of the freshly created database
data-dump.sql is the data dump file to be imported, located in the current directory
If the command runs successfully, it won’t produce any output. If any errors occur during the process, mysql will print them to the terminal instead. To check if the import was successful, log in to the MySQL shell and inspect the data. Selecting the new database with USE new_database and then use SHOW TABLES; or a similar command to look at some of the data.
Conclusion
In this tutorial you created a database dump from a MySQL or MariaDB database. You then imported that data dump into a new database. mysqldump has additional settings that you can use to alter how the system creates data dumps. You can learn more about from the official mysqldump documentation page.
In our guide today, we are looking at how to install MySQL 5.7 on Ubuntu 20.04 (Focal Fossa) Server. MySQL is one of the most commonly used Database Management Systems. It uses the concept of relational databases and has a client/server architecture. It can be installed on various operating systems including Windows, CentOS and Debian among others.
Installing MySQL 5.7 on Ubuntu 20.04 (Focal Fossa)
The below steps describe how to install and configure MySQL 5.7 on Ubuntu 20.04. It start with adding APT repository with packages for MySQL then dives to the actual package installations and configurations.
Step 1: Add MySQL APT repository in Ubuntu
Ubuntu already comes with the default MySQL package repositories. In order to add or install the latest repositories, we are going to install package repositories . Download the repository using the below command:
Hit the y key to start installation of MySQL 5.7 on Ubuntu 20.04 Linux.
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libgdbm-compat4 libmecab2 libperl5.30 libtinfo5 mysql-common mysql-community-client perl perl-modules-5.30
Suggested packages:
perl-doc libterm-readline-gnu-perl | libterm-readline-perl-perl make libb-debug-perl liblocale-codes-perl
The following NEW packages will be installed:
libgdbm-compat4 libmecab2 libperl5.30 libtinfo5 mysql-client mysql-common mysql-community-client mysql-community-server mysql-server perl perl-modules-5.30
0 upgraded, 11 newly installed, 0 to remove and 35 not upgraded.
Need to get 58.1 MB of archives.
After this operation, 361 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Enter and re-enter root password when prompted
Step 3: Secure MySQL 5.7 Installation on Ubuntu 20.04
Run the command below to secure MySQL
$ sudo mysql_secure_installation
Press Enter. When prompted for password, provide the root password set above.
Answer the prompts as below:
Enter current password for root (enter for none): <Enter password>
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: Y
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Using existing password for root.
Estimated strength of the password: 25
Change the password for root ? ((Press y|Y for Yes, any other key for No) : d
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y
Thanks for using MariaDB!
Check MySQL version.
To confirm the installed version, first, connect to MySQL with the set root password.
$ mysql -u root -p
Run the below command to display version
$ SHOW VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.31 |
+-----------+
1 row in set (0.00 sec)
Step 4: Create MySQL User (Optional, testing only)
While still connected to MySQL, run the following commands to create a user:
CREATE USER 'user'@'%' IDENTIFIED BY 'MyStrongPass.';
GRANT ALL PRIVILEGES ON * . * TO 'user'@'%';
FLUSH PRIVILEGES;
exit
Step 5: Enable MySQL remote access (Optional)
By default, MySQL remote access is disabled. To enable it we need to edit mysqld.cnf file as below:
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
Look for the line ‘bind_address’ and change as below:
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
bind-address = 0.0.0.0
Save the file and restart mysql
$ sudo systemctl restart mysql
Allow remote connections through the firewall
$ sudo ufw allow from <remote_IP_address> to any port 3306
$ sudo ufw enable
To access the database from a remote machine, run the following command:
$ mysql -u user -h database_server_ip -p
You have successfully installed MySQL 5.7 on Ubuntu 20.04.
