MySQL principles and deployment

I. Principle of MySQL master-slave Replication

What is mysql master-slave Replication

Mysql supports several different forms of replication, such as single bidirectional, chained cascade, and asynchronous replication. During the replication process, one server acts as the master server and one or more other servers act as the slave server (slave). If chained cascade replication is set, in addition to the slave server, the slave server serves as the master server of other servers.

After master-slave replication is configured, the database content update should be performed on the master server to avoid conflicts between the user's updates to the database content on the master server and the updates to the database content on the slave server. In the production environment, the synchronization of authorization tables is generally ignored, and only select read permission is granted to users on the slave server. Or add the read-only parameter in the my. cnf configuration file to ensure read-only from the database. Of course, the two operations at the same time are better.

Generally, there will be one M --> S or one M --> multiple S in the production environment. Two-way synchronization of M can also be performed during synchronization <=> M. cascade is A --> B --> C --> D. in my. add the read-only parameter to the cnf configuration file to ensure that the slave database is read-only. In general, it is not allowed. Of course, if you can ensure that data is not written to the same table, you can. Or we can only grant the select permission to the user on the slave database.

The role of the mysql master-slave structure:

1) the configuration of the master/slave server architecture can greatly enhance the robustness of the data architecture. When the master server encounters a problem, we can switch to the slave server to continue providing services.

This is similar to the NFS storage data synchronization process to the backup nfs in real time through inotify + rsync. This is not just about synchronizing the content of the mysql database. It uses the built-in mysql tool.

2) The Master/Slave server price can implement read/write splitting for user requests, that is, by processing user select query requests on the slave server, the user query response time is reduced. And read/write pressure on the master server. The updated data (update, insert, delete) is still handed over to the master server for processing, ensuring that the master server and slave server are synchronized in real time.

If a website is a non-Update-oriented business, such as a blog or www Service, and there are many read requests, the Server Load balancer policy on the server will be effective, this is the structure of the read/write splitting database.

3) several different slave servers can be split based on the company's business. For example, the slave server that provides query services for external users can be used to back up slave servers, there are also slave servers that provide internal background, scripts, log analysis, and developer services. In this way, splitting not only reduces the pressure on the master server. Allows external users to browse and process internal user services of the company and DBA backup services without affecting each other. For details, refer to the simple architecture below:

We are a master node and a slave node. As long as we write data to this master node, the slave node will be updated at the same time. Tip: s1 2 3 can be used as the load balancing mode through lvs. In this way, 4th slave databases are provided to Our backend for internal access.

The back-end database is independently installed on the backup database, so that the data we test will not affect the online environment and ensure the real situation. In our backup database, we need to enable the binlog function for Incremental backup of the database.

------- Summary ----------------------------------------------------------------

The same is true for database master-slave synchronization and sersync real-time synchronization. Our sersync only synchronizes files, but this time we synchronize database data. MySQL synchronization uses built-in tools for synchronization. In the master-slave architecture, we can execute select query requests on the slave database to reduce the user's query response time and the pressure on the server from reading and writing. There is also a problem in the database: When the database is updated, there is no way to perform other operations, such as queries. Update inserter delete is usually used to update data.

If the website is dominated by non-Update Services (dominated by browsing) and has many read requests, the Server Load balancer policy on the slave server is effective at this time. This is the so-called read/write splitting structure. The role of slave database has many different purposes. Slave servers that provide query services for external users, slave servers that can be used for backup, as well as slave servers that provide internal company background, scripts, log analysis and development services. This splitting reduces the pressure on the master server.

------- Summary ----------------------------------------------------------------

How to install MySQL 6.4 in RHEL 5.6 (i386)

Linux website architecture series-Apache-deployment

Complete MySQL installation and master-slave dual-machine configuration

Install MySQL database in rpm mode on RHEL6 Platform

RedHat 6.3 MySQL-server-5.6.13-1.el6.x86_64.rpm Installation

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

1 2 Next Page