mysql 主从 mix_MySQL主从配置 - mixuyulv的个人空间 - OSCHINA - 中文开源技术交流社区...

一 MySQL主从介绍

•MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的

• MySQL主从是基于binlog的，主上须开启binlog才能进行主从。

• 主从过程大致有3个步骤

1)主将更改操作记录到binlog里

2)从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里

3)从根据relaylog里面的sql语句按顺序执行

• 主上有一个log dump线程，用来和从的I/O线程传递binlog

• 从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

其中binlog  二进制日志

relaylog  中继日志

MySQL主从原理图如下：

MySQL主从配置使用场景

1)将从用于做数据备份

2)从不仅用于数据备份，而且还用于web客户端读取从上的数据，减轻主读的压力

二 准备工作

准备两台机器，每台机器都要搭建好并启动MySQL服务 ，下面我们来将IP为136机器做成主库、130的机器做成从库，当前136机器mysql服务已经搭建好，下面我们来将130的mysql服务搭建起来

1、安装mysql

1)下载

下载MySQL5.6 64位MySQL二进制免编译包

# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

2 )解压

# tar xzvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

3)移动目录

将解压后的文件包移动到目录/usr/local/下并更名为mysql

# mv mysql-5.6.36-linux-glibc2.5-x86_64 /usr/local/mysql

4)创建使用MySQL的系统用户及数据存储目录

# useradd mysql

# mkdir /data/

5)初始化

# cd /usr/local/mysql

# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

当出现两个OK则说明初始化OK

6)修改配置文件

# vim /etc/my.cnf

修改这个文件中如下位置

7)拷贝并编辑启动脚本

# cp support-files/mysql.server /etc/init.d/mysqld

# vim /etc/init.d/mysqld

# chkconfig mysqld on    //配置mysqld开机启动

将启动脚本修改下面位置如下图

8)启动mysqld

# /etc/init.d/mysqld start        //启动mysqld

# ps aux |grep mysqld            //查看mysqld是否成功启动

三 配置主

将前面的准备工作做好之后，我们再来配置主库

1、修改配置文件

# vim /etc/my.cnf

在配置文件中增加以下内容

server-id=136       //后面的id号可以随便定义

log_bin=lijie1       //这里定义的是binlog的前缀，这个前缀名可以随便定义

2、重启mysql服务

# /etc/init.d/mysqld restart   //重启mysqld

# ls -lt /data/mysql/        //配置完重启后可以看到这个目录下多了两个文件

上图中的lijie1.000001是第一个二进制日志文件，后面还会生成很多个

lijie1.index是必需有的索引文件

没有这些文件主从就没有办法完成

3、准备测试用的数据

我们需要将之前的库备份并恢复成lijie库，作为测试数据

# mysqldump -uroot -p112233 zrlog > /tmp/zrlog.sql     //备份库

# mysql -uroot -p112233 -e "create database lijie"          //创建测试库

# mysql -uroot -p112233 lijie < /tmp/zrlog.sql                //恢复数据到测试库

4、创建用作同步数据的用户

# mysql -uroot -p112233

> grant replication slave on *.* to 'repl'@192.168.31.149 identified by '112233';  //创建数据库用户repl并将其绑定到固定从机192.168.31.149上，且只授予其同步数据的权限

5、锁表

锁表的目的是将表固定在当前状态，不能再写入数据，后面我们还要将备份的数据同步到从机上，以保证主从数据的一致性

> flush tables with read lock;

6、查看主库状态

> show master status;

四 配置从

1、编辑配置文件

# vim /etc/my.cnf

在这个配置文件中加入server-id，要求和主不一样，在这里我们不需要添加log_bin日志的值了，只有主上才需要二进制日志文件

2、重启mysql服务

# /etc/init.d/mysqld restart   //重启mysqld

# ls -lt /data/mysql/        //配置完重启后可以看到这个目录下没有什么变化

3、同步主上的数据文件到本机

# scp 192.168.31.157:/tmp/*.sql /tmp

4、创建库

由于我们还没有将mysql的路径加入到PATH中，因此我们可以先做一个alias

# alias 'mysql=/usr/local/mysql/bin/mysql'

# alias 'mysqldump=/usr/local/mysql/bin/mysqldump'

# mysql -uroot -p123123

> create database lijie;

> create database zrlog;

5、恢复数据

# mysqldump -uroot -p123123 lijie < /tmp/lijie.sql

# mysqldump -uroot -p123123 zrlog < /tmp/zrlog.sql

6、实现主从

# mysql -uroot -p123123

> stop slave;

> change master to master_host='192.168.31.157', master_user='repl', master_password='123123', master_log_file='lijie1.000001',master_log_pos=10555;

上面命令中包含的主库信息有：IP 登录主数据库的用户名、密码、日志文件名，日志路径，我们还可以在命令中加入端口信息，默认3306端口可以不用写

> start slave;

7、判断主从是否配置成功

从上执行

• mysql -uroot

• show slave stauts\G

看是否有

• Slave_IO_Running: Yes

• Slave_SQL_Running: Yes

还需关注

• Seconds_Behind_Master: 0  //为主从延迟的时间

• Last_IO_Errno: 0

• Last_IO_Error:

• Last_SQL_Errno: 0

• Last_SQL_Error:

我们使用下面这个命令来查看

# show slave status\G     //如果出现下图中两个YES则表示主从配置成功

如果show  slave  status中Slave_IO_Running=connecting则需要查查有没有iptables规则，或者是否开启了selinux ，还要保证用户名密码是对的

8、取消锁定

主从配置好后，还要到主上去取消对表写入的锁定

> unlock tables;

五 测试主从同步

1、几个配置参数

下面的参数是在mysql配置文件my.cnf中去定义的，可以在主上配置，也可以在从上配置

如果是在主服务器上，

• binlog-do-db=      //指定需要同步的库，如果有多个库，需要用英文逗号分割

• binlog-ignore-db= //忽略指定库

• 从服务器上

• replicate_do_db=

• replicate_ignore_db=

• replicate_do_table=

• replicate_ignore_table=

• replicate_wild_do_table=   //如aming.%, 支持通配符%，建议从服务器上不使用上面四行配置，仅使用后面这两行配置

• replicate_wild_ignore_table=

2、测试同步

主上

• mysql -uroot aming

• select count(*) from db;

• truncate table db;

到从上

• mysql -uroot aming

• select count(*) from db;

主上继续drop table db;

从上查看db表

1)首先我们分别在主从上查看下lijie.user表的行数

主 > use lijie;                                                          从 > use lijie;

> select count(*) user;                                            > select count(*) user;

  

         可以看到两边的结果是一致的

2)我们在主上清空表lijie.user

> truncate table user;      //清空表user的数据

> select * from user;         // 查询表中内容，可以看到表是空的

再到从上查询表user中的数据，也为空

3)我们在主上删除表lijie.user

> drop table user;

如果在上述操作中我们不小心在从上做了删除等操作使主从的数据不一致时，我们可以尝试重启slave来恢复，如果还是不行的话，就需要重新做主从了，如果此刻主从数据修改为一致后，可以直接从change master这一步开始走。