Home >Database >Mysql Tutorial >使用xtrabackup实现MySQL主从复制_MySQL
环境描述
主从环境 |
|
|
项目 |
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)