Installing mysql (Mariadb) in linux

My environment is the VMware virtual machine centos7. It's easier to install mysql in win environment, but it's hard to remember various commands when installing in linux. So I sorted out the installation process of Mariadb,

Some centos7 has installed MariaDB by default. You can check whether you have installed it (rpm -qa | grep MariaDB or directly check the status of MariaDB service MariaDB). If you have not installed it, you can install it again. If you have installed it, you can uninstall it or reinstall it. The uninstall command is Yum remove MariaDB server

1. Install MariaDB

Install it via yum. It's simple and fast. Installing mariadb server depends on installing mariadb by default. One is the server and the other is the client.

yum install mariadb-server

2. Configure MariaDB

1) After the installation is completed, first turn on the MariaDB service and set it to startup

systemctl start mariadb  # Open service
systemctl enable mariadb  # Set as startup service

2) The first installation requires database configuration, and the commands are the same as mysql

mysql_secure_installation

3) Options when configuring

Enter current password for root (enter for none):  # Enter the password of the database super administrator root (note that it is not the password of the system root). Enter directly if the password has not been set for the first time

Set root password? [Y/n]  # Set password, y

New password:  # New password
Re-enter new password:  # Enter password again

Remove anonymous users? [Y/n]  # Remove anonymous user, y

Disallow root login remotely? [Y/n]  # Deny root remote login. No matter y/n, root remote login will be denied

Remove test database and access to it? [Y/n]  # Delete test database, y: delete. n: If it is not deleted, there will be a test database in the database. Generally, it is not needed

Reload privilege tables now? [Y/n]  # Reload permission table, y. Or restart the service maybe

 

4) Test whether you can log in successfully. If "MariaDB [(none)] > appears, it means that you can log in and use the MariaDB database normally

[root@mini ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

 

3. Set MariaDB character set to utf-8

1)/etc/my.cnf file

Add under the [mysqld] tab

init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

 2)/etc/my.cnf.d/client.cnf file

Add under the [client] tab

default-character-set=utf8

3)/etc/my.cnf.d/mysql-clients.cnf Documents

Add under the [mysql] tab

default-character-set=utf8

4) Restart service

systemctl restart mariadb

5) Enter mariadb to view character set

 

#Before character not set
MariaDB [(none)]> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]>

 

#After setting character
MariaDB [(none)]> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

MariaDB [(none)]>

4. Remote link to mariadb database

mariadb denies root remote login by default. The navicat software is used to connect the database

1) Turn off firewall

① Turn off the firewall systemctl stop firewalld

systemctl stop firewalld

(2) allow foreign links of a port without shutting down the firewall. The steps are as follows: open port 3306 and restart the firewall

[root@mini ~]# firewall-cmd --query-port=3306/tcp  # Check whether the 3306 port is open
no
[root@mini ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent  # Open 3306 port
success
[root@mini ~]# firewall-cmd --reload  # service iptables restart 
success
[root@mini ~]# firewall-cmd --query-port=3306/tcp  # Check whether the 3306 port is open
yes

2) First, look at the user table in the mysql database

 

[root@mini ~]# mysql -u root -p  # Enter the database through local link first

MariaDB [(none)]> use mysql;

MariaDB [mysql]> select host, user from user;
+-----------+------+
| host      | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1       | root |
| mini      | root |
+-----------+------+
3 rows in set (0.00 sec)

3) Change the field equal to the host name to "%", my host name is mini

MariaDB [mysql]> update user set host='%' where host='mini';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> select host, user from user;
+-----------+------+
| host      | user |
+-----------+------+
| %         | root |
| 127.0.0.1 | root |
| localhost | root |
+-----------+------+
3 rows in set (0.00 sec)

4) refresh the permission table or restart the mariadb service. Select one of the following

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@mini ~]# systemctl restart mariadb

Note: the refresh permission table is in the database, and the restart service is in the external command line

 

Tags: MariaDB MySQL Database firewall

Posted on Sat, 06 Jun 2020 00:46:49 -0400 by sledge4