xtrabackup实现多实例备份实践

多实例备份,我一直未曾去学习使用它,最近,因公司报表机数据增长太快,dump备份太慢,也很容易卡住,因此,决定弃用dunp备份,改用xtrabackup备份,实现增备、全备;

目前,DB存在3307,3308,3309 三个实例,使用的mysqld_multi部署的多实例;

这里,如果需要使用xtrabackup备份多实例的话,需要针对每个实例,将配置文件独立写出一份,放在/home/backup/conf目录下

我们备份的目录为:/home/backup

备份的最新检查点放在:/home/backup/last_backup

备份存放路径:/home/backup/BackupDir_xtrabackup

备份日志存放路径:/home/backup/log

具体目录结构如下:

[root@slave1 home]# tree /home/backup
/home/backup
├── BackupDir_xtrabackup
├── backup_xtrabackup.sh
├── conf
│ ├── my3307.cnf
│ ├── my3308.cnf
│ └── my3309.cnf
├── last_backup
└── log

percona xtrabackup 官方文档地址:

https://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/innobackupex_script.html

下面,简单讲一下,xtrbackup备份多实例的使用

xtrbackup常用的几个参数:

–user: mysql用户

–password: 用户密码

–defaults-file: 指定my.cnf文件路径,若不指定则读取mysql默认的my.cnf文件,这里我们会指定我们在conf目录独立出的各个实例的文件

–socket:mysql实例对应的socket文件

全备示例:

innobackupex –user=root –password=123456 –socket=/tmp/mysql3307.sock –defaults-file=/home/backup/conf/my3307.cnf /home/backup/BackupDir_xtrabackup/3307/

当备份日志出现:innobackupex: completed OK! 则代表备份成功。

备份结束后,备份目录会出现几个文件,下面,我们解释一下:

backup-my.cnf:主要是记录innobackupex中使用到Mysql参数。

xtrabackup_checkpoints:记录备份类型及开始及结束的lsn位置。backup_type 有两种full-prepared (全备)、incremental (增备)。

xtrabackup_info:记录mysql相关信息。

xtrabackup_logfile: xtrabackup自己的日志文件,新版本中不直接可见。

xtrabackup_binlog_info :这个会记录你备份结束时,主上binlog的位置,可以用于重建主从

增量备份,由于是基于全备执行,而且,方法比较简单,这里就不演示

下面描述一下,恢复需要注意的问题:

1. 对全量备份进行操作

了解两个参数 :

–apply-log :创建新的事务日志,从backup-my.cnf文件中读取innodb配置信息。

–redo-only:只读已提交的事务,在最后一次增量合并时,不需要填写这个参数。

全量备份恢复前准备

innobackupex –apply-log –redo-only /home/backup/BackupDir_xtrabackup/3307/……

2. 将第一次增量备份的数据合并到全量备份中

innobackupex –apply-log –redo-only /home/backup/BackupDir_xtrabackup/3307/…full –incremental-dir= /home/backup/BackupDir_xtrabackup/3307/…..inc

3.将第二次全量备份的数据合并到全量备份中(最后一个增量备份)

innobackupex –apply-log /home/backup/BackupDir_xtrabackup/3307/…full –incremental-dir= /home/backup/BackupDir_xtrabackup/3307/…..inc

注:最后一次的合并操作中不需要添加–redo-only参数。

4.这里需要对全备,再做一次apply-log,网上很多都没有这步,但官方文档上有

innobackupex –apply-log /home/backup/BackupDir_xtrabackup/3307/…full

5.停止mysql服务

恢复时需要停掉MySQL,并且数据目录必须为空,所以,我们需要多原有的数据目录进行备份,最简单方式:mv data data_1011_bak

然后,再新建一个数据目录:mkdir data

6. 恢复数据。

innobackupex –defaults-file=/home/backup/conf/my3307.cnf –copy-back /home/backup/BackupDir_xtrabackup/3307/…full

7.权限设置

恢复完成后,需要对数据目录设置mysql权限

chown -R mysql:mysql /data/

8.启动mysql实例

9. 查看是否恢复成功,这里恢复算完成了,但是,实际情况下,我们还需要根据xtrbackup_binlog_info的信息去恢复 binlog的部分数据,这个环节网上也没有提到,所以,刚才对数据目录的备份就十分有用,这里,就不做演示,相信基于binlog的恢复,应该都十分熟练了

