Mysql 主从复制简易操作指南

24123r2

一、主从复制的优点：

有利于数据库构架的健壮性，提升访问速度和易于维护管理

二、应用场景

1、主从服务器互为备份（类似nfs 的rsync+secsever）

a、先挂主，在将主的binglog 日志拉到从库，再补充从库

b、双写

c、应用程序每分钟写一次来进行判定，一旦出错，将binglog 日志拉到从库，再补充从库

d、Mysql 插件，半同步插件google 出品

2、主从服务器读写分离分担网站压力：读写分离

    大中型公司：通过程序（php,java）

    测试环境，代理软件(mysql-proxy，amoeba)

    门户网站：分布式dbproxy（读写分离，hash 负载均衡,健康检查）

  3、根据服务器拆分业务，拆分从库

  三、主从复制原理

   Mysql的 Replication 是一个异步的复制过程，从一个 Mysql instace(我们称之为 Master)复制到另一个 Mysql instance(我们称之 Slave)。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成，其中两个线程(Sql线程和IO线程)在 Slave 端，另外一个线程(IO线程)在 Master 端。

　　要实现 MySQL 的 Replication ，首先必须打开 Master 端的Binary Log(mysql-bin.xxxxxx)功能，否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全 顺序的执行日志中所记录的各种操作。打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使用 “--log-bin” 参数选项，或者在 my.cnf 配置文件中的 mysqld 参数组([mysqld]标识后的参数部分)增加 “log-bin” 参数项。

　　MySQL 复制的基本过程如下：

  1.Slave 上面的IO线程连接上 Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

  2. Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO 线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置;

　　3. Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master- info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”

　 　4. Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。这样，实际上就是在 Master 端和 Slave 端执行了同样的 Query，所以两端的数据是完全一样的。

  四、系统准备

  使用两台服务器做测试

Master server	192.168.200.180	Centos 6.8	Mysql5.5.53
Slave server	192.168.200.140	Centos 6.8	Mysql5.5.53

注意：做主从服务器的原则是，MYSQL版本要相同，如果不能满足，最起码从服务器的MYSQL的版本必须高于主服务器的MYSQL版本

  五、配置Master server

1	Notes:安装mysql 的部分此处省略，如要了解请参考blog 中安装mysql内容

5.1 开启binlog 并设置server-id

1 2 3 4 5

[iyunv@master mysql]# vi /etc/my.cnf ####在my.cnf 文件中开启log-bin，设定server-id [iyunv@master mysql]# egrep "log-bin|server" /etc/my.cnf # The MySQL server server-id= 1 log-bin=/application/mysql/data/mysql-bin

注意server-id 和binlog 日志的参数均在[mysqld] 区域内进行设置。

5.2 设置slave server 访问master server mysql 的帐号

创建rep 帐号，并授权帐号为replication slave，密码为think

1 2	[iyunv@master mysql]# ./bin/mysql -e "grant replication slave on *.* to 'rep'@'192.168.200.%’identified by 'think';" [iyunv@master mysql]# ./bin/mysql -e "flush privileges;"

检查是添加帐号是否成功

1 2 3 4 5 6 7 8

[iyunv@master mysql]# ./bin/mysql -e "select user,host from mysql.user;" +------+---------------+ | user | host          | +------+---------------+ | root | 127.0.0.1     | | rep  | 192.168.200.% | | root | localhost     | +------+---------------+

5.3 检查master 设置状态

5.3.1重启mysql 服务

1 2 3

[iyunv@mastermysql]# /etc/init.d/mysqld restart Shutting down MySQL. SUCCESS! Starting MySQL.. SUCCESS!

5.3.2检查master binlog 日志是否开启

1 2 3 4 5 6

[iyunv@mastermysql]# /application/mysql/bin/mysql -e "show variables like 'log_bin';" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin       | ON    | +---------------+-------+

5.4 备份master sql 数据库

5.4.1锁定master server 上的mysql 数据库

1	[iyunv@mastermysql]# ./bin/mysql -e "flush table with read lock;"

1 2	注意：5.1版本的mysql锁表的语句为 flush tables with read lock; 锁表的时间由wait_timeout 和varactive_timeout 决定

5.4.2 检查master server mysql 的状态；

1 2 3 4 5 6 7 8 9 10 11 12

[iyunv@mastermysql]# ./bin/mysql -e "show master status;" +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 |      328 |              |                  | +------------------+----------+--------------+------------------+ [iyunv@mastermysql]# ./bin/mysql -e "show master logs;" +------------------+-----------+ | Log_name         | File_size | +------------------+-----------+ | mysql-bin.000001 |       328 | +------------------+-----------+

1	注意，需要记录master postition 与file 的值，为后面配置slave做准备

5.4.3备份master 数据库数据

