设为首页 - 加入收藏 - 网站地图 欢迎加QQ群 :14364084
当前位置:谭博的博客 > 数据库 > mysql > 正文

用XtraBackup实现MySQL的主从复制快速部署【主不锁

时间:2015-01-27 16:55 来源:未知 标签: 作者:谭博 阅读:

详细的说明~

其中最大的不足在于mysqldump备份数据库,会锁定所有的表,无法写入, 
如果数据库比较大,就是一件很悲剧的事情。 
 
今天介绍一下使用 percona 的xtrabackup 快速且无锁表地建立mysql主从体系。 
 
在mysql 中创建用户的步骤就都略过了。 
 
安装xtrabackup: 
 
rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm 
这个是64位的。 
32位的地址是: 
http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm  
  
如果装了这个套装之后却找不到innobackupex命令。。。就。。。: 
http://www.percona.com/software/percona-xtrabackup/downloads/ 

mkdir /data/backup -p 
确保在my.cnf中存在[mysqld] 
并且在[mysqld]后面存在 datadir = .... 
  
[root@localhost ~]# innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf    /data/backup
  
  
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 
and Percona Inc 2009-2012.  All Rights Reserved. 
  
This software is published under 
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 
  
120419 10:46:26  innobackupex: Starting mysql with options:  --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 
120419 10:46:26  innobackupex: Connected to database with mysql child process (pid=4394) 
120419 10:46:32  innobackupex: Connection to database server closed 
IMPORTANT: Please check that the backup run completes successfully. 
          At the end of a successful backup run innobackupex 
          prints "completed OK!". 
  
innobackupex: Using mysql  Ver 14.14 Distrib 5.1.58, for RedHat-linux-gnu (i686) using readline 5.1 
innobackupex: Using mysql server version Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. 
  
innobackupex: Created backup directory /data/backup/2012-04-19_10-46-32 
120419 10:46:32  innobackupex: Starting mysql with options:  --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 
120419 10:46:32  innobackupex: Connected to database with mysql child process (pid=4418) 
120419 10:46:34  innobackupex: Connection to database server closed 
  
120419 10:46:34  innobackupex: Starting ibbackup with command: xtrabackup_51  --defaults-file="/etc/my.cnf" --backup --suspend-at-end --target-dir=/data/backup/2012-04-19_10-46-32 
innobackupex: Waiting for ibbackup (pid=4426) to suspend 
innobackupex: Suspend file '/data/backup/2012-04-19_10-46-32/xtrabackup_suspended' 
  
xtrabackup_51 version 2.0.0 for MySQL server 5.1.59 pc-linux-gnu (i686) (revision id: 417) 
xtrabackup: uses posix_fadvise(). 
xtrabackup: cd to /var/lib/mysql 
xtrabackup: Target instance is assumed as followings. 
xtrabackup:  innodb_data_home_dir = ./ 
xtrabackup:  innodb_data_file_path = ibdata1:10M:autoextend 
xtrabackup:  innodb_log_group_home_dir = ./ 
xtrabackup:  innodb_log_files_in_group = 2 
xtrabackup:  innodb_log_file_size = 5242880 
>> log scanned up to (0 9651018) 
[01] Copying ./ibdata1 to /data/backup/2012-04-19_10-46-32/ibdata1 
[01]        ...done 
  
120419 10:46:38  innobackupex: Continuing after ibbackup has suspended 
120419 10:46:38  innobackupex: Starting mysql with options:  --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 
120419 10:46:38  innobackupex: Connected to database with mysql child process (pid=4434) 
>> log scanned up to (0 9651018) 
120419 10:46:40  innobackupex: Starting to lock all tables... 
>> log scanned up to (0 9651018) 
>> log scanned up to (0 9651018) 
120419 10:46:50  innobackupex: All tables locked and flushed to disk 
  
120419 10:46:50  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI, 
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in 
innobackupex: subdirectories of '/var/lib/mysql' 
innobackupex: Backing up files '/var/lib/mysql/shipincon/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (14 files) 
innobackupex: Backing up file '/var/lib/mysql/test/t.MYI' 
innobackupex: Backing up file '/var/lib/mysql/test/t.MYD' 
innobackupex: Backing up file '/var/lib/mysql/test/t.frm' 
innobackupex: Backing up files '/var/lib/mysql/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (54 files) 
120419 10:46:50  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files 
  
innobackupex: Resuming ibbackup 
  
xtrabackup: The latest check point (for incremental): '0:9651018' 
xtrabackup: Stopping log copying thread. 
.>> log scanned up to (0 9651018) 
  
xtrabackup: Transaction log of lsn (0 9651018) to (0 9651018) was copied. 
120419 10:46:53  innobackupex: All tables unlocked 
120419 10:46:53  innobackupex: Connection to database server closed 
  
innobackupex: Backup created in directory '/data/backup/2012-04-19_10-46-32' 
innobackupex: MySQL binlog position: filename 'log_bin.000027', position 2973624 
120419 10:46:53  innobackupex: completed OK! 
最后输出 completed OK! 表示备份成功了。 
可以看到在备份myisam类型表的时候,还是会锁表~~ innodb就不会锁表。哼。 
 
备份好的文件保存在 /data/backup目录中,比如: 
/data/backup/2012-04-19_10-46-32/ 
[root@localhost ~]# ls /data/backup/2012-04-19_10-46-32/ 
backup-my.cnf ibdata1 mysql shipincon test xtrabackup_binary xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_logfile 
 
备份日志: 
刚刚备份好的数据文件,并不是直接可用的。大概是处于一种数据库挂掉的状态~~~, 
细节不讲了,要用日志对其进行恢复: 
 
[root@localhost ~]# innobackupex --apply-log  /data/backup/2012-04-19_10-46-32/ 
这个过程与数据库挂掉之后重启mysqld时的自动修复过程差不多。 
 
把数据复制到从服务器: 
 
$ scp -r /data/backup/2012-04-19_10-46-32/ root@newslave:/data/ 
关闭从服务器并切换数据: 
 
$ /etc/init.d/mysql stop 
$ cd /data 
$ mv mysql mysql_old 
$ mv 2012-04-19_10-46-32 mysql 
修改my.cnf, 给它一个独一无二的server_id。 
一个比较好的办法是用服务器的IP地址,把其中的.去掉即可。 
 
然后启动mysqld: 
 
$ /etc/init.d/mysql start 
最后change master。 
与mysqldump备份的步骤比起来,这次我们没有flush tables with read lock, 
也没有show master status来获取日志文件名和座标。 
因为xtrabackup完成备份之后,自动保存了这些信息。 
 
$ cat /data/mysql/xtrabackup_binlog_info 
  
log_bin.000027 2973624 
mysql> CHANGE master to-> master_user=’rep’,-> master_password=’rep’, 
-> master_host=’10.20.30.40′, 
  
-> master_log_file=’log_bin.000027′, 
  
-> master_log_pos= 2973624; 
然后 start slave 即可。

推荐利用xtrabackup实现从服务器的部署~ 速度真的很快~

 

关注微信公众号

微信扫一扫,打赏我

热评文章
    内容不错,支持一下
    评论列表(网友评论仅供网友表达个人看法,并不表明本站同意其观点或证实其描述)