rhel6下,mysql 5.6.14 主(master)从(slave)复制(也称mysql AB复制)环境配置[基于binlog]

一、mysql主(称master)从(称slave)复制的原理:

(1).master将数据改变记录到二进制日志(binary log)中,也即是配置文件log-bin指定的文件(这些记录叫做二进制日志事件,binary log events)

(2).slave将master的binary log events拷贝到它的中继日志(relay log)

(3).slave重做中继日志中的事件,将改变反映它自己的数据(数据重演)

附简要原理图:
replication

二、mysql主从复制支持的类型:

(1).基于语句的复制:在主服务器上执行的SQL语句,在从服务器上执行同样的语句.MySQL默认采用基于语句的复制,效率比较高

(2).基于行的复制:把改变的内容直接复制过去,而不关心到底改变该内容是由哪条语句引发的 . 从mysql5.0开始支持

(3).混合类型的复制: 默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制.

三、主从配置需要注意的地方:

(1).主DB server和从DB server数据库的版本一致

(2).主DB server和从DB server数据库数据一致[ 这里就会可以把主的备份在从上还原,也可以直接将主的数据目录拷贝到从的相应数据目录]

(3).主DB server开启二进制日志,主DB server和从DB server的server_id都必须唯一

四、主从配置的简要步骤:

附简要示意图:
单向主从

1.主DB SERVER上的配置

(1).安装数据库

(2).修改数据库配置文件,指明server_id,开启二进制日志(log-bin)

(3).启动数据库,查看当前是哪个日志,position号是多少

(4).登陆数据库,授权用户[ip地址为从机IP地址,如果是双向主从,这里的还需要授权本机的IP地址(此时自己的IP地址就是从IP地址)]

(5).备份数据库[记得加锁和解锁]

(6).传送备份到从DB server上

(7).启动数据库
以下步骤,为单向主从搭建成功,想搭建双向主从需要的步骤:

(1).登陆数据库,指定主DB server的地址,用户,密码等信息[此步仅双向主从时,需要]

(2).开启同步,查看状态

2.从DB SERVER上的配置

(1).安装数据库

(2).修改数据库配置文件,指明server_id[如果是搭建双向主从的话,也要开启二进制日志(log-bin)]

(3).启动数据库,还原备份

(4).查看当前是哪个日志,position号是多少[单向主从此步不需要,双向主从需要]

(5).指定主DB server的地址,用户,密码等信息

(6).开启同步,查看状态

五、单向主从环境[也称 mysql A/B复制]的搭建案例:

1.主DB server和从DB server都安装相应版本的数据库,我的两台DB server都已经安装好(5.6.14版本),都会是双实例,这里就不演示安装,可以参考mysql源码编译安装和mysql多实例配置两篇文章

注:两台机器的的selinux都是disable(永久关闭selinux,请修改/etc/selinux/config,将SELINUX改为disabled),防火墙可以选择关闭,开启的话也行[不行的话,添加防火墙策略]

2.修改主DB server的配置文件(/etc/my.cnf),开启日志功能,设置server_id值,保证唯一[client102为主DB server]

  1. [root@client102 scripts]# vim /etc/my.cnf  
  2. # 修改配置文件里,下面两个参数:  
  3. # 设置server_id,一般建议设置为IP,或者再加一些数字  
  4. server_id =102  
  5. # 开启二进制日志功能,可以随便取,最好有含义  
  6. log-bin=mysql3306-bin  

3.启动数据库服务器,并登陆数据库,授予相应的用户用于同步

  1. # 我这里是多实例mysql,所以启动是这样的,如果大家是单实例的,就直接启动就可以[/etc/init.d/mysqld start]  
  2. [root@client102 scripts]# mysqld_multi start 3306  
  3. # 登陆mysql 服务器  
  4. [root@client102 scripts]# mysql -uroot -S /usr/local/mysql/mysqld3306.sock -p  
  5. # 授予用户权限用于主从同步  
  6. mysql> grant replication slave on *.* to ‘kongzhong’@’192.168.1.100’ identified by ‘kongzhong’;  
  7. Query OK, 0 rows affected (0.00 sec)  
  8. # 刷新授权表信息  
  9. mysql> flush privileges;  
  10. Query OK, 0 rows affected (0.00 sec)  
  11. # 查看position 号,记下position 号(很重要,从机上需要这个position号和现在的日志文件,我这里是414和mysql3306-bin.000001)  
  12. mysql> show master status;  
  13. +———————-+———-+————–+——————+——————-+  
  14. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |  
  15. +———————-+———-+————–+——————+——————-+  
  16. | mysql3306-bin.000001 | 414 | | | |  
  17. +———————-+———-+————–+——————+——————-+  
  18. 1 row in set (0.00 sec)  

