Mysql is a master-slave problem-log synchronization data is not synchronized _ MySQL

Mysql is a master-slave problem-log synchronization data is not synchronized bitsCN.com

I set up mysql for master-slave replication.

The mysql environment of the two machines is identical

Part 1 test:

Synchronization test for master A and slave B

Create a table lian on B and insert data
Mysql> create table lian (a int, B char (10 ));

Mysql> insert into lian (a, B) values (22, 'hahah ');

Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Lian |
+ ---------------- +
Mysql> select * from lian;
+ ------ + ------- +
| A | B |
+ ------ + ------- +
| 22 | hahah |
+ ------ + ------- +

Check the binlog of master-B and check whether the above operations have logged:
Cat mysql-bin.000002
.? Nh? @ Stdtestcreate table lian (a int, B char (10 ))?? Nl> @ stdtestinsert into lian (a, B) values (22, 'hahahah ')

Now, check the relay log of slave-A and find that the log has been synchronized.
[Root @ XKWB5510 var] # cat XKWB5510-relay-bin.000003
.? Nh? @ Stdtestcreate table lian (a int, B char (10 ))?? Nl> @ stdtestinsert into lian (a, B) values (22, 'hahahah ')

Check whether the lian table exists in the database on slave-:
Mysql> use test;
Database changed
Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Aniya |
| Lian |
+ ---------------- +
2 rows in set (0.00 sec)

Data B A master slave synchronization is successful.
---------------------------------------------------------------------------
Part 2 test:

Test the synchronization of A as master B as slave

Create table From246 on Table A and insert data
Mysql> use test;
Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| A246 |
| Aniya |
| Lian |
+ ---------------- +
3 rows in set (0.00 sec)

Mysql> create table From246 (Name varchar (255), Sex varchar (255), Age int (10 ));
Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| A246 |
| From246 |
| Aniya |
| Lian |
+ ---------------- +
4 rows in set (0.00 sec)

Mysql> insert into From246 (Name, Sex, Age) values ('zhaoyj', 'gir', 24 );
Mysql> select * from From246;
+ -------- + ------ +
| Name | Sex | Age |
+ -------- + ------ +
| Zhaoyj | Girl | 24 |
+ -------- + ------ +
1 row in set (0.00 sec)

Check the binlog log of master-A to verify that the operation is successful.
[Root @ XKWB5510 var] # tail-1 mysql-bin.000002
Testcreate table From246 (Name varchar (255), Sex varchar (255), Age int (10 ))? N? R @ stdtestinsert into From246 (Name, Sex, Age) values ('zhaoyj', 'gir', 24)

View the log status of master-
[Root @ XKWB5510 var] #/usr/local/mysql/bin/mysqlbinlog mysql-bin.000003 | tail-15
/*! */;
# At 702
#110926 14:01:51 server id 1 end_log_pos 838 Query thread_id = 5 exec_time = 0 error_code = 0
Set timestamp = 1317016911 /*! */;
Create table From246 (Name varchar (255), Sex varchar (255), Age int (10 ))
/*! */;
# At 838
#110926 14:02:05 server id 1 end_log_pos 966 Query thread_id = 5 exec_time = 0 error_code = 0
Set timestamp = 1317016925 /*! */;
Insert into From246 (Name, Sex, Age) values ('zhaoyj', 'gir', 24)
/*! */;
DELIMITER;
# End of log file
ROLLBACK/* added by mysqlbinlog */;
/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;

Check the relay-B log. the log is synchronized successfully.
Testcreate table From246 (Name varchar (255), Sex varchar (255), Age int (10 ))? N? R @ stdtestinsert into From246 (Name, Sex, Age) values ('zhaoyj', 'gir', 24) [root @ XKWB5705 var]

