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

First of all, the environment I need: I installed two MySQL versions 5.7 and 5.5 locally: after the installation, they are as follows:

1. The principle of replication:

Principle:

There is a binary log called bin in MySQL, which records all modified sql statements about this database (including insert,update,delete,grant, etc.). Master-slave replication is to use this binary bin log to create a user on the master database, read the bin log from the database through this user, and then execute it again on the slave database.

2. Modify the configuration file of the main database:

One of the problems I encountered in the process of changing the configuration file was that my main database chose version 5.7, but there was no my.ini file, only my-default.ini. If you install it on disk C, you may also find a path: C:\ programData\ mysql\ my.ini. Just change this.

There are several other values that can be configured:

Binlog-do-db=su # the name of the database to be synchronized, multiple writes and multiple lines. If not configured, all are synchronized.

Binlog_format=mixed # Log mixing

3. The startup process of the master database:

Cmd runs as an administrator, and then enter the command: net start MySQL57 (your own name for installation), as follows:

4. Connect to the master database:

After starting successfully, connect to the database command: mysql-u root-p, as follows:

This command cannot be used if the environment variable is not configured.

5. To create users from the database:

First, you can take a look at the status of the binary log file. The command is: show global variables like'% log_bin%'; (note the semicolon after it). The result is as follows:

To create a user from the server, that is, authorize the slave database, the command is: grant replication slave on. To 'myslave'@'%' identified by' 123456'

% means that in addition to being locally accessible, it can also be accessed remotely, while localhost can only be accessed locally.

Replication slave means that you can only grant permissions to slave, or you can add select,insert and so on.

Check the status of the master database after authorization, as follows:

Record the values of File and Position, which are needed later for slave server operations.

6. Configure from the database:

You can also add other things:

Relay_log = mysql-relay-bin / / Relay Lo

Log_slave_updates = 1 / / slave writes replication events to its own binary log

7. Execute the process from the database:

Start the slave database first, and then execute the synchronous SQL statement, as follows:

Then start the Slave synchronization process, and check the master-slave synchronization, as follows:

When both values are YES, the status is normal.

The current state is abnormal, and the reasons for this are as follows:

It is said that the server-id is the same, but I configured 1 and 2 respectively. You can try the following solutions:

A, a configuration file auto.cnf of MySQL records the uuid,server_uuid of the mysql server: server identity ID. When you start Mysql for the first time, a server_uuid is automatically generated and written to the auto.cnf file in the data directory. Try changing this value.

B. Find out if there are other my.ini files and delete them after finding them.

C. Set server-id=2 through the command:

Mysql > set global server_id=2; # the values here are the same as those set in my.cnf

Mysql > slave start

Test after successful modification:

Then the table from the database will automatically add this data, so that the master-slave replication of the database is completed.

Finally, let's introduce the process of MySQL master-slave replication:

A, the primary node enables this bin binary log (log-bin)

B. Start a thread (Icano Thread) from the slave node, connect the users on the master node, and read the events in the log file.

C. The master node starts a thread (dump Thread) to check the events in its own binary log and compare it with the location requested by the other party. If there is no request location parameter, the master node will send one by one to the slave node from the first event in the first log file.

D. Receive the data sent by the master node from the node and place it in the relay log (Relay log) file. And record which binary log file and which location of the request to the primary node

Start another thread (sql Thread) from the node, read out the events in replaylog, and execute it again locally.

Explanation:

Thread O Thread: requests binary log events from Master and saves them in the relay log

Sql Thread: read log events from the relay log and replay locally

Dump Thread: start a dump thread for each Slave O Thread to send binary events to the slave node

Note: if the slave node needs to be the master of another node, you need to open the binary log file (log-bin). This situation is called cascading replication. If only as a slave node, you do not need to create binaries.

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.