1. View the current mariadb version.
rpm -qa |grep mariadb
2. Uninstall the current old version of mariadb and delete the configuration file and directory (version 5.5 does not support dynamic columns).
rm -f /etc/my.cnf rm -rf /var/lib/mysql/
3. Add the yum source for mariadb 10.2 and refresh the yum source.
vim /etc/yum.repos.d/Mariadb.repo
Add the following content to this file, press i to insert content, press: wq to exit after saving, press: q! Exit without saving.
! It is not recommended to use vim editor. After connecting the virtual machine with native ftp, open the file in the directory and save it after editing with native text editor.
[mariadb] name = MariaDB baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64 gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB gpgcheck=1
Refresh the yum source and install mariadb.
yum clean all yum makecache all yum install mariaDB-server mariaDB-client -y
4. Set automatic startup, startup, view status, initialization, open 3306 port and set remote connection.
systemctl enable mariadb.service systemctl start mariadb.service systemctl status mariadb.service mysql_secure_installation
Initialization, option N Y
5. Log in to the database and modify the permissions, modify the configuration file, and open the port.
Log in to the database. You don't need to enter a password to log in for the first time. Enter directly. The '%' in the second statement means that any ip in the network layer can be accessed. If you want to restrict access to a network segment, you can change the '%' to '192.168.3. *'
Exit exit
! After connecting to the database, the statement must be executed with a semicolon!!!
mysql -uroot -p GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; flush privileges; exit
Permanently open port 3306 of the firewall with the permission of root user (3306 is the database connection port used by mariadb by default. If necessary, you can change it to another port number in the configuration file), -- permanent parameter means it will take effect permanently, even if the server is restarted.
!!! Opening tomcat8080 port is the same as opening any port.
firewall-cmd --zone=public --add-port=3306/tcp --permanent firewall-cmd --reload firewall-cmd --list-all
Test whether the virtual machine port is connected locally
Command telnet address port
For example, telnet 192.168.3.68 3306. After entering, see Figure 2, which indicates that the port is connected.
Using the native database connection tool Navicat Premium: enter the ip, account number (usually root), and password (the password set previously). The connection is successful.
be careful! If the virtual machine cannot be connected and the error code is 1045, you can modify my.cnf as shown in the figure below
# # This group is read both by the client and the server # use it for options that affect everything # [client] default-character-set=utf8 [mysqld] character-set-server=utf8 collation-server=utf8_general_ci [client-server] # # include *.cnf from the config directory # !includedir /etc/my.cnf.d
Then enter the password to connect to the database and execute the following statement to change% (all passwords) to the password just entered on the terminal (that is, the password beginning with * EAD after localhost)
For example, I change the% (all passwords) user password of the remote connection to the password of my local connection.
be careful! The password here is encoded
mysql -uroot -p use mysql select user,host,password from user; update user set password="*EAD66C46B97C2603445AA5693D27F5CC4E2BA384" where host="%";
Restart database
systemctl restart mariadb.service
If it cannot be started, enter the following to view the log
systemctl status mariadb.service
For example, I wrote an additional in the configuration file before, An error is reported when restarting. After removing it, restart again successfully.