Mysql innodb replication

---

2015-09-25 by dongnan

环境

操作系统: CentOS 6.5
软件版本: mysql 5.6.x
存储引擎: innodb

目标

配置 mysql主从环境，master 已有数据添加 slave从库。

步骤

准备工作

配置文件见 http://archive.zongming.net/read-232#924

Master导出数据

mysqldump --all-databases --master-data=1 --single-transaction -uroot -p | gzip > all.sql.gz

Slave导入数据

gzip -dc all.sql.gz | mysql -uroot -p

获得 MASTER_LOG_FILE 与 MASTER_LOG_POS

head -n50 all.sql | awk '/CHANGE MASTER TO MASTER_LOG_FILE/'
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=19148;

配置Slave

连接到master，注意 MASTER_LOG_FILE 与 MASTER_LOG_POS 值。

mysql> change master to master_host='10.0.100.12',master_user='rep1',master_password='password',master_log_file='mysql-bin.000006',master_log_pos=19148;

启动slave进程

mysql> start slave;

验证

Slave状态

mysql> show slave status \G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.100.12
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 19148
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
#...略
      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:
     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)

Master状态

mysql> show processlist \G;

*************************** 1. row ***************************
     Id: 768
   User: rep_user
   Host: slave_ip:51870
     db: NULL
Command: Binlog Dump
   Time: 135
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************
#...省略

命令帮助

--master-data[=value]

   Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a
   slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log
   coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave
   should start replicating after you load the dump file into the slave.

   If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has
   no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes
   effect when the dump file is reloaded. If no option value is specified, the default value is 1.

   This option requires the RELOAD privilege and the binary log must be enabled.

   The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless
   --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning
   of the dump (see the description for --single-transaction). In all cases, any action on logs happens at the exact moment of
   the dump.

   It is also possible to set up a slave by dumping an existing slave of the master, using the --dump-slave option, which
   overrides --master-data and causes it to be ignored if both options are used.