Please add tutorial for mysql remote database setup on ubuntu 16.04

March 29, 2017 3.5k views
MySQL Server Optimization Ubuntu 16.04
C4f1151075b447779af31e99d6cf70e2c6eb47ac
By:
newbie

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

1 comment
  • intensetech February 26, 2018

    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.

Log In to Comment
4 Answers
ryanpq MOD March 29, 2017

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.

Reply
  • newbie March 29, 2017

    @ryanpq
    really appreciate your attention here.

    u see, even though there is no major changes, but i really don't wanna take any chances when its for my production server.

    • ryanpq MOD March 29, 2017

      Completely understand and agree. Our writing team reports that this is already on their todo list but we don't have an ETA we can share.

jtittle1 March 29, 2017

@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.

Reply
  • newbie March 29, 2017

    @jtittle thanks but i would like to stick to php 7.0 instead of 5.6,
    ill contact u if i get into any issue for sure.
    really appreciate it.

    • jtittle1 March 29, 2017

      @newbie

      No problem -- as a general note, that repository works for PHP 5.6, 7.0, and 7.1, so you can use any of them. I generally use it as an alternative to Ubuntu's repositories as it tends to be more up to date and often contains more packages (such as php-redis, php-memcached, etc).

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

      Once you run the above, you can run:

      apt-cache search php5.6 --names-only

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

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

      ... and it'll show the corresponding packages.

      • newbie March 30, 2017

        @jtittle buddy,
        suppose i created a remote setup, then i took snapshot and later when i rebuild it, can i just change user privilege by giving the same command with correct private IP?

        GRANT ALL ON wordpress.* TO 'wordpressuser'@'Web_server_ip' IDENTIFIED BY 'password';

        or that will create another user keeping the old one.

        • jtittle1 March 30, 2017

          @newbie

          Creating one user will not remove another. You'd need to remove the specific user that was created manually using the CLI, phpMyAdmin, or Adminer.

          From the CLI, you can use:

          drop user 'username'@'host';

          Where username is the users' username and host is the host you specified, for example:

          drop user 'wordpress'@'localhost'
          • newbie March 30, 2017

            @jtittle
            i understand.
            i was talking about the same user but on different IP. like i granted access for one IP but some days later my Ip got changed. would i still need to remove old one and create the same user on different IP?

jtittle1 March 30, 2017

@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.

Reply
  • newbie March 30, 2017

    understood completely.
    appreciate it.

    one more thing from your previous comment,
    after adding 

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

    if i run

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

    will i get specifically updates on php7.0 even if i run

    sudo apt-get update && sudo apt-get upgrade && sudo apt-get dist-upgrade

    also by running 

    sudo apt-get dist-upgrade

    does it update ubuntu 16.04 to 16.10 also?

jtittle1 March 30, 2017

@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.

Reply
  • newbie March 30, 2017

    @jtittle
    no no, i dont want to update to 16.10. thats why i was asking that it will upgrade to 16.10 or not. because if it is, that will be problem for me as i want to stick to the LTS version.

    • jtittle1 March 30, 2017

      @newbie

      apt-get -y upgrade vs apt-get -y dist-upgrade

      apt-get -y upgrade will upgrade existing packages, but may not install new packages or the latest versions if those packages have additional dependencies. For example, if you're using 16.04 and a new kernel is released that can be installed by apt, using upgrade won't do the install as the kernel most likely has multiple dependencies that may not already be installed on your OS.

      apt-get -y dist-upgrade, however, will install new packages and their dependencies. Going back to the kernel as an example, where upgrade will say that X, Y, Z packages have been held back, dist-upgrade will install them.

      The same would apply to PHP. If, for example, you're using PHP 7.0.x and PHP 7.1.x is available, upgrade won't change your PHP version. Even though it can see that 7.1 is indeed available, it doesn't upgrade in that sense. It'll update 7.0.1, 7.0.2, 7.0.3, etc, but not major versions such as 7.1, 7.2, 7.3, etc.

      The same for MariaDB, upgrade won't upgrade you from 10.0 to 10.1, dist-upgrade, will.

Have another answer? Share your knowledge.

Related Questions