mysql主从复制sql_delay_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)