Remove existing MySQL/MariaDB packages
MySQL is no longer included in the CentOS 7 repository, and has been replaced by MariaDB.
1. Use rpm -qa | grep mariadb to find existing MariaDB packages:
If present, use rpm -e --nodeps mariadb-* to remove them:
[root@localhost ~]# rpm -qa | grep mariadb
mariadb-server-5.5.52-1.el7.x86_64
mariadb-libs-5.5.52-1.el7.x86_64
[root@localhost ~]# rpm -e mysql-*
Error: No package mysql-* is installed.
2. Use rpm -qa | grep mariadb to find existing MariaDB packages:
If present, use yum remove mysql mysql-server mysql-libs compat-mysql51 to remove them:
[root@localhost ~]# yum remove mysql mysql-server mysql-libs compat-mysql51
Loaded plugins: fastestmirror, langpacks
No match for argument: mysql
No match for argument: mysql-server
No match for argument: compat-mysql51
Resolving dependencies
--> Examining transaction
---> Package mariadb-libs.x86_64 1:5.5.52-1.el7 will be removed
--> Processing dependency: libmysqlclient.so.18()(64bit), required by package perl-DBD-MySQL-4.023-5.el7.x86_64
--> Processing dependency: libmysqlclient.so.18()(64bit), required by package 2:postfix-2.10.1-6.el7.x86_64
--> Processing dependency: libmysqlclient.so.18()(64bit), required by package 1:qt-mysql-4.8.5-13.el7.x86_64.........
Removed:
mariadb-libs.x86_64 1:5.5.52-1.el7
Dependent removed:
akonadi-mysql.x86_64 0:1.9.2-4.el7 mariadb-server.x86_64 1:5.5.52-1.el7
perl-DBD-MySQL.x86_64 0:4.023-5.el7 postfix.x86_64 2:2.10.1-6.el7
qt-mysql.x86_64 1:4.8.5-13.el7
Done!
[root@localhost ~]# rpm -qa|grep mariadb
[root@localhost ~]#
3. Begin installation, create a MariaDB.repo file
vi /etc/yum.repos.d/MariaDB.repo
Insert content by selecting your system and version at: https://downloads.mariadb.org/mariadb/repositories/#mirror=tuna
This example uses the following:
# MariaDB 10.4 CentOS repository list - created 2021-08-27 01:31 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos8-amd64
module_hotfixes=1
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
4. Run the installation command to install MariaDB
sudo yum install -y MariaDB-server MariaDB-client
First download the packages, then proceed with automatic installation. After installation, start the MariaDB service.
Management commands
systemctl start mariadb # start service
systemctl enable mariadb # set to start on boot
systemctl restart mariadb # restart
systemctl status mariadb # check status
systemctl stop mariadb.service # stop MariaDB
5. Log into the database
Use the command mysql -uroot to log into MariaDB. The root password is empty by default.
6. Perform simple configuration for MariaDB
Use the command mysql_secure_installation for configuration:
[root]$ mysql_secure_installation
# Enter current password for root (enter for none):
# Switch to unix_socket authentication [Y/n] n
# Change the root password? [Y/n]
# If Y, enter the password twice
New password:
Re-enter new password:
# Remove anonymous users? [Y/n]
# Disallow root login remotely? [Y/n] n
# Remove test database and access to it? [Y/n] n
# Reload privilege tables now? [Y/n] y
Initialization of MariaDB is complete. Next, test login:
mysql -uroot -ppassword
7. Configure MariaDB character set
Check the content of /etc/my.cnf file, which includes the line !includedir /etc/my.cnf.d indiccating that this configuration file includes files in the /etc/my.cnf.d directory.
7.1 Use vi server.cnf to edit the server.cnf file, add the following under [mysqld] tag:
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
If there is no server.cnf file in /etc/my.cnf.d, add the above content directly under the [mysqld] tag in /etc/my.cnf.
7.2 File /etc/my.cnf.d/client.cnf
vi /etc/my.cnf.d/client.cnf
Add the following under [cleint]:
default-character-set=utf8
7.3 File /etc/my.cnf.d/mysql-clients.cnf
vi /etc/my.cnf.d/mysql-clients.cnf
Add the following under [mysql]:
default-character-set=utf8
All configurations are complete, restart mariadb:
systemctl restart mariadb
Then enter MariaDB to check the character set:
mysql> show variables like "%character%";show variables like "%collation%";
The output should be:
+--------------------------+----------------------------+
| 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)
Character set configuration is complete.
8. Add user and set permistions
Create user command:
CREATE user 'username'@'hostname' IDENTIFIED BY 'password';
Directly create user and grant command:
mysql>grant all on *.* to username@localhost indentified by 'password';
Grant permission for remote login:
mysql>grant all privileges on *.* to username@'%' identified by 'password';
Grant permission and allow granting:
mysql>grant all privileges on *.* to username@'hostname' identified by 'password' with grant option;
Check user:
MariaDB [mysql]> select host,user,password from user;
Allow specific remote address:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;
Restart:
FLUSH PRIVILEGES;