【第一部分】 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)
