Shulou(Shulou.com)06/01 Report--

In order to soften a large number of concurrent visits, large websites are far from enough except to achieve distributed load balancing on the website. To the data business layer, data access layer, if it is still the traditional data structure, or just rely on a server to carry, so many database connection operations, the database is bound to collapse, data loss, the consequences are even more unimaginable. At this time, we will consider how to reduce database connections, on the one hand, the use of excellent code framework, code optimization, the use of excellent data caching technology such as: memcached, if the funds are rich, we will inevitably want to assume the server farm to share the pressure of the main database. Ok cuts to the topic of today's article, using MySQL master-slave configuration to achieve read-write separation and reduce the pressure on the database. This way, in today's many websites are used, it is not anything new, summed up today, convenient for everyone to learn and refer to.

Overview: set up one Master server (win8.1 system, Ip:192.168.0.104) and two Slave servers (virtual machine-one Ubuntu, one Windows Server 2003)

Principle: the master server (Master) is responsible for the NonQuery operation of the website, and the slave server is responsible for the Query operation. Users can regularly access the Slave server according to the functional module feature block of the website, or write a pool or queue themselves, and freely assign slave server connections for requests. The master-slave server uses the binary log files of MySQL to achieve data synchronization. The binary log is generated by the master server, and the synchronous database is obtained from the server response.

Specific implementation:

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

Not to mention installing mysql in windows, ordinary people on earth should be able to do so. I would like to talk a little bit about the MySQL installation of Ubuntu. I recommend not downloading and installing online, but offline installation. You can refer to https://www.jb51.net/article/157282.htm, who doesn't know whether the eldest brother is a sister or not. If you write it well, you can install it according to this. There may be several phenomena during installation, which can be solved by reference:

(1) if you are not logging in with a root user, it is recommended that you switch from su-root to the Root user installation, so you don't have to sudo all the time.

(2) store the extracted mysql folder, and the name of the folder should be changed to mysql.

(3) when support-files/mysql.server start starts MySQL, there may be a warning, which means that when starting the service to read the file, the my.cnf file is ignored. That is because there is a problem with the file permissions of my.cnf, and mysql will think that the file is dangerous and will not be executed. However, mysql will still start successfully, but if the following configuration modifies the my.cnf file from the server parameters, 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 under the mysql folder when you list. This is all because the my.cnf is not read when MySQL starts. At this time, as long as you change the file permissions of my.cnf to the same permissions of my_new.cnf, Ok, command: chmod 644 my.cnf Ok

(4) there is no Vim to modify the content of the document in Ubuntu, so it is best to install Vim and apt-get install vim, otherwise you will probably freak out.

At this point, I believe MySQL should be installed.

2. Configure the Master master server

(1) create a user 'repl', on Master MySQL and allow other Slave servers to access Master remotely and read binary logs through that user to achieve data synchronization.

(2) find the MySQL installation folder to modify the my.Ini file. There are several ways to log in mysql, which is not the focus of today. We just need to start the binary log log-bin to ok.

Add the following lines of code under [mysqld]

(3) View the log

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

Restart the MySQL service

3. Configure Slave slave server (windows)

(1) find the MySQL installation folder to modify the my.ini file, and add the following lines of code under [mysqld]

Restart the MySQL service

(2) Connect to Master

Change master to master_host='192.168.0.104', / / Master server log master _ log_pos=0 generated by the Ipmaster_port=3306,master_user='repl',master_password='mysql', master_log_file='master-bin.000001',//Master server

(3) start Slave

Start slave

4. Slave slave server (Ubuntu)

(1) find the MySQL installation folder to modify the my.cnf file, vim my.cnf

(2). / support-files/myql.server restart restarts the MySQL service, and. / bin/mysql enters the MySQL command window

(3) Connect to Master

Change master to master_host='192.168.0.104', / / Master server log master _ log_pos=0 generated by the Ipmaster_port=3306,master_user='repl',master_password='mysql', master_log_file='master-bin.000001',//Master server

(4) start Slave

Start slave

All the configurations of OK have been completed, so you can test in Master Mysql at this time, because we monitor all the operation logs of Master mysql, so any changes you make to the master server database will be synchronized to the slave server. Create a database and try the table.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.