innobackupex全备份+binlog恢复,出现问题server_id相同,但是我已经修改了server_id.

环境:单机双实例,IP192.168.2.102,实例1端口3306,实例2端口3307.
步骤:
1.innobackupex --user=root --password=123456 --no-timestamp --defaults-file=/etc/my3306.cnf /data/mysqldata3306/bak/20160118_full/
 
root@localhost:mysql3306.sock  10:03:22 >insert into t2(name,memo) values('bbbbbbbbbb','1234567890');
Query OK, 1 row affected (0.01 sec)

root@localhost:mysql3306.sock  10:03:25 >insert into t2(name,memo) values('bbbbbbbbbb','1234567890');
Query OK, 1 row affected (0.01 sec)

root@localhost:mysql3306.sock  10:03:26 >select * from t2;
+----+------------+------------+
| id | name       | memo       |
+----+------------+------------+
|  1 | aaaaaaaaaa | 1234567890 |
|  2 | bbbbbbbbbb | 1234567890 |
|  3 | bbbbbbbbbb | 1234567890 |
|  4 | bbbbbbbbbb | 1234567890 |
+----+------------+------------+
4 rows in set (0.00 sec)

root@localhost:mysql3306.sock  10:03:27 >insert into t2(name,memo) values('cccccccccc','1234567890');
Query OK, 1 row affected (0.00 sec)

root@localhost:mysql3306.sock  10:03:35 >select * from t2;
+----+------------+------------+
| id | name       | memo       |
+----+------------+------------+
|  1 | aaaaaaaaaa | 1234567890 |
|  2 | bbbbbbbbbb | 1234567890 |
|  3 | bbbbbbbbbb | 1234567890 |
|  4 | bbbbbbbbbb | 1234567890 |
|  5 | cccccccccc | 1234567890 |
+----+------------+------------+
5 rows in set (0.00 sec)

root@localhost:mysql3306.sock  10:03:36 >insert into t2(name,memo) values('cccccccccc','1234567890');
Query OK, 1 row affected (0.00 sec)

root@localhost:mysql3306.sock  10:03:39 >insert into t2(name,memo) values('dddddddddd','1234567890');
Query OK, 1 row affected (0.00 sec)

root@localhost:mysql3306.sock  10:03:46 >select * from t2;
+----+------------+------------+
| id | name       | memo       |
+----+------------+------------+
|  1 | aaaaaaaaaa | 1234567890 |
|  2 | bbbbbbbbbb | 1234567890 |
|  3 | bbbbbbbbbb | 1234567890 |
|  4 | bbbbbbbbbb | 1234567890 |
|  5 | cccccccccc | 1234567890 |
|  6 | cccccccccc | 1234567890 |
|  7 | dddddddddd | 1234567890 |
+----+------------+------------+
7 rows in set (0.00 sec)

root@localhost:mysql3306.sock  10:03:50 >flush logs;
Query OK, 0 rows affected (0.02 sec)

root@localhost:mysql3306.sock  10:03:55 >flush logs;
Query OK, 0 rows affected (0.01 sec)

root@localhost:mysql3306.sock  10:03:55 >insert into t2(name,memo) values('eeeeeeeeee','1234567890');
Query OK, 1 row affected (0.00 sec)

root@localhost:mysql3306.sock  10:04:03 >insert into t2(name,memo) values('eeeeeeeeee','1234567890');
Query OK, 1 row affected (0.00 sec)

root@localhost:mysql3306.sock  10:04:04 >insert into t2(name,memo) values('ffffffffff','1234567890');
Query OK, 1 row affected (0.00 sec)

root@localhost:mysql3306.sock  10:04:10 >insert into t2(name,memo) values('ffffffffff','1234567890');
Query OK, 1 row affected (0.00 sec)

root@localhost:mysql3306.sock  10:04:10 >insert into t2(name,memo) values('gggggggggg','1234567890');
Query OK, 1 row affected (0.00 sec)

root@localhost:mysql3306.sock  10:04:17 >insert into t2(name,memo) values('gggggggggg','1234567890');
Query OK, 1 row affected (0.00 sec)

root@localhost:mysql3306.sock  10:04:18 >select * from t2;
+----+------------+------------+
| id | name       | memo       |
+----+------------+------------+
|  1 | aaaaaaaaaa | 1234567890 |
|  2 | bbbbbbbbbb | 1234567890 |
|  3 | bbbbbbbbbb | 1234567890 |
|  4 | bbbbbbbbbb | 1234567890 |
|  5 | cccccccccc | 1234567890 |
|  6 | cccccccccc | 1234567890 |
|  7 | dddddddddd | 1234567890 |
|  8 | eeeeeeeeee | 1234567890 |
|  9 | eeeeeeeeee | 1234567890 |
| 10 | ffffffffff | 1234567890 |
| 11 | ffffffffff | 1234567890 |
| 12 | gggggggggg | 1234567890 |
| 13 | gggggggggg | 1234567890 |
+----+------------+------------+
13 rows in set (0.00 sec)

