Page 1 of 1

Remote MariaDB on Ubuntu 16.04 Tutorial

Posted: Tue Mar 28, 2017 9:22 pm
by 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!

Re: Remote MariaDB on Ubuntu 16.04 Tutorial

Posted: Wed Mar 29, 2017 7:45 am
by 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...

Re: Remote MariaDB on Ubuntu 16.04 Tutorial

Posted: Wed Sep 06, 2017 9:00 am
by 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

Re: Remote MariaDB on Ubuntu 16.04 Tutorial

Posted: Sun Mar 18, 2018 12:36 pm
by rhyker2u
Thanks for this guide!

Re: Remote MariaDB on Ubuntu 16.04 Tutorial

Posted: Sun Jan 13, 2019 6:49 pm
by 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

Re: Remote MariaDB on Ubuntu 16.04 Tutorial

Posted: Thu Jan 17, 2019 8:48 pm
by 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.

Re: Remote MariaDB on Ubuntu 16.04 Tutorial

Posted: Sun Jan 20, 2019 4:45 pm
by skamasle
Gordon55M wrote:
Thu Jan 17, 2019 8:48 pm
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

Re: Remote MariaDB on Ubuntu 16.04 Tutorial

Posted: Sat Apr 03, 2021 3:08 pm
by dichvuseo
skamasle wrote:
Sun Jan 13, 2019 6:49 pm
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.