Mysql数据库-主从同步

mysql的主从同步(Mysql REPLICATION)

  • 什么是MYSQL PERLICATION?
  1. replication可以实现将数据从一台数据库服务器(master)复制到一或多台数据服务器(slave)
  2. 默认情况下属于异步复制,无需长时间的维持连接
  3. 通过配置,可以复制所有的库或者几个库,甚至库中的一些表
  4. 是MYsql内建,本身自带的

DML:SQL操作语句:

update、insert、delete

Mysql两种日志

  • Relay log:中继日志

  • binlog:归档日志

  • Mysql-WAL(Write-Ahead logging)技术:先写日志后写磁盘技术,把要写入的先记录redo然后写到内存,在适当系统空闲时候写磁盘

异同点

  • redo log是InnoDB引擎特有的:binlog是MySQL的Service层实现的,所有的引擎都可以使用

  • redo log是物理日志,记录的是在某一个数据页面上做了什么修改,binlog是逻辑日志,记录的是这个语句的原始逻辑

  • redo log是循环写,空间固定会用完,binlog是可以追加写当到达一定大小后会切换到下一个不会覆盖之前的日志

`为什么会有两份日志呢?

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM,但是 MyISAM 没有 crash-safe 的能力,binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的,既然只依靠 binlog 是没有 crash-safe 能力的,所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

replication(复制)的原理

简单的说就是MSATER将数据库的改变写入二进制日志,slave同步这些二进制日志,并根据日志进行数据库操作

replication的作用

  • 1.Fail over 故障切换
  • 2.Backup Server 备份服务器,无法对SQL语句执行产生的故障恢复,有限的备份
  • 3.High Performance 高性能,可以多台slave实现读写分离

replication如何工作

  • 整体上来说,复制3个步骤
  • 1.master将改变记录到二进制日志(binary log)中(这些记录叫做二进制事件,binary log events)
  • 2.slave将master的binary log events拷贝到了它的中继日志(relay log)
  • 3.slave重做中继日志的事件,修改salve上的数据

主从同步的原理

show processlist;

记录主从同步的文件

master.info     #主库上面的信息
mysql-salve-relay-bin.xxxxxx        #中继日志文件,主库上执行的SQL命令
mysql-salve-relay-bin.index     #记录当前已经有的中继日志文件,的索引文件
relay-log.info      #中继日志文件,记录当前使用的中继日志的信息
查看当前的日志文件:mysqlbinlog mysql-salve-relay-bin.xxxxxx

从库的IO线程和SQL线程的作用

IO线程:把主库binlog日志里面的sql命令记录到本纪的中继日志文件中
SQL线程:执行本机中继日志文件里面的sql命令,把数据写进本机中

IO线程报错原因:

从库失败原因(ping grant firewalld selinux)
从库指定主库的日志信息错误(日志名:偏移量)
重点看Last_IO_Error查看报错信息

修改的步骤:

mysql> stop slave;
mysql> change master to 选项="值";
mysql> start slave;

SQL线程报错的原因:

1.执行本机器中中继日志文件里面的sql语句,用到库或表在本机不存在。
2.具体查看Last_IO_Error报错信息时什么。
例子:
server_id=51  -> testdb库
server_id=52  -> testdb不存在

Mysql主从备份中:

第一步:

master记录二进制日志,在每个事务更新数据完成之前,master在二进制日志记录这些改变,
Mysql将事务写入二进制日志,即使事务中的语句都是交叉执行的,在事件写入二进制日志完成之后,
master通知存储引擎提交事务。

第二步:

slave将master的binary log拷贝到它自己的中继日志,首先,slave开始一个工作线程——I/o线程,
I/O线程在master上打开一个普通的连接,然后开始binlog dump process,Binlog dump process从master
的二进制日志中读取事件,如果已经执行完毕master产生的所有文件,它会睡眠并等待master产生新的事件,
I/O线程将这些事件写入中继日志。

第三步:

SQL slave thread(SQL从线程)处理该过程的最后一步。SQL线程从中继日志读取事件,并重新执行
其中的事件而更新slave的数据,使其与master中的数据一致

主服务器的配置

首先拷贝数据库的所有文件到从数据库中
        ①mysqldump -r -p >文件名.sql
        ②scp 文件名.sql 从服务器IP:/拷贝到的路径
        ③Are you sure you want to continue connecting (yes/no)? yes
        ④回车后输入密码,提示拷贝成功
        ⑤service mysqld stop 停止数据库运行
        ⑥修改mysql配置文件:vim /etc/my.cnf
        ⑦在[mysql]中添加
            server-id=1                                 指定主机master
            log-bin=mysql-bin-master(可以自定义名称)   启用二进制日志文件
            binlog-do-db=库名称                            (指定备份库的名称)
            binlog-ignore-db=mysql                      (指定不复制的库)
        ⑧启动数据库并赋予权限
            service mysqld start
            mysql -r -p         交互模式下

            授权创建用户给从服务器:
                grant replication 用户名 on 要复制主的库 to 用户名@从服务器IP identified by "密码"
                创建一个用户只能用某个IP登入该用户,从主服务器中复制指定的数据库(*.*指的是任意)

            grant replication slave on *.* to slave@从服务器IP identified by "123456"; 
            set global validate_password_policy=0;      政策定义
            set global validate_password_length=1;      定义默认长度(系统默认是八位)

            flush privileges;       刷新权限
            show master status;     查看master二进制状态,同时会在数据库目录生成文件。
                mysql> show master status;
                +-------------------------+----------+--------------+------------------+-------------------+
                | File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
                +-------------------------+----------+--------------+------------------+-------------------+
                | mysql-bin-master.000001 |      902 | HA           | mysql            |                   |
                +-------------------------+----------+--------------+------------------+-------------------+
            show binlog eventsG     所有的命令记录查看

        查询当前数据库的用户及其拥有的权限:
            select distinct concat('User:''',user,'''@''',host,''';')as query from mysql.user;

从服务器配置

    1.修改配置文件
        vim /etc/my.cnf
                [mysqld]
                server_id=52
        systemctl restart mysqld
    2.授权从同步主
        mysql> change master to
                -> master_host="192.168.4.51",
                -> master_user="slave",
                -> master_password="123456",
                -> master_log_file="master51.000003",
                -> master_log_pos=449;
    3.开启slave同步
        start slave;
    4.查看是否同步成功
        show slave statusG;
                Slave_IO_Running: Yes       #成功
                Slave_SQL_Running: Yes      #成功

解除主从配置

停止用户

slave stop
rm -rf /var/lib/mysql/master.info
rm -rf 主机名-raly-bin-xxxxxxxx 主机名-relay-bin.index relay-log-info
systemclt restart mysqld

Mysql主从的同步结构模式

  • 一主一从
  • 一主多从
  • 主从从
  • 主主主

Mysql主从同步的常用的配置参数

vim /etc/my.cnf         #配置主库的配置参数
        [mysqld]
        Log_slave_updates               #级联复制
        relay_log=中继日志文件
        Binlog_Do_DB=库名称            #只允许同步的库
        Binlog_Ignore_DB=库的列表       #只允许不同步的

51主库的配置

1.启动binlog日志及其相关文件配置

vim /etc/my.cnf
        [mysqld]
        log-bin=db51
        server_id=51
        binlog-format='mixed'
        validate_password_policy=0
        validate_password_length=6
systemctl restart mysqld

2.查看正在使用中的日志信息

mysql> show master status;
    +-------------+----------+--------------+------------------+-------------------+
    | File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +-------------+----------+--------------+------------------+-------------------+
    | db51.000001 |      435 |              |                  |                   |
    +-------------+----------+--------------+------------------+-------------------+

52从库的配置

用户授权

grant replication slave on *.* to user53@"192.168.4.53" identified by '123456';

启动binlog日志,和允许级联系复制

[mysqld]
server_id=52
log_bin=db52
binlog-format="mixed"
log_slave_updates
validate_password_policy=0
validate_password_length=1

查看正在使用的日志信息

ls /var/lib/mysql/

验证主库的授权用户

mysql -udd -p123456 -h192.168.4.51          #登入成功验证成功

管理员登入指定主库信息

mysql> change master to
        -> master_host="192.168.4.51",
        -> master_user="dd",
        -> master_password="123456",
        -> master_log_file="db51.000001",
        -> master_log_pos=435;

启动slave进程

start slave 

查看进程状态

show slave statusG;

53从库的配置

修改配置文件

vim /etc/my.cnf 
        server_id=53
        validate_password_policy=0
        validate_password_length=6

开启同步

mysql> change master to
        -> master_host="192.168.4.52",
        -> master_user="user53",
        -> master_password="123456",
        -> master_log_file="db52.000001",
        -> master_log_pos=450;
start slave;

客户端验证配置

主库上授授权访问某库的用户

mysql> grant all on gg.* to nb@"%" identified by "123456";

客户端登入进行增删改查

mysql> use gg;
mysql> create table gg.a(id int);
mysql> insert into gg.a values(1);
返回从库52,53查看是否同步成功

Mysql主从同步复制模式

异步复制(Asynchronous replication)
  • 主库在执行完客户端提交的事务后会立即将结果返给客户端,并不关心从库是否已经接收并处理。
全同步复制(Fully synchronous replication)
  • 当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。
半同步复制(Semisynchronous replication)
  • 介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。

开启异步复制

查看是否允许动态加载模块

mysql> show variables like 'have_dynamic_loading';
            +----------------------+-------+
            | Variable_name        | Value |
            +----------------------+-------+
            | have_dynamic_loading | YES   |
            +----------------------+-------+

主库安装模块

mysql>  INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从库安装模块

mysql>  INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

查看是否安装成功

mysql> SELECT  PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';

开启半同步复制模式

主库

mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;       #l临时生效

从库

mysql>SET GLOBAL rpl_semi_sync_slave_enabled=1;

查看是否开启

mysql>show  variables  like  "rpl_semi_sync_%_enabled";

永久配置文件生效

主库配置文件

plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1

从库配置文件

plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

发表评论