root@localhost:mysql3306.sock  10:04:21 >select * from t2;
+----+------------+------------+
| id | name       | memo       |
+----+------------+------------+
|  1 | aaaaaaaaaa | 1234567890 |
|  2 | bbbbbbbbbb | 1234567890 |
|  3 | bbbbbbbbbb | 1234567890 |
|  4 | bbbbbbbbbb | 1234567890 |
|  5 | cccccccccc | 1234567890 |
|  6 | cccccccccc | 1234567890 |
|  7 | dddddddddd | 1234567890 |
|  8 | eeeeeeeeee | 1234567890 |
|  9 | eeeeeeeeee | 1234567890 |
| 10 | ffffffffff | 1234567890 |
| 11 | ffffffffff | 1234567890 |
| 12 | gggggggggg | 1234567890 |
| 13 | gggggggggg | 1234567890 |
+----+------------+------------+
13 rows in set (0.00 sec)
 
exit;
 
innobackupex --apply-log /data/mysqldata3306/bak/20160118_full/
 
rm -rf /data/mysqldata3307/binlog/*
cp /data/mysqldata3306/binlog/* /data/mysqldata3307/binlog/*
sed -i 's/3306/3307/g' mysql-bin.index
chown -R mysql:mysql /data/mysqldata3307/binlog/*
mysqladmin -S /data/mysqldata3307/sock/mysql3307.sock shutdown
 
rm -rf /data/mysqldata3307/mysqldata3307/*
rm -rf /data/mysqldata3307/innodb_log/*
innobackupex --copy-back /data/mysqldata3306/bak/20160118_full/ --defaults-file=/etc/my3307.cnf
chown -R mysql:mysql /data/mysqldata3307/mysqldata3307/*
chown -R mysql:mysql /data/mysqldata3307/innodb_log/*
 
mysqld --defaults-file=/etc/my3307.cnf &
登录3307端口的MySQL
root@localhost:mysql3307.sock  10:17:02 >show binary logs;
有日志
 
 change master to
master_host='192.168.2.102',
 master_port=3307,
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000020',
master_log_pos=120;
start slave;
show slave status\G
 
root@localhost:mysql3307.sock  10:24:32 >show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.2.102
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000020
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqlmaster02-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000020
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1593
                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).
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10233071
                  Master_UUID: 
             Master_Info_File: /data/mysqldata3307/mysqldata/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 160119 10:24:29
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
 
 
mysqlbinlog mysql-binlog.000001 |less
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#151229 14:42:37 server id 1023306  end_log_pos 120 CRC32 0x0a7f818a    Start: binlog v 4, server v 5.6.27-log created 151229 14:42:37 at startup
ROLLBACK/*!*/;
BINLOG '
XSuCVg9KnQ8AdAAAAHgAAAAAAAQANS42LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABdK4JWEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYqB
fwo=
'/*!*/;
# at 120
 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160119 10:16:32 server id 1023307  end_log_pos 120 CRC32 0xbde55302    Start: binlog v 4, server v 5.6.27-log created 160119 10:16:32 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
 
 
root@localhost:mysql3307.sock  10:24:47 >show variables like '%server_id%';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| server_id      | 10233071 |
| server_id_bits | 32       |
+----------------+----------+
2 rows in set (0.00 sec)
 
 

root@localhost:mysql3307.sock  11:10:04 >select * from t2;
+----+------------+------------+
| id | name       | memo       |
+----+------------+------------+
|  1 | aaaaaaaaaa | 1234567890 |
|  2 | bbbbbbbbbb | 1234567890 |
+----+------------+------------+
2 rows in set (0.00 sec)
 
数据没有同步过来,请教各位好手。
 
 
 
 
 
 
已邀请:

yaowenlong

赞同来自:

有点长没有后面没有看完,
你再看看一下操作是不是有问题,master_port=3307
登录3307端口的MySQL
root@localhost:mysql3307.sock  10:17:02 >show binary logs;
有日志
 
 change master to
master_host='192.168.2.102',
 master_port=3307,
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000020',
master_log_pos=120;
start slave;
show slave status\G

wubx - 专注MySQL及架构设计

赞同来自:

看了一下,感觉还是有点乱。 先咨询一下:
1.  我看你把库恢复到3307上了。 
2. change master 你也change 到3307那个端口
   change master to
master_host='192.168.2.102',
 master_port=3307,
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000020',
master_log_pos=120;
 
3. 你在3307那个端口上执行的show slave status\G;  
 root@localhost:mysql3307.sock  10:24:32 >show slave status\G
 
我猜测是你想用3306做从,然后同步3307上的数据,但3307上的日志是来自于3306端口,所以3307上copy过去的Server-id和3306上的一样。
如果是这样,你在3306上stop slave; set global server-id=1033061;  start slave; 在试一下。
 

要回复问题请先登录注册