文档章节

XtraBackup在线数据库不停机热备

atompi
 atompi
发布于 2017/05/16 11:06
字数 2599
阅读 78
收藏 1

简介

转载一下主从同步和XtraBackup的简介:

MySQL主从同步原理

MySQL主从同步是在MySQL主从复制(Master-Slave Replication)基础上实现的,通过设置在Master MySQL上的binlog(使其处于打开状态),Slave MySQL上通过一个I/O线程从Master MySQL上读取binlog,然后传输到Slave MySQL的中继日志中,然后Slave MySQL的SQL线程从中继日志中读取中继日志,然后应用到Slave MySQL的数据库中。这样实现了主从数据同步功能。

XtraBackup备份原理

innobackupex在后台线程不断追踪InnoDB的日志文件,然后复制InnoDB的数据文件。数据文件复制完成之后,日志的复制线程也会结束。这样就得到了不在同一时间点的数据副本和开始备份以后的事务日志。完成上面的步骤之后,就可以使用InnoDB崩溃恢复代码执行事务日志(redo log),以达到数据的一致性。

备份分为两个过程:

  1. backup,备份阶段,追踪事务日志和复制数据文件(物理备份)。
  2. preparing,重放事务日志,使所有的数据处于同一个时间点,达到一致性状态。

XtraBackup的优点

  1. 可以快速可靠的完成数据备份(复制数据文件和追踪事务日志)
  2. 数据备份过程中不会中断事务的处理(热备份)
  3. 节约磁盘空间和网络带宽
  4. 自动完成备份鉴定
  5. 因更快的恢复时间而提高在线时间

操作

完整的步骤

  1. 主、从服务器上都搭好MySQL服务,从服务器上MySQL版本大于等于主服务器,最好完全一致
  2. 在要做主从同步的服务器上分别安装XtraBackup
  3. 如果从服务器上有MySQL实例,停掉服务,备份删除数据库内容,保留数据库目录
  4. 配置主从服务器打开主从同步功能
  5. 主服务器上执行备份
  6. 传输备份文件到从服务器,并同步数据文件(apply-log)
  7. 从服务器上恢复备份
  8. 主服务器上授权同步帐号
  9. 从服务器上设置MASTER并开启同步
  10. 查看主从同步状态

完成,可以检查同步状态了!

具体操作过程

master host: 192.168.2.11
slave host: 192.168.2.12

一、主从服务器上搭建MySQL服务,并检查MySQL版本:(略)

二、主从服务器上分别安装XtraBackup,根据官方网站指导使用打包好的二进制,选择最新的稳定版2.4:

# master & slave
> wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
> sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
> sudo apt-get update
> sudo apt-get install percona-xtrabackup-24

三、停掉从服务器上MySQL服务,备份原有数据库,并删除原有数据库内容:(可选,如果从服务器是新机器可以跳过)

> mysqldump -u$USER -p$PASSWORD --routines --default-character-set=utf8 --locak-all-tables --add-drop-database -A dball.sql
> sudo pkill mysql
> sudo cd /data/mysql
# 下面这句千万别打错了,后果会很严重
> sudo rm -rf *

四、配置MySQL打开主从同步功能

主服务器上编辑/etc/mysql/my.conf文件:

[mysqld]
# 注意主从之间的server-id不能相同
server-id=1
log_bin=/var/log/mysql/mysql-bin.log

master my.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port=3306
socket=/var/run/mysqld/mysqld.sock
#user=zabbix
#host=localhost
#password=

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket=/var/run/mysqld/mysqld.sock
nice=0

[mysqld]
#
# * Basic Settings
#
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
port=3306
basedir=/usr/local/mysql
datadir=/data/mysql
tmpdir=/tmp
#lc-messages-dir=/usr/share/mysql
skip-external-locking

max_connections=1000

event_scheduler=ON
max_allowed_packet=48M
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address=0.0.0.0
#
# * Fine Tuning
#
key_buffer=512M
max_allowed_packet=64M
thread_stack=192K
thread_cache_size=24
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover=BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit=16M
query_cache_size=128M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error=/var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id        = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days=10
max_binlog_size=100M
#binlog_do_db        = include_database_name
#binlog_ignore_db    = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

innodb_file_per_table=1
innodb_open_files=2048

innodb_log_group_home_dir=/data/mysql
innodb_buffer_pool_instances=24
innodb_buffer_pool_size=24G
innodb_additional_mem_pool_size=64M
innodb_log_file_size=64M
innodb_lock_wait_timeout=50
innodb_flush_log_at_trx_commit=0
### small swap / IO
innodb_flush_method=O_DIRECT
innodb_write_io_threads=16
innodb_read_io_threads=16
innodb_thread_concurrency=16
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=90

skip-name-resolve
slow_query_log
long_query_time=0.1

