Managing MariaDB

Creating database environment

• yum -y install mariadb mariadb-libs mariadb-test
• systemctl start mariadb
• systemctl enable mariadb
• mysql_secure_installation
• set mysql root password

Creating a Database

• mysql -uroot -p
• create database friends;
• use friends;
• MariaDB [friends]> create table name (FirstName VARCHAR(40), LastName VARCHAR(40), age INT, year INT);
• MariaDB [friends]> insert into name (FirstName,LastName,age,year) values ('Ion','Rivera',35,2010);
• MariaDB [friends]> insert into name (FirstName,LastName,age,year) values ('Syl','Carrion',35,2010);
• MariaDB [friends]> insert into name (FirstName,LastName,age,year) values ('Harold','Gray',35,2010);

MariaDB [friends]> select * from name;

+-----------+----------+------+------+

| FirstName | LastName | age  | year |

+-----------+----------+------+------+

| Ion       | Rivera   |   35 | 2010 |

| Syl       | Carrion  |   35 | 2010 |

| Harold    | Gray     |   35 | 2010 |

+-----------+----------+------+------+

3 rows in set (0.01 sec)

Managing User

• MariaDB [(none)]> create user erick@localhost identified by '123456';
• MariaDB [mysql]> select user from user where user='erick';
• MariaDB [mysql]>use friends;
• MariaDB [friends]> grant select,update,insert,delete on friends.* to erick@localhost;
• MariaDB [friends]> flush privileges;
• MariaDB [friends]> show grants for erick@localhost;

MariaDB [friends]> show grants for erick@localhost;

+--------------------------------------------------------------------------------------------------------------+

| Grants for erick@localhost                                                                                   |

+--------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'erick'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `friends`.* TO 'erick'@'localhost'                                   |

+--------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

• [root@server6 ~]# mysql -uerick -p
• MariaDB [(none)]> quit

Backing up Database

• Creating a Logical Database Backup
• mysqldump -u root -p friends > /root/friends-db.dump
• mysqldump -u root -p --all-databases > /root/all-db.dump
• Creating a Physical Database Backup
• mount |grep mysql
• mysql -uroot -p
• MariaDB [(none)]> flush tables with read lock; or systemctl stop mariadb
• [root@server6 ~]# lvcreate -L 5G -s -n lv_mariadb-snapshot /dev/vgsan/lv_mariadb
• MariaDB [none]> unlock tables;
• mkdir /mariadb_snapshot
• mount -o nouuid /dev/vgsan/lv_mariadb-snapshot /mariadb_snapshot/
• tar -cvf mariadb-snapshot.04-28-2016.tar /mariadb_snapshot/
• umount /mariadb_snapshot
• tar -tvf mariadb-snapshot.04-28-2016.tar

Restoring a Database backup

• Logical Backup
• mysql -u root -p < mariadb.backup
• Physical Backup
• systemctl stop mariadb
• rm -rf /var/lib/mysql/*
• tar -xvf mariadb-snapshot.tar -C /var/lib/
• mv mariadb_snapshot/* mysql/
• rm -rf mariadb_snapshot
• semanage fcontext -a -t mysqld_db_t "(/var/lib/.*?)"
• restorecon -Rv /var/lib/