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