search

Home >Database >Mysql Tutorial >Mysql互为主从问题--日志同步数据不同步_MySQL

Mysql互为主从问题--日志同步数据不同步_MySQL

WBOY
WBOYOriginal
2016-06-01 13:45:03862browse

bitsCN.com

我搭建的是mysql 互为主从 复制

两台机器的mysql环境完全相同

 

第一部分测试：

B为master  A为slave的同步测试


在B上创建表lian，并插入数据
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 |
+------+-------+

查看一下master-B的binlog日志，查看以上操作是否记录了日志：
cat mysql-bin.000002                                                                                                                                                                                  
.?Nh?@stdtestcreate table lian (a int,b char(10))??Nl>@stdtestinsert into lian (a,b)values(22,'hahah')

现在查看slave-A的relay日志，发现日志已经同步了
[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,'hahah')

再在slave-A上看一下数据库是不是存在lian这个表：
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| aniya          |
| lian           |
+----------------+
2 rows in set (0.00 sec)

 

现在说明数据B A 主 从 同步成功
---------------------------------------------------------------------------
第二部分测试：

A为master  B为slave的同步测试

 

在A上创建表From246，并插入数据
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','Girl',24);
mysql> select * from From246;
+--------+------+------+
| Name   | Sex  | Age  |
+--------+------+------+
| Zhaoyj | Girl |   24 |
+--------+------+------+
1 row in set (0.00 sec)


查看master-A的binlog日志,证明上述操作成功
[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','Girl',24)

查看master-A的日志状态
[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','Girl',24)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;


查看relay-B的日志，同步日志成功
testcreate table From246(Name varchar(255),Sex varchar(255),Age int(10))?N?R@stdtestinsert into From246 (Name,Sex,Age)values('Zhaoyj','Girl',24)[root@XKWB5705 var]

查看relay-B日志状态，可以看到日志已经同步
[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','Girl',24)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;


但是数据却没有插入到relay-B的数据库
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| lian           |
+----------------+
1 row in set (0.00 sec)

当我删除master-A上的表时，relay-B日志也同步了
[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

 

这是什么奇怪问题 ？？
------------------------------------------------------------------------------
问题排查：

首先在Master-A上用

show processlist; 查看下进程是否Sleep太多。发现很正常

show master status; 也正常

再跑到Slave上查看 show slave status; 也正常


当我手动从A导入B数据时，发现一个问题：
mysql> load table From246 from master;
ERROR 1115 (42000): Unknown character set: 'gbk'

怀疑：难道是因为字符串的问题导致AB主从复制失败 ？


通过show character set 命令查看到
master-A有gbk字符集而slave-B没有
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)


那现在应该是在启动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 &

再次在B上从A导入数据：
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)

现在字符集的问题解决了

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


现在手动启动一下“将日志应用于数据库”的线程：SLAVE start SQL_THREAD
和“把master段的日志写到本地”的线程：SLAVE start IO_THREAD

发现同步数据还是失败，那说明不是线程的问题

如果发现 Seconds_Behind_Master 为 (null)
解决：
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;
之后Slave会和Master去同步 主要看Seconds_Behind_Master是否为0，直到为0时就已经同步了。。

 


-----------------------------------
slave B机器上master.info信息，与master A上的信息是否是同步的

mater A：
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

从以上可以看到是同步的

作者“ANLJF的专栏”
 

bitsCN.com
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：Mysql安装及日常使用一_MySQLNext article：修改mysql的root密码_MySQL

Related articles

See more