mysql master slave server synchronization experience

I have seen the implementation of MYSQL synchronization data, but I have not touched it. Today, I have nothing to do but to play with it. There is another empty computer beside me, all under the same router. Haha, just right.

However, it took me a long time to find the configuration file. Since I used the xampp installation package, I could not find my.cnf in the xampp/mysql/bin directory, and
in the c:windows directory


In the picture above, all you see is an my speed dial thing, not a file. I found it for a long time before I read on the Internet that I needed to open editplus first and then open the file from editplus. Sure enough...

Configuration is not to say, there are many on the Internet, I originally also turned a.

Today, I'm going to focus on one important aspect of configuring master slave servers:

If your database has been running for some time, it is recommended that you delete all the binary log files, including the index xxx.index.

All binary log files have been deleted from the server, including, of course, one master.info file. This file is used to record the log files coming from the primary server and record the location. If you don't delete it, it's going to do what it did before, so it's going to go wrong, and I've wasted a lot of time here.

Master server diagnosis:

show   processlist; Displays all processes.

show   master   status; Displays the log file and pointer location for the primary server.

mysql >   show   master   status;
+ + - - - - - - - + + - - - - - - - - - +
File   Position   Binlog_Do_DB   Binlog_Ignore_DB
+ + - - - - - - - + + - - - - - - - - - +
mysql-bin.000001   603   videoCommunity
+ + - - - - - - - + + - - - - - - - - - +
1   row   in   set   (0.00   sec)

mysql >   show   master   status\G
***************************   1.   row   ***************************
File:   mysql-bin.000001
Position:   603
Binlog_Do_DB:   videoCommunity
Binlog_Ignore_DB:
1   row   in   set   (0.00   sec)

As shown in the figure above, mysql-es95.000001 is the log file and 603 is the pointer position.

Diagnose from the server (slave) :

mysql >   show   slave   status\G
***************************   1.   row   ***************************
Slave_IO_State:   Waiting   for   master   to   send   event
Master_Host:   master1
Master_User:   root
Master_Port:   3306
Connect_Retry:   60
Master_Log_File:   mysql-bin.000001
Read_Master_Log_Pos:   603
Relay_Log_File:   master2-relay-bin.000053
Relay_Log_Pos:   740
Relay_Master_Log_File:   mysql-bin.000001
Slave_IO_Running:   Yes
Slave_SQL_Running:   Yes
Replicate_Do_DB:   videoCommunity
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:   0
Last_Error:
Skip_Counter:   0
Exec_Master_Log_Pos:   603
Relay_Log_Space:   740
Until_Condition:   None
Until_Log_File:
Until_Log_Pos:   0
Master_SSL_Allowed:   No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:   0
1   row   in   set   (0.00   sec)

Verify that the above information is consistent with the primary server.

In addition, each of the above parameters has the following meaning:

SHOW   SLAVE   STATUS   returns the following information:

Slave_IO_State  
SHOW   PROCESSLIST   output a copy of the   State   field. This tells you whether the thread is connecting to the master server, whether it is waiting for an event from the master server, whether it is reconnecting to the master server, and so on. All possible replication implementation details are listed in the state manager manual. This information is important, such as the thread running but not successfully connecting to the main server, and only this field can tell the user about the connection problem. Because the   SQL   thread is simple, there is no copy of its state. If it runs, there is no problem, and if it does not run, an error can be found in the   Last_Error   domain (described below).

Master_Host  
Current master server host.

Master_User  
The current user connecting to the primary server.

Master_Port  
Current primary server port.

Connect_Retry  
Option  , the current value of master-connect-retry  .

Master_Log_File  
Name of the primary server binary log file currently read by the I/O   thread.

Read_Master_Log_Pos  
The starting point of the location where the I/O   thread reads from the current primary server binary log.

Relay_Log_File  
Name of the relay log file currently read and executed by the SQL   thread.

Relay_Log_Pos  
The SQL   thread currently reads the starting point of the execution from the relay log.

Relay_Master_Log_File  
The name of the binary log file in the primary server that contains the last event executed by the   SQL   thread.

Slave_IO_Running  
Is I/O   thread started?

Slave_SQL_Running  
SQL   thread started.

Replicate_Do_DB,   Replicate_Ignore_DB  
If so, the list of databases specified by the options  , replicate-do-db   and  , replicate-ignore-db  .

Replicate_Do_Table,   Replicate_Ignore_Table,   Replicate_Wild_Do_Table,   Replicate_Wild_Ignore_Table  
If so, it is a list of the tables specified in the options  , replicate-do-table,  , replicate-ignore-table,  , replicate-wild-table,  , replicate-wild-ignore_table  .

Last_Errno,   Last_Error  
The error number and error message returned by the most recently executed query. The error number is   0   and an empty message indicates that there is no error. If the   Last_Error   value is not empty, an error message will also appear in the error log from the server.