MariaDB on CentOS 7 commands and functions

MariaDB on CentOS 7

Enable MariaDB to start on boot and then start the service:

1. sudo systemctl enable mariadb
2. sudo systemctl start mariadb

Harden MariaDB Server

1. Run the mysql_secure_installation script to address several security concerns in a default MariaDB installation:
2. sudo mysql_secure_installation

Root Login

1. To log in to MariaDB as the root user:
2. mysql -u root -p

Create a New MariaDB User and Database

In the example below, testdb is the name of the database, testuser is the user, and passwordis the user’s password:

1. create database testdb;
2.  create user 'testuser'@localhost identified by 'password';
3.  grant all on testdb.* to 'testuser' identified by 'password';

You can shorten this process by creating the user while assigning database permissions:

1. create database testdb;
2.  grant all on testdb.* to 'testuser' identified by 'password';

View the new Databases:

1. show databases;

View the new table:

1. show tables;

To retrieve all Users in MariaDB, you can execute the following SQL statement:

1. SELECT User FROM mysql.user;

Reset the MariaDB Root Password

If you forget your root MariaDB password, it can be reset.

1. Stop the current MariaDB server instance, then restart it with an option to not ask for a password:
2. sudo systemctl stop mariadb
3. sudo mysqld_safe --skip-grant-tables &

Reconnect to the MariaDB server with the MariaDB root account:

1. mysql -u root

Use the following commands to reset root’s password. Replace password with a strong password:

1. use mysql;
2. update user SET PASSWORD=PASSWORD("password") WHERE USER='root';
3. flush privileges;
4. exit

Then restart MariaDB:

1. sudo systemctl start mariadb

However, note that this query shows a large listing of MySQL user information, including user permission information, so as a practical matter you may want to trim down some of the fields to display, something like this:

1. mysql> select host, user, password from mysql.user;

The next section provides details and background information about this second query.

How to reduce the amount of ‘user’ information shown

You can get a listing of the fields in the mysql.user table by running this MySQL query:

1. mysql> desc mysql.user;

So for most cases where you want to show MySQL user accounts you'll probably want to limit your MySQL users' query to a few important columns, something like this:

1. select host, user, password from mysql.user;