Separating Read and Write Structures in MySQL: A Step-by-Step Guide
Introduction
In this article, we will cover the installation of MySQL on a CentOS 7 system, which is a common requirement in internet projects. We will also discuss the importance of separating read and write structures in MySQL. Song Ge, the author, has successfully implemented this on a Linux system and will share the steps with us today.
Why Separate Read and Write Structures?
Separating read and write structures in MySQL is crucial for improving performance. This approach allows for better load balancing, reduced latency, and increased scalability. By separating these operations, we can optimize the database’s performance and ensure that it can handle high traffic and large data volumes.
Installing MySQL on CentOS 7
Song Ge prefers to use his own Linux Ubuntu system, but companies often use CentOS-based systems. Therefore, we will use CentOS 7 as an example to demonstrate the MySQL installation process.
Step 1: Check for Existing MariaDB Installation
Before installing MySQL, we need to check if MariaDB is already installed on our system. We can do this by running the following command:
yum list installed | grep mariadb
If MariaDB is already installed, we will see the following output:
mariadb-libs.x86_64 1: 5.5.52-1.el7 @anaconda
Step 2: Uninstall MariaDB
If MariaDB is installed, we need to uninstall it by running the following command:
yum -y remove mariadb*
Step 3: Download and Install the MySQL RPM Package
We will download the MySQL RPM package from the official MySQL website using the following command:
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Once the download is complete, we will install the RPM package by running the following command:
rpm -ivh mysql57-community-release-el7-11.noarch.rpm
Step 4: Check the MySQL Source
To ensure that the MySQL source is installed successfully, we will run the following command:
yum repolist enabled | grep "mysql *-community *.."
If the installation is successful, we will see the following output:
mysql57-community 11
Step 5: Install MySQL
We will install MySQL by running the following command:
yum install mysql-server
Step 6: Start and Stop MySQL
We will start MySQL by running the following command:
systemctl start mysqld.service
To stop MySQL, we will run the following command:
systemctl stop mysqld.service
Step 7: Login to MySQL
To login to MySQL, we will use the following command:
mysql -u root -p
Since there is no default password, we will see a prompt to enter a password. We will use the default password and then change it to a more secure password.
Step 8: Change Password Policy
We will change the password policy to a more complex one by running the following command:
set global validate_password_policy = 0;
Step 9: Reset Password
We will reset the password by running the following command:
set password = password ('123');
flush privileges;
Step 10: Grant Privileges
We will grant all privileges on the *.* database to the root user by running the following command:
grant all privileges on *.* to 'root' @ '%' identified by '123' with grant option;
flush privileges;
Step 11: Modify MySQL Library User Table
We will modify the MySQL library user table by changing the root user’s host to % by running the following command:
update mysql.user set host = '%' where user = 'root';
Step 12: Restart MySQL
We will restart MySQL by running the following command:
systemctl restart mysqld.service
Step 13: Disable Firewall
We will disable the firewall by running the following command:
systemctl disable firewalld.service
Conclusion
In this article, we have covered the installation of MySQL on a CentOS 7 system and the importance of separating read and write structures in MySQL. We have also discussed the steps to separate read and write structures in MySQL, which includes granting privileges, modifying the MySQL library user table, and disabling the firewall.