MySQL5.7.19 - 主从复制 - 日志点

【第一部分】 Master - Lebron - 192.168.1.122

1. 开放3306端口

[root@lebron sysconfig]# vim /etc/sysconfig/iptables
# Firewall configuration written by system-config-firewall
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT

#mysql port 
-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

2. 重启网络

[root@lebron sysconfig]# service iptables restart
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]
iptables: Applying firewall rules:                         [  OK  ]

3. 开启binlog

[root@lebron log]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

4. 重启mysqld

service mysqld restart

5. 登录mysql，创建复制用户

create user 'lxl'@'192.168.%' identified by '123456';

6. 赋权

grant replication slave on *.* to lxl@'192.168.%';

7. 创建测试数据库

create database lixinlei;

8. 创建测试表

use lixinlei;
create table t(id int, c1 varchar(10), primary key(id));

9. 插入测试数据

insert into t values(1,'aa'),(2,'bb'),(3,'cc');

10. 退出mysql

quit

11. 备份数据库

[root@lebron tmp]# cd /tmp
[root@lebron tmp]# mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot -p > all.sql

12. 拷贝备份文件到Slave - Kyrie

[root@lebron tmp]# scp all.sql root@192.168.1.123:/tmp

【第二部分】 Slave - Kyrie - 192.168.1.123

1. 开放3306端口

[root@lebron sysconfig]# vim /etc/sysconfig/iptables
# Firewall configuration written by system-config-firewall
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT

#mysql port 
-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

2. 重启网络

[root@lebron sysconfig]# service iptables restart
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]
iptables: Applying firewall rules:                         [  OK  ]

3. 开启binlog

[root@lebron mysql]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-slave
server-id=2

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

4. 重启mysqld

service mysqld restart

5. 检查备份文件是否拷贝成功

[root@kyrie ~]# cd /tmp
[root@kyrie tmp]# ls
all.sql  yum.log

6. 恢复

[root@kyrie tmp]# mysql -uroot -p < all.sql

7. 记录Master的日志文件&日志点

[root@kyrie tmp]# more all.sql
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

8. 登录mysql，检查恢复是否成功

[root@lebron tmp]# mysql -uroot -p'y;WQx*H>?78K'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lixinlei           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use lixinlei;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_lixinlei |
+--------------------+
| t                  |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+----+------+
| id | c1   |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
+----+------+
3 rows in set (0.00 sec)

9. 配置复制链路

mysql> change master to master_host='192.168.1.122',
    -> master_user='lxl',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=154;

10. 启动Slave

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

11. 查看Slave状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.122
                  Master_User: lxl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: lebron-relay-bin.000005
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             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: 154
              Relay_Log_Space: 528
              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: 1
                  Master_UUID: a41e6957-dc02-11e7-a9aa-0800271c6804
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

【第三部分】 验证

1. Mater插入数据

[root@lebron sysconfig]# mysql -uroot -p'y;WQx*H>?78K'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use lixinlei;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_lixinlei |
+--------------------+
| t                  |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+----+------+
| id | c1   |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into t values(4,'dd');
Query OK, 1 row affected (0.02 sec)

2. Slave验证是否同步成功

[root@lebron mysql]# mysql -uroot -p'y;WQx*H>?78K'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use lixinlei;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t;
+----+------+
| id | c1   |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
+----+------+
4 rows in set (0.00 sec)