MariaDB is one of the most popular database servers. Made by the original developers of MySQL. Guaranteed to stay open source.
01. Installing mariadb server and client:
yum -y install mariadb mariadb-server
Default configuration file is /etc/my.cnf
you may add configuration file with .cnf extension at /etc/my.cnf.d/
vim /etc/my.cnf #the path where Databases are stored by default, you may change it to LVM partition to use benefits of LVM in backup and snapshots. if you change it, restart the service. datadir=/var/lib/mysql
02. Run the service:
systemctl enable mariadb systemctl start mariadb # check it is running and enabled systemctl status mariadb #check if it is listening (default port is 3306\tcp ) netstat -tlp | grep mysql #OR netstat -tlpn | grep 3306
03. Configure Firewall to allow incoming connection only if required:
firewall-cmd --permanent --add-service=mysql firewall-cmd --reload
04. Initial Setup:
MariaDB comes with script to configure basic startup settings called ( mysql_secure_installation ) , it is interactive easy script to answer some question , let’s use it (read my comments above every question).
mysql_secure_installation
#the default root password in null , so just press Enter.
Enter current password for root (enter for none):
# recommended to use strong password, type y and press Enter , and provide your password twice.
Set root password? [Y/n] y
New password:
Re-enter new password:
# recommended to prevent anonymous user from accessing our DB, so type y and press Enter.
Remove anonymous users? [Y/n] y
# if you plan to manage it remotely, please don't use root Super Power, use normal user, so answer y and press Enter.
Disallow root login remotely? [Y/n] y
# there is a test database , you may use it for training and testing, but for production you may remove it, I will answer y and press Enter.
Remove test database and access to it? [Y/n] y
# Now it is all done, you Must accept that unless you forget something and want to start over from begin , i'm happy with this setting , so i type y and press Enter.
Reload privilege tables now? [Y/n] y
05. Using MariaDB:
login with user and password using command ( mysql -u USER -p ) , (-p) will prompt for password.
if you want to login within a script you may add password in the same command after (-p) without any space , and (-e) followed by commands in double quotes like this (mysql -u USER -pPASSWORD -e “mysql_commands”).
Example:
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 13 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]>
OR from one line (user: root , password: akm) :
[root@localhost ~]# mysql -u root -pakm -e "show databases" +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ [root@localhost ~]#
06. SQL Backup:
MaraiaDB backup have many tools and third parties software, there is tow different type of backup (logical and physical), fore simplicity :
Logical backup: can run while DB online , slower , backup sql statement that makes the DBs.
Physical backup: based on LVM partitions so you should plan it from the start, faster , need to locks DBs before backup , not flexible to restore in different DB systems.
Logical backup Example using tool (mysqldump):
# Backup mysqldump -u root -p dbName > /backup/mydb.sql # Restore mysqldump -u root -p dbName < /backup/mydb.sql
Physical backup example using tool (mysqlhotcopy):
mysqlhotcopy -u rootdbName /path/to/backupDIR mysqlhotcopy dbName1 dbName2 dbNameX /path/to/backupDIR
Backup needs a separate tutorial , i hope it was simple.
Thanks for reading my tutorial , comments always available.
Enjoy !.