MySQL主从复制原理实践

MySQL主从复制
  MySQL主从复制实则是将原本创建和修改数据库的SQL命令集合复制到从库本地,在从库本地重新执行这些SQL命令来建立与主库一样的数据。搭建从库以后,包括复制主库当前截止位置的所有数据,和接下来主库还在不断增长的数据。事实上,停库备份全部数据再复制过去没什么问题,问题在于怎么实时复制数据库增长的这段数据?而这就要借助binlog日志的功能...

主从复制原理

MySQL主从复制原理实践

1)开启主库log-bin功能,是为了用户在写数据库过程中,MySQL会同时建立一份binlog日志来记录数据库所做的增删改等操作;还要建立一个从库和主库之间验证关系的换用账号rep,并授权;

2)锁表,对数据库做全备操作,最大程度保证数据一致性,最重要的是获取当前数据库binlog日志分界点;
3)将停库后的数据库全量恢复到从库
4)在从库中执行CHANGE MASTER TO...命令来验证用户,和告诉从库当前备份的数据库binlog日志分界点在哪,并生成master.info文件;
5)开启从库,此时mysql replication功能才真正发挥作用,从库B会利用自己的IO线程不断询问主库A:哥们,我有暗号(密码)是自己人,呃...我这本地有这些这些货,你那有没有新货(新增的数据),也给我搞点。然后主库A就会去看自己binlog日志,发现真有些新货,就会给这些新货发给从库B,并且附赠一份清单,告诉从库B他现在仓库里有哪些货,下次从库B再来找他要新货只要拿出这个清单A就知道他那里的货是不是和自己一样的了,如果一样就不发了,如果不一样,就按照这个清单上的货截止位置发最新的,这里的新货就可以理解为binlog文件内容,清单就为binlog位置信息post;
  从库每次拿到新货就会很开心啊,启动自己的SQL线程将这些新货解析到自己的数据库中,保证与主库A完全一致。

主从复制案例实践

在本地多mysql多实例中,将/data/3306/mysql.sock作为主库,/data/3307/mysql.sock作为从库

主库

1)开主库binlog功能,并且保证server id不一样
[root@db02 ~]# egrep "log-bin|server-id" log-bin /data/3306/my.cnf /data/3307/my.cnf
/data/3306/my.cnf:log-bin = /data/3306/mysql-bin
/data/3306/my.cnf:server-id = 1
/data/3307/my.cnf:#log-bin = /data/3307/mysql-bin
/data/3307/my.cnf:server-id = 3

2)主库授权从库账号rep
mysql> grant replication slave on *.* to rep@'172.16.2.%' identified by 'oldboy123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;

3)锁表(锁表后不能退出当前mysql控制窗口)
mysql> flush table with read lock;

4)查看binlog临界点
mysql> show master status;
+------------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000015 |    3204 |              |                  |
+------------------+----------+--------------+------------------+

5)全备

[root@db02 ~]# mysqldump -uroot -poldboy1234 -S /data/3306/mysql.sock -A -B --events|gzip > /server/backup/mysql_$(date +%F).sql.gz
[root@db02 backup]# ll mysql_2016-07-07.sql.gz
-rw-r--r-- 1 root root 144620 Jul  7 04:18 mysql_2016-07-07.sql.gz

6)解锁表
1 mysql> unlock table;

从库

1)确保server ID 不同
[root@db02 ~]# grep server-id /data/3307/my.cnf
server-id = 4

2)恢复全备

[root@db02 backup]# mysql -uroot -poldboy456 -S /data/3307/mysql.sock </server/backup/mysql_2016-07-07.sql

3)配置master,填写正确的binlog位置点

mysql> CHANGE MASTER TO MASTER_HOST='172.16.2.10', MASTER_PORT=3306, MASTER_USER='rep',
MASTER_PASSWORD='oldboy123', MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=3204;
##此时如果master密码配置错误,重新改master.info文件不生效,需执行reset slave all;
[root@db02 data]# cat master.info
18
mysql-bin.000015
3204
172.16.2.10
rep
oldboy123
3306
60
0

4)开启从库复制开关,验证数据

mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.2.10
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 3289
              Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 338
        Relay_Master_Log_File: mysql-bin.000015
            Slave_IO_Running: Yes    #从库从主库复制binlog日志进程
            Slave_SQL_Running: Yes    #从库读取中继日志转换成SQL语句应用到数据库进程
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
          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: 3289
              Relay_Log_Space: 488
              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

验证完成。

相关推荐