log-bin=mysql-bin
binlog_format=mixed
relay-log=relay-bin
relay-log-index=slave-relay-bin.index
#auto-increment-increment=2
#auto-increment-offset=1
server-id=1
log_slave_updates=1
read_only=0


[mysqldump]
quick
quote-names
max_allowed_packet=48M

[mysql]
#no-auto-rehash    # faster start of mysql but no tab completition

[isamchk]
key_buffer=16M
max_allowed_packet=48M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

如果主服务器上MySQL是已经上线的系统,需要重启一下(实测/etc/init.d/mysql reload不起作用):

> sudo pkill mysql
> cd /usr/local/mysql
> sudo bin/mysqld_safe --user=mysql --datadir=/data/mysql --pid-file=/var/run/mysqld/mysqld.pid &

从服务器上编辑/etc/mysql/my.conf文件:

[mysqld]
# 注意主从之间的server-id不能相同
server-id=2
# 最好设置从服务器为只读
# 注意:即使这里设置了只读,使用具有super权限的用户登录,也还是可以做写操作的
read_only=1

slave my.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port=3306
socket=/var/run/mysqld/mysqld.sock
#user=zabbix
#host=localhost
#password=

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket=/var/run/mysqld/mysqld.sock
nice=0

[mysqld]
#
# * Basic Settings
#
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
port=3306
basedir=/usr/local/mysql
datadir=/data/mysql
tmpdir=/tmp
#lc-messages-dir=/usr/share/mysql
skip-external-locking

max_connections=1000

event_scheduler=ON
max_allowed_packet=48M
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address=0.0.0.0
#
# * Fine Tuning
#
key_buffer=512M
max_allowed_packet=64M
thread_stack=192K
thread_cache_size=24
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover=BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit=16M
query_cache_size=128M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error=/var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id        = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days=10
max_binlog_size=100M
#binlog_do_db        = include_database_name
#binlog_ignore_db    = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

innodb_file_per_table=1
innodb_open_files=2048

innodb_log_group_home_dir=/data/mysql
innodb_buffer_pool_instances=24
innodb_buffer_pool_size=24G
innodb_additional_mem_pool_size=64M
innodb_log_file_size=64M
innodb_lock_wait_timeout=50
innodb_flush_log_at_trx_commit=0
### small swap / IO
innodb_flush_method=O_DIRECT
innodb_write_io_threads=16
innodb_read_io_threads=16
innodb_thread_concurrency=16
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=90

skip-name-resolve
slow_query_log
long_query_time=0.1

log-bin=mysql-bin
binlog_format=mixed
relay-log=relay-bin
relay-log-index=slave-relay-bin.index
#auto-increment-increment=2
#auto-increment-offset=1
server-id=2
log_slave_updates=1
read_only=0


[mysqldump]
quick
quote-names
max_allowed_packet=48M

[mysql]
#no-auto-rehash    # faster start of mysql but no tab completition

[isamchk]
key_buffer=16M
max_allowed_packet=48M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

查询主从服务器状态:

> mysql -uroot -p -e "show global variables like 'server_id';"
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | server_id     | 1     |
    +---------------+-------+

> mysql -uroot -p -e "show global variables like 'log_bin';"
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | log_bin       | ON    |
    +---------------+-------+

五、主服务器上执行备份操作

> sudo innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=$PASSWORD --parallel=4 /tmp/mybackup

命令输出的最后几行通常类似这样:

innobackupex: Backup created in directory '/tmp/mybackup/2017-05-12_17-41-51' innobackupex: MySQL binlog position: filename 'mysql-bin.000003', position 1946 111225 00:00:53 innobackupex: completed OK!

命令执行完在/tmp/mybackup目录下生成的2017-05-12_17-41-51目录,里面存储的是备份的数据,下一步要传输到从服务器上的即是这个文件夹。

输出中的MySQL binlog position: filename 'mysql-bin.000003', position 1946里面的两个数字,要记录以下,后面恢复到从服务器上的时候要用到。

六、传输并同步备份数据

读取备份数据需要ROOT权限,下面的命令需要使用sudo执行。

> sudo scp -r /tmp/mybackup git@192.168.2.12:/tmp/

在从服务器上执行:

> sudo innobackupex --apply-log /tmp/mybackup/2017-05-12

七、从服务器上恢复备份数据

# 恢复数据
> sudo innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=$PASSWORD --copy-back /tmp/mybackup/2017-05-12/
# 需要恢复权限给mysql
> sudo chown -R mysql:mysql /data/mysql
# 启动MySQL
> cd /usr/local/mysql
> sudo bin/mysqld_safe --user=mysql --datadir=/data/mysql --pid-file=/var/run/mysqld/mysqld.pid &

八、主服务器上授权同步帐号

