1200字范文 > 使用xtrabackup实现MySQL主从复制【MySQL】

使用xtrabackup实现MySQL主从复制【MySQL】

时间：2019-11-06 04:47:44

数据库|mysql教程

主从

数据库-mysql教程

环境描述

android pdf 阅读器源码,vscode 严谨,ubuntu 发送邮件,tomcat7.0版本安装包,开源监控 sqlite,腾讯云服务器配置ftp,dz首页n格插件,前端统计页面框架,崔庆才python3爬虫视频教程,php后台管理框架,seo客服,网站评价代码,jq网页在线聊天代码,跳转模板,天猫页面延迟加载如何使用锚点,asp学生信息管理系统,用vb编写文件加密程序lzw

主从库相关配置确认

（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/f /data/backup/innobackupex

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright , Innobase Oy

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:

/xb/p

140731 11:20:44 innobackupex: Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_file=/etc/f;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/-07-31_11-20-44

140731 11:20:44 innobackupex: Starting ibbackup with command: xtrabackup –defaults-file=”/etc/f” –defaults-group=”mysqld” –backup –suspend-at-end –target-dir=/data/backup/innobackupex/-07-31_11-20-44 –tmpdir=/tmp –extra-lsndir=’/tmp’

innobackupex: Waiting for ibbackup (pid=25767) to suspend

innobackupex: Suspend file ‘/data/backup/innobackupex/-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/-07-31_11-20-44/ibdata1

>> log scanned up to (6118588398)

[01] …done

[01] Copying ./test/tt.ibd to /data/backup/innobackupex/-07-31_11-20-44/test/tt.ibd

[01] …done

[01] Copying ./tp50/warehouse.ibd to /data/backup/innobackupex/-07-31_11-20-44/tp50/warehouse.ibd

[01] …done

[01] Copying ./tp50/item.ibd to /data/backup/innobackupex/-07-31_11-20-44/tp50/item.ibd

[01] …done

>> log scanned up to (6118588398)

[01] Copying ./tp50/district.ibd to /data/backup/innobackupex/-07-31_11-20-44/tp50/district.ibd

[01] …done

[01] Copying ./tp50/customer.ibd to /data/backup/innobackupex/-07-31_11-20-44/tp50/customer.ibd

>> log scanned up to (6118588398)

[01] …done

[01] Copying ./tp50/history.ibd to /data/backup/innobackupex/-07-31_11-20-44/tp50/history.ibd

>> log scanned up to (6118588398)

[01] …done

[01] Copying ./tp50/new_orders.ibd to /data/backup/innobackupex/-07-31_11-20-44/tp50/new_orders.ibd

[01] …done

[01] Copying ./tp50/orders.ibd to /data/backup/innobackupex/-07-31_11-20-44/tp50/orders.ibd

>> log scanned up to (6118588398)

[01] …done

[01] Copying ./tp50/order_line.ibd to /data/backup/innobackupex/-07-31_11-20-44/tp50/order_line.ibd

>> log scanned up to (6118588398)

[01] …done

[01] Copying ./tp50/stock.ibd to /data/backup/innobackupex/-07-31_11-20-44/tp50/stock.ibd

>> log scanned up to (6118588398)

[01] …done

>> log scanned up to (6118588398)

xtrabackup: Creating suspend file ‘/data/backup/innobackupex/-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/-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/-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/-07-31_11-20-44 # ls -l

总计 1048608

-rw-r–r– 1 root root 357 07-31 11:20 backup-f

-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/-07-31_11-20-44

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright , Innobase Oy

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:

/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/-07-31_11-20-44/backup-f” –defaults-group=”mysqld” –prepare –target-dir=/data/backup/innobackupex/-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/-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/-07-31_11-20-44/backup-f” –defaults-group=”mysqld” –prepare –target-dir=/data/backup/innobackupex/-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/-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上创建同步账号并授权REPLICATION

mysql> 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 ./-07-31_11-20-44 192.168.223.133:/data/backup

将Slave上的MySQL停掉，然后将Master的备份文件放到Slave的datadir目录下：

NDMC75:~ # mv /data/backup/-07-31_11-20-44 /data/mysql/mysql3306/data

NDMC75:~ # cd /data/mysql/mysql3306

NDMC75:/data/mysql/mysql3306 # chown -R mysql:mysql data

注意：Slave的f文件和Master的一样，只是修改一下server-id即可。

启动Slave数据库

NDMC75:~ # /usr/local/mysql/bin/mysqld –defaults-file=/etc/f &

NDMC75:~ # ps -ef |grep mysqld

mysql 1293 1 1 11:50 pts/0 00:00:01 /usr/local/mysql/bin/mysqld –defaults-file=/etc/f

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/-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