Blog

MariaDB 配置

Posted by Kylin Soong on Nov 17, 2016 categorized as linux

About MariaDB

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.

Installing MariaDB

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

NOTE: The /etc/my.cnf file has default configurations for MariaDB, /etc/my.cnf.d/ directory holding the configuration of MariaDB. /var/log/mariadb/mariadb.log file 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:

mysql_secure_installation

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';

Account Examples

  • user@localhost - user can connect just from localhost.
  • user@192.168.1.5 - user can connect from 192.168.1.5 host.
  • user@192.168.1.% - 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 Examples

  • 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

Logical restore

mysql -u root -p inventory < /backup/mariadb.dump

Physical restore

systemctl stop mariadb

Copy backup to /var/lib/mysql