Mysql 主从复制简易操作指南

wdx1992828

　　一、主从复制的优点：
　　有利于数据库构架的健壮性，提升访问速度和易于维护管理
　　二、应用场景
　　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 线程检测到>　　四、系统准备
　　使用两台服务器做测试
　　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
Notes:安装mysql 的部分此处省略，如要了解请参考blog 中安装mysql内容　　5.1 开启binlog 并设置server-id
[root@master mysql]# vi /etc/my.cnf ####在my.cnf 文件中开启log-bin，设定server-id　　
[root@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
[root@master mysql]# ./bin/mysql -e "grant replication slave on *.* to 'rep'@'192.168.200.%’identified by 'think';"　　
[root@master mysql]# ./bin/mysql -e "flush privileges;"
　　检查是添加帐号是否成功
[root@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 服务
[root@mastermysql]# /etc/init.d/mysqld restart　　
Shutting down MySQL. SUCCESS!
　　
Starting MySQL.. SUCCESS!
　　5.3.2检查master binlog 日志是否开启
[root@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 数据库
[root@mastermysql]# ./bin/mysql -e "flush table with read lock;"注意：5.1版本的mysql锁表的语句为 flush tables with read lock;　　
锁表的时间由wait_timeout 和varactive_timeout 决定
　　
　　5.4.2 检查master server mysql 的状态；
[root@mastermysql]# ./bin/mysql -e "show master status;"　　
+------------------+----------+--------------+------------------+
　　
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　
+------------------+----------+--------------+------------------+
　　
| mysql-bin.000001 |      328 |              |                  |
　　
+------------------+----------+--------------+------------------+
　　
[root@mastermysql]# ./bin/mysql -e "show master logs;"
　　
+------------------+-----------+
　　
| Log_name         | File_size |
　　
+------------------+-----------+
　　
| mysql-bin.000001 |       328 |
　　
+------------------+-----------+
注意，需要记录master postition 与file 的值，为后面配置slave做准备　　5.4.3备份master 数据库数据
[root@master~]# /application/mysql/bin/mysqldump -uroot -p -A -B --events --master-data=2 >/application/mysql/data/rep.sql命令行参数的含义　　
-A:转储所有数据库中的所有表。与使用---database选项相同，在命令行中命名所有数据库。
　　
-B：转储几个数据库。通常情况，mysqldump将命令行中的第1个名字参量看作数据库名，后面的名看作表名。使用该选项，它将所有名字参量看作数据库名
　　
--events：导出事件
　　
--master-data:选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。如果选项值等于2，CHANGE MASTER语句被写成SQL注释。如果value被省略，这是默认动作。
　　5.4.4 主库解锁
mysql>unlocak tables;　　
　　六、配置Slave server
　　设置my.cnf
　　#skip-networking
server-id  = 2；####不要与master 的server-id 一致　　设置slave 连master的参数
[root@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;"
　　检查是否设置成功
[root@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
[root@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
mysql> source ./data/rep.sql　　重启mysql 服务
　　开启slave server 上的mysql slave 服务
Mysql>start slave;　　检查slave server 复制功能的状态
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)
注：Slave_IO_Runing 及Slave_SQL_Running 进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。　　
以上操作过程，主从服务器配置完成。
　　七：主从服务器测试
　　主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：
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查询：
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。有兴趣的可以自行测试。
/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。
下篇文章会进行说明主主备份，一主多从的操作过程。