MARIADB installation Configuration

CentOS 7 Installation mariadb detailed and related configuration

First step: Add the MariaDB yum repository

First, add the MariaDB Yum profile Mariadb.repo file under the/etc/yum.repos.d/directory in the CentOS operating system.

Vi/etc/yum.repos.d/mariadb.repo

In the file, add the following to save:

[MARIADB]
Name = MariaDB
BaseURL = Http://yum.mariadb.org/10.2/centos7-amd64
Gpgkey=https://yum.mariadb.org/rpm-gpg-key-mariadb
Gpgcheck=1

Step Two: Install MariaDB

Easily install MariaDB with the Yum command.

Yum Install Mariadb-server mariadb-client-y

MariaDB Start the database service daemon immediately after the installation is complete.

Systemctl Start mariadb

Set MariaDB to start the service automatically after the operating system restarts.

Systemctl Enable MARIADB

View the current status of the MariaDB service.

Systemctl Status mariadb

Step three: Secure configuration of the MariaDB

Security configuration by the following command, according to the actual situation with y/n reply to the following questions: Set MariaDB root account password, delete anonymous users, disable root telnet, delete the test database, reload the permissions table.

Mysql_secure_installation

I have chosen y all and then press ENTER.

After configuring the security configuration of the database, you can view the version with the following command to confirm that MARIADB has been installed successfully.

MySQL--version

You can log on by MariaDB the command line and then perform SQL query operations on the database.

Mysql-u root-p

Fourth Step: Configure remote access permissions for MariaDB

In the third step, if you disable root telnet to select Y, you cannot connect to the database by using tools such as Navicat on another computer, you will need to assign permissions to the corresponding MariaDB account and allow remote connection to MariaDB using that account. You can enter the following command to view your account information:

Select User, host from Mysql.user;

The host entry in the root account is localhost, which means that the account can only log on locally, we need to modify the permissions and enter the command:

GRANT all privileges on * * to ' root ' @ '% ' identified by ' password ' with GRANT OPTION;

Modify Permissions. % indicates that for all ip,password the root user will be logged in with this password, and if you want to have only one IP segment host connected, you can modify it to:

GRANT all privileges on * * to ' root ' @ ' 192.168.71.% ' identified by ' My-new-password ' with GRANT OPTION;

Finally, don't forget:

FLUSH privileges;

After saving the changes, look at the user account information:

This time found that compared to the previous one, its host item is%, this time the configuration is successful, we can use the account for remote access.

Fifth Step: CentOS 7 Open firewall port

After the fourth step if you still cannot connect to the database remotely, it should be 3306 port is blocked by the firewall, then we need to shut down the firewall or open firewall port.

To turn off the firewall:

Systemctl Stop Firewalld.service #停止firewall

Systemctl Disable Firewalld.service #禁止firewall开机启动

Open firewall port to restart the firewall after opening:

Firewall-cmd--zone=public--add-port=3306/tcp--permanent

Firewall-cmd--reload

Sixth step: Set database letter case insensitive

Vi/etc/my.cnf.d/server.cnf

Under [mysqld], add "

Lower_case_table_names=1

The default is equal to 0, which is case sensitive. Change to 1 is OK. If you have already built a database to delete the previously established database, the rebuild will take effect.

Seventh Step: Set MARIADB database default encoding

The default encoding for MARIADB is latin1, and inserting Chinese is garbled, so you need to change the encoding to UTF8.

1. Login, use the following command to view the currently used character set, there should be several not UTF8 format.

SHOW VARIABLES like ' character% ';

2. Modified configuration file

Vi/etc/my.cnf.d/client.cnf

Add in the [Client] field

Default-character-set=utf8

Vi/etc/my.cnf.d/server.cnf

In the [Mysqld] field, add

Character-set-server=utf83. Restart the MariaDB configuration to take effect.

Systemctl Restart MARIADB

MARIADB installation Configuration