MySQL Multi-data source note 1-mysql master-slave replication

1. Why do I have to copy from the Boss?

1, in the business complex system, there is a scenario, there is a SQL statement need to lock the table, resulting in the temporary inability to use the Read service, then it will affect the operation of the business, the use of master-slave replication, let the main library is responsible for writing, read from the library, so that even if the main library has a lock table, The normal operation of the business can also be ensured by reading from the library.

2, do the hot preparation of data.

3, the extension of the architecture. More and more business volume, I/O access frequency is too high, single machine can not meet, at this time to store more storage, reduce the frequency of disk I/O access, improve the I/O performance of individual machines.

The following architecture diagram:

You can see that the architecture diagram of MySQL if with the increasing volume of business, I/O access frequency is too high, single machine is not satisfied. So this is the MySQL master-slave replication, read and write separation, so that share the pressure of a single database, data backup, high availability and fault tolerance.

so summarize MySQL master-slave replication technology can solve the problem, the following 4 points:

1 distribution (data distribution)
2. Load balancing (Load Balancing)
3. Backup (Backups)
4. High-availability and fault-tolerant lines of availability and failover

Two. mysql master-slave replication principle

The basic interactive process for MySQL master-slave replication is as follows:

Binary logs is a binary file in the main library that holds all the update event logs.

the basis for master-slave replication is that all changes to the Master repository record database are recorded in the Binlog binary file. Binlog is the moment the database server starts, saving all files that modify the structure or content of the database.

MySQL master-slave replication is an asynchronous replication process in which the main library sends update events to the library, reads the update records from the library, and executes the update records, keeping the contents of the library consistent with the main library.

In the main library, whenever an update event occurs, it is written to the Binlog in turn and then pushed to the data source from the library as a copy from the library.

Specific details are as follows:

1. Whenever there is a connection from the library to the main library, the main library creates a thread (binlog output thread ) and then sends the Binlog content to the slave library.
For each SQL event that is about to be sent to the slave library, the Binlog output thread locks it. Once the event has been read by the thread, the lock is freed, even when the event is completely sent from the library.

In the library, when replication starts, two threads are created from the Library for processing:

When the start SLAVE statement is executed from the library, an I/O thread (from the library I/O thread ) is created from the library, and the line thread attached to the main library and requests the main library to send the update records from the Binlog to the library.
Reads the updates sent from the Binlog output thread of the main library from the library I/O thread and copies the updates to a local file, including the relay log file. Create a SQL thread from the library that reads the update event written to relay log (MYSQL-RELAY-BIN.XXXXXX) from the library I/O thread and executes .

For each master-slave replication connection, there are three threads. Having multiple main libraries from the library creates a binlog output thread for each slave library connected to the main library, each with its own I/O thread and SQL thread from the library.

By creating two separate threads from the library, the read and write separation from the library is made when copying. Therefore, even if the thread responsible for execution is slow, the thread responsible for reading the UPDATE statement does not become slow.

For example, if the library is not running for a while, when it is started, its I/O thread can quickly read all the Binlog content from the main library, even though its SQL thread is slow to execute.

This way, even if the library is stopped before the SQL thread executes all the read-through statements, the I/O thread has at least completely read all the contents and securely backs up the relay log locally from the library, ready to execute the statement at the next boot from the library.

To sum up is:

　　1. After executing SQL, master logs the binary log file (Bin-log).

2, slave connect master, and get Binlog from master, save in local relay-log, then execute SQL statement from last remembered position, and stop synchronizing once an error is encountered.

To configure master-slave replication, follow these steps:

1. Use your own msql to install the directory copy and put it in another directory, as follows:

Then copy two copies and proceed from naming, as follows:

The two configuration files from the library are then modified, and the My-default.ini configuration file is activated and renamed to My.ini to activate, such as:

Then modify the configuration in the My.ini, the port, and the base directory and data directory, such as:

Then create a new data file in the two from the database directory, respectively, such as:

Next, the following folders in the main library are copied to two data files from the library, by the way, the master-slave assignment is also assigned to two from the library, as follows [email protected]:

Then perform two service installations from the library:

After completion, open the service as follows:

Then make the connection, as follows:

Connected, will not open the database for master-slave replication, because the two ibdata1 files from the library missing data, so the main library of the ibdata1 files copied over:

Then turn on the service.

Then go back to modify the main library and the My.ini file from the library, name the main Library ID 1, two from the library named 2, and 3 configured as follows:

Next, you also specify the database to which the master database is to be replicated, as follows:

Specify the database to be synchronized from the database as follows:

The following query is then made in the primary database connection:

Then execute from the database to give the script, such as:

Then from the database connection to query the success is not as follows, if the following information is the master-slave replication configuration succeeds, as follows:

MySQL Multi-data source note 1-mysql master-slave replication