Introduction
In terms of visualizing and understanding a database server from a very fundamental level, we can conceptualize it basically as a case of beer. The case represents the server itself, in this case (no pun intended) MariaDB, while the individual bottles inside represent the individual database containers.
There can be many, many individual database containers within a single database server or just a single very large one. When a database container is created, it’s empty, there’s nothing in it and it is essentially of little value. Once we start adding data it becomes more valuable over time due to the time and effort put into filling it, which is often irreplaceable.
Once the database has data, this can be visualized as a sort of “ship in a bottle”, where someone has put great effort into constructing the model and the time and effort is irreplaceable.
Install MariaDB using yum with Centos 7, RHEL 7 or Fedora or, alternatively, use the MariaDB Repo Configuration Tool for your distro.
Doing the Work
- Install the required packages
- Start the MariaDB service and verify it’s running (note: if you’ve installed MariaDB from the Maria repos, the service will be called “mysql“, if you’ve installed it from the official centos repos, it will be called “mariadb“.
- Login as root to the MariaDB server command line interface.
- Delete all users who are not root.
- Set root password.
- Change root username to something less known.
- Removed anonymous (passwordless) access to the database server.
- Add a new DBA (Database Administrator).
- Create a new database container.
- Add a new admin user to a specfic database container only.
- Flush privileges
yum install mariadb mariadb-server mariadb-bench mariadb-libs
This should pull all the dependencies you’ll need and install all the packages. Note: the package mariadb-libs is no longer built as of Fedora 28, this is now included in the package mariadb-connector-c. See Bugzilla article.
C6: service mysql start
C6: service mysql status
Output:
SUCCESS! MySQL running (24476)
C7: systemctl start mariadb.service
C7: systemctl status mariadb.service
Output:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | mariadb.service - MariaDB database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled) Active: active (running) since Wed 2015-12-09 20:42:48 GMT; 18s ago Process: 23132 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS) Process: 23052 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS) Main PID: 23131 (mysqld_safe) CGroup: /system.slice/mariadb.service ├─23131 /bin/sh /usr/bin/mysqld_safe --basedir=/usr └─23288 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock Dec 09 20:42:46 quetzal.co.uk mariadb-prepare-db-dir[23052]: The latest information about MariaDB is available at http://mariadb.org/. Dec 09 20:42:46 quetzal.co.uk mariadb-prepare-db-dir[23052]: You can find additional information about the MySQL part at: Dec 09 20:42:46 quetzal.co.uk mariadb-prepare-db-dir[23052]: http://dev.mysql.com Dec 09 20:42:46 quetzal.co.uk mariadb-prepare-db-dir[23052]: Support MariaDB development by buying support/new features from MariaDB Dec 09 20:42:46 quetzal.co.uk mariadb-prepare-db-dir[23052]: Corporation Ab. You can contact us about this at sales@mariadb.com. Dec 09 20:42:46 quetzal.co.uk mariadb-prepare-db-dir[23052]: Alternatively consider joining our community based development effort: Dec 09 20:42:46 quetzal.co.uk mariadb-prepare-db-dir[23052]: http://mariadb.com/kb/en/contributing-to-the-mariadb-project/ Dec 09 20:42:46 quetzal.co.uk mysqld_safe[23131]: 151209 20:42:46 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'. Dec 09 20:42:46 quetzal.co.uk mysqld_safe[23131]: 151209 20:42:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql Dec 09 20:42:48 quetzal.co.uk systemd[1]: Started MariaDB database server. |
1 2 3 4 5 6 7 8 | [root@quetzal ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.0.21-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
1 2 | MariaDB [(none)]> delete from mysql.user where not (host="localhost" and user="root"); Query OK, 5 rows affected (0.00 sec) |
1 2 | MariaDB [(none)]> set password for 'root'@'localhost' = password('strong_password_here'); Query OK, 0 rows affected (0.00 sec) |
1 2 3 | MariaDB [(none)]> update mysql.user set user="dbadmin" where user="root"; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
1 2 | MariaDB [(none)]> delete from mysql.user where User = ''; Query OK, 0 rows affected (0.00 sec) |
1 2 | MariaDB [(none)]> grant all privileges on *.* TO 'warren'@'localhost' identified by 'strong_password_here' with grant option; Query OK, 0 rows affected (0.00 sec) |
1 2 | MariaDB [(none)]> create database local_host; Query OK, 1 row affected (0.00 sec) |
1 2 | MariaDB [(none)]> grant all privileges on local_host.* TO 'local_admin'@'localhost' identified by 'strong_password_here'; Query OK, 0 rows affected (0.00 sec) |
1 2 | MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) |
Extra Security
/etc/my.cnf.d/server.cnf/
1 2 3 4 5 6 7 8 9 10 11 | [mariadb] # Disabling symbolic-links is recommended to prevent assorted security risks # symbolic-links=0 # Disable LOAD DATA LOCAL INFILE, prevent remote injections # local-infile=0 # If the database server will be used only by locally installed applications, we can freely disable listening on port 3306 # # This will limit possibilities of attacking the database server by direct TCP/IP connections from other hosts. # # Local communication will be still possible throw the mysql.sock unix socket. # skip-networking |