mysql互为主从复制详解----mysql-mm - 数据库编程

sql> reset master;
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 106
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
[root@XKWB5510 ~]# ls -l /var/mysql/database/data/
total 0

查看data目录下是否有报错文件：
[root@XKWB5510 data]# ls /var/mysql/database/data/

重新执行change master to 命令以后，再启动slave，再看一下slave的状态，I/O进程起来了

进程都起来之后，就实施监控
-------------------------------------------------------------------------------------------------------------
报错：
1）
change master导致的：
Last_IO_Error: error connecting to master 'repl1@A_IP:3306' - retry-time: 60 retries

2）
在没有解锁的情况下停止slave进程：
mysql> stop slave;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

3）
change master语法错误，落下逗号
mysql> change master to
-> master_host='211.100.97.250'
-> master_user='repl2',
-> master_password='123456',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=106;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master_user='repl2',
master_password='123456',
master_log_file='mysql-bin.000002' at line 3

4）
在没有停止slave进程的情况下change master
mysql> change master to master_host='211.100.97.246', master_user='repl1', master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=106;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

5）
A B的server-id相同：
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;
these ids must be different for replication to work (or the --replicate-same-server-id option must be used on
slave but this does not always make sense; please check the manual before using it).
查看server-id
mysql> show variables like 'server_id';
手动修改server-id
mysql> set global server_id=2; #此处的数值和my.cnf里设置的一样就行
mysql> slave start;

6）change master之后，查看slave的状态，发现slave_IO_running 为NO
需要注意的是，做完上述操作之后最后重启mysql进程
---------------------------------------
同步数据情况
A：在A上插入数据
mysql> create table aniya (id int not null auto_increment primary key, str varchar(255) not null);
mysql> insert into aniya(str) values
-> ('This is a master to master test table');

mysql> select * from aniya;
+----+---------------------------------------+
| id | str |
+----+---------------------------------------+
| 1 | This is a master to master test table |
+----+---------------------------------------+
1 row in set (0.00 sec)

查看B的日志：
[root@XKWB5705 var]# ls -lrth XKWB5705-relay-bin.000003
-rw-rw---- 1 mysql mysql 576 Sep 26 12:29 XKWB5705-relay-bin.000003
[root@XKWB5705 var]# more XKWB5705-relay-bin.000003
.in.N

(id int not null auto_increment primary key,
str varchar(255) not null)3
('This is a master to master test table')

-----
B A主从同步测试

在B上创建表lian，并插入数据
mysql> create table lian (a int,b char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into lian (a,b)values(22,hahah);
ERROR 1054 (42S22): Unknown column 'hahah' in 'field list'
mysql> insert into lian (a,b)values(22,'hahah');
Query OK, 1 row affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| lian |
+----------------+
1 row in set (0.00 sec)

mysql> select * from lian;
+------+-------+
| a | b |
+------+-------+
| 22 | hahah |
+------+-------+
1 row in set (0.00 sec)

查看一下B的master日志，证明以上操作成功：
cat mysql-bin.000002
. Nh @stdtestcreate table lian (a int,b char(10)) Nl>@stdtestinsert into lian (a,b)values(22,'hahah')

现在查看从服务器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')

现在说明数据B A 主从同步成功
---------------------------------------------------------------------------
测试A B主从

mysql> create table From246(Name varchar(255),Sex varchar(255),Age int(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into From246 (Name,Sex,Age)values('Zhaoyj','Girl',24);
Query OK, 1 row affected (0.00 sec)

mysql> select * from From246;
+--------+------+------+
| Name | Sex | Age |
+--------+------+------+
| Zhaoyj | Girl | 24 |
+--------+------+------+
1 row in set (0.00 sec)

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

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

查看B的relay日志，同步日志成功
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]

查看B的relay日志状态
[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*/;

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

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

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'

那现在应该是在启动mysql的时候统一他们的字符集
A :[root@XKWB5510 var]# /usr/local/mysql/bin/mysqld_safe --default-character-set=latin1 &
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

从以上可以看到是同步的

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

flush master
flush slave

摘自：ANLJF的专栏

mysql互为主从复制详解----mysql-mm(三)