MYSQL主从复制操作文档

一、环境准备

操作系统	主机名	IP	Mysql版本
Redhat5.8	localhost.localdomain	192.168.254.253	5.5.32
Redhat5.8	localhost.localdomain	192.168.254.250	5.5.32

二．主库配置

1. 打开二进制日志log-bin ,进入my.cnf文件编辑

[root@localhost ~]# vi /etc/my.cnf

log-bin=/application/mysql-5.5.32/data/mysql-bin

2. 验证修改配置文件是否成功

[root@localhost ~]# egrep "log-bin|server" /etc/my.cnf

# The MySQL server

server-id       = 1

log-bin=/application/mysql-5.5.32/data/mysql-bin

3. 重启MYSQL服务

[root@localhost ~]# service mysqld restart

Shutting down MySQL.[  OK  ]

Starting MySQL..[  OK  ]

4. 验证二进制日志（log-bin）文件是否成功打开

mysql> show variables like '%log_bin%';

5.创建用户用于主从同步

 

mysql>  grant replication slave on *.* to 'rep'@'192.168.254.250' identified by 'oldboy123';

6. 锁定表为只读

mysql>  flush table with read lock;

7. 查询主的log-bin日志以及POS点

mysql> show master status;

 

8. 备份主库

[root@localhost /]# mysqldump -uroot -p12345qwert -A -B --events --master-data=2 > /backup/rep.sql

9. 解锁主库只读模式为读写模式

mysql> unlock tables;

10. 把主库备份拷贝到从库

[root@localhost backup]# scp rep.sql  192.168.254.250:/backup

三、从库配置

1.打开二进制日志log-bin ,进入my.cnf文件编辑

[root@localhost ~]# vi /etc/my.cnf

server-id       = 3

2重启MYSQL服务

[root@localhost ~]# service mysqld restart

Shutting down MySQL.[  OK  ]

Starting MySQL..[  OK  ]

 

3.把从主库拷贝过来的备份恢复到从库

# mysql -uroot -p12345qwert < /backup/rep.sql

4.在数据库中执行相关的注册信息CHANGE MASTER TO

CHANGE MASTER TO

MASTER_HOST='192.168.254.253',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='oldboy123',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=265;

5. 启动从库命令

mysql> start slave;

6. 查看从库状态，IO线程和sql线程为yes,表示成功

mysql> show slave status \G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

四．验证

1. 在MASTER建立一个新库ying

mysql> create database ying;

2. 在SLAVE查看数据库

mysql> show databases;

+--------------------+

| Database           

+--------------------+

| ying               

+--------------------+

 

 

 --------------------------------------------------------

 --------------------------------------------------------

 

 

 

 

 

转载于:https://blog.51cto.com/xin521long/1830227