> mysql -u$USER -p$PASSWORD
> create user slave@'192.168.2.12' identified by 'password';
> grant replication slave on *.* to 'slave'@'192.168.2.12' identified by 'password';
> flush privileges;
> select distinct concat('User: ''',user,'''@''',host,''';') as query from mysql.user;
> exit

倒数第二条语句查询当前数据库中的用户信息,检查slave是否在其中。

九、配置从服务器开启同步

mysql -u$USER -p$PASSWORD
> change master to
> master_host = '192.168.2.11',
> master_user = 'slave',
> master_password = 'password',
> master_port = 3306,
> master_log_file = 'mysql-bin.000003',
> master_log_pos = 1946;
> start slave;

十、查看主从同步状态

# master
> mysql -u$USER -p -e "show master status \G;"
> mysql -u$USER -p -e "show processlist \G;" | grep -i 'master'
# 第二条输出是否类似“State: Master has sent all binlog to slave; waiting for binlog to be updated”这样。

# slave
> mysql -u$USER -p -e "show slave status \G;"
# 输出是否包含类似下面这样的语句:
    Slave_IO_State: Waiting for master to send event
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
    
> mysql -u$USER -p -e "show processlist \G;" | egrep -i '(master|slave)'
# 输出包含类似以下内容:
    State: Waiting for master to send event
    State: Slave has read all relay log; waiting for the slave I/O thread to update it

参考文献:http://hshsh.me/post/2016-04-26-mysql-hot-backup-with-xtrabackup/

© 著作权归作者所有

atompi

atompi

粉丝 20
博文 5
码字总数 7555
作品 0
深圳
运维
私信 提问
实战 innobackupex 全量备份&恢复 MySQL

1、背景 我们有一台业务数据库一直都只做了主从,虽然一定程度上解决了读写性能问题,但是这个是有风险的,比如某同学删除主库数据,从库也会跟着删除,所以及时的备份还是很有必要的。计划是...

大数据之路
2013/04/22
2.5K
1
mysql不停库不锁表在线主从配置

mysql不停库不锁表在线主从配置: 说明: 10G以下的数据库:使用mysqldump导出数据和备份恢复比较合适,便捷。 100-500G数据库:使用Percona-Xtrabackup备份工具,在线热备,全量、增量、单表...

ZHENG-JY
2018/09/04
23
0
mysql运维-备份恢复之percona-xtrabackup

mysql的物理备份分两种: 冷备:停止服务,拷贝data目录,然后重启服务,完成。如果需要恢复,停止服务,删掉当前在用data,把之前拷贝的data复制过来,再开启服务,就OK了!因为冷备需要停掉...

坦途abc
2018/08/28
34
0
mysql快速备份xtrabackup和innobackupex

Xtrabackup是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写). Xtrabackup有两个主要的工具:xtrabackup、innobackupex (1)xtrabackup只能备份InnoDB和XtraDB两种数...

庆沉
2015/07/17
26
0
XtraBackup 原理与安装

阅读目录 1. XtraBackup 简介 2. XtraBackup 原理 3. XtraBackup 安装 4. XtraBackup 备份恢复 1. XtraBackup 简介 XtraBackup(PXB) 工具是 Percona 公司用 perl 语言开发的一个用于 MySQL ...

xiaocao13140
2018/05/21
0
0

没有更多内容

加载失败,请刷新页面

加载更多

计算机实现原理专题--二进制减法器(二)

在计算机实现原理专题--二进制减法器(一)中说明了基本原理,现准备说明如何来实现。 首先第一步255-b运算相当于对b进行按位取反,因此可将8个非门组成如下图的形式: 由于每次做减法时,我...

FAT_mt
昨天
6
0
好程序员大数据学习路线分享函数+map映射+元祖

好程序员大数据学习路线分享函数+map映射+元祖,大数据各个平台上的语言实现 hadoop 由java实现,2003年至今,三大块:数据处理,数据存储,数据计算 存储: hbase --> 数据成表 处理: hive --> 数...

好程序员官方
昨天
7
0
tabel 中含有复选框的列 数据理解

1、el-ui中实现某一列为复选框 实现多选非常简单: 手动添加一个el-table-column,设type属性为selction即可; 2、@selection-change事件:选项发生勾选状态变化时触发该事件 <el-table @sel...

everthing
昨天
6
0
【技术分享】TestFlight测试的流程文档

上架基本需求资料 1、苹果开发者账号(如还没账号先申请-苹果开发者账号申请教程) 2、开发好的APP 通过本篇教程,可以学习到ios证书申请和打包ipa上传到appstoreconnect.apple.com进行TestF...

qtb999
昨天
10
0
再见 Spring Boot 1.X,Spring Boot 2.X 走向舞台中心

2019年8月6日,Spring 官方在其博客宣布,Spring Boot 1.x 停止维护,Spring Boot 1.x 生命周期正式结束。 其实早在2018年7月30号,Spring 官方就已经在博客进行过预告,Spring Boot 1.X 将维...

Java技术剑
昨天
18
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部