View the relay-B log status. you can see that the log has been synchronized.
[Root @ XKWB5705 var] #/usr/local/mysql/bin/mysqlbinlog XKWB5705-relay-bin.000005 | tail-13
/Usr/local/mysql/bin/mysqlbinlog: character set '# 28' is not a compiled character set and is not specified in the'/usr/local/mysql/share/mysql/charsets/Index. xml 'file
#110926 14:01:51 server id 1 end_log_pos 838 Query thread_id = 5 exec_time = 0 error_code = 0
Set timestamp = 1317016911 /*! */;
Create table From246 (Name varchar (255), Sex varchar (255), Age int (10 ))
/*! */;
# At 853
#110926 14:02:05 server id 1 end_log_pos 966 Query thread_id = 5 exec_time = 0 error_code = 0
Set timestamp = 1317016925 /*! */;
Insert into From246 (Name, Sex, Age) values ('zhaoyj', 'gir', 24)
/*! */;
DELIMITER;
# End of log file
ROLLBACK/* added by mysqlbinlog */;
/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;

However, the data is not inserted into the relay-B database.
Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Lian |
+ ---------------- +
1 row in set (0.00 sec)

When I delete the table on master-A, the relay-B log is also synchronized.
[Root @ XKWB5705 var] # tail-4 XKWB5705-relay-bin.000005
?? NS? @ Stdtestdrop table A246 ?? NT @ stdtestdrop table aniya ?? NSd @ stdtestdrop table lian ?? NV? @ Stdtestdrop table From246

What's the strange problem ??
------------------------------------------------------------------------------
Troubleshooting:

First, use

Show processlist; check whether there are too many Sleep processes. Found to be normal

Show master status; also normal

Run the "show Slave status" command to view the slave status.

When I manually import data from A to B, I find A problem:
Mysql> load table From246 from master;
ERROR 1115 (42000): Unknown character set: 'gbk'

Suspect: Is it because of a string problem that AB master-slave replication fails?

Run the show character set command.
Master-A has the gbk character set while slave-B does not
Mysql> show character set;
+ ---------- + ----------------------------- + --------------------- + -------- +
| Charset | Description | Default collation | Maxlen |
+ ---------- + ----------------------------- + --------------------- + -------- +
| Dec8 | DEC West European | dec8_swedish_ci | 1 |
| Cp850 | DOS West European | cp850_general_ci | 1 |
| Hp8 | HP West European | hp8_english_ci | 1 |
| Koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| Latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| Latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| Swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| Ascii | us ascii | ascii_general_ci | 1 |
| Hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| Koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| Greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| Cp1250 | Windows Central European | cp1250_general_ci | 1 |
| Gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| Latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| Armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| Utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| Cp866 | DOS Russian | cp866_general_ci | 1 |
| Keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| Macce | Mac Central European | macce_general_ci | 1 |
| Macroman | Mac West European | macroman_general_ci | 1 |
| Cp852 | DOS Central European | cp852_general_ci | 1 |
| Latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| Cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| Cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| Cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| Binary | Binary pseudo charset | binary | 1 |
| Geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
+ ---------- + ----------------------------- + --------------------- + -------- +
27 rows in set (0.00 sec)

Now we should unify their character set when starting mysql.
Master-A: [root @ XKWB5510 var] #/usr/local/mysql/bin/mysqld_safe -- default-character-set = latin1 &
Slave-B: [root @ XKWB5705 var] #/usr/local/mysql/bin/mysqld_safe -- default-character-set = latin1 &

Import data from A on B again:
Mysql> show tables;
Empty set (0.00 sec)

Mysql> load table From246 from master;
Query OK, 0 rows affected (0.01 sec)

Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| From246 |
+ ---------------- +
1 row in set (0.00 sec)

Now the character set problem has been solved.

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

Now manually start the "apply logs to database" thread: SLAVE start SQL _THREAD
And "write logs from the master segment to the local" thread: SLAVE start IO_THREAD

If data synchronization still fails, it is not a thread problem.

If Seconds_Behind_Master is found to be (null)
Solution:
Stop slave;
Set global SQL _slave_skip_counter = 1;
Start slave;
After that, Slave will synchronize with the Master to check whether Seconds_Behind_Master is 0 until it is 0 ..

-----------------------------------
Master.info information on server load balancer B and master A are synchronized

Mater:
Mysql> show master status/G;
* *************************** 1. row ***************************
File: mysql-bin.000004
Position: 808
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

Slave B:
[Root @ XKWB5705 var] # cat master.info
15
Mysql-bin.000004
808
211.100.97.246
Repl2
123456
3306
60
0

We can see from the above that it is synchronized.

Author's "ANLJF column"

BitsCN.com