MySQL master-slave replication (master-slave)

Expected results: the data on a master MySQL server is changed, and the data on the slave server is automatically changed.

Principle: The slave server registers an account on the master server and sends the binary log file generated by the addition, deletion, and modification operations on the master server to the slave server,

The slave server adds, deletes, and modifies the binary log to synchronize data.

Minor details: the slave server will specify two threads, one for requesting and receiving binary logs sent, and the other for converting binary logs into relay logs.

(No matter what a relay log is, it is a type of log specifically for the subordinate server ),

Then convert the relay logs into corresponding SQL statements and execute them on the slave server to achieve data synchronization.

Parameter comparison description:

Virtual Machine

(1) use the default VMware net configuration method to install two centos 5.5 servers

(2) Use an RPM package to install MySQL 5.1.54

(3) Disable SELinux and firewall to connect two MySQL instances (Telnet 192.168.80. * 3306)

MASTER: 192.168.80.135 slave: 192.168.80.136

MASTER:

1. Create an account and grant the following permissions:

Mysql> grant replication slave, replication client on *. * To 'bak' @ '192. 168.80.136 'identified by '20140901 ';

2. log on to the master database from the database for testing.

Mysql> mysql-h192.168.80.135-ubak-p123456
Configuration File Settings

 

server_id           = 1log_bin             = mysql-binlog_bin_index       = mysql-bin.indexsync_binlog         = 1max_binlog_size     = 200M
binlog_do_db　　　　= db_1
binlog_do_db　　　　= db_2
......
binlog_ignore_db　　= db_x
binlog_ignore_db　　= db_y
......

Server_id server ID. do not conflict with other servers. during practice, the master server is generally set to 1, to distinguish the names of binary log files explicitly specified by log_bin on other servers (the 'enable' mentioned in other articles is to remove the '#' sign before the change, and if nothing is written afterwards,
Binary log files are stored in the database path by default, and the default file name is the host name. If the specified file name is suffixed,
MySQL will ignore this option. In general, this option is very casual, as long as the '#' number is removed.) The path and file name settings of this option do not need to be consistent with that of the slave server,
The slave server may not disable this option. The previous option log_bin_index will generate a series of files (file name. Six digits: e.g. hostname.000001 ),
This option will generate another MySQL file with the specified suffix, which records the names of all files generated by the previous option,
It is not very important, and it does not need to be consistent with the slave server. This option is used together with the previous option, you can also set sync_binlog to 1 without enabling it, that is, synchronize the logs of each MySQL modification and event to the hard disk in real time, max_binlog_size, and specify the binary log size.
Binlog_do_db specifies the name of the database on which the master server is allocated. Note: 1: The name starts with BINLOG and the name starts with replicate.
NOTE 2: Do not write a single row for the database to be synchronized (I did not succeed, but I don't know what's going on). There are several lines to write, which have nothing to do with the order. You don't need to match the settings on the slave server one by one.
Binlog_ignore_db does not require a database to be synchronized. The setting and note are the same as those of the previous item.

MySQL reads the configuration file only when it is started. Therefore, if you modify the configuration file, you must restart MySQL service MySQL (mysqld) restart.

From:

server_id           = 2relay_log           = mysql-relay-binrelay_log_index     = mysql-relay-bin.indexmax_binlog_size     = 200Mslave-skip-errors   = 1062,1053skip_slave_start    = 1replicate_do_db　　 = db_1
replicate_do_db　　 = db_2
......

replicate_ignore_db　　 = db_x
replicate_ignore_db　　 = db_y

Relay_log relay log, dedicated from the server, I understand is equivalent to buffer,
The binary logs of the master server --> A series of slave server relay logs --> reading the relay logs one by one --> restoring to SQL statement execution --> Synchronization
Slave-Skip-errors ignores some errors during the replication process so that the replication continues. Errors 1062 and 1053 are ignored here,
If you want to ignore all errors, set it to all. Once an error occurs, synchronization stops. You can use show salve status \ G to view the last error,
Then manually modify (the e.g. Id field is auto-incrementing, and an id = 2 is inserted in the primary MySQL database. If Id = 2 is already available for various reasons,
The synchronization will stop due to an error, that is, the thread reading relay logs mentioned above will stop ),
Restart salve (change master to...; Start slave)
Skip_slave_start prevents slave servers from automatically starting after the server crashes to give you enough time to fix the problem. (There is also a saying that the slave server does not start the slave server thread when the server starts. Use the start slave statement to start the thread later .)

Some commands: (it is best to have the super permission to use these commands)

MASTER: show Master Status; check which binary log file is being written by the master server and the offset position (I am also fuzzy, it seems like a timestamp mark)

Show processlist;

View the status of the slave server from: Show slave status \ G. The information is large but easy to understand. The binary information of the master server read and the information of the active relay log are displayed,

The two threads mentioned above, the last error information, and so on.

Stop slave; stop listening to master server information

Change master to... dynamically set the configuration information to tell the slave server to synchronize the data of the database, or write it in the slave server configuration file,

By default, a master.info file is generated in the path of the database, which is read every time MySQL is started,

Therefore, if an error occurs or some settings need to be changed, restart the server and delete master.info to regenerate it.

Start slave; start to listen to the data of the master server

 

Command usage:

First use show Master Status on the master server; record the log file name and offset location in use

Then change master to on the slave server (a bunch of parameters tell the slave server to connect to which master server and which log file to use)

----------------------------------------

Some parameters are not clear and are updated later.

Suddenly saw a very detailed article: http://www.litvip.com/2011/06/01/291

In some cases, the slave server fails to be updated. First, you need to determine whether the table on the slave server is different from that on the master server. If the table structure is the cause, modify the table on the slave server to be consistent with that on the master server, and then run start slave again.
If the update fails because of different table structures, you need to confirm whether the manual update is secure, ignore the update failure Statement on the independent server, and skip the statements on the independent server, the command is set global SQL _slave_skip_counter = n. Where, n = 1 indicates that the update statement of the autonomous server does not use auto_increment or last_insert_id (). If n = 2, the opposite is true, the reason is that the statement using auto_increment or last_insert_id needs to get two events from the binary log.
Select master_pos_wait ('mysql-bin.000004 ', '123 ');

This SELECT statement will block until the slave server reaches the specified log file and offset, and return 0. If it is-1, it indicates timeout and when the query is 0, indicates that the slave and master servers have been synchronized (http://www.blogjava.net/dongbule/archive/2010/08/23/329714.html)

If binary logs are enabled for the slave server (so that the slave server can be the master server one day), the slave server does not actively write the SQL statement executed by the preceding thread into the binary, if the slave server is also master, you must enable the logs-slave-updates setting to record all data changes on the slave server.

Configuration File Settings for master-slave Synchronization

Http://hi.baidu.com/dc_life/item/d5e92ef7317acb40932af258

Http://www.blogjava.net/dongbule/archive/2010/09/04/331050.html

How to switch between the master and slave roles and other knowledge points not involved

Http://yyl-211.blog.163.com/blog/static/9348961201010173153872/

Tip: http://www.cnblogs.com/iLoveMyD/archive/2012/06/15/2550925.html