MySQL – Allow external access in Ubuntu and making a dump

Sometimes it is useful to allow external access to our database, a common use of this is to allow us to get a dump of database into our own machine.

Allow others IPs in MySQL

Let’s edit the configuration file of MySQL to allow the database to listen to external IPs

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

Change the bind-address directive to 0.0.0.0, it will allow any IP to access db.

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1 // change to 0.0.0.0
. . .

Now lets configure Ubuntu firewall to allow requests from external IPs at 3306 port, the port used by MySQL.

sudo ufw allow from **remote_IP_address** to any port 3306

This is the most secure way to do this, allow an specific IP, you can allow any IP but it is not recommended if you have sensitive data.

sudo ufw allow 3306

Now we can activate this by restarting mysql service.

sudo systemctl restart mysql

Create an authorized User

Now that we have a server that permits us to access the database, we need to create a user that can access this database through our machine.

Creating an user that should allow access from any host:

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Creating an user that should allow access from a specific host:

CREATE USER 'username'@'YOU.IP.HERE' IDENTIFIED BY 'password';

GRANT ALL ON *.* TO 'username'@'YOU.IP.HERE' IDENTIFIED BY 'password' WITH GRANT OPTION;

Now you should be able to access your mysql database from an external computer.

Dump database

mysqldump -h HOST -u USER -p TABLE --single-transaction --column-statistics=0 --quick --lock-tables=false > backup-$(date +%F).sql

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *