欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

使用二进制热备份创建MySQL主从复制

程序员文章站 2022-06-08 17:17:53
...

搭建环境说明 机器说明 Master 190: 192.168.1.190 (开启binlog, server-id=1)Slave 191: 192.168.1.191Slave 192: 192.168.1

搭建环境说明

  • 机器说明

    Master 190: 192.168.1.190 (开启binlog, server-id=1)
    Slave 191: 192.168.1.191
    Slave 192: 192.168.1.192
    以上三台机器都安装了MySQL 5.5.29, Percona XtraBackup 2.06

  • 基于Master的二进制热备份建立新Slave

  • 在Master 190上创建Slave 191用于MySQL复制的账号 mysql> create user 'newrepl'@'192.168.1.191' identified by '123456'; mysql> grant replication slave on *.* to 'newrepl'@'192.168.1.191'; mysql> flush privileges;
  • 将Master 190的MySQL数据直接远程热备份到Slave 191 [root@CentOS190 ~]# innobackupex --user=backup --password=123456 --parallel=4 --stream=tar ./ | ssh root@192.168.1.191 "tar -ixf - -C /var/lib/mysql/data"
  • 在Slave 191上应用备份,并对备份数据目录做相应权限设置 [root@centos191 ~]# innobackupex --apply-log --use-memory=4G /var/lib/mysql/data [root@centos191 ~]# chown -R mysql:mysql /var/lib/mysql/data
  • Slave 191上停止MySQL,进行MySQL配置,然后启动MySQL [root@centos191 mysql]# service mysql stop [root@centos191 mysql]# scp root@192.168.1.190:/etc/my.cnf /etc/my.cnf

    修改/etc/my.cnf:

    ... [mysqld] datadir = /var/lib/mysql/data server-id = 2 # Master的server-id = 1 relay-log = slave-relay-bin relay-log-index = slave-relay-bin.index ...

    配置完后,启动MySQL:

    [root@centos191 mysql]# service mysql start Starting MySQL.... [ OK ]
  • 建立复制连接开始复制,并检查slave运行状态

    查看热备份的binlog文件名和偏移位置信息:

    [root@centos191 mysql]# cat /var/lib/mysql/data/xtrabackup_binlog_info master-bin.000005 107

    建立复制连接并检查是否成功:

    mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.1.190', -> MASTER_USER='newrepl', -> MASTER_PASSWORD='123456', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-bin.000005', -> MASTER_LOG_POS=107; Query OK, 0 rows affected (0.96 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show global status like 'Slave_running'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slave_running | ON | +---------------+-------+ 1 row in set (0.00 sec)
  • 基于Slave的二进制热备份建立新Slave

  • 在Master 190上创建Slave 192用于MySQL复制的账号 [root@centos191 mysql]# mysql -uroot -p -h192.168.1.190 ... mysql> create user 'newrepl'@'192.168.1.192' identified by '123456'; mysql> grant replication slave on *.* to 'newrepl'@'192.168.1.192'; mysql> flush privileges;
  • 将Slave 191的MySQL数据直接远程热备份到Slave 192 # innobackupex --user=backup --password=123456 --parallel=4 --slave-info --safe-slave-backup --stream=tar ./ | ssh root@192.168.1.192 "tar -ixf - -C /var/lib/mysql/data"

    注:这里备份使用--slave-info可以将Master的binary log的文件名和偏移位置记录到xtrabackup_slave_info文件中.而使用--safe-slave-backup会暂停Slave的SQL线程直到备份结束,这样可以确保一致性的复制状态.

  • 在新Slave 192上应用备份,并对备份数据目录做相应权限设置

    [root@centos192 ~]# innobackupex –apply-log –use-memory=4G /var/lib/mysql/data
    [root@centos192 ~]# chown -R mysql:mysql /var/lib/mysql/data

  • 新Slave 192上停止MySQL,进行MySQL配置,然后启动MySQL [root@centos192 mysql]# service mysql stop [root@centos192 mysql]# scp root@192.168.1.191:/etc/my.cnf /etc/my.cnf

    修改/etc/my.cnf:

    ... [mysqld] server-id = 3 skip-slave-start ...

    启动MySQL:

    [root@centos192 mysql]# service mysql start Starting MySQL... [ OK ]
  • 建立复制连接开始复制,并检查slave运行状态

    查看Slave热备份的binlog文件名和偏移位置信息:

    [root@centos192 mysql]# cat /var/lib/mysql/data/xtrabackup_slave_info CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000005', MASTER_LOG_POS=614672

    建立复制连接并检查是否成功:

    mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.1.190', -> MASTER_USER='newrepl', -> MASTER_PASSWORD='123456', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-bin.000005', -> MASTER_LOG_POS=614672; Query OK, 0 rows affected (0.33 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \G; ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Seconds_Behind_Master: 0 ...

    修改/etc/my.cnf,注释掉”skip-slave-start”,然后重启MySQL即可.

    # sed -i 's/skip-slave-start/#skip-slave-start/g' /etc/my.cnf # service mysql restart
  • 推荐阅读:

    Ubuntu下Nginx做负载实现高性能WEB服务器5---MySQL主主同步

    生产环境MySQL主主同步主键冲突处理

    MySQL主从失败 错误Got fatal error 1236

    MySQL主从复制,,单台服务器上实施

    使用二进制热备份创建MySQL主从复制