Use MySQL master-slave configuration to achieve read-write separation and reduce database pressure

In order to handle a large number of concurrent visits, large websites need more than just distributed load balancing on the website. When it comes to the data business layer and data access layer, if the traditional data structure is still used, or if only one server is used, the database will inevitably crash due to so many database connection operations, and the consequences of data loss will be even more disastrous. At this time, we will consider how to reduce database connections. On the one hand, we will use an excellent code framework to optimize the code and adopt excellent data caching technology such as memcached. If we have sufficient funds, we will definitely think of assuming a server cluster to share the pressure of the main database. Ok, let's get into the topic of today's article. Use MySQL master-slave configuration to achieve read-write separation and reduce database pressure. This method is used in many websites nowadays and it is nothing new. I will summarize it today for your reference.

Overview: Set up a Master server (win8.1 system, IP: 192.168.0.104) and two Slave servers (virtual machines - one Ubuntu, one Windows Server 2003)

Principle: The master server is responsible for the website's NonQuery operations, and the slave server is responsible for the Query operations. Users can access the slave server in a fixed manner according to the website's functional model, or write a pool or queue to freely allocate slave server connections for requests. The master and slave servers use MySQL binary log files to achieve data synchronization. The binary log is generated by the master server, and the slave server responds to obtain the synchronized database.

Specific implementation:

1. Install MySQL database on both the master and slave servers. I installed mysql_5.5.25.msi version on Windows system and mysql-5.6.22-linux-glibc2.5-i686.tar on Ubuntu.

I won’t talk about installing MySQL on Windows, as most people on earth should know how to do it. Let me talk a little about the installation of MySQL on Ubuntu. I suggest not to download and install it online, it is better to install it offline. You can refer to https://www.jb51.net/article/157282.htm. I don’t know if this person is a brother or a sister, but she wrote it very well and you can install it according to this. There may be several problems during installation. You can refer to the following solutions:

(1) If you are not logged in as the root user, it is recommended that you use su - root to switch to the root user for installation. This way, you do not need to use sudo all the time.

(2) Store the unzipped mysql folder. It is best to change the folder name to mysql

(3) When starting MySQL with ./support-files/mysql.server start, a warning may appear. The Chinese meaning is that when starting the service to run the read file, the my.cnf file was ignored. This is because there is a problem with the file permissions of my.cnf. MySQL will consider the file dangerous and will not execute it. However, mysql will still start successfully. However, if you modify the my.cnf file from the server parameters in the following configuration, you will find that the file has been changed, but when you restart the service, the modified configuration is not executed, and you will find many intermediate files such as .my.cnf.swp in the mysql folder if you list it. This is because MySQL does not read my.cnf when it starts. At this time, just change the file permissions of my.cnf to the same permissions as my_new.cnf, command: chmod 644 my.cnf is OK

(4) There is no Vim in Ubuntu to modify document contents. It is best to install Vim, apt-get install vim, otherwise you will probably go crazy.

At this point I believe MySQL should be installed.

2. Configure the Master server

(1) Create a user 'repl' on the Master MySQL server and allow other Slave servers to remotely access the Master server and read the binary logs through this user to achieve data synchronization.

(2) Find the MySQL installation folder and modify the my.Ini file. There are several logging methods in MySQL, which is not the focus today. We just need to start the binary log log-bin.

Add the following lines of code under [mysqld]

(3) View logs

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 1285 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Restart MySQL service

3. Configure the Slave server (windows)

(1) Find the MySQL installation folder and modify the my.ini file. Add the following lines of code under [mysqld]

Restart MySQL service

(2) Connecting to the Master

change master to master_host='192.168.0.104', //Master server IP
master_port=3306,
master_user='repl',
master_password = 'mysql', 
master_log_file='master-bin.000001', //Log generated by the Master server master_log_pos=0;

(3) Start the Slave

start slave;

4. Slave Server (Ubuntu)

(1) Find the MySQL installation folder and modify the my.cnf file, vim my.cnf

(2) ./support-files/myql.server restart to restart the MySQL service, ./bin/mysql to enter the MySQL command window

(3) Connecting to the Master

change master to master_host='192.168.0.104', //Master server IP
master_port=3306,
master_user='repl',
master_password = 'mysql', 
master_log_file='master-bin.000001', //Log generated by the Master server master_log_pos=0;

(4) Start the Slave

start slave;

OK, all configurations are completed. Now you can test it in Master MySQL. Because we monitor all operation logs of Master MySQL, any operation that changes the database of the master server will be synchronized to the slave server. Create a database and table to try it out. .

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links