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