LowEndBox - Cheap VPS, Hosting and Dedicated Server Deals

MySQL on CentOS 7 (MariaDB)

lowendtutorial

Welcome back to yet another tutorial in the CentOS 7 series! This time I will (again) continue where I left of last time, with the installation of PHP (specifically, PHP-FPM). The last tutorials are a prerequisite for this one, so if you haven’t followed those yet, now is the time to do so.

We’re going to install MySQL to make our server fairly complete, that is for a large number of PHP-based applications (like WordPress, MyBB, or WHMCS). MySQL, however, is no longer the database of choice for Linux distributions. The fact that it was purchased by Oracle was something most people could live with, but when parts of the source code were becoming private they crossed a line.

Thankfully, the original founder of MySQL jumped in and started MariaDB. MariaDB started out as a “fork” of MySQL and has since been adding features. The nice thing is that it’s completely binary compatible, meaning that all the stuff you can do with MySQL you can do with MariaDB, and more! Therefore MariaDB is nowadays the default choice for many Linux distributions. This means it’s also available on CentOS.

With the theory explain, let’s get started!

Preparing your environment

First let’s start by ensuring your system is up-to-date. This will not only update any packages that may have an update pending, but will also update the repository caches ensuring that any packages added meanwhile have been added:

sudo yum update

When is asks you to continue, please type ‘y’ to ensure everything gets updated properly.

With that out of the way there’s actually nothing else to do before we can start with the installation, so let’s move ahead!

Installation

To install MariaDB, run the following command:

sudo yum install mariadb-server

It again asks you if you want to continue. Several other packages have been selected as well as dependencies. This includes ‘mariadb’ which is the client rather than the server. Type ‘y’ again to start the installation.

Once the installation is finished, MariaDB will have been installed but is not yet running. In order to start it, type:

sudo systemctl start mariadb

To ensure it also starts automatically with the server, run the following command:

sudo systemctl enable mariadb

So, now you’ve got MariaDB running but other than connecting to it, you can’t really use it from PHP yet because we haven’t installed the proper package for that. So, in case you want to connect to MariaDB from PHP (big chance that you will), run the following command:

sudo yum install php-mysql

This installs the proper MySQL packages for PHP (including php-pdo, which is for PDO –  a database abstraction layer). When done, run:

sudo systemctl restart php-fpm

This ensure that PHP-FPM also gets these recent changes.

Well, that’s it! If you now put an application (say, WordPress) into the directory mentioned in the NGINX tutorial and run the installer, you should be all good!


With the basics now covered, it’s time for the next step: multiple websites per server. The next tutorial will show you how that’s done!

mpkossen

8 Comments

  1. MKV:

    If one is using a true lowendbox with limited resources (especially memory) it might be a good idea to add a couple of settings to /etc/my.cnf or /etc/my.cnf.d/ to save memory. The least one can do is add

    performance_schema=off

    under section [mysqld]. Performance schema takes up a HUGE amount of available memory. And I really mean HUGE, up to something like 75% of available memory on a 512MB box as I have seen. Another setting that might be handy if one is using InnoDB tables and does not want to see the ibdata growing and never shrinking is

    innodb_file_per_table=1

    Using that settings deleting a database will also free most of the space. Otherwise the space is gone. There are other settings as well and it is worth googling for “mysql configuration low memory”.

    October 19, 2015 @ 9:30 pm | Reply
  2. Thanks for tuts, right now i’m prefer mariadb only for my server :D

    October 20, 2015 @ 9:44 am | Reply
  3. John M.:

    This one’s absolutely useless. Might as well name the tutorial “Handling services in CentOS 7”. You could pretty much replace “MariaDB” with any other package/service and it would be another “tutorial”.

    What about the most basic task after installing MariaDB/MySQL? Securing it. Easiest way is to run “mysql_secure_installation”.

    Also, since a lot of VPSs come with 256MB/512MB of RAM, the default MariaDB config isn’t really going to cut it. It really needs some optimization (for example, disabling InnoDB; tuning caches; etc).

    Really lazy, Maarten.

    October 20, 2015 @ 3:29 pm | Reply
    • Kiko:

      Hey John, could you please point us to this secured and optimized mysql ?

      Thanks

      October 21, 2015 @ 7:54 pm | Reply
      • John M.:

        There’s loads of tutorials and manuals on the internet, although things can get really confusing.

        If you just want to get started quickly, I’d suggest you to get mysqltuner (google it). I think a good starting point would be something like this:

        bind-address=127.0.0.1
        skip-innodb
        default_storage_engine=MYISAM
        key_buffer=4M
        max_connections=15
        query_cache_size=8M
        tmp_table_size=32M
        max_heap_table_size=32M
        thread_cache_size=8
        max_allowed_packet=1M
        table_cache=32

        Run mysqltuner, look at the recommendations and adjust accordingly. If you have the time, read manuals and learn what those values actually mean.

        For securing, run

        mysql_secure_installation

        after installing/starting. Should be enough if you’re not thinking of enabling remote access.

        October 22, 2015 @ 8:28 am | Reply
      • John M.:

        Also, ignore all those LEB-MySQL-optimization etc scripts, most of them set some of the values too low. There’s no need to, for example, set the key_buffer to 512KB or what ever those scripts set it to.

        October 22, 2015 @ 8:34 am | Reply
    • MKV:

      Disabling InnoDB may not always be an option because some applications might need it. I believe Roundcube webmail, for instance neeeds it and I have seen many others. It also has quite a few benefits from an appication programmers point of view. MyISAM is not a very reliable engine, either.

      However, when using InnoDB in a low-end box the

      innodb_file_per_table=1

      setting should definitely be added to the configuration. And one should NEVER EVER have the performance_schema on, always off, like I wrote above. So

      performance_schema=off

      is the way to go.

      Just like you said it is easy to tune the wrong parts and key_buffer is definitely one of them. If one needs to tune such important settings to ridiculously low value one should consider switching to a VPS with a bit higher memory.

      October 22, 2015 @ 5:07 pm | Reply
  4. I’m pretty sure that mysql has been replaced by MariaDB…

    http://www.livetv.pk/hum_tv.html

    November 13, 2015 @ 6:01 am | Reply

Leave a Reply

Some notes on commenting on LowEndBox:

  • Do not use LowEndBox for support issues. Go to your hosting provider and issue a ticket there. Coming here saying "my VPS is down, what do I do?!" will only have your comments removed.
  • Akismet is used for spam detection. Some comments may be held temporarily for manual approval.
  • Use <pre>...</pre> to quote the output from your terminal/console, or consider using a pastebin service.

Your email address will not be published. Required fields are marked *