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

The optimization scheme when a single MYSQL server cannot meet the current site traffic. Mysql cluster technology needs to be built.

1. Function:

When data is inserted into the master server | modified | deleted, the data is automatically synchronized to the slave server.

Note: master-slave replication is one-way, only master-> slave

It is divided into two types: launch type (one master and multi-slave): generally used in: backup, read-write separation.

Ring (multi-master and multi-slave): general use: when the master server is under great pressure, cross-regional websites to achieve data synchronization

In a ring structure, "ID conflicts" occur if records are inserted into the same table on three servers at the same time.

Solution: let three servers generate different ID

The first one: 1, 4, 4, 7.

Station 2: 2, 5, 5, 8.

Radio 3: 3, 6, 6, 9.

This can be set in the configuration file of MYSQL:

Second, the principle of master-slave (using bin log)

There is a type of log in Mysql called bin log (binary log). This log records all SQL statements (insert,update,delete,ALTER TABLE,grant, etc.) that have modified the database. The principle of master-slave replication is to copy the BIN logs from the master server to the slave server for execution, so that the data on the slave server is the same as that on the master server.

Extension: logs in mysql:

Query log error log Bin log

Slow log: you can set a time threshold, such as 0.5 seconds, so that future SQL statements that take longer than this value will be recorded. In this way, we can record slow SQL statements and specialize in optimization.

Purpose: you can quickly locate the SQL in the website to compare the drag site, and then you can optimize: index and cache the results of this SQL.

III. Actual configuration

Take the MYSQL of the windows system as the master server and the LINUX as the slave server.

Primary server:

1. Open bin log

Modify the configuration file of mysql: add my.ini:

Specify a server-id for the server (the ID value of the master and slave server cannot be duplicated)

If it is a ring server, you need to add the following items:

Log-slave-updates = on / / if it is a ring multi-server, set this item

Create an account on the master server to synchronize data for the slave server

Log in to MYSQL

Execute a SQL:

Create an account with only REPLICATION SLAVE permission: user name: slave password: 1234

Perform SQL on the primary server to view the status of the current bin log of the primary server

Note: these two values change each time you modify the data, so after viewing these two values, do not manipulate the master server directly until the slave server is configured, otherwise the synchronization will fail.

Slave server (linux):

Open bin log

Modify configuration file / etc/my.cnf

Set a server-id:

3. If it is a ring server, you need to add the following items:

Log-slave-updates = on / / if it is a ring multi-server, set this item

4. Execute the SQL statement on the slave server to configure the address of the master server:

Log in to MYSQL:

Set up slave server and start replication function

5. Execute SQL to query whether the status of the slave server is configured successfully:

If it is two YES, then the code is successful! Done!

Description: the data on the master server will not automatically come to the slave server until the configuration is successful. So you need to manually import all the data on the master server to the slave server before configuration, and then after configuring the master and slave, the data will be synchronized later.

Application situation:

The slave server is used as the backup server for the data. When the pressure on the server is high, the master-slave server can be used to separate read and write to reduce the pressure on the server. Optimization of cross-regional websites

The function of making a message board, taking into account the architectural ideas of optimization in different regions?

The above is all the principle knowledge about MYSQL master-slave replication. Thank you for your study and support.

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.