This Banner is For Sale !!
Get your ad here for a week in 20$ only and get upto 15k traffic Daily!!!

How to Backup locally a Remote Mysql Database on Linux




1) Be certain that the Mysql server can pay attention for connections out of your the Web

The Mysql server makes use of the MySQL Bind-Address instrument that allows to find out which connections the server listens.
For Debian-based distros:
The Mysql bind-address is explicitly configured in /and so forth/mysql/mysql.conf.d/mysqld/mysqld.cnf
For RedHat-based distros:
The Mysql bind-address must be be configured in /and so forth/my.cnf

There are three primary methods to configure bind-address:

  • bind-address = 127.0.0.1 which implies the server pay attention on solely connections from the server itself
  • bind-address = 0.0.0.0 which implies the server pay attention on any connections from the Web
  • bind-address = An outlined IP deal with or vary which implies the server will pay attention on connections from the outlined IP deal with or vary.

For safety causes, it might make sense to set the bind-address to an outlined deal with. Nonetheless, along with the bind-address instrument there’s a one other characteristic that set the place customers are allowed to attach from. We are going to set it after having configured the bind-address.

  1. Get your Public IP deal with (Ignore this step if you wish to let the server to pay attention on any connections over the Web)
curl ifconfig.me
Enter fullscreen mode

Exit fullscreen mode

2.Set the bind-address to pay attention on the specified community
For Debian-based distros:
For nano editor

nano /and so forth/mysql/mysql.conf.d/mysqld.cnf
Enter fullscreen mode

Exit fullscreen mode

For vim editor

vi /and so forth/mysql/mysql.conf.d/mysqld.cnf
Enter fullscreen mode

Exit fullscreen mode

Discover the bind-address line and set it both on
bind-address = Your IP deal with or bind-address = 0.0.0.0

For RedHat-based distros:
For nano editor

nano /and so forth/my.cnf
Enter fullscreen mode

Exit fullscreen mode

For vim editor

vi /and so forth/my.cnf
Enter fullscreen mode

Exit fullscreen mode

Beneath [mysqld]
Add bind-address = Your IP deal with or bind-address = 0.0.0.0

Now the bind-address is ready, we nonetheless can’t backup the info
base remotely

Image description



2) Permitting The Database to Get Accessed From Your Public IP Handle

1.Test what customers exist

Log in your Mysql Server

sudo mysql -u <user-name> -p
Enter fullscreen mode

Exit fullscreen mode

Then enter your password.

Now examine the customers registered within the database

SELECT Person, Host from mysql.person;
Enter fullscreen mode

Exit fullscreen mode

Check Database Users

Right here we will see solely a root person allowed to attach from 127.0.0.1 ==> IPv4 Localhost, ::1 ==> IPv6 Localhost, ip-172-31-84-33.ec2.inside ==> Non-public IP deal with allotted by AWS to this occasion; and localhost.

In consequence, there isn’t any person that may entry from the Web(0.0.0.0) to the server. So you should register a brand new person with a particular host (IP deal with)

Open a brand new tab in your terminal and sort:

curl ifconfig.me
Enter fullscreen mode

Exit fullscreen mode

Now return to the earlier tab and sort:

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, on this instance I wish to backup the pattern database.

Databases list

To take action, execute the next command

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

Enter fullscreen mode

Exit fullscreen mode

Mysql backup command
Test the file the place the database has been dumped

cat <file_name>.sql
Enter fullscreen mode

Exit fullscreen mode

The next image exhibits the start of the database
Database dumped

The next image exhibits some content material of the database; and we will see some Private Informations equivalent to (Age,First Identify,Surname,e mail deal with,Gender, Favourite Shade)
These Private Informations are fictive and been randomly created
Database dumped2

The Article was Inspired from tech community site.
Contact us if this is inspired from your article and we will give you credit for it for serving the community.

This Banner is For Sale !!
Get your ad here for a week in 20$ only and get upto 10k Tech related traffic daily !!!

Leave a Reply

Your email address will not be published. Required fields are marked *

Want to Contribute to us or want to have 15k+ Audience read your Article ? Or Just want to make a strong Backlink?