4.为保证主DB server和从DB server的数据一致,这里采用主备份,从还原来实现初始数据一致

  1. # 临时锁表  
  2. mysql> flush tables with read lock;  
  3. # 我这里实行的全库备份,在实际中,我们可能只同步某一个库,可以只备份一个库  
  4. # 新开一个终端,执行如下操作  
  5. [root@client102 data]# mysqldump -p3306 -uroot -p -S /usr/local/mysql/mysqld3306.sock –all-databases > /tmp/mysql.sql  
  6. # 解锁  
  7. mysql> unlock tables;  
  8. # 将备份的数据传送到从机上,用于恢复  
  9. [root@client102 data]# scp /tmp/mysql.sql root@192.168.1.100:/tmp  

5.从DB server配置文件只需修改一项,其余用命令行做

  1. [root@client100 ~]# vim /etc/my.cnf  
  2. # 设置server_id,一般建议设置为IP,或者再加一些数字  
  3. server_id =100  

6.启动数据库,还原备份数据

  1. # 启动数据库  
  2. [root@client100 ~]# mysqld_multi start 3306  
  3. # 还原主DB server备份的数据  
  4. [root@client100 ~]# mysql -uroot -S /usr/local/mysql/mysqld3306.sock -p < /tmp/mysql.sql 7.登陆数据库,添加相关参数(主DBserver的ip/端口/同步用户/密码/position号/读取哪个日志文件)   
  5. [root@client100 ~]# mysql -uroot -S /usr/local/mysql/mysqld3306.sock -p  
  6. mysql> change master to  
  7. > master_host=’192.168.1.102′,  
  8. > master_user=’kongzhong’,  
  9. > master_password=’kongzhong’,  
  10. > master_port=3306,  
  11. > master_log_file=’mysql3306-bin.000001′,  
  12. > master_log_pos=414;  
  13. #/* 下面是一部分注解:  
  14. #/* 指定主DB server的IP地址  
  15. master_host=’192.168.1.102′  
  16. #/* 指定用于同步的用户[这个就是我们在主DB server授权的用户]  
  17. master_user=’kongzhong’  
  18. #/* 指定用于同步的用户的密码  
  19. master_password=’kongzhong’  
  20. #/* 指定主DB server的端口[下面一个例子,可以重点看这个]  
  21. master_port=3306  
  22. #/* 指定从DB server 从哪个日志文件开始读[在主DB server上使用show master status查看到日志]  
  23. master_log_file=’mysql3306-bin.000001′  
  24. #/* 指定 从哪个POSITION号开始读  
  25. master_log_pos=414  
  26.   
  27. # 开启主从同步  
  28. mysql> start slave;  
  29. # 查看主从同步状态  
  30. mysql> show slave status\G;  
  31. # 主要看以下两个参数:[这两个参数如果是yes就表示主从同步正常]  
  32. Slave_IO_Running: Yes  
  33. Slave_SQL_Running: Yes  

8.下面大家就可以在主DB server上新建一个表,看是否能同步到从DB server上,我这里就不测试了

[注:千万不要在从DB server手动插入数据,那样数据就不一致,主从就会断开,需要重新配置了]

如果有问题,可以尝试关闭IPTABLES(/etc/init.d/iptables stop)和selinux(setenforce 0:临时关闭selinux,永久关闭selinux,请修改/etc/selinux/config,将SELINUX改为disabled)

9.上面所搭建的是单向主从,也是用的比较多的,有人想了解双向主从是如何搭建,其实,就是主DB server和从DB sever都开启日志功能,然后在主DB SERVER执行授权用户[这里授权的是自己作为从服务器,也就是这里的IP地址是主DB server的IP地址],然后再在主DB server上进行chang master操作.有不理解的可以留言询问.