1	[iyunv@master~]# /application/mysql/bin/mysqldump -uroot -p -A -B --events --master-data=2 >/application/mysql/data/rep.sql

1 2 3 4 5

命令行参数的含义 -A:转储所有数据库中的所有表。与使用---database选项相同，在命令行中命名所有数据库。 -B：转储几个数据库。通常情况，mysqldump将命令行中的第1个名字参量看作数据库名，后面的名看作表名。使用该选项，它将所有名字参量看作数据库名 --events：导出事件 --master-data:选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。如果选项值等于2，CHANGE MASTER语句被写成SQL注释。如果value被省略，这是默认动作。

5.4.4 主库解锁

1	mysql>unlocak tables;

六、配置Slave server

设置my.cnf

#skip-networking

1	server-id  = 2；####不要与master 的server-id 一致

设置slave 连master的参数

1 2 3 4 5 6 7

[iyunv@slave mysql]# ./bin/mysql -e "change master to \ master_host='192.168.200.180', \ master_port='3306', \ master_user='rep', \ masster_password='think', \ master_log_file='mysql-bin.000001', \ master_log_pos=328;"

检查是否设置成功

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

[iyunv@slave mysql]# cat ./data/master.info 18 mysql-bin.000001 328 192.168.200.180 rep think 3306 60 0    0 1800.000    0

导入master 备份出来的数据到slave

拷贝备份文件到slave server

1 2 3 4 5 6 7 8

[iyunv@master data]# scp rep.sql root@192.168.200.140:/application/mysql/data The authenticity of host '192.168.200.140 (192.168.200.140)' can't be established. RSA key fingerprint is d9:dd:4a:7d:df:1c:36:b9:e2:4f:ac:2d:8b:e0:f7:9d. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.200.140' (RSA) to the list of known hosts. TTEMPT! root@192.168.200.140's password: rep.sql                 100%  541KB 541.4KB/s   00:00

导入master 备份文件到slave server

1	mysql> source ./data/rep.sql

重启mysql 服务

开启slave server 上的mysql slave 服务

1	Mysql>start slave;

检查slave server 复制功能的状态

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.200.180                   Master_User: rep                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000002           Read_Master_Log_Pos: 107                Relay_Log_File: bogon-relay-bin.000005                 Relay_Log_Pos: 253         Relay_Master_Log_File: mysql-bin.000002              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                    Last_Error:                  Skip_Counter: 0           Exec_Master_Log_Pos: 107               Relay_Log_Space: 555               Until_Condition: None                Until_Log_File:                 Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 1 1 row in set (0.00 sec)

1 2	注：Slave_IO_Runing 及Slave_SQL_Running 进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。 以上操作过程，主从服务器配置完成。

七：主从服务器测试

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> create database master01; Query OK, 1 row affected (0.00 sec) mysql> use master01 Database changed mysql> create table master(id int(3),name char(10)); Query OK, 0 rows affected (0.02 sec) mysql> insert into master values(001,'think'); Query OK, 1 row affected (0.02 sec) mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | master01           | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.31 sec)

从服务器Mysql查询：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | master01           | | mysql              | | performance_schema | | test               | | wordpress          | +--------------------+ 6 rows in set (0.02 sec) mysql> use master01; Database changed mysql> select * from master; +------+-------+ | id   | name  | +------+-------+ |    1 | think | +------+-------+ 1 row in set (0.30 sec)

主从复制原理部署总结（重点）：

1、主库（Master）

A、开启log_bin，设置server-id

B、为slave 库创建一个replication slave 的帐号

C、完整备份需要同步的数据库，并同步给从库（slave ），

D、锁表查看master status并记录相应的情况

2、从库需要配置

A、设置server-id

B、设置change master 参数

CHANGE MASTER TO.

Master_host=  master 主机地址

Master_port=  master 主机mysql 端口

master_user=  登录master 主机user

Master_password= 登录master 主机的密码

Master_file=  log_bin 日志的路径

Master_pos=  log_bin 日志的开始位置；确保主从数据库的位置点之前的数据是一致的

C、导入备份的master 数据库

D、start slave；这个执行过程其实就是从库打开开关的过程，其实就是让IO和sql 两个线程开始工作的过程

server

5.5 以上的mysql 在备份数据库时可以使用如下命令，可以不需要进行锁表操作，slave 服务器上也不需要在change master 中指定master_file 和Master_pos。有兴趣的可以自行测试。

1	/application/mysql/bin/mysqldump -uroot -p -A -B -x --events --master-data=1 >/application/mysql/data/rep.sql

如果要做slave 级联，则slave my.cnf 配置文件中需要开启log-bin 与log-slave-update。

1	下篇文章会进行说明主主备份，一主多从的操作过程。