"mysql optimization topic" Master-slave copy interview treasure! " Interviewers don't know much about you! (11)

More content, can be first collection, the directory is as follows: First, what is the master-slave replication of the role of the master-slave replication (emphasis) Three, the principle of master-slave replication (the most important) four or three easy to build master-slave Five, must ask questions dry analysis (most importantly) one, what is master-slave replication (technical text)

Master-slave replication is used to build a database environment exactly the same as the primary database, called from the database, and the primary database is generally a quasi-real-time business database.

Second, the role of Master-slave replication (benefits, or why to call the main from) Focus! ：

1, do the hot preparation of data, as a backup database, the primary database server failure, you can switch to continue to work from the database to avoid data loss.

2, 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.

3, read and write separation, so that the database can support greater concurrency. is especially important in reports. Because some of the report SQL statements are very slow, it causes the lock table to affect the foreground service. If the foreground uses the master, the report uses slave, then the report SQL will not cause the foreground lock, guaranteed the foreground speed.

Third, the principle of master-slave replication (the most serious, interview must ask):

1. The database has a bin-log binary file that records all SQL statements.

2. Our goal is to copy the SQL statements from the Bin-log file of the primary database.

3. Let it execute these SQL statements again in the Relay-log redo log file from the data.

4. The following master-slave configuration is configured around this principle

5. Requires three threads to operate:

Binlog the output thread. Whenever there is a connection from the library to the main library, the main library creates a thread and then sends the Binlog content to the slave library.

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

From the library I/O thread. When the start slave statement is executed from the library, an I/O thread is created from the library that 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.

The SQL thread from the library. Create a SQL thread from the library that reads the update event written to relay log from the library I/O thread and executes.

As you can see, there are three threads for each master-slave replication connection. 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. (technical article)

Master-slave replication

Schematic diagram

Still don't understand? It doesn't matter, the picture is the same:

• Step One: Update events (UPDATE, insert, delete) for the main library db are written to Binlog

• Step Two: Initiate a connection from the library, connect to the main library

• Step three: At this point the main library creates a Binlog dump thread that sends the contents of the Binlog to the slave library

• Step four: After starting from the library, create an I/O thread, read the Binlog content from the main library and write it to the relay log.

• Step Five: Also creates a SQL thread, reads from the relay Log, executes the read-to-update event from the Exec_master_log_pos location, writes the update to the DB of the Slave (technical article)

Don't you understand? No way, leave a message for your doubts.

It's long! Intermission, three laps to the right three laps left. Can not look down to the first collection of attention ha.

Need to know is that the principle of the interview process will not let you talk so long, general, as long as the 1234 points out, and then said briefly three threads, this is the full score! Four or three easy steps to build master-slave (technical text):

First, the configuration on Master master server (103.251.237.42)

1. Edit my.cnf (Command Find file location: Find/-name my.cnf)

Comment out bind-address = 127.0.0.1 in [mysqld] or MySQL cannot remotely

Server-id = 1 in 1 is self-defined, but it needs to be unique, and is the unique identity of the server

1.log_bin 启动MySQL二进制日志

2.binlog_do_db 指定记录二进制日志的数据库

3.binlog_ignore_db 指定不记录二进制日志的数据库。

Commenting out binlog_do_db and binlog_ignore_db, it means backing up all databases

After doing this, restart the database

2. Log in to the master server MySQL to create the account and permissions to use from the server;

After @ IP can access the master server, where the value is determined from the server IP

Create a new Masterbackup user with a password of masterbackup and give replication slave permissions

You can see that the user Masterbackup has been added

3. View the status of the primary database

Record mysql-bin.000007 and 276, write the following command to be used;

Change Master to master_host= ' 103.251.237.42 ', master_port=3306,master_user= ' masterbackup ', master_password= ' Masterbackup ', master_log_file= ' mysql-bin.000007 ', master_log_pos=276;

Ii. slave configuration from server configuration (103.251.237.45)

1. Edit my.cnf (Command Find file location: Find/-name my.cnf)

in [mysqld]

Relay-log = Slave-relay-bin

Relay-log-index = Slave-relay-bin.index

I don't know for a moment what this is. Join these two articles.

Restart MySQL Service

Log in to MySQL and stop the sync command

Execute the command prepared with the above; log slave from the server, connect the Master master server:

Restart data synchronization;

View slave information; if both are yes, the status is OK

Third, from the master server test results

Create a database on the primary server

View the database you just created from the server

It can be found that the master-slave server configuration is complete. Of course, there is the master copy, if interested friends can leave a message.

In fact, the master-slave replication also has some problems:

1. Load balancing, due to the time lag of the replication, can not guarantee synchronous reading, and write still a single point, unable to write more, I understand this is dabbler reading and writing balance.

2. Disaster tolerance, the basic is to damage the disaster, because the data is out of sync, who use who know, Dabbler disaster.

May just provide a low-cost data backup solution with imperfect disaster tolerance and load balancing, this scenario is destined to be a transition scenario, which the individual believes must be updated. Of course, it is an optimized solution in situations where the volume is not huge.

Five, the Interview problem Dry analysis (if asked the database master-slave problem, must ask the following questions):

1. What are the advantages of master and slave?

2, the principal and subordinate principle is?

3, from the database read the delay problem to understand it? How to solve?

4, the master from the server hanging from the boss how to do?

"mysql optimization topic" Master-slave copy interview treasure! " Interviewers don't know much about you! (11)