How to Backup Locally a Remote Mysql Database on Linux

1) Make sure the Mysql server can listen for connections from the Internet

The Mysql server uses the MySQL Bind-Address tool that enables to determine which connections the server listens.
For Debian-based distros:
The Mysql bind-address is explicitly configured in /etc/mysql/mysql.conf.d/mysqld/mysqld.cnf
For RedHat-based distros:
The Mysql bind-address has to be configured in /etc/my.cnf

There are three basic ways to configure bind-address:

• bind-address = 127.0.0.1 which means the server listen on only connections from the server itself
• bind-address = 0.0.0.0 which means the server listen on any connections from the Internet
• bind-address = A defined IP address or range which means the server will listen on connections from the defined IP address or range.

For security reasons, it would make sense to set the bind-address to a defined address. However, in addition to the bind-address tool there is a another feature that set where users are allowed to connect from. We will set it after having configured the bind-address.

1. Get your Public IP address (Ignore this step if you want to let the server to listen on any connections over the Internet)

curl ifconfig.me

2.Set the bind-address to listen on the desired network
For Debian-based distros:
For nano editor

nano /etc/mysql/mysql.conf.d/mysqld.cnf

For vim editor

vi /etc/mysql/mysql.conf.d/mysqld.cnf

Find the bind-address line and set it either on
bind-address = Your IP address or bind-address = 0.0.0.0

For RedHat-based distros:
For nano editor

nano /etc/my.cnf

For vim editor

vi /etc/my.cnf

Under [mysqld]
Add bind-address = Your IP address or bind-address = 0.0.0.0

Now the bind-address is set, we still cannot backup the data
base remotely

2) Allowing The Database to Get Accessed From Your Public IP Address

1.Check what users exist

Log in your Mysql Server

sudo mysql -u <user-name> -p

Then enter your password.

Now check the users registered in the database

SELECT User, Host from mysql.user;

Here we can see only a root user allowed to connect from 127.0.0.1 ==> IPv4 Localhost, ::1 ==> IPv6 Localhost, ip-172-31-84-33.ec2.internal ==> Private IP address allocated by AWS to this instance; and localhost.

As a result, there is no user that can access from the Internet(0.0.0.0) to the server. So you need to register a new user with a specific host (IP address)

Open a new tab in your terminal and type:

curl ifconfig.me

Now go back to the previous tab and type:

CREATE USER 'user_name'@'Your_Public_IP_Address' IDENTIFIED BY 'Your_Password';
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'Your_Public_IP_Address' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Now let see our databases

SHOW DATABASES;

And backup a database, in this example I want to backup the sample database.

To do so, execute the following command

sudo mysqldump --user <username> -h <IP_server> -p <database_name> > <file_name>.sql

Check the file where the database has been dumped

cat <file_name>.sql

The following picture shows the beginning of the database

The following picture shows some content of the database; and we can see some Personal Informations such as (Age,First Name,Surname,email address,Gender, Favorite Color)
Those Personal Informations are fictive and been randomly created