mysql主从配置

mysql主从介绍

• mysql主从又叫做Repication或者是AB复制。简单讲，就是A、B2台机器做主从后，在A（master）上写数据,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语句执行一遍

• 原理图
  

• 应用场景
  （1）主上进行读写操作，从上只是单纯的做一个备份
  （2）主上进行写操作，从上进行读操作，减轻主库的压力。

• 准备2台都安装了mysql的机器进行主从配置

---

配置主

• 修改主配置文件/etc/my.cnf，修改如下

[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
server-id=130
log_bin=zcy1

server-id=xxx /自定义
log_bin= xxx /binglog前缀

• 修改完后重启mysql,在/data/mysql下会生成2个已自定义log_bin为前缀的 文件

[root@localhost mysql]# ls  -l 
总用量 110636
-rw-rw----. 1 mysql mysql       56 4月  28 01:09 auto.cnf
-rw-rw----. 1 mysql mysql 12582912 4月  30 23:44 ibdata1
-rw-rw----. 1 mysql mysql 50331648 4月  30 23:44 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 4月  28 01:04 ib_logfile1
-rw-rw----. 1 mysql mysql    15658 4月  30 23:44 localhost.localdomain.err
-rw-rw----. 1 mysql mysql        5 4月  30 23:44 localhost.localdomain.pid
drwx------. 2 mysql mysql     4096 4月  28 01:04 mysql
drwx------. 2 mysql mysql     4096 4月  28 01:04 performance_schema
drwx------. 2 mysql mysql        6 4月  28 01:04 test
-rw-rw----. 1 mysql mysql      120 4月  30 23:44 zcy1.000001
-rw-rw----. 1 mysql mysql       14 4月  30 23:44 zcy1.index
drwx------. 2 mysql mysql     4096 4月  29 21:38 zrlog

其中zcy1.000001为二进制文件的第一个，后面还会继续生成。

zcy1.index 这个文件的索引，是必须要有的

如果没有这2个文件，主从是不能完成的。

• 备份测试目录

/usr/local/mysql/bin/mysqldump -uroot  zrlog > /tmp/zrlog.sql

• 创建新的数据库，并将备份的目录导入新创建的数据库中

mysql -uroot  -e "create database test1"

mysql -uroot test1 < /tmp/zrlog.sql

现在做主从操作的就是test1这个库。

• 可以看到zcy.000001(bin_log)文件大小发生了变化，bin_log里面完整了记录了库的创建过程。使用通过zcy.000001(bin_log)是可以去恢复这个数据库中的数据，前提是bin_log需要完整。

  -创建用作同步数据的用户（进入mysql中操作）

grant replication slave on *.* to 'repl'@'192.168.244.135' identified by 'zchuny123';

这里IP填写从的IP

• 锁住表，不让它再继续写入数据，保证数据一致

flush tables with read lock;

• 查看信息

mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| zcy1.000001 |    10616 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记录File | Position信息，后面会使用到，退出mysql

• 查看主上都有哪些库，为了跟生常环境中一样，需要同步所有的库，mysql库除外。

---

配置从

##

• 查看my.cnf进行配置，在其中添加server-id=135 (定义的值需要和主上不相同）

# These are commonly set, remove the # and set as required.
 basedir = /usr/local/mysql
 datadir = /data/mysql
# port = .....
# server_id = .....
# socket = .....
server-id=135  //添加内容

bin_log不需要配置，只有主上才需要这个2进制文件。

重启mysql服务

• 将j主上的sql传输到从上，并进行恢复

scp 192.168.244.139:/tmp/*.sql  /tmp/

• 进行到数据库中，创建库

 create database zrlog;

• 恢复主上复制过来的sql文件到新创建的库中

mysql -uroot zrlog < /tmp/zrlog.sql

然后查看主和从上/data/mysql目录下是否是一样的

• 进入mysql,执行以下命令

stop slave ; 

change master to master_host='192.168.244.139', master_user='repl', master_password='zchuny123', master_log_file='zcy1.000001',mastster_log_pos=10616;


 start slave;

• 判定主从配置是否成功

show  slave status\G  //
查看信息是否有 Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

如果其中有一个NO，说明主从已经断开了。 这时候主从已经建立

• 主上表恢复写操作（mysql 中进行）

unlock tables;

---

测试主从同步

• 关于/etc/my.cnf中参数解释
  
  这些参数默认是没有配置的，那默认是会同步所有的库

replicate_ignore_table=aming.db1 
它过滤的是库名字，只有过滤到use aming 这个语句时，才会忽略后面的sql语句。如果sql语句中用的是aming.tb1，则不会忽略这条语句的。

建议在从库中使用replicate-wild-ignore-table=dbname.%的参数对数据库表的过滤。

使用replicate_do_db和replicate_ignore_db时有一个隐患，跨库更新时会出错。

如在Master（主）服务器上设置 replicate_do_db=test（my.conf中设置）
use mysql;
update test.table1 set ……
那么Slave（从）服务器上第二句将不会被执行

如Master设置 replicate_ignore_db=mysql
use mysql;
update test.table1 set ……
那么Slave上第二句会被忽略执行

原因是设置replicate_do_db或replicate_ignore_db后，MySQL执行sql前检查的是当前默认数据库，所以跨库更新语句在Slave上会被忽略。

可以在Slave上使用 replicate_wild_do_table 和 replicate_wild_ignore_table 来解决跨库更新的问题，如：
replicate_wild_do_table=test.%
或
replicate_wild_ignore_table=mysql.%

• 测试主从是否真的成功
  （1）在主上创建一个库，那么从上也应该是可以看到新创建的这个库的，当删除这个库时，那么从上的库也会消失
  （2）一旦我们不小心在从上写了数据,那么主从复制也就被破坏了。
  （3）如果要重启master,务必先关闭slave(即 stop slave),然后再去重启master的mysql服务，否则主从复制很有可能就会中断。当然重启master后，还需要执行start slave命令开启主从复制服务
  (4) 如果主从复制时因为在从上进行了写操作而中断，那么只能重新进行在主上执行show master status; 查看Position的值,然后在从上stop slave 后重新进行change master to 这步，再start slave 后查看show slave status\G