Article directory
- 1 Introduction
- 2 references
- 3 Start installation
-
- 3.1 Run the official script and add the source of MariaDB
- 3.2 Install MariaDB Server
- 3.3 Start MariaDB
- 4 SSH login to MariaDB
-
- 4.1 Log in using the root account on ssh
- 4.2 Create a new administrator account and authorize it
- 5 Release port 3306
-
- 5.1 Set up port forwarding on VirtualBox
- 5.2 Rocky Linux firewall allows port 3306
- 6 Use DBeaver to connect to MariaDB on the virtual machine
- 7 MariaDB settings Table names are not case sensitive
-
- 7.1 First create the database
- 7.2 Then execute the following SQL to create the table
- 7.3 Then query this table
- 7.4 Solutions
- 8 Using mariadb in spring boot
-
- 8.1 maven dependencies
- 8.2 application.yml
- 9 Conclusion
1 Preface
After installing RockyLinux on VirtualBox on Windows 10, you can start installing the database. I heard that MariaDB is compatible with MySQL and its performance seems to be better than MySQL, so I tried to install it this time.
For the installation tutorial of RockLinux, please refer to my other article “Installing RockyLinux in VirtualBox and accessing it using ssh”
2 Reference
The official website of MariaDB is https://mariadb.com/. Since RockLinux is a system similar to CentOS, use yum to install MariaDB. There is also an official detailed guide https://mariadb.com/kb/en/yum. We will refer to this guide to complete the installation of MariaDB.
3 Start installation
3.1 Run the official script and add the source of MariaDB
The first step is to run the following command to add the source of MariaDB to the /etc/yum.repos.d/ directory.
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
The running results are as follows:
The above line of command will add the latest version of MariaDB (this is September 2023, the latest version is 11.1.2) to the source by default. So if you want to install an older version of MariaDB, you have to add some parameters to this command line. If you want to add parameters, you can refer to another official document https://mariadb.com/kb/en/mariadb-package-repository-setup-and-usage. I won’t go into details here.
3.2 Install MariaDB Server
Execute the following command and press Enter.
sudo dnf install MariaDB-server
The running result is as shown below, enter y and press Enter
After continuing to run for a while, Complete will be displayed, indicating that the installation is complete, as shown below:
3.3 Start MariaDB
After the installation is complete, MariaDB is not running by default.
# First check the status of MariaDB to see if it is already running systemctl status mariadb
The running result is as shown below. It can be seen that MariaDB is not running, and it does not start automatically at boot.
Start MariaDB
systemctl start mariadb
Then check the status of MariaDB to see that it is already running.
4 SSH login to MariaDB
4.1 Log in with root account on ssh
Enter the root username on Putty and log in directly. No password is required.
mariadb -uroot
4.2 Create a new administrator account and authorize it
/* Step 1: Create a new administrator account sjzadmin is the account name (pick it casually) '%' means any Host, that is, you can connect remotely P_sjz123 is the password */ CREATE USER sjzadmin@'%' IDENTIFIED BY 'P_sjz123'; /* Step 2, Authorize */ GRANT ALL PRIVILEGES ON *.* TO 'sjzadmin'@'%' WITH GRANT OPTION; /* Step 3, refresh */ FLUSH PRIVILEGES;
5 Release port 3306
Set up port forwarding on 5.1 VirtualBox
5.2 Rocky Linux firewall allows port 3306
## Step 1 Check whether the firewall is running. Under normal circumstances, it starts automatically when the computer is turned on. firewall-cmd --state ## Step 2 (optional) If the firewall is not running, you can start the firewall through the following command and set it to start automatically at boot. # Start firewall systemctl start firewalld # Start the firewall automatically at boot systemctl enable firewalld ## Step 3 Check whether port 3306 has been released # Query which ports are currently allowed firewall-cmd --zone=public --list-ports # Check whether port 3306 is allowed firewall-cmd --query-port=3306/tcp ## Step 4 If it is found in step 3 that port 3306 is not allowed, then allow it. # Permanently release the specified port firewall-cmd --zone=public --add-port=3306/tcp --permanent # Reload the firewall configuration to make the previous configuration take effect firewall-cmd --reload ## Step 5 (optional) If you no longer want to release a certain port, you can permanently close it firewall-cmd --zone=public --remove-port=3306/tcp --permanent
6 Use DBeaver to connect to MariaDB on a virtual machine
Open DBeaver, press the shortcut keys Ctrl + Shift + N, and the window for creating a new database connection will pop up, as shown below:
Enter URL username and password to connect to MariaDB
Like this, the connection is successful.
7 MariaDB settings table names are not case sensitive
After the above steps, our DBeaver can connect to MariaDB in the virtual machine. However, when we create a new database and then create a new table, we find that the table name is case-sensitive. details as follows:
7.1 First create the database
As shown below, select utf8mb4 for the character set and utf8mb4_general_ci for the sorting rule.
7.2 Then execute the following SQL to create the table
DROP TABLE IF EXISTS USER_ACCOUNT;
CREATE TABLE USER_ACCOUNT
(
`USER_ID` VARCHAR(32) NOT NULL COMMENT 'User ID',
`USERNAME_EN` VARCHAR(32) NOT NULL COMMENT 'Username in English',
`USERNAME_ZH_CN` VARCHAR(255) NOT NULL COMMENT 'Username Chinese',
`ENCRYPTED_PASSWORD` VARCHAR(255) NOT NULL COMMENT 'Encrypted user password',
`USER_STATUS` VARCHAR(32) NOT NULL COMMENT 'User status',
`TENANT_ID` VARCHAR(32) COMMENT 'Tenant number',
`REVISION` INT COMMENT 'optimistic lock',
`CREATED_BY` VARCHAR(32) COMMENT 'Creator',
`CREATED_TIME` DATETIME COMMENT 'Creation time',
`UPDATED_BY` VARCHAR(32) COMMENT 'Updated by',
`UPDATED_TIME` DATETIME COMMENT 'Update time',
PRIMARY KEY (USER_ID)
) COMMENT = 'User account table;';
7.3 Then query this table
If you find that the table name is in uppercase, you can query it. If you find that the table name is in lowercase, an error will be reported:
--The table name is in capital letters, which means it can be queried if it is consistent with the previous DDL statement. SELECT * FROM USER_ACCOUNT; -- If the table name is not in lowercase, an error will be reported: Table 'mytest.user_account' doesn't exist SELECT * FROM user_account;
7.4 Solution
Putty connects to RockLinux of the virtual machine. In the /etc/my.cnf.d/ directory, edit the server.cnf file as follows:
vim server.cnf
Make the following modifications:
Then restart mariadb, as follows:
systemctl restart mariadb
Then verify the effect and log in to mariadb on Putty:
Finally, delete all the previously built databases and tables and rebuild them.
8 Using mariadb in spring boot
8.1 maven dependencies
Go to https://mvnrepository.com/ and search for mariadb-java-client and then you can get the following maven dependencies:
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>3.2.0</version>
</dependency>
In addition, visit the official website of MariaDB Connector/J https://mariadb.com/kb/en/about-mariadb-connector-j/. It can be seen that version 3.x is compatible with java8, java11, and java17, while 2.7.x The version is only compatible with java8 and java11.
8.2 application.yml
as follows:
spring:
application:
name: my-test
datasource:
url: jdbc:mariadb://192.168.56.1:3306/mytest?autoReconnect=true & amp;allowMultiQueries=true
username: sjzadmin
password: P_sjz123
driver-class-name: org.mariadb.jdbc.Driver
The parameters after the question mark in spring.datasource.url above can be searched to get their meaning from the official document https://mariadb.com/kb/en/about-mariadb-connector-j/.
9 Conclusion
Thanks for reading~