Question

Please add tutorial for mysql remote database setup on ubuntu 16.04

Hi, i have already requested the same before and also few other peoples requested the same thing. i know theres not too much difference in 16.04 from 14.04 for this particular setup, but still want a complete guide so that we dont make any mistake. as many peoples are here not that much expert in managing own VPS.

Please add a fresh tutorial on same subject but for ubuntu 16.04 https://www.digitalocean.com/community/tutorials/how-to-set-up-a-remote-database-to-optimize-site-performance-with-mysql

Subscribe
Share

Hello.
Actually, looking into this today - I’ve determined that there are quite a bit of , significant changes in your 16.04 droplet that vary greatly from the Ubuntu 14 droplet MySQL remote setup.

This include changes to the location of the mysqld.cnf file,
the presense of the MySQL password file .digitalocean_password, and that UFW (the firewall) needs to be specifically opened up.

I’m currently working on a (maybe overcomplicated, but friendly) script to do all of this for you, as well as create a remote user in mySQL.

Please reply / advise if you’d like a copy of the script.

Also, after working on this for the past few hours - I’m pretty sure now I could do a tutorial on getting this setup done manually. Let me know if you’d like me to do a write-up/tutorial.


Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

@newbie

apt-cache search php7.0 --names-only

Will only find packages tagged as php7.0 or compatible with php7.0, it won’t install, update, or upgrade any packages. You’d have to do that manually. It’s a simplified way of searching packages.

Now if you run:

add-apt-repository -y ppa:ondrej/php

Then:

apt-get -y upgrade

Two things can happen, it’ll either upgrade all existing packages that you’ve installed or it’ll throw an error stating that something is compatible. It’s normally best to purge existing packages before adding a repository.

i.e.

apt-get -y purge php*

If there’s anything configuration wise that you need to backup, you need to do that before you run that command. Generally with PHP this isn’t the case, but worth noting.

As far as Ubuntu version, there’s really no need to upgrade from 16.04 to 16.10. Ubuntu 16.04 is the LTS, or Long-Term Support release, and it’s up to date and supported for 5 years.

When you deploy a server, or multiple servers, you should deploy with use-case in mind. If you’re constantly upgrading to one version or the next, unless it’s security related, you’re creating more work for yourself than needed.

My typical/standard deployment for a single server is Ubuntu 16.04, NGINX Mainline w/ a few modules (requires a source compile), PHP 7.1.x (which is in that repository I mentioned), MariaDB 10.1 or 10.2 (though I don’t recommend 10.2 right now as it’s an RC thus not 100%), and a few other pieces of software – depending on what I need.

You don’t want to deploy Ubuntu 16.04, install a stack, upgrade to 16.10, change the stack, downgrade X, Y, Z, etc constantly unless you’re 100% sure you’re able to troubleshoot the issues that can come along with it.

@newbie

Yes, the user that you create when running the command is only valid for that user and that host that is specified at the time of creation. If the IP changes, you need to remove the user and create a new one with the new IP or host.

You can update the user, of course, though I find it easier to just drop the user and recreate them.

@newbie

Looking over the guide that you’ve linked to, I can confirm that it should work just the same on 16.04 and 16.10 as it would on 14.04. The only differences would be with the version of PHP that you would be installing – Ubuntu 16.x uses PHP 7.x not PHP 5.x.

You can add a repository to bring back PHP 5.6.x support, which could be done using:

add-apt-repository -y ppa:ondrej/php && apt-get update

That being said, the guide could be simplified a little more. For example, running:

CREATE USER 'wordpressuser'@'localhost' IDENTIFIED BY 'password';

and

GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'localhost';

is the same as running:

grant all on wordpress.* to 'wordpressuser'@'localhost' identified by 'password';

You can also limit privileges using the same by replacing all with any combination, such as:

grant select,update on wordpress.* to 'wordpressuser'@'localhost' identified by 'password';

Using both commands is a good way to learn SQL, though it’s not needed.

With that said, is there a specific part of the guide you’re not comfortable with? If so, let me know. I’ll be more than happy to help you out.

Thanks for the suggestion. While I agree that this would be a great addition it was not a high priority update due to the fact that there are no major changes in how to enable remote access between 14.04 and 16.04. That being said, this should also make it an easy one to add since only some basic testing should be required and not a full rewrite. I am happy to pass this feedback along to our writing and editorial teams.