复制搭建

环境准备:

两台mysql服务器(mysql 5.6.27)

MySQL01 192.168.11.129

MySQL02 192.168.11.140

一 复制搭建

MySQL01:

创建复制账号

 

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*  TO repl@'192.168.11.%' IDENTIFIED BY 'repl';

mysql>show grants for  'repl'@'192.168.11.%';
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for repl@192.168.11.%                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.11.%' IDENTIFIED BY PASSWORD '*A424E797037BF97C19A2E88CF7891C5C2038C039' |
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  14:49:14 [school]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000021 |      740 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

MySQL02:

root@localhost:mysql.sock  22:51:10 [(none)]>CHANGE MASTER TO MASTER_HOST='192.168.11.129', MASTER_USER='repl',MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.000021', MASTER_LOG_POS=740;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

root@localhost:mysql.sock  00:04:34 [school]>show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.129
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000021
          Read_Master_Log_Pos: 1392
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 935
        Relay_Master_Log_File: mysql-bin.000021
             Slave_IO_Running: Yes
            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: 1392
              Relay_Log_Space: 1108
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1763306
                  Master_UUID: 4269ee4d-e156-11e5-8ae9-000c295799ba
             Master_Info_File: /data/mysql/data/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: 
     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)

ERROR: 
No query specified

MySQL02

# show master status ; 

MySQL01 

mysql> change master to ... 完成主主搭建.

 

复制跳过语句:

1.跳过指定数量的事务
root@localhost:mysql.sock  18:48:16 [(none)]>stop slave;
root@localhost:mysql.sock  18:48:18 [(none)]>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 ; #跳过一个事务
root@localhost:mysql.sock  18:48:24 [(none)]>start slave;
2.修改配置文件,通过slave_skip_errors参数来跳所有错误或指定类型的错误

  vi /etc/my.cnf
  [mysqld]
  #slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
  #slave-skip-errors=all #跳过所有错误

 

 

MySQL01主库执行如下SQL语句

root@localhost:mysql.sock  14:57:14 [school]>use school;
Database changed
root@localhost:mysql.sock  15:01:36 [school]>create table s(sno int,sname varchar(50));
Query OK, 0 rows affected (0.11 sec)

root@localhost:mysql.sock  15:01:49 [school]>create table c(cno int,cname varchar(50));
Query OK, 0 rows affected (0.05 sec)

root@localhost:mysql.sock  15:02:03 [school]>insert into s values(1,'s01'),(2,'s02');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost:mysql.sock  15:02:12 [school]>insert into c values(1,'c01'),(2,'c02');
Query OK, 2 rows affected (0.09 sec)

MySQL02从库查看是否同步

root@localhost:mysql.sock  23:04:39 [school]>use school;
Database changed
root@localhost:mysql.sock  23:04:42 [school]>show tables;
+------------------+
| Tables_in_school |
+------------------+
| c                |
| s                |
+------------------+
2 rows in set (0.00 sec)

root@localhost:mysql.sock  23:04:44 [school]>select * from s;
+------+-------+
| sno  | sname |
+------+-------+
|    1 | s01   |
|    2 | s02   |
+------+-------+
2 rows in set (0.00 sec)

root@localhost:mysql.sock  23:04:49 [school]>select * from c;
+------+-------+
| cno  | cname |
+------+-------+
|    1 | c01   |
|    2 | c02   |
+------+-------+
2 rows in set (0.00 sec)
发现已经同步.

二 状态检查

MySQL01主库

在master上，你可以看到slave的I/O线程创建的连接：
root@localhost:mysql.sock  15:05:32 [school]>show processlist \G;
*************************** 1. row ***************************
     Id: 3
   User: repl
   Host: 192.168.11.140:52216
     db: NULL
Command: Binlog Dump
   Time: 1012
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************
     Id: 4
   User: root
   Host: localhost
     db: school
Command: Query
   Time: 0
  State: init
   Info: show processlist
2 rows in set (0.00 sec)

ERROR: 
No query specified

 

MySQL02从库

在slave上,行1为I/O线程,行2为SQL线程:
root@localhost:mysql.sock  23:08:01 [school]>show processlist \G;
*************************** 1. row ***************************
     Id: 3
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 1072
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 4
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 29343
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 3. row ***************************
     Id: 5
   User: root
   Host: localhost
     db: school
Command: Query
   Time: 0
  State: init
   Info: show processlist
3 rows in set (0.00 sec)

ERROR: 
No query specified

三  复制相关文件(从库)

MySQL02从库

1.binlog

2.relay log

3.master.info,保存master的相关信息,不要删除它，否则，slave重启后不能连接master

[root@centos03 data]# cat /data/mysql/data/master.info 
23
mysql-bin.000021
1392
192.168.11.129
repl
repl
3306
60
0





0
1800.000

0
4269ee4d-e156-11e5-8ae9-000c295799ba
86400


0

4.relay-log.info,包含slave中当前二进制日志和中继日志的信息

[root@centos03 data]# cat /data/mysql/data/relay-log.info 
7
./mysql-relay-bin.000002
935
mysql-bin.000021
1392
0
0
1