Remote MariaDB on Ubuntu 16.04 Tutorial

[Gordon55M]

I'm posting this in hopes it will help someone out. I'm sure the code isn't the greatest and could have security issues, but it worked for me in my prototype environment.

The Setup:

• 2 Digital Ocean 2 CPU/2GB RAM Droplets
  • VestaCP Webserver:
    • Public IP: 123.456.789.1
      Private IP: 987.654.321.1
    MariaDB Remote Database:
    • Public IP: 123.456.789.2
      Private IP: 987.654.321.2

Install MariaDB on Remote Database Server

Code: Select all

apt-get update && apt-get upgrade && apt-get dist-upgrade
sudo apt-get install software-properties-common
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.1/ubuntu xenial main'
sudo apt update

In this next step, you will be asked to enter a root password for MySQL Root User. Enter and confirm.

Code: Select all

sudo apt install mariadb-server

Now it's time to install PHPMyAdmin . During the process below you will have to choose Apache 2 (Space Bar) and select NO for the dbconfig-common question that pops up.

Code: Select all

sudo apt-get update
sudo apt-get install phpmyadmin php-mbstring php-gettext
sudo phpenmod mcrypt
sudo phpenmod mbstring
sudo service apache2 restart

To allow remote connections, we will need to edit the MySQL my.cnf file to change the bind address to the current server private IP address. Using the steps below, find the [mysqld] part in the my.cnf file and change the database IP on the line Bind Address = XX.XX.XX.XX to this database server private IP address of 987.654.321.2.

Code: Select all

sudo nano /etc/mysql/my.cnf

Restart MySQL Service

Code: Select all

sudo service mysql restart

Login to MySQL and Create VestaCP Remote User with Full Permissions

Code: Select all

mysql -u root -p
Create User vestacproot@'%' Identified By 'VestaAndCookies1';
Grant All On *.* To vestacproot@'%' With Grant Option;
FLUSH PRIVILEGES;
exit;

Restart MySQL Service

Code: Select all

sudo service mysql restart

Secure MariaDB

Code: Select all

mysql_secure_installation

Change php.ini if you want to modify upload sizes, memory limit, execution time, etc.

Code: Select all

sudo nano /etc/php/7.0/apache2/php.ini

Restart Apache

Code: Select all

sudo service apache2 restart 

Install VestaCP on Webserver

Code: Select all

apt-get update && apt-get upgrade && apt-get dist-upgrade
wget https://vestacp.com/pub/vst-install.sh
sudo apt-get install mysql-client
bash vst-install.sh --force --nginx yes --apache yes --phpfpm no --named yes --remi yes --vsftpd yes --proftpd no --iptables yes --fail2ban yes --quota no --exim no --dovecot no --spamassassin no --clamav no --mysql no --postgresql no --hostname panel.yoururl.com --email [email protected] --password ILikeCookies2

Setup Remote Database Connection in VestaCP using the MySQL user info created above with the MySQL Private IP Address.

Code: Select all

export VESTA=/usr/local/vesta/
/usr/local/vesta/bin/v-add-database-host mysql 987.654.321.2 vestacproot VestaAndCookies1
/usr/local/vesta/bin/v-list-database-hosts

Change php.ini if you want to modify upload sizes, memory limit, execution time, etc.

Code: Select all

sudo nano /etc/php/7.0/apache2/php.ini

Restart Apache

Code: Select all

sudo service apache2 restart 

ALL DONE!
I am still working on my PHPMyAdmin settings. I got it to work once before where the actual link in VestaCP took you to the correct PHPMyAdmin on the remote server. Otherwise everything works great in my experience and localhost is nowhere to be found!

[Falzo]

really great write up!

suggestion/things to consider:

- you could also use lighttpd or nginx on the remote db server instead of apache

- there is also a mariadb-client package you could go with on the vestacp server instead of mysql-client. this was installed by default through the vestacp setup script on debian jessie at least with "exim/dovecot yes" options (probably related to roundcube).

- if you intend to use mail and roundcube you either need to change the roundcube config to use the remote mysql or switch it to something like roundcube-sqlite3 (jessie-backports) to keep that config local. dpkg-reconfigure roundcube-core does a lot of magic ^^

- if you are trying to restore backups from another vesta install you need to make sure that you edit the db.conf inside the backup tar as it keeps an entry of the (former) database host. this might result in restore process trying to connect to localhost or something like that.
seems like there is no check or fallback in the restore routine if the given (former) mysql host really exists on the new server (should probably be a feature request)

- you might want to limit connections to port 3306 of your remote database server to the IP(s) of your vestacp servers with something like iptables

Code: Select all

iptables -A INPUT -p tcp --dport 3306 -s xx.xx.xx.xx -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -s yy.yy.yy.yy -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

- for the problem with the phpmyadmin url consider using a domain name instead of the IP when adding the database host with v-add-database-host.
vestacp uses this entry to generate the phpmyadmin url by simply adding /phpmyadmin to it.
if you have your phpmyadmin installation running under some other domain/path you can either change that by editing vesta files (wouldn't recommend cause might get lost on the next update) or put up an alias or redirect on the apache of your remote mysql-server...

[hdavis84]

How to utilize the private IP so you don't use bandwidth between servers while also being able to use phpmyadmin:

Example Private IP = 192.168.104.87
Example Domain = mysql.example.com

Create domain for remote server pointing to public IP

Edit /etc/hosts on VestaCP server to point that domain to the private IP

Code: Select all

192.168.104.87    mysql.example.com

Add the database

Code: Select all

v-add-database-host mysql mysql.example.com vestaroot password

Now your vestacp server will communicate with the remote database via the private IP and give you a working link to phpmyadmin on the remote database server

IF you have any issues, check out this Ubuntu Q&A post:
https://askubuntu.com/questions/29474/w ... file-entry

[rhyker2u]

Thanks for this guide!

[skamasle]

Hi

You also can install mysql-proxy and connect your server to localhost and redirect request from localhost:3306 to your remote mariadb server, is easyway if you run a lot off apps in server than connect to localhost, so you can add that option with mysql-proxy

[Gordon55M]

I saw a stack exchange post that seems to say that this project is abandoned.

https://askubuntu.com/questions/844143/ ... untu-16-04

Thoughts? Perhaps proxysql is the way to go? You have any steps you would use to install and configure either, I'd love to try it out and would modify the tutorial as needed.

[skamasle]

I saw a stack exchange post that seems to say that this project is abandoned.

https://askubuntu.com/questions/844143/ ... untu-16-04

Thoughts? Perhaps proxysql is the way to go? You have any steps you would use to install and configure either, I'd love to try it out and would modify the tutorial as needed.

Yes is obsolete and you have orther alternatives -> iptables forwarding mysql port or haproxy

Of course this is not needed if you install vesta with remote port or if you use some host in your actual config ( like some subdomain and not localhost ) but if you want externalize mysql and you have much web sites you can still connect to localhost and then forward traffic with haproxy, iptables, mysqlproxy or any other method, that's only alternatives

[dichvuseo]

Hi

You also can install mysql-proxy and connect your server to localhost and redirect request from localhost:3306 to your remote mariadb server, is easyway if you run a lot off apps in server than connect to localhost, so you can add that option with mysql-proxy

Please tell me If I used Maria DB, I can change port 3008.