How to Backup Locally a Remote Mysql Database on Linux
Tags: mysql, linux, security, networking
Published: 2022-03-16 20:22:31

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

The Mysql server uses the [MySQL Bind-Address](https://serversforhackers.com/c/mysql-network-security) 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





![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zqic79dxvfmd9t57fbp9.png)
## 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;
```


![Check Database Users ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dn858ulxi60my7udosoc.png)

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;
```

![Register New Mysql User](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wpvarqki1u6mkt32xfaq.png)

Now let see our databases

```
SHOW DATABASES;
```
And backup a database, in this example I want to backup the sample database.


![Databases list](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0dbf5s4ylmlt2lwaggaa.png)

To do so, execute the following command
```
sudo mysqldump --user <username> -h <IP_server> -p <database_name> > <file_name>.sql

```

![Mysql backup command ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tlb0h3ez5yd76i4ygjcx.png)
Check the file where the database has been dumped

```
cat <file_name>.sql
```
The following picture shows the beginning of the database 
![Database dumped](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gje4yzopbckbfrg64eib.png)

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**
![Database dumped2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fhhlgh4zvzgvry3975vp.png)