DEV Community

Cover image for How to Backup Locally a Remote Mysql Database on Linux
Aissa Laribi
Aissa Laribi

Posted on • Updated on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

For vim editor

vi /etc/mysql/mysql.conf.d/mysqld.cnf
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

For vim editor

vi /etc/my.cnf
Enter fullscreen mode Exit fullscreen mode

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

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
Enter fullscreen mode Exit fullscreen mode

Then enter your password.

Now check the users registered in the database

SELECT User, Host from mysql.user;
Enter fullscreen mode Exit fullscreen mode

Check Database Users

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Register New Mysql User

Now let see our databases

SHOW DATABASES;
Enter fullscreen mode Exit fullscreen mode

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

Databases list

To do so, execute the following command

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

Enter fullscreen mode Exit fullscreen mode

Mysql backup command
Check the file where the database has been dumped

cat <file_name>.sql
Enter fullscreen mode Exit fullscreen mode

The following picture shows the beginning of the database
Database dumped

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

Top comments (0)