MYSQL增加从库方式介绍(2)-木庄网络博客

在master上查找binlog

[root@Ucenter /data/mysqldata]# ll -t *bin*
-rw-rw---- 1 mysql mysql   30709744 Nov  2 21:32 mysql-bin.000268
-rw-rw---- 1 mysql mysql        171 Nov  2 19:41 mysql-bin.index
-rw-rw---- 1 mysql mysql 1021101094 Nov  2 19:41 mysql-bin.000267
-rw-rw---- 1 mysql mysql 1073742415 Oct 30 06:07 mysql-bin.000266
-rw-rw---- 1 mysql mysql 1073742062 Oct 26 12:03 mysql-bin.000265
-rw-rw---- 1 mysql mysql 1073742636 Oct 22 19:21 mysql-bin.000264
-rw-rw---- 1 mysql mysql 1073741961 Oct 18 22:49 mysql-bin.000263
-rw-rw---- 1 mysql mysql 1073742141 Oct 15 12:47 mysql-bin.000262
-rw-rw---- 1 mysql mysql 1073742284 Oct 11 10:18 mysql-bin.000261
-rw-rw---- 1 mysql mysql 1073742184 Oct  7 09:49 mysql-bin.000260

我的备份时间是2号4点开始，所以应该在mysql-bin.000267里查找2号4点前的pos，开始定位

[root@Ucenter /data/mysqldata]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000267 |grep '3:59:' |grep -v '13:59:'
#171102 3:59:58 server id 21323 end_log_pos 824385760 Query thread_id=3023086382 exec_time=0 error_code=0
#171102 3:59:58 server id 21323 end_log_pos 824386335 Query    thread_id=3023086386    exec_time=0    error_code=0
#171102 3:59:58 server id 21323 end_log_pos 824386363 Intvar
#171102 3:59:58 server id 21323 end_log_pos 824386698 Query    thread_id=3023086386    exec_time=0    error_code=0
#171102 3:59:58 server id 21323 end_log_pos 824387706 Query    thread_id=3023086388    exec_time=0    error_code=0
#171102 3:59:58 server id 21323 end_log_pos 824387734 Intvar
#171102 3:59:58 server id 21323 end_log_pos 824388209 Query    thread_id=3023086388    exec_time=0    error_code=0
#171102 3:59:58 server id 21323 end_log_pos 824388534 Query    thread_id=3023086398    exec_time=0    error_code=0

所以定位到最后 end_log_pos 824388534

4、在另外一台slave上查看master.info

[root@LeduPass02/data/mysqldata]# cat master.info?
15
mysql-bin.000268
11367810
192.168.100.70
omsync
om@123
3306
60
0

0
0

5、在slave 配置slave，进行同步

在Slave上配置相关设置，以便告诉Slave如何去连接Master，这包括主机地址，登陆凭证等等

[root@ucenter_slave /data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.51-Community-Server-log Source

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql (none)>change master to master_host='192.168.100.70',master_port=3306,master_user='omsync',master_password='om@123',master_log_file='mysql-bin.000267',master_log_pos=824388534;

开启slave