作为初学者,要想取得进步,成为高手,首先应该了解自己的不足之处.
全部博文(117)
分类: Mysql/postgreSQL
2011-06-02 19:16:30
结构如下
M——M(log-slave-update)——slave
\——slave
……
系统环境CentOS5.5
MySQL版本5.5
利用mysqld_multi工具,搭建单机多实例
master1 ora01.dh.cn 3307
master2 ora01.dh.cn 3308
slave ora01.dh.cn 3309
扩展知识
prompt http://blog.chinaunix.net/space.php?uid=16844903&do=blog&id=342376
multi http://blog.chinaunix.net/space.php?uid=16844903&do=blog&id=335025
log-slave-update 将relay-log写入自己binlog
skip-slave-start 开启mysql slave,不自动打开slave复制进程
配置文件如下
# cat /etc/my.cnf
#mysql muliti config file
[mysql]
prompt=\\u@\\h \\d \\r:\\m:\\s>\ \
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
[mysqld2]
socket = /var/lib/mysql2/mysql2.sock
port = 3307
datadir = /var/lib/mysql2
user = mysql
server-id = 2
log-slave-update
log-bin = mysqld2
log-bin-index = mysqld2.index
relay-log = relay-mysqld2
relay-log-index = relay-mysqld2.index
skip-slave-start
[mysqld3]
socket = /var/lib/mysql3/mysql3.sock
port = 3308
datadir = /var/lib/mysql3
user = mysql
server-id = 3
log-slave-update
log-bin = mysqld3
log-bin-index = mysqld3.index
relay-log = relay-mysqld3
relay-log-index = relay-mysqld3.index
skip-slave-start
[mysqld4]
socket = /var/lib/mysql4/mysql4.sock
port = 3309
datadir = /var/lib/mysql4
user = mysql
server-id = 4
log-bin = mysqld4
log-bin-index = mysqld4.index
relay-log = relay-mysqld4
relay-log-index = relay-mysqld4.index
skip-slave-start
# mysqld_multi start
# mysqld_multi report
环境如下
master1 mysql2 3307端口
master2 mysql3 3308端口
salve mysql4 3309端口
change master to master_host='192.168.60.134',master_port=3308, master_user='repli',master_password='123456',master_log_file='mysqld3.000002',master_log_pos=107;
在slave执行完上面的语句后,就会生成master.info和relay-log.info文件
start slave;
在没有打开log_bin的服务器上,执行reset master,则会报错
root@localhost test 05:09:32> reset master;
ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER
需要打开log_bin文件,执行reset master后
Slave_IO_Running: No
Slave_SQL_Running: No
会自动关闭,但是master.info和relay-log.info不会自动删除。如果此时执行start slave;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这两个线程会启动。并且会接受更新的数据。这让我想不明白?
reset master 与 stop slave这两个命令有什么区别?这里先留下一个疑问。疑问揭开,reset master命令会让log_bin重新开始,并且之前的log_bin就删除了。
root@localhost test 05:19:16> show master status;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| mysqld4.000001 | 107 | | |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注意,一般执行顺序:先stop slave;再reset master,为保证数据不丢失。
测试一
原理如下
master1和master2都有relay-log,数据流程:
master1更新数据,写入logbin,由master1的IO线程传到master2上,master2的slave io_thread线程接受,并写入relay-log中,同时写入自己的logbin。然后master1接受由master2传过来的logbin,master1将接收的日志写入自己relay-log,但是不会再写入自己的binlog(过滤),因为server_id是自己的。(经过下面的测试,这种规则是不是在MySQL5.5中已经做了修改???)
master上面的io发送线程会随着slave数量的增加而增加。
做个实验,进行验证
master1与master2做了MM复制
为了方便测试,刷新log_bin。(flush logs)
1·在master1上创建一个表
CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` datetime DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
查看log_bin内容
[root@ora01 mysql2]# mysqlbinlog mysqld2.000006
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#110602 17:25:17 server id 2 end_log_pos 107 Start: binlog v 4, server v 5.5.11-log created 110602 17:25:17
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
/VbnTQ8CAAAAZwAAAGsAAAABAAQANS41LjExLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#110602 17:29:23 server id 2 end_log_pos 316 Query thread_id=171 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1307006963/*!*/;
SET @@session.pseudo_thread_id=171/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` datetime DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
2·查看master1的relay-log
没变化
3·在mater2上查看relay-log(这里再插入一个问题:flush logs命令可以刷新log_bin,但是relay-log用啥命令刷新??,有一种方法是先stop slave再reset slave)
[root@ora01 mysql3]# mysqlbinlog relay-mysqld3.000020
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#110602 17:26:01 server id 3 end_log_pos 107 Start: binlog v 4, server v 5.5.11-log created 110602 17:26:01
BINLOG '
KVfnTQ8DAAAAZwAAAGsAAAAAAAQANS41LjExLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#110602 17:25:17 server id 2 end_log_pos 0 Start: binlog v 4, server v 5.5.11-log created 110602 17:25:17
BINLOG '
/VbnTQ8CAAAAZwAAAAAAAAAgAAQANS41LjExLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 210
#110602 17:29:23 server id 2 end_log_pos 316 Query thread_id=171 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1307006963/*!*/;
SET @@session.pseudo_thread_id=171/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` datetime DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
4·再查看master2的log_bin文件
[root@ora01 mysql3]# mysqlbinlog mysqld3.000006
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#110602 17:26:01 server id 3 end_log_pos 107 Start: binlog v 4, server v 5.5.11-log created 110602 17:26:01
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
KVfnTQ8DAAAAZwAAAGsAAAABAAQANS41LjExLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#110602 17:29:23 server id 2 end_log_pos 316 Query thread_id=171 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1307006963/*!*/;
SET @@session.pseudo_thread_id=171/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` datetime DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
查看master1的relay-log没有更新的记录???
再做如下测试,在master2端(对应mysqld3),删除刚才建的表。
查看log_bin和relay-log
-rw-rw---- 1 mysql mysql 420 Jun 2 18:02 mysqld3.000006
-rw-rw---- 1 mysql mysql 419 Jun 2 17:29 relay-mysqld3.000020
-rw-rw---- 1 mysql mysql 420 Jun 2 18:02 mysqld2.000006
-rw-rw---- 1 mysql mysql 355 Jun 2 18:02 relay-mysqld2.000009
---------------------------------------------------------
个人总结:
前提,两个主的server_id不一样?(提出个疑问:当master1宕机后,master2会不会因为server-id不一样,而出现slave的同步问题...)
在master1上更新数据,记录到master1的log_bin中,然后通过master的IO线程发送到master2上,由master2的IO线程,写入master2的relay-log,并写入自己的log_bin(log_slave_update参数)。但是log_bin的更改,并不会发送到master1上面,而且master1上面的relay-log没有更新。可能在此就会涉及到server_id的问题。如果server-id相同,是不会发送log_bin的。
参考《高性能MySQL 第二版》的276页,主动-被动模式下的主主复制。
我在MySQL 5.0的版本下做了同样的测试,结果在MySQL 5.5中一样,这样排除了版本的问题。
下面还有两个问题:
1·虚拟机环境问题(xen、vmware都做了测试)
2·参数配置问题
3·“选择唯一的server_id”
配置过程遇到的问题:
1·如果服务器是从其他平台拿过来的,要把不用的数据清理干净,特别是master.info和relay-log.info文件。
2·查看salve状态总是No,排查了用户权限和日志问题后,最后得出:是因为没有执行start slave;
Slave_IO_Running: No
Slave_SQL_Running: No
参考
1·《MySQL性能调优与架构设计》——当Master A crash 的时候如果Master B 作为Slave 的IO 线程如果还没有读取完Master A 的二进制日志的话,就会出现数据丢失的问题。要完全解决这个问题,我们只能通过第三方patch(google 开发)来镜像MySQL 的二进制日志到Master B上面,才能完全避免不丢失任何数据。
2·google patch 测试http://blog.sina.com.cn/s/blog_499740cb0100g3v9.html