第十七章、mysql主从配置

17.1 MySQL主从介绍

17.2 准备工作

17.3 配置主

17.4 配置从

17.5 测试主从同步

 

17.1 MySQL主从介绍

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

 

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

 

主从过程大致有3个步骤

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

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

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

 

主从共有3个线程：

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

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

 

主从原理图

当有写入操作时，会记录到Master的binlog里面，主上（Master）的log dump 线程会和从上（Slave）的I/O线程相互通信；从上将binlog信息读到Slave，生成一个Relaylog中继日志；从上的SQL线程将Relaylog里面的sql语句执行

 

主从应用场景：

1、做数据备份；主机器宕机，可以启动从机器，给web、客户端提供服务。

2、不仅做数据备份，还要作为读库，让web、客户端从从机器读取数据，减缓主机器读取压力

 

 

17.2 准备工作

准备两台机器，都要有mysql服务

###注意，进行主从配置同步操作时一定要保持两边数据一致

 

17.3 配置主

一、安装mysql

 

二、修改my.cnf，增加server-id=150和log_bin=xin1

vim /etc/my.cnf

server-id=150

log_bin=xin2

 

三、修改完配置文件后，启动或者重启mysqld服务

/etc/init.d/mysqld restart

 

四、把mysql库备份并恢复成xin库，作为测试数据

mysqldump -uroot -p123456 mysql > /tmp/mysql.sql

mysql -uroot  -p123456 -e "create database xin"

mysql -uroot -p123456 xin < /tmp/mysql.sql

 

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

grant replication slave on *.* to 'repl'@slave_ip identified by 'password';

#slave_ip为从机器的ip

grant replication slave on *.* to 'repl'@192.168.233.129 identified by '123456';

 

flush tables with read lock;    

#锁表，防止表继续写入

 

show master status;

#查看binlog的位置和数值，需要记住，待会要用到

 

 

17.4 配置从

一、安装mysql

 

 

二、编辑my.cnf，配置server-id=129，（要求和主上不一样，且从上没有binlog日志）

vim /etc/my.cnf

server-id=129

 

 

三、修改完配置文件后，启动或者重启mysqld服务

/etc/init.d/mysqld restart

 

四、把主上xin库同步到从上

scp  192.168.233.150:/tmp/*.sql  /tmp/

[root@xinlinux-01 ~]# scp  192.168.233.150:/tmp/*.sql  /tmp/

root@192.168.233.150's password:

xin.sql                                        100%  649KB   8.6MB/s   00:00

zrlog.sql                                      100% 9863     1.9MB/s   00:00

 

五、可以先创建xin库，然后把主上的/tmp/mysql.sql拷贝到从上，然后导入xin库

mysql -uroot

create database xin;

create database zrlog;

mysql -uroot xin <  /tmp/xin.sql

mysql -uroot zrlog <  /tmp/zrlog.sql

 

六、实现主从同步

mysql -uroot 

stop slave;

 

change master to master_host='', master_user='repl', master_password='', master_log_file='', master_log_pos=xx;

#master_host为主机器ip，msaster_pasword为主机器密码，master_log_file为binlog日志名称，master_log_pos为binlog日志数值

 

change master to master_host='192.168.233.150',master_user='repl',master_password='123456',master_log_file='xin2.000002',master_log_pos=665019;

 

 

start slave;

 

#判定主从是否配置成功

show  slave status\G

 

查看主从同步是否正常

1、查看Slave_IO_Running和Slave_SQL_Running是否为yes

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

 

2、关注是否有error信息

Seconds_Behind_Master: 0  #为主从延迟的时间

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

 

七、还要到主上执行 

unlock tables;        #解锁表

 

 

17.5 测试主从同步

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=  #忽略指定库，可用通配符%（常用）

 

 

测试主从

主上操作

 mysql -uroot  -p123456;

use xin;

show tables;

select count(*)  db;

mysql> select count(*)  db;

+----+

| db |

+----+

|  1 |

+----+

1 row in set (0.06 sec)

 

从上操作

 mysql -uroot

use xin;

select count(*)  db;

mysql> select count(*)  db;

+----+

| db |

+----+

|  1 |

+----+

 

对比可知主从的数据完全一样，限制开始对主机器的数据进行操作，查看从机器是否同步操作的数据

主上操作清空db表数据

truncate table db;

select * from  db;

mysql> select * from  db;

Empty set (0.00 sec)

 

从上操作

select * from  db;

mysql> select * from  db;

Empty set (0.00 sec)

#发现从机器的db表数据清空了

 

主上继续操作

drop table db;

 

从上查看wp_users表

select count(*)  db;

 

如果不小心对从机器进行的误删等操作导致主从机器不一致：

1、可以先stop slave，再start slave；查看slave show slave status 两个yes是否在；

 

2、如果还是不行，就需要在从机器重新配置主从同步（前提是要保持数据一致）

stop slave；

 

change master to master_host='', master_user='repl', msaster_pasword='', master_log_file='', master_log_pos=xx;

 

start slave;