RHCE Objectives: Database Services
- Install and configure MariaDB
- Backup and restore a database
- Create a simple database schema
- Perform simple SQL queries against a database
Installing MariaDB is easy enough; yum install mariadb. This will install the main files, server, and libs.
mariadb-5.5.41-2.el7_0.x86_64
mariadb-server-5.5.41-2.el7_0.x86_64
mariadb-libs-5.5.41-2.el7_0.x86_64
Standard configuration by running mysql_secure_installation and following the prompts.
Use the systemctl commands to enable and start the database.
systemctl enable mariadb.service
systemctl start mariadb.service
systemctl status mariadb.service
To log in, simply type in mysql. If you’ve set up a password for the root user, you’ll need to pass -u root -p or –user=root -p to log in.
Database stuff is pretty easy if you’ve done anything with it.
MariaDB [(none)]> create database username;
Query OK, 1 row affected (0.00 sec)
Piece of cake.
You’ll want to create a table (schema) for querying to satisfy the last requirement.
MariaDB [(none)]> use database username;
Database changed
MariaDB [username]> create table users (
-> usr_id int(10) not null auto_increment,
-> usr_last char(30) not null default ”,
-> usr_first char(30) not null default ”,
-> usr_name char(30) not null default ”,
-> usr_phone char(30) not null default ”,
-> usr_email char(255) not null default ”,
-> primary key (usr_id)
-> );
Query OK, 0 rows affected (0.04 sec)
And you’ll need to add some data
MariaDB [cschelin]> insert into users set usr_id = null,usr_last=’Lastname’,usr_first=’Firstname’,usr_name=’FLastnam’,usr_phone=”303-555-1212″,usr_email=”user@testing.com”;
Query OK, 1 row affected (0.02 sec)
Okay, to perform a simple query, run the following command:
MariaDB [cschelin]> select * from users;
+——–+———-+———–+———-+————–+——————+
| usr_id | usr_last | usr_first | usr_name | usr_phone | usr_email |
+——–+———-+———–+———-+————–+——————+
| 1 | Lastname | Firstname | FLastnam | 303-555-1212 | user@testing.com |
+——–+———-+———–+———-+————–+——————+
1 row in set (0.00 sec)
Again, piece of cake.
Backing up a database is simple as well:
mysqldump --user=root -p username
Enter password:
-- MySQL dump 10.14 Distrib 5.5.41-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: username
-- ------------------------------------------------------
-- Server version 5.5.41-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `users`
--
DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
`usr_id` int(10) NOT NULL AUTO_INCREMENT,
`usr_last` char(30) NOT NULL DEFAULT '',
`usr_first` char(30) NOT NULL DEFAULT '',
`usr_name` char(30) NOT NULL DEFAULT '',
`usr_phone` char(30) NOT NULL DEFAULT '',
`usr_email` char(255) NOT NULL DEFAULT '',
PRIMARY KEY (`usr_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `users`
--
LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,'Lastname','Firstname','FLastnam','303-555-1212','user@testing.com');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2015-08-11 10:06:22
And reloading the data is easy as well. You’ll need to recreate the database first though.
MariaDB [(none)]> drop database username;
Query OK, 1 row affected (0.03 sec)
MariaDB [(none)]> create database username;
Query OK, 1 row affected (0.00 sec)
Exit out and restore your backup. The thing to note is that the data file needs to be where mariadb can get to the file. Saving it to your home directory fails because of the access restrictions. I save to /var/tmp for this but generally I have a mariadb directory in /var/tmp for the daily backups.
mysql –user=root -p username < username.sql
Once done (and after entering your password), log in to mariadb, use username, show tables, select * from users. Done!
