mysql 5.5 slave 配置_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