MySQL on CentOS 7 (MariaDB)

Oct 19, 2015 @ 1:00 am

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!

Have You Checked Out RackNerd's TikTok Channel? Video Content That Brings Web Hosting Mainstream!

From Oracle Cloud to TOR to Telegram: Check Out LowEndTalk Tutorials!

LowEndBox BUSTED for Dealing in Mind Expanding Substances

Guest Post: Top Five Reasons Why Most Businesses Either Fail or Stay Stagnant by Dustin B. Cisneros,...

Ramdisks: Why You Might Enjoy One, Plus a Performance Puzzler

How To Compile The 3 Items Needed To Begin MIT’s Free Xv6 Online Operating System Course!

Author
Recent Posts

mpkossen

Maarten Kossen was the administrator of LowEndBox from 2013 to 2015, and brought many ideas and improvements to the website during his leadership. Today he is member of our community and LowEndTalk.

8 Comments

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
Ferri Sutanto:
Thanks for tuts, right now i’m prefer mariadb only for my server :D
October 20, 2015 @ 9:44 am | Reply
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
Jaheen100:
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