- About MariaDB
- Installing MariaDB
- Working with MariaDB Databases
- Managing Database Users and Access Rights
- Creating and Restoring MariaDB Backups
A community-developed branch of MySQL built by some of the original authors of MySQL. It offers a rich set of feature enhancements, including alternate storage engines, server optimizations, and patches. The MariaDB Foundation works closely and cooperatively with the larger community of users and developers in the spirit of free and open source software.
A full MariaDB database installation requires both the mariadb and mariadb-client groups of software to be installed.
The following packages will be installed with the mariadb group:
- mariadb-server - The MariaDB server and related files (mandatory package).
- mariadb-bench — MariaDB benchmark scripts and data (optional package).
- mariadb-test — The test suite distributed with MariaDB (optional package).
The following packages will be installed with the mariadb-client group:
- mariadb — A community-developed branch of MySQL (mandatory package).
- MySQL-python — A MariaDB interface for Python (default package).
- mysql-connector-odbc — ODBC driver for MariaDB (default package).
- libdbi-dbd-mysql — MariaDB plug-in for libdbi (optional package).
- mysql-connector-java — Native Java driver for MariaDB (optional package).
- perl-DBD-MySQL — A MariaDB interface for Perl (optional package).
Installing MariaDB on Fedora 20
yum install mariadb* -y systemctl start mariadb systemctl enable mariadb systemctl status mariadb ss -tulpn | grep mysql
/etc/my.cnffile has default configurations for MariaDB,
/etc/my.cnf.d/directory holding the configuration of MariaDB.
/var/log/mariadb/mariadb.logfile has default log for MariaDB.
Insalling MariaDB on RHEL 7
yum groupinstall mariadb mariadb-client -y systemctl start mariadb systemctl enable mariadb systemctl status mariadb
Improve MariaDB installation security
MariaDB provides a program to improve security from the baseline install state. Run mysql_secure_installation without arguments:
MariaDB and networking
MariaDB can be configured to be accessed remotely, or limited to just local connections. MariaDB network configuration directives are found in the
/etc/my.cnf file, under the [mysqld] section.
- bind-address - The server will listen based on this directive. Only one value may be entered. Possible values are: Host name, IPv4 address, IPv6 address
- skip-networking - If set to 1, the server will listen only for local clients. All interaction with the server will be through a socket, located by default at /var/lib/mysql/mysql.sock. This location can be changed with a socket value in /etc/my.cnf.
- port - Port to listen on for TCP/IP connections.
Logging with root
mysql -u root -p show databases; exit
Working with MariaDB Databases
Creating a database
SHOW DATABASES; CREATE DATABASE inventory; USE inventory; SHOW TABLES; USE mysql; DESCRIBE servers;
Managing Database Users and Access Rights
Creating user accounts with MariaDB
CREATE USER inventory_user@localhost IDENTIFIED BY 'redhat'; SELECT host,user,password FROM user WHERE user = 'inventory_user'; SELECT Select_priv, Insert_priv, Update_priv, Delete_priv FROM user WHERE user = 'inventory_user' AND host = 'localhost';
user@localhost- user can connect just from localhost.
email@example.com- user can connect from 192.168.1.5 host.
firstname.lastname@example.org.%- user can connect from any host that belongs to the network 192.168.1.0.
user@%- user can connect from any host.
user@000:472:18:b51:c32:a21- user can connect from 2000:472:18:b51:c32:a21 host.
Create a table under inventory
mysql -u root -p SHOW DATABASES; USE inventory; SHOW TABLES; CREATE TABLE IF NOT EXISTS category(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, PRIMARY KEY(id)); INSERT INTO category (name) VALUES ('Networking'); INSERT INTO category (name) VALUES ('Servers'); INSERT INTO category (name) VALUES ('Ssd'); SELECT * FROM category;
Granting and revoking privileges for user account
GRANT SELECT, UPDATE, DELETE, INSERT on inventory.category to inventory_user@localhost; FLUSH PRIVILEGES; SHOW GRANTS FOR inventory_user@localhost; REVOKE SELECT, UPDATE, DELETE, INSERT on inventory.category FROM inventory_user@localhost; SHOW GRANTS FOR inventory_user@localhost; mysql -u inventory_user -p USE inventory; SELECT * FROM category;
GRANT SELECT ON database.table TO username@hostname- Grant select privilege for a specific table in a specific database to a specific user.
GRANT SELECT ON database.* TO username@hostname- Grant select privilege for all tables in all databases to a specific user.
GRANT SELECT ON *.* TO username@hostname- Grant select privilege for all tables in all databases to a specific user.
GRANT CREATE, ALTER, DROP ON database.* TO username@hostname- Grant privilege to create, alter, and drop tables in a specific database to a specific user.
GRANT ALL PRIVILEGES ON *.* to username@hostname- Grant all available privileges for all databases to a specific user, effectively creating a superuser, similar to root.
Creating and Restoring MariaDB Backups
Creating a backup
There are two ways to back up MariaDB:
- Logical - export information and records in plain text files
- Physical (raw) - copy all files and directories that store content.
Performing a logical backup
mysqldump -u root -p inventory > /backup/inventory.dump mysqldump -u root -p --all-databases > /backup/mariadb.dump
Performing a physical backup
lvcreate -L20G -s -n mariadb-backup /dev/vg0/mariadb mkdir /mnt/snapshot mount /dev/vg0/mariadb-backup /mnt/snapshot umount /mnt/snapshot lvremove /dev/vg0/mariadb-backup
Restoring a backup
mysql -u root -p inventory < /backup/mariadb.dump
systemctl stop mariadb
Copy backup to