rhel6下,mysql5.6.14 多实例配置

       在我们的生产环境中,很少一台db server就一个mysql实例,从节约成本和充分利用资源的角度出发,我们往往会在主从环境下,对从机的db server 安装多个mysql实例,用来同步主db server的相应实例

      1.mysql 多实例的控制原理:
       多实例通过Mysqld_multi 可以管理多个监听不同的 unix 套字节文件和 TCP/IP 端口的连接的 mysqld 进程,包括 start,stop,status。
        默认的 mysqld_multi 是读取 my.cnf 中的[mysqld/N]中的数据,N 一定是正整数,如果你是单独写的文件,可以通过–config-file 参数来指定读取的 configfile。
  1. mysqld_multi 的 option  
  2. usage:  
  3. myysqld_multi [optinos] {start|stop|report} [GNR[,GNR]..]  
  4. option:  
  5. start|stop |report 这些大家都懂得  

GNR 指的是[msyqld/N]中的 N,GNR 中不能有空格和 tab

mysqld_multi start 10 就是启动 my.cnf 中的 10 的实例;mysqld_multi start 1,4-6 就是启动 1,4 到 6 个实例

如果任何参数都不加入的话,默认会启动所有的实例

登陆会话,则指定对应mysq实例的套接字文件:mysql -uuser -ppassword -S sockpath

2.下面我们简单搭建一台DB server启动两个mysql实例[主从同步,会在下一篇文章中搭建]

(1).我默认在这台DB server上已经安装一个mysql 实例[ 我这里是源码安装的mysql,各位可以采用RPM、二进制、源码等方式安装一个mysql ]

数据库安装目录:/usr/local/mysql

3306实例数据存放目录:/home/mysql/data

(2).新建第二个mysql实例数据存放目录,目录名为:data_3307

  1. [root@client100 mysql]# mkdir -p /home/mysql/data_3307  
  2. # 修改数据目录的属主  
  3. [root@client100 mysql]# chown  mysql. -R  /home/mysql/data_3307  

(3).编辑数据库配置文件:/etc/my.cnf

  1. [root@client100 mysql]# vim /etc/my.cnf  
  2. [mysqld_multi]  
  3. # 指定相关命令的路径  
  4. mysqld     =  /usr/local/mysql/bin/mysqld_safe  
  5. mysqladmin =  /usr/local/mysql/bin/mysqladmin  
  6. # 指定用户,用于启动和关闭mysql服务[这里尽量使用root用户,默认root用户拥有所以权限,记得在两个实例中root密码一致]  
  7. user = root  
  8. password = kongzhong  
  9. # 指定错误日志路径  
  10. log = /usr/local/mysql/multi.log  
  11.   
  12. [mysqld3306]  
  13. basedir =/usr/local/mysql  
  14. datadir =/home/mysql/data  
  15. socket = /usr/local/mysql/mysqld3306.sock  
  16. pid-file = /home/mysql/data/mysql3306.pid  
  17. general_log = 1  
  18. general_log_file = /home/mysql/data/mysql3306.log  
  19. port =3306  
  20. server_id =100  
  21. user = mysql  
  22. join_buffer_size = 128M  
  23. sort_buffer_size = 2M  
  24. read_rnd_buffer_size = 2M  
  25. default_storage_engine = MYISAM  
  26. join_buffer_size = 128M  
  27. max_allowed_packet= 1M  
  28. net_buffer_length= 8K  
  29. skip-external-locking  
  30. skip-host-cache  
  31. skip-name-resolve  
  32. # InnoDB  
  33. innodb_buffer_pool_size = 128M  
  34. innodb_log_file_size = 48M  
  35. innodb_file_per_table = 1  
  36. innodb_flush_method = O_DIRECT  
  37. # MyISAM  
  38. key_buffer_size = 48M  
  39. character-set-server=utf8  
  40. collation-server=utf8_general_ci  
  41. # LOG  
  42. log_error = /home/mysql/data/mysql-error.log  
  43. long_query_time = 1  
  44. slow-query-log  
  45. slow_query_log_file = /home/mysql/data/mysql-slow.log  
  46. # Others  
  47. explicit_defaults_for_timestamp=true  
  48. max_connections = 500  
  49. open_files_limit = 65535  
  50. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  
  51.   
  52. [mysqld3307]  
  53. basedir =/usr/local/mysql  
  54. datadir =/home/mysql/data_3307  
  55. socket = /usr/local/mysql/mysqld3307.sock  
  56. pid-file = /home/mysql/data_3307/mysql3307.pid  
  57. general_log = 1  
  58. general_log_file = /home/mysql/data_3307/mysql3307.log  
  59. port =3307  
  60. server_id =101  
  61. user = mysql  
  62. join_buffer_size = 128M  
  63. sort_buffer_size = 2M  
  64. read_rnd_buffer_size = 2M  
  65. default_storage_engine = MYISAM  
  66. join_buffer_size = 128M  
  67. max_allowed_packet= 1M  
  68. net_buffer_length= 8K  
  69. skip-external-locking  
  70. skip-host-cache  
  71. skip-name-resolve  
  72. # InnoDB  
  73. innodb_buffer_pool_size = 128M  
  74. innodb_log_file_size = 48M  
  75. innodb_file_per_table = 1  
  76. innodb_flush_method = O_DIRECT  
  77. # MyISAM  
  78. key_buffer_size = 48M  
  79. character-set-server=utf8  
  80. collation-server=utf8_general_ci  
  81. # LOG  
  82. log_error = /home/mysql/data_3307/mysql-error.log  
  83. long_query_time = 1  
  84. slow-query-log  
  85. slow_query_log_file = /home/mysql/data_3307/mysql-slow.log  
  86. # Others  
  87. explicit_defaults_for_timestamp=true  
  88. max_connections = 500  
  89. open_files_limit = 65535  
  90. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  

(4).为第二个实例初始化数据库:

  1. # 进入数据库安装目录  
  2. [root@client100 mysql]# cd /usr/local/mysql/scripts/  
  3. # 执行初始化脚本,指定数据目录/用户  
  4. [root@client100 scripts]# ./mysql_install_db –basedir=/usr/local/mysql –datadir=/home/mysql/data_3307 –user=mysql  

(5).使用mysqld_multi命令启动相应实例[如果my.cnf不在默认位置,需指明位置–defaults-extra-file=****]

  1. # 启动相应实例  
  2. [root@client100 ~]# mysqld_multi start 3306  
  3. [root@client100 ~]# mysqld_multi start 3307  
  4. # 使用如下命令检测 mysql 相应实例进程是否启动  
  5. [root@client100 ~]# ps -ef |grep mysql  
  6. # 使用netstat 检测mysql两个实例端口是否启动[看到两个端口的监听都出现,就说明搭建正常]  
  7. [root@client100 ~]# netstat -ntlp |grep mysql  
  8. # 使用如下两条命令,可登陆到对应实例数据库  
  9. [root@client100 ~]# mysql -uroot -S /usr/local/mysql/mysqld3306.sock -p  
  10. [root@client100 ~]# mysql -uroot -S /usr/local/mysql/mysqld3307.sock -p  
  11. # 登陆后,可查看参数,查看数据存放目录,以确认是否登陆自己要登陆的实例  
  12. mysql> show variables like ‘%datadir%’  
  13. # 关闭实例  
  14. [root@client100 ~]# mysqld_multi stop 3306  
  15. [root@client100 ~]# mysqld_multi stop 3307  
  16. # 如果执行以上命令,不能关闭的话,请使用以下方式关闭  
  17. [root@client100 ~]# mysqladmin -uroot -p -S /usr/local/mysql/mysqld3307.sock shutdown  
  18. [root@client100 ~]# mysqladmin -uroot -p -S /usr/local/mysql/mysqld3306.sock shutdown  
  19.   
  20. ## 有的朋友可能在使用 mysqld_multi stop 关闭时,始终有实例关闭不了,这里就是/etc/my.cnf中user/password的作用  
  21. ## 需要做如下操作:  
  22. ## 如果你指定的用户不是root用户,那最好拥有mysql的所有目录权限,不然会报权限不够,所以建议用mysql或root用户  
  23. ## 如果是root用户,因为root拥有所有权限,所以只要设置两个实例的密码都是一样的就行  
  24. ## 而对于mysql,则编译安装时,创建用户是就要允许此用户登录,即不要加nologin限制  
  25. ## 并登录到实例数据库后,授予shutdown权限,设置密码  
  26. mysql>  GRANT SHUTDOWN ON *.* TO ‘mysql’@’localhost’ IDENTIFIED BY ‘kongzhong’;  
  27. mysql> flush privileges  
  28. ## 现在再用 mysqld_multi stop 关闭,就可正常关闭[还是那句,建议用root]