How To Make a Backup and Restore MySQL on Ubuntu 20.04 


    Created October 21, 2021
    Author Alex
    Category Backup	


When working with databases, you have to make backups. This tutorial will focus
on MySQL on Ubuntu 20.04. The process will be described in detailed commands and
also to secure your data.



 Copying your data from the Ubuntu server 

First, you need to be logged in as a user with root privileges. After successful
login, you need to check the list of databases. This can be done by entering a 
simple command:

mysql -u USERNAME

Further, after a successful login, you need to enter a special command that will
provide you with the entire list of databases. This command looks like this:

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| test_data          |
| important_db       |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.012 sec)


Then you can close the command line by typing exit;



 Using Mysqldump 

You may need to use a password for login. To do this, just add -p to the command
below. If you need to keep all databases, you can use the shortcut –all-databases 
instead of –databases names

mysqldump -u USERNAME --databases test_data important_db > database_dump.sql


Then the file is automatically generated. It will be named “database_dump.sql”.
Its content includes all data for restoring the databases that you specify.



 Protecting Your Backup Process 

An important recommendation: if the data in the database contains sensitive 
data, then you should definitely encrypt it before you save or start the process
of moving between servers. Run the first command below and then enter your 
password; it will not appear on your screen.

openssl enc -aes-256-cbc -pbkdf2 -in database_dump.sql -out database_dump.sql.enc
  enter aes-256-cbc encryption password:
  Verifying - enter aes-256-cbc encryption password:


rm database_dump.sql


In order for you to be able to decrypt your backup, you can use a special 
command, which is indicated below:
 
openssl enc -d -aes-256-cbc -pbkdf2 -in database_dump.sql.enc -out database_dump.sql
  enter aes-256-cbc encryption password:




 The process of moving a backup 

During the backup process, the .sql size can be bulky in size. This will 
negatively affect the file and data transfer process. Instead, you can use tools
such as rsync to exchange data directly between servers.

rsync -a ./database_dump.sql user@185.186.244.123:/tmp/

    185.186.244.123 – should be changed to your server address
    user is the username on the remote server
    tmp/ – should be replaced with your remote server directory

To restore your database, you can use the special mysql utility, an example is 
shown below:

mysql -u USERNAME