环境描述
主从环境
项目
master
slave
os版本
suse 11sp1 x86_64
suse 11sp1 x86_64
mysql版本
官方版本5.5.37
官方版本5.5.37
hostname
ndmc74
ndmc75
ip
192.168.223.132
192.168.223.133
port
3306
3306
server-id
1323306
1333306
参数文件
/etc/my.cnf
/etc/my.cnf
datadir
/data/mysql/mysql3306/data
/data/mysql/mysql3306/data
主从库相关配置确认（1）server-id配置
master节点：
ndmc74:~ # mysql -s /tmp/mysql.sock -e show global variables like 'server_id';
+---------------+---------+
| variable_name | value |
+---------------+---------+
| server_id | 1323306 |
+---------------+---------+
slaver节点：
ndmc75:~ # mysql -s /tmp/mysql.sock -e show global variables like 'server_id';
+---------------+---------+
| variable_name | value |
+---------------+---------+
| server_id | 1333306 |
+---------------+---------+
（2）确认binlog是否开启
ndmc74:~ # mysql -s /tmp/mysql.sock -e show global variables like 'log_bin';
+---------------+-------+
| variable_name | value |
+---------------+-------+
| log_bin | on |
+---------------+-------+
master上使用xtrabackup做全库备份ndmc74:~ # innobackupex --defaults-file=/etc/my.cnf /data/backup/innobackupex
innodb backup utility v1.5.1-xtrabackup; copyright 2003, 2009 innobase oy
and percona llc and/or its affiliates 2009-2013. all rights reserved.
this software is published under
the gnu general public license version 2, june 1991.
get the latest version of percona xtrabackup, documentation, and help resources:
http://www.percona.com/xb/p
140731 11:20:44 innobackupex: connecting to mysql server with dsn 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' (using password: no).
140731 11:20:44 innobackupex: connected to mysql server
140731 11:20:44 innobackupex: executing a version check against the server...
140731 11:20:44 innobackupex: done.
important: please check that the backup run completes successfully.
at the end of a successful backup run innobackupex
prints completed ok!.
innobackupex: using mysql server version 5.5.37-log
innobackupex: created backup directory /data/backup/innobackupex/2014-07-31_11-20-44
140731 11:20:44 innobackupex: starting ibbackup with command: xtrabackup --defaults-file=/etc/my.cnf --defaults-group=mysqld --backup --suspend-at-end --target-dir=/data/backup/innobackupex/2014-07-31_11-20-44 --tmpdir=/tmp --extra-lsndir='/tmp'
innobackupex: waiting for ibbackup (pid=25767) to suspend
innobackupex: suspend file '/data/backup/innobackupex/2014-07-31_11-20-44/xtrabackup_suspended_2'
xtrabackup version 2.2.3 based on mysql server 5.6.17 linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/mysql3306/data
xtrabackup: open files limit requested 8192, set to 8192
xtrabackup: using the following innodb configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1g:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
>> log scanned up to (6118588398)
[01] copying ./ibdata1 to /data/backup/innobackupex/2014-07-31_11-20-44/ibdata1
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
[01] ...done
[01] copying ./test/tt.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/test/tt.ibd
[01] ...done
[01] copying ./tp50/warehouse.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/warehouse.ibd
[01] ...done
[01] copying ./tp50/item.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/item.ibd
[01] ...done
>> log scanned up to (6118588398)
[01] copying ./tp50/district.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/district.ibd
[01] ...done
[01] copying ./tp50/customer.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/customer.ibd
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
[01] ...done
[01] copying ./tp50/history.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/history.ibd
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
[01] ...done
[01] copying ./tp50/new_orders.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/new_orders.ibd
[01] ...done
[01] copying ./tp50/orders.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/orders.ibd
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
[01] ...done
[01] copying ./tp50/order_line.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/order_line.ibd
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
[01] ...done
[01] copying ./tp50/stock.ibd to /data/backup/innobackupex/2014-07-31_11-20-44/tp50/stock.ibd
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
>> log scanned up to (6118588398)
[01] ...done
>> log scanned up to (6118588398)
xtrabackup: creating suspend file '/data/backup/innobackupex/2014-07-31_11-20-44/xtrabackup_suspended_2' with pid '25767'
140731 11:22:07 innobackupex: continuing after ibbackup has suspended
140731 11:22:07 innobackupex: executing flush tables with read lock...
140731 11:22:07 innobackupex: all tables locked and flushed to disk
140731 11:22:07 innobackupex: starting to backup non-innodb tables and files
innobackupex: in subdirectories of '/data/mysql/mysql3306/data'
innobackupex: backing up files '/data/mysql/mysql3306/data/mysql/*.{frm,isl,myd,myi,mad,mai,mrg,trg,trn,arm,arz,csm,csv,opt,par}' (72 files)
>> log scanned up to (6118588398)
innobackupex: backing up file '/data/mysql/mysql3306/data/test/tt.frm'
innobackupex: backing up file '/data/mysql/mysql3306/data/test/v1.frm'
innobackupex: backing up file '/data/mysql/mysql3306/data/test/v2.frm'
innobackupex: backing up file '/data/mysql/mysql3306/data/test/v3.frm'
innobackupex: backing up files '/data/mysql/mysql3306/data/performance_schema/*.{frm,isl,myd,myi,mad,mai,mrg,trg,trn,arm,arz,csm,csv,opt,par}' (18 files)
innobackupex: backing up files '/data/mysql/mysql3306/data/tp50/*.{frm,isl,myd,myi,mad,mai,mrg,trg,trn,arm,arz,csm,csv,opt,par}' (10 files)
140731 11:22:08 innobackupex: finished backing up non-innodb tables and files
140731 11:22:08 innobackupex: executing flush engine logs...
140731 11:22:08 innobackupex: waiting for log copying to finish
xtrabackup: the latest check point (for incremental): '6118588398'
xtrabackup: stopping log copying thread.
.>> log scanned up to (6118588398)
xtrabackup: creating suspend file '/data/backup/innobackupex/2014-07-31_11-20-44/xtrabackup_log_copied' with pid '25767'
xtrabackup: transaction log of lsn (6118588398) to (6118588398) was copied.
140731 11:22:09 innobackupex: all tables unlocked
innobackupex: backup created in directory '/data/backup/innobackupex/2014-07-31_11-20-44'
innobackupex: mysql binlog position: filename 'mysql-bin.000009', position 376
140731 11:22:09 innobackupex: connection to database server closed
140731 11:22:09 innobackupex: completed ok!
ndmc74:~ #
ndmc74:/data/backup/innobackupex/2014-07-31_11-20-44 # ls -l
总计 1048608
-rw-r--r-- 1 root root 357 07-31 11:20 backup-my.cnf
-rw-r----- 1 root root 1073741824 07-31 11:20 ibdata1
drwxr-xr-x 2 root root 4096 07-31 11:22 mysql
drwxr-xr-x 2 root root 4096 07-31 11:22 performance_schema
drwx------ 2 root root 71 07-31 11:22 test
drwx------ 2 root root 4096 07-31 11:22 tp50
-rw-r--r-- 1 root root 23 07-31 11:22 xtrabackup_binlog_info
-rw-r----- 1 root root 95 07-31 11:22 xtrabackup_checkpoints
-rw-r--r-- 1 root root 572 07-31 11:22 xtrabackup_info
-rw-r----- 1 root root 2560 07-31 11:22 xtrabackup_logfile
为了保证备份集中的数据一致，需要操作：
ndmc74:~ # innobackupex --apply-log /data/backup/innobackupex/2014-07-31_11-20-44
innodb backup utility v1.5.1-xtrabackup; copyright 2003, 2009 innobase oy
and percona llc and/or its affiliates 2009-2013. all rights reserved.
this software is published under
the gnu general public license version 2, june 1991.
get the latest version of percona xtrabackup, documentation, and help resources:
http://www.percona.com/xb/p
important: please check that the apply-log run completes successfully.
at the end of a successful apply-log run innobackupex
prints completed ok!.
140731 11:28:16 innobackupex: starting ibbackup with command: xtrabackup --defaults-file=/data/backup/innobackupex/2014-07-31_11-20-44/backup-my.cnf --defaults-group=mysqld --prepare --target-dir=/data/backup/innobackupex/2014-07-31_11-20-44 --tmpdir=/tmp
xtrabackup version 2.2.3 based on mysql server 5.6.17 linux (x86_64) (revision id: )
xtrabackup: cd to /data/backup/innobackupex/2014-07-31_11-20-44
xtrabackup: this target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(6118588398)
xtrabackup: using the following innodb configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1g:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: using the following innodb configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1g:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: starting innodb instance for recovery.
xtrabackup: using 104857600 bytes for buffer pool (set by --use-memory parameter)
innodb: using atomics to ref count buffer pool pages
innodb: the innodb memory heap is disabled
innodb: mutexes and rw_locks use gcc atomic builtins
innodb: compressed tables use zlib 1.2.3
innodb: using cpu crc32 instructions
innodb: initializing buffer pool, size = 100.0m
innodb: completed initialization of buffer pool
innodb: highest supported file format is barracuda.
innodb: 128 rollback segment(s) are active.
innodb: waiting for purge to start
innodb: 5.6.17 started; log sequence number 6118588398
[notice (again)]
if you use binary log and don't use any hack of group commit,
the binary log position seems to be:
innodb: last mysql binlog file position 0 324258362, file name ./mysql-bin.000008
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
innodb: fts optimize thread exiting.
innodb: starting shutdown...
innodb: shutdown completed; log sequence number 6118588408
140731 11:28:18 innobackupex: restarting xtrabackup with command: xtrabackup --defaults-file=/data/backup/innobackupex/2014-07-31_11-20-44/backup-my.cnf --defaults-group=mysqld --prepare --target-dir=/data/backup/innobackupex/2014-07-31_11-20-44 --tmpdir=/tmp
for creating ib_logfile*
xtrabackup version 2.2.3 based on mysql server 5.6.17 linux (x86_64) (revision id: )
xtrabackup: cd to /data/backup/innobackupex/2014-07-31_11-20-44
xtrabackup: this target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following innodb configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1g:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: using the following innodb configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:1g:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: starting innodb instance for recovery.
xtrabackup: using 104857600 bytes for buffer pool (set by --use-memory parameter)
innodb: using atomics to ref count buffer pool pages
innodb: the innodb memory heap is disabled
innodb: mutexes and rw_locks use gcc atomic builtins
innodb: compressed tables use zlib 1.2.3
innodb: using cpu crc32 instructions
innodb: initializing buffer pool, size = 100.0m
innodb: completed initialization of buffer pool
innodb: setting log file ./ib_logfile101 size to 256 mb
innodb: progress in mb: 100 200
innodb: setting log file ./ib_logfile1 size to 256 mb
innodb: progress in mb: 100 200
innodb: setting log file ./ib_logfile2 size to 256 mb
innodb: progress in mb: 100 200
innodb: renaming log file ./ib_logfile101 to ./ib_logfile0
innodb: new log files created, lsn=6118588408
innodb: highest supported file format is barracuda.
innodb: 128 rollback segment(s) are active.
innodb: waiting for purge to start
innodb: 5.6.17 started; log sequence number 6118588428
[notice (again)]
if you use binary log and don't use any hack of group commit,
the binary log position seems to be:
innodb: last mysql binlog file position 0 324258362, file name ./mysql-bin.000008
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
innodb: fts optimize thread exiting.
innodb: starting shutdown...
innodb: shutdown completed; log sequence number 6118588438
140731 11:28:26 innobackupex: completed ok!
ndmc74:~ # 
master上创建同步账号并授权replicationmysql> create user 'repl'@'192.168.223.%' identified by 'replpass';
query ok, 0 rows affected (0.02 sec)
mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.223.%';
query ok, 0 rows affected (0.02 sec)
然后在slave上测试，看看能否使用repl用户登录master服务器：
ndmc75:~ # mysql -h192.168.223.132 -urepl -preplpass
mysql> show grants\g
*************************** 1. row ***************************
grants for repl@192.168.223.%: grant replication slave, replication client on *.* to 'repl'@'192.168.223.%' identified by password '*d98280f03d0f78162ebdbb9c883fc01395dea2bf'
1 row in set (0.00 sec) 
将master的全备scp到slave主机上ndmc74:~ # cd /data/backup/innobackupex/
ndmc74:/data/backup/innobackupex # scp -r ./2014-07-31_11-20-44 192.168.223.133:/data/backup
将slave上的mysql停掉，然后将master的备份文件放到slave的datadir目录下：
ndmc75:~ # mv /data/backup/2014-07-31_11-20-44 /data/mysql/mysql3306/data
ndmc75:~ # cd /data/mysql/mysql3306
ndmc75:/data/mysql/mysql3306 # chown -r mysql:mysql data
注意：slave的my.cnf文件和master的一样，只是修改一下server-id即可。
启动slave数据库ndmc75:~ # /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf &
ndmc75:~ # ps -ef |grep mysqld
mysql 1293 1 1 11:50 pts/0 00:00:01 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
root 3124 19598 0 11:51 pts/0 00:00:00 grep mysqld
同时查看一下error.log文件，看看有没有错误信息。
在slave上执行change master设置主服务器复制信息master binlog信息从备份文件中可以获得：
ndmc74:~ # cd /data/backup/innobackupex/2014-07-31_11-20-44
ndmc74:~ # cat xtrabackup_binlog_info
mysql-bin.000009 376
mysql> change master to master_host='192.168.223.132', master_user='repl', master_password='replpass',master_port=3306,master_log_file='mysql-bin.000009',master_log_pos=376;
query ok, 0 rows affected (0.05 sec)
在slave上启动复制mysql> start slave;
query ok, 0 rows affected (0.00 sec)
检查主从复制是否正常mysql> show slave status\g
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 192.168.223.132
master_user: repl
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000009
read_master_log_pos: 452
relay_log_file: mysql-relay-bin.000002
relay_log_pos: 329
relay_master_log_file: mysql-bin.000009
slave_io_running: yes
slave_sql_running: yes
replicate_do_db:
replicate_ignore_db:
replicate_do_table:
replicate_ignore_table:
replicate_wild_do_table:
replicate_wild_ignore_table:
last_errno: 0
last_error:
skip_counter: 0
exec_master_log_pos: 452
relay_log_space: 485
until_condition: none
until_log_file:
until_log_pos: 0
master_ssl_allowed: no
master_ssl_ca_file:
master_ssl_ca_path:
master_ssl_cert:
master_ssl_cipher:
master_ssl_key:
seconds_behind_master: 0
master_ssl_verify_server_cert: no
last_io_errno: 0
last_io_error:
last_sql_errno: 0
last_sql_error:
replicate_ignore_server_ids:
master_server_id: 1323306
1 row in set (0.00 sec)