About Relational and NoSQL Databases
Relational Databases (RDBMs) store data with some natural links between the data sets, usually in a parent-to-child relation. Examples of this is Customers in a Web Shop, Employees in a Company or Products in a Category. In these examples there exist a natural many-to-one relation (multiple Products in a Category). There can also be many-to-many relations, such as User Roles where multiple users in an application can hold multiple roles, such as Administrator, Business User and Analytic User. The language used for querying the database and manipulating the data is SQL, which has many flavors.
Flat Databases, where NoSQL is the most popular subset, is basically key-value lookup tables. As long as you know the key of the object you want to fetch, you can retrieve it from the Server. This is considerably faster than the relational database, but does not offer the same data structures. It is very useful as a Item Store, for fast retrieval of often fetched objects.
Since this is a Virtual LAMP setup, we'll install the MySQL Relational Database Manager. It started of as a Open Source project, later purchased by Oracle, but till available under GPL Licence. However, the MySQL packages that come with CentOS 6 is older than our universe itself, you we will install a more modern version from packages.
- Shared components (MySQL-shared-5.6.24-1.el6.x86_64.rpm)
- Compatibility Libraries (MySQL-shared-compat-5.6.24-1.el6.x86_64.rpm)
- Client Utilities (MySQL-client-5.6.24-1.el6.x86_64.rpm)
- MySQL Server (MySQL-server-5.6.24-1.el6.x86_64.rpm)
Download them directly to your instance using wget or curl, or download them to your local computer, and transfer them to the LAMP instance using an auto-share (see Step 4).
[root@centos66 ~]# yum list installed | grep mysql
If the script output is listing an installed package called "mysql-libs" with version 5.1, you should remove this package first.
[root@centos66 ~]# yum remove mysql-libs
Now you can install the downloaded packages. Go to the folder where you downloaded the RPM-files, and execute:
[root@centos66 mysql-5.6.24]# yum install MySQL-*.rpm
Time to start the MySQL server!
[root@centos66 mysql-5.6.24]# service mysql start
Make sure that the database starts automatically, when the server is rebooted
[root@centos66 mysql-5.6.24]# chkconfig --level=345 mysql on
[root@centos66 mysql-5.6.24]# cat /root/.mysql_secret
# The random password set for the root user at Mon May 4 13:26:51 (local time): 4PrWvJa8
Now when the MySQL server is installed, and you know the temporary root password, you can start the post-installation configuration. The following step will give you the chance to change the temporary password (and you really should!), as well as removing test databases and anonymous access (which you really should do as well!).
Start the configuration by:
[root@centos66 mysql-5.6.24]# /usr/bin/mysql_secure_installation
You should answer Y (yes) on all questions given:
- Change the root password: Y
- Remove anonymous users: Y
- Disallow root login remotely: Y
- Remove test databases and access to it: Y
- Reload privileges tables now: Y
Create Application User
[root@centos66 ~]# mysql -u root -p
To create a database schema for your application, use:
mysql > CREATE DATABASE demoapp DEFAULT CHARSET utf8;
Now you need to create a user for your web application- Since the web application is located on the same server instance as the database itself, you should only allow access from the IP-address "127.0.0.1", which is the server itself and nothing more. Never allow a user to access the database from more IP-addresses than needed. Here, less-is-more is absolute key!
To create a user called 'app_demoapp' which can only connect from the server instance itself, with the username "app_demoapp" and a very secret password:
mysql> CREATE USER 'app_demoapp'@'127.0.0.1' IDENTIFIED BY 'mySecretPassword123';
Next, the user needs some rights to access the database schema. Don't be lazy and just grant ALL PRIVILEGES. Instead start by granting the user a limited set of privileges, and only on the specific application schema, and never globally.
This is a good initial privilege set to grant to an application user:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES ON demoapp.* TO 'app_demoapp'@'127.0.0.1';
If the Web Application does not work, you should investigate what other privileges which the application might require. Consider this list to find the proper privilege to grant. However, also be skeptic to applications which requires very liberal privileges.
In the Part 8 of the article, we will install and configure the PHP scriptt language, and cover some nice-to-have additions.