mysql 5.5 master slave搭建

1、环境

centos 7

 

2、配置master

修改/etc/my.cnf增加

[mysqld]
log-bin = master-bin
log-bin-index = master-bin.index
server-id = 1

　　

3、master创建一个复制用户

CREATE USER repl_user;

GRANT REPLICATION SLAVE ON *.* TO repl_user IDENTIFIED BY '123456';

　　

重启master

 

4、配置slave

修改/etc/my.cnf增加

[mysqld]
server-id = 2
relay-log-index = slave-relay-bin.index
relay-log slave-relay-bin

　　

重启slave

 

 

5、master 刷新所有表，并阻止写入

mysql> flush tables with read lock;
当前二进制日志的文件名和位置
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

　　

6、mysqldump导出所有需要复制的数据库

mysqldump -uroot -pmysql --all-databases --lock-all-tables >dbdump.db

　　

7、解锁之前锁定的表

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

　　

 

8、把备份文件拷贝到从库

[root@localhost opt]# scp dbdump.db root@192.168.137.106:/opt/

　　

9、从库执行：

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

导入文件
source /opt/dbdump.db

 

 

10、连接master和slave

CHANGE MASTER TO
MASTER_HOST = '192.168.137.105',
MASTER_PORT = 3306,
MASTER_USER = 'repl_user',
MASTER_PASSWORD = '123456',
MASTER_LOG_FILE = 'mysql-bin.000006',
MASTER_LOG_POS = 107;

START SLAVE;

　　

11、查看复制状态

show slave status\G

　　

12、问题

12.1如果出现Last_IO_Error: error connecting to master

原因：
1、主库防火墙是否关闭
service firewalld status
关闭防火墙
systemctl stop firewalld.service
禁止开机启动
systemctl disable firewalld.service