Linux之MariaDB/MySQL主从复制之借助Percona的XtraBackup实现不锁表不停库热同步

  • A+
所属分类:Linux  Linux中级  MySQL&MariaDB  数据库
摘要

MariaDB主从复制允许在多个服务器上实现数据的异步复制,可有效提升数据库的负载能力,也是实现读写分离方案的基础,是一种常用的HA方案。
但是在一些小公司中,开始的业务量或者某些原因没重视这些东西,等发现数据库负载压力变大、数据灾备等的重要性的时候,源数据库已经有很多内容了,这时候做数据库,需要停库或者锁表来实现从库的复制,但是这样会非常的影响线上业务运行,本文就是基于此情况来实现在主数据库不停库或锁表的情况下实现从库的复制实现热上线主从数据库。

 

一、准备工作
Linux之MariaDB/MySQL主从复制之借助Percona的XtraBackup实现不锁表不停库热同步

 

MariaDB/MySQL的安装就不说了,可以参考:

MariaDB/MySQL在 CentOS 6.6上的编译安装/二进制源码包+授权详解

Master节点配置文件 仅供参考

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock

basedir = /usr/local/mariadb
datadir = /data/mariadb
pid-file = /data/mariadb/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1
#这里的server-id的参数不能和其他节点一样,务必记住

log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30

skip-name-resolve
#skip-networking
back_log = 300

max_connections = 1000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 256
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 32M

read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 16M

thread_cache_size = 16

query_cache_type = 1
query_cache_size = 16M
query_cache_limit = 2M

ft_min_word_len = 4

log_error = /data/mariadb/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mariadb/mysql-slow.log

performance_schema = 0

#lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 128M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 16M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

interactive_timeout = 28800
wait_timeout = 28800

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 16M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

Slave节点配置文件 仅供参考

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock

basedir = /usr/local/mariadb
datadir = /data/mariadb
pid-file = /data/mariadb/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 2

log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30

skip-name-resolve
#skip-networking
back_log = 300

max_connections = 1000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 256
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 32M

read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 16M

thread_cache_size = 16

query_cache_type = 1
query_cache_size = 16M
query_cache_limit = 2M

ft_min_word_len = 4

log_error = /data/mariadb/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mariadb/mysql-slow.log

performance_schema = 0

#lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 128M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 16M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

interactive_timeout = 28800
wait_timeout = 28800

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 16M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

二、实施过程

1、检查下主、从库的情况
Linux之MariaDB/MySQL主从复制之借助Percona的XtraBackup实现不锁表不停库热同步

Linux之MariaDB/MySQL主从复制之借助Percona的XtraBackup实现不锁表不停库热同步
上面两种动态图可以看出我们的从库是新库,而主库是一个有数据不能锁表不能停库的(模拟环境)。下面我们就开始做具体配置了

2、确认binlog是开启的哦

[root@Legion100 ~]# mysql -uroot -plookback -e "show global variables like 'log_bin';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
[root@Legion100 ~]# 
[root@Legion101 ~]# mysql -uroot -plookback -e "show global variables like 'log_bin';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
[root@Legion101 ~]# 

3、安装Percona的XtraBackup

[root@Legion100 ~]# wget http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
[root@Legion100 ~]# yum install percona-release-0.1-3.noarch.rpm
[root@Legion100 ~]# yum list all | grep '^percona'
percona-release.noarch                     0.1-3                     @/percona-release-0.1-3.noarch
percona-agent.x86_64                       1.0.13-2.el6              percona-release-x86_64
percona-cacti-templates.noarch             1.1.5-1                   percona-release-noarch
percona-nagios-plugins.noarch              1.1.5-1                   percona-release-noarch
percona-playback.x86_64                    0.7-2.el6                 percona-release-x86_64
percona-playback-debuginfo.x86_64          0.7-2.el6                 percona-release-x86_64
percona-playback-devel.x86_64              0.7-2.el6                 percona-release-x86_64
percona-toolkit.noarch                     2.2.14-1                  percona-release-noarch
percona-xtrabackup.x86_64                  2.2.11-1.el6              percona-release-x86_64
percona-xtrabackup-20.x86_64               2.0.8-587.rhel6           percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64     2.0.8-587.rhel6           percona-release-x86_64
percona-xtrabackup-20-test.x86_64          2.0.8-587.rhel6           percona-release-x86_64
percona-xtrabackup-21.x86_64               2.1.9-746.rhel6           percona-release-x86_64
percona-xtrabackup-21-debuginfo.x86_64     2.1.9-746.rhel6           percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64        2.2.11-1.el6              percona-release-x86_64
percona-xtrabackup-test.x86_64             2.2.11-1.el6              percona-release-x86_64
percona-xtrabackup-test-21.x86_64          2.1.9-746.rhel6           percona-release-x86_64
percona-zabbix-templates.noarch            1.1.5-1                   percona-release-noarch
[root@Legion100 ~]# yum install percona-xtrabackup-21 percona-xtrabackup-21-debuginfo percona-xtrabackup-test-21 -y 

Linux之MariaDB/MySQL主从复制之借助Percona的XtraBackup实现不锁表不停库热同步

[root@Legion100 ~]# mkdir -p /data/backup/innobackupex
#Master上使用xtrabackup做全库备份
[root@Legion100 ~]# innobackupex --user=root --password=lookback --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

150710 22:57:49  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
150710 22:57:49  innobackupex: Connected to MySQL server
150710 22:57:49  innobackupex: Executing a version check against the server...
150710 22:57:51  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 10.0.20-MariaDB-log

innobackupex: Created backup directory /data/backup/innobackupex/2015-07-10_22-57-51

150710 22:57:51  innobackupex: Starting ibbackup with command: xtrabackup_56  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/data/backup/innobackupex/2015-07-10_22-57-51 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=3604) to suspend
innobackupex: Suspend file '/data/backup/innobackupex/2015-07-10_22-57-51/xtrabackup_suspended_2'

xtrabackup_56 version 2.1.9 for MySQL server 5.6.17 Linux (x86_64) (revision id: 746)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mariadb
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
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 = 3
xtrabackup:   innodb_log_file_size = 33554432
>> log scanned up to (13810069)
[01] Copying ./ibdata1 to /data/backup/innobackupex/2015-07-10_22-57-51/ibdata1
>> log scanned up to (13810069)
[01]        ...done
>> log scanned up to (13810069)
[01] Copying ./lookback/wp_term_relationships.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/lookback/wp_term_relationships.ibd
[01]        ...done
[01] Copying ./lookback/wp_commentmeta.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/lookback/wp_commentmeta.ibd
[01]        ...done
[01] Copying ./lookback/wp_posts.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/lookback/wp_posts.ibd
[01]        ...done
[01] Copying ./lookback/wp_postmeta.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/lookback/wp_postmeta.ibd
[01]        ...done
>> log scanned up to (13810069)
[01] Copying ./lookback/wp_users.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/lookback/wp_users.ibd
[01]        ...done
[01] Copying ./lookback/wp_term_taxonomy.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/lookback/wp_term_taxonomy.ibd
[01]        ...done
[01] Copying ./lookback/wp_links.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/lookback/wp_links.ibd
[01]        ...done
[01] Copying ./lookback/wp_terms.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/lookback/wp_terms.ibd
[01]        ...done
[01] Copying ./lookback/wp_options.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/lookback/wp_options.ibd
[01]        ...done
[01] Copying ./lookback/wp_comments.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/lookback/wp_comments.ibd
[01]        ...done
[01] Copying ./lookback/wp_usermeta.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/lookback/wp_usermeta.ibd
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/mysql/innodb_table_stats.ibd
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/mysql/innodb_index_stats.ibd
[01]        ...done
[01] Copying ./mysql/gtid_slave_pos.ibd to /data/backup/innobackupex/2015-07-10_22-57-51/mysql/gtid_slave_pos.ibd
[01]        ...done
>> log scanned up to (13810069)
xtrabackup: Creating suspend file '/data/backup/innobackupex/2015-07-10_22-57-51/xtrabackup_suspended_2' with pid '3604'

150710 22:57:56  innobackupex: Continuing after ibbackup has suspended
150710 22:57:56  innobackupex: Starting to lock all tables...
150710 22:57:56  innobackupex: All tables locked and flushed to disk

150710 22:57:56  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/data/mariadb'
innobackupex: Backing up files '/data/mariadb/lookback/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (15 files)
>> log scanned up to (13810069)
innobackupex: Backing up files '/data/mariadb/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
innobackupex: Backing up files '/data/mariadb/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (84 files)
150710 22:57:57  innobackupex: Finished backing up non-InnoDB tables and files

150710 22:57:57  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '13810069'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (13810069)

xtrabackup: Creating suspend file '/data/backup/innobackupex/2015-07-10_22-57-51/xtrabackup_log_copied' with pid '3604'
xtrabackup: Transaction log of lsn (13810069) to (13810069) was copied.
150710 22:57:58  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/data/backup/innobackupex/2015-07-10_22-57-51'
innobackupex: MySQL binlog position: filename 'mysql-bin.000003', position 3025154
150710 22:57:58  innobackupex: Connection to database server closed
150710 22:57:58  innobackupex: completed OK!
[root@Legion100 ~]# ls -l /data/backup/innobackupex/*
总用量 77860
-rw-r--r-- 1 root root      357 7月  10 22:57 backup-my.cnf
-rw-r----- 1 root root 79691776 7月  10 22:57 ibdata1
drwx------ 2 root root     4096 7月  10 22:57 lookback
drwx------ 2 root root     4096 7月  10 22:57 mysql
drwxr-xr-x 2 root root     4096 7月  10 22:57 performance_schema
-rw-r--r-- 1 root root       13 7月  10 22:57 xtrabackup_binary
-rw-r--r-- 1 root root       27 7月  10 22:57 xtrabackup_binlog_info
-rw-r----- 1 root root       91 7月  10 22:57 xtrabackup_checkpoints
-rw-r----- 1 root root     2560 7月  10 22:57 xtrabackup_logfile
#为了保证备份集中的数据一致,需要操作
[root@Legion100 ~]# innobackupex --apply-log /data/backup/innobackupex/2015-07-10_22-57-51

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!".

150710 23:01:40  innobackupex: Starting ibbackup with command: xtrabackup_56  --defaults-file="/data/backup/innobackupex/2015-07-10_22-57-51/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/data/backup/innobackupex/2015-07-10_22-57-51 --tmpdir=/tmp

xtrabackup_56 version 2.1.9 for MySQL server 5.6.17 Linux (x86_64) (revision id: 746)
xtrabackup: cd to /data/backup/innobackupex/2015-07-10_22-57-51
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(13810069)
xtrabackup: using the following InnoDB configuration for recovery:
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 = 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:10M: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: The log sequence numbers 1616727 and 1616727 in ibdata files do not match the log sequence number 13810069 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 3018713, file name ./mysql-bin.000003
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.17 started; log sequence number 13810069

[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 3018713, file name ./mysql-bin.000003

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13811234

150710 23:01:42  innobackupex: Restarting xtrabackup with command: xtrabackup_56  --defaults-file="/data/backup/innobackupex/2015-07-10_22-57-51/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/data/backup/innobackupex/2015-07-10_22-57-51 --tmpdir=/tmp
for creating ib_logfile*

xtrabackup_56 version 2.1.9 for MySQL server 5.6.17 Linux (x86_64) (revision id: 746)
xtrabackup: cd to /data/backup/innobackupex/2015-07-10_22-57-51
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:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 33554432
xtrabackup: using the following InnoDB configuration for recovery:
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 = 3
xtrabackup:   innodb_log_file_size = 33554432
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 32 MB
InnoDB: Setting log file ./ib_logfile1 size to 32 MB
InnoDB: Setting log file ./ib_logfile2 size to 32 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=13811234
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 13811724

[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 3018713, file name ./mysql-bin.000003

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13812533
150710 23:01:48  innobackupex: completed OK!
[root@Legion100 ~]# 

4、Master上创建同步账号并授权REPLICATION

MariaDB [(none)]> CREATE USER 'legion'@'172.16.%.%' IDENTIFIED BY 'legionpasswd';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'legion'@'172.16.%.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 

5、测试Master上的权限正确性

#是在Slave节点上执行哦
[root@Legion101 ~]# mysql -ulegion -plegionpasswd -h172.16.6.100 -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
[root@Legion101 ~]# 

6、复制Master节点上备份文件到Slave上

#Slave节点上执行
[root@Legion101 data]# ls
lost+found  mariadb  mariadb-2015-07-10-backup
[root@Legion101 data]# mkdir -p /data/backup
[root@Legion101 data]# service mysqld stop
Shutting down MySQL... SUCCESS!
[root@Legion101 data]# 
#Master节点上执行
[root@Legion100 ~]# cd /data/backup/innobackupex/
[root@Legion100 innobackupex]# ls -l
总用量 4
drwxr-xr-x 5 root root 4096 7月  10 23:01 2015-07-10_22-57-51
[root@Legion100 innobackupex]# scp -r ./2015-07-10_22-57-51 172.16.6.101:/data/backup
root@172.16.6.101's password:
xtrabackup_binlog_info                                                                                                             100%   27     0.0KB/s   00:00
backup-my.cnf                                                                                                                      100%  357     0.4KB/s   00:00
wp_term_relationships.ibd                                                                                                          100%  112KB 112.0KB/s   00:00
wp_commentmeta.ibd                                                                                                                 100%  128KB 128.0KB/s   00:00
wp_hugeit_lightbox.MYD                                                                                                             100% 3080     3.0KB/s   00:00
wp_posts.ibd                                                                                                                       100%   10MB  10.0MB/s   00:00
wp_postmeta.frm                                                                                                                    100% 3030     3.0KB/s   00:00
wp_hugeit_lightbox.frm                                                                                                             100% 1019     1.0KB/s   00:00
wp_posts.frm                                                                                                                       100% 6282     6.1KB/s   00:00
db.opt                                                                                                                             100%   65     0.1KB/s   00:00
wp_hugeit_lightbox.MYI                                                                                                             100% 2048     2.0KB/s   00:00
wp_postmeta.ibd                                                                                                                    100% 9216KB   9.0MB/s   00:01
wp_links.frm                                                                                                                       100% 8105     7.9KB/s   00:00
wp_users.ibd                                                                                                                       100%  128KB 128.0KB/s   00:00
wp_term_taxonomy.ibd                                                                                                               100%  128KB 128.0KB/s   00:00
wp_links.ibd                                                                                                                       100%  112KB 112.0KB/s   00:00
wp_commentmeta.frm                                                                                                                 100% 3033     3.0KB/s   00:00
wp_terms.ibd                                                                                                                       100%  128KB 128.0KB/s   00:00
wp_options.ibd                                                                                                                     100% 2048KB   2.0MB/s   00:00
wp_terms.frm                                                                                                                       100% 3592     3.5KB/s   00:00
wp_comments.ibd                                                                                                                    100%  176KB 176.0KB/s   00:00
wp_term_relationships.frm                                                                                                          100% 1496     1.5KB/s   00:00
wp_users.frm                                                                                                                       100% 4931     4.8KB/s   00:00
wp_options.frm                                                                                                                     100% 1857     1.8KB/s   00:00
wp_usermeta.frm                                                                                                                    100% 3031     3.0KB/s   00:00
wp_usermeta.ibd                                                                                                                    100%  224KB 224.0KB/s   00:00
wp_term_taxonomy.frm                                                                                                               100% 2209     2.2KB/s   00:00
wp_comments.frm                                                                                                                    100% 6723     6.6KB/s   00:00
xtrabackup_binary                                                                                                                  100%   13     0.0KB/s   00:00
ib_logfile2                                                                                                                        100%   32MB  32.0MB/s   00:00
xtrabackup_checkpoints                                                                                                             100%   91     0.1KB/s   00:00
xtrabackup_logfile                                                                                                                 100% 2048KB   2.0MB/s   00:00
ib_logfile0                                                                                                                        100%   32MB  32.0MB/s   00:00
xtrabackup_binlog_pos_innodb                                                                                                       100%   27     0.0KB/s   00:00
events_statements_summary_by_digest.frm                                                                                            100% 1888     1.8KB/s   00:00
socket_instances.frm                                                                                                               100% 1225     1.2KB/s   00:00
events_stages_history_long.frm                                                                                                     100% 1369     1.3KB/s   00:00
setup_instruments.frm                                                                                                              100%  887     0.9KB/s   00:00
file_instances.frm                                                                                                                 100% 2431     2.4KB/s   00:00
events_waits_summary_by_user_by_event_name.frm                                                                                     100% 1101     1.1KB/s   00:00
events_stages_summary_by_thread_by_event_name.frm                                                                                  100% 1065     1.0KB/s   00:00
events_statements_summary_by_thread_by_event_name.frm                                                                              100% 1889     1.8KB/s   00:00
events_statements_summary_by_host_by_event_name.frm                                                                                100% 2057     2.0KB/s   00:00
events_stages_summary_by_account_by_event_name.frm                                                                                 100% 1303     1.3KB/s   00:00
setup_objects.frm                                                                                                                  100%  964     0.9KB/s   00:00
file_summary_by_instance.frm                                                                                                       100% 3336     3.3KB/s   00:00
objects_summary_global_by_type.frm                                                                                                 100% 1285     1.3KB/s   00:00
socket_summary_by_event_name.frm                                                                                                   100% 1724     1.7KB/s   00:00
table_lock_waits_summary_by_table.frm                                                                                              100% 4493     4.4KB/s   00:00
events_stages_history.frm                                                                                                          100% 1369     1.3KB/s   00:00
events_statements_history_long.frm                                                                                                 100% 3759     3.7KB/s   00:00
events_stages_current.frm                                                                                                          100% 1369     1.3KB/s   00:00
db.opt                                                                                                                             100%   61     0.1KB/s   00:00
events_stages_summary_by_user_by_event_name.frm                                                                                    100% 1101     1.1KB/s   00:00
events_waits_current.frm                                                                                                           100% 3870     3.8KB/s   00:00
events_statements_current.frm                                                                                                      100% 3759     3.7KB/s   00:00
table_io_waits_summary_by_index_usage.frm                                                                                          100% 2722     2.7KB/s   00:00
setup_consumers.frm                                                                                                                100%  670     0.7KB/s   00:00
events_waits_summary_by_host_by_event_name.frm                                                                                     100% 1233     1.2KB/s   00:00
hosts.frm                                                                                                                          100%  711     0.7KB/s   00:00
events_waits_summary_by_instance.frm                                                                                               100% 1077     1.1KB/s   00:00
threads.frm                                                                                                                        100% 2180     2.1KB/s   00:00
table_io_waits_summary_by_table.frm                                                                                                100% 2501     2.4KB/s   00:00
events_statements_summary_by_user_by_event_name.frm                                                                                100% 1925     1.9KB/s   00:00
cond_instances.frm                                                                                                                 100%  875     0.9KB/s   00:00
events_waits_summary_global_by_event_name.frm                                                                                      100% 1030     1.0KB/s   00:00
events_waits_history_long.frm                                                                                                      100% 3870     3.8KB/s   00:00
events_waits_history.frm                                                                                                           100% 3870     3.8KB/s   00:00
events_stages_summary_global_by_event_name.frm                                                                                     100% 1030     1.0KB/s   00:00
host_cache.frm                                                                                                                     100% 2734     2.7KB/s   00:00
events_stages_summary_by_host_by_event_name.frm                                                                                    100% 1233     1.2KB/s   00:00
events_statements_history.frm                                                                                                      100% 3759     3.7KB/s   00:00
session_account_connect_attrs.frm                                                                                                  100% 3721     3.6KB/s   00:00
events_waits_summary_by_account_by_event_name.frm                                                                                  100% 1303     1.3KB/s   00:00
setup_timers.frm                                                                                                                   100%  712     0.7KB/s   00:00
file_summary_by_event_name.frm                                                                                                     100% 1724     1.7KB/s   00:00
session_connect_attrs.frm                                                                                                          100% 3721     3.6KB/s   00:00
mutex_instances.frm                                                                                                                100%  921     0.9KB/s   00:00
performance_timers.frm                                                                                                             100%  621     0.6KB/s   00:00
events_waits_summary_by_thread_by_event_name.frm                                                                                   100% 1065     1.0KB/s   00:00
users.frm                                                                                                                          100%  579     0.6KB/s   00:00
rwlock_instances.frm                                                                                                               100%  969     1.0KB/s   00:00
setup_actors.frm                                                                                                                   100%  763     0.8KB/s   00:00
socket_summary_by_instance.frm                                                                                                     100% 1771     1.7KB/s   00:00
accounts.frm                                                                                                                       100%  781     0.8KB/s   00:00
events_statements_summary_global_by_event_name.frm                                                                                 100% 1854     1.8KB/s   00:00
events_statements_summary_by_account_by_event_name.frm                                                                             100% 2127     2.1KB/s   00:00
user.frm                                                                                                                           100% 2879     2.8KB/s   00:00
func.MYD                                                                                                                           100%    0     0.0KB/s   00:00
help_keyword.MYD                                                                                                                   100%   89KB  89.3KB/s   00:00
column_stats.MYD                                                                                                                   100%    0     0.0KB/s   00:00
func.MYI                                                                                                                           100% 1024     1.0KB/s   00:00
general_log.CSM                                                                                                                    100%   35     0.0KB/s   00:00
proc.frm                                                                                                                           100% 3418     3.3KB/s   00:00
servers.MYD                                                                                                                        100%    0     0.0KB/s   00:00
tables_priv.MYI                                                                                                                    100% 4096     4.0KB/s   00:00
help_relation.frm                                                                                                                  100%  970     1.0KB/s   00:00
innodb_index_stats.frm                                                                                                             100% 4998     4.9KB/s   00:00
time_zone_transition_type.frm                                                                                                      100% 1079     1.1KB/s   00:00
time_zone.frm                                                                                                                      100%  973     1.0KB/s   00:00
innodb_table_stats.ibd                                                                                                             100%   96KB  96.0KB/s   00:00
slow_log.CSV                                                                                                                       100%    0     0.0KB/s   00:00
table_stats.MYD                                                                                                                    100%    0     0.0KB/s   00:00
time_zone_name.MYI                                                                                                                 100% 1024     1.0KB/s   00:00
procs_priv.frm                                                                                                                     100% 2874     2.8KB/s   00:00
time_zone_transition_type.MYD                                                                                                      100%    0     0.0KB/s   00:00
proxies_priv.MYD                                                                                                                   100% 2538     2.5KB/s   00:00
help_keyword.frm                                                                                                                   100% 1638     1.6KB/s   00:00
proxies_priv.MYI                                                                                                                   100%   10KB  10.0KB/s   00:00
event.frm                                                                                                                          100% 3695     3.6KB/s   00:00
general_log.CSV                                                                                                                    100%    0     0.0KB/s   00:00
help_topic.MYD                                                                                                                     100%  440KB 439.8KB/s   00:00
db.MYI                                                                                                                             100% 9216     9.0KB/s   00:00
tables_priv.frm                                                                                                                    100% 2957     2.9KB/s   00:00
table_stats.frm                                                                                                                    100% 1374     1.3KB/s   00:00
roles_mapping.frm                                                                                                                  100% 1661     1.6KB/s   00:00
db.frm                                                                                                                             100% 2677     2.6KB/s   00:00
time_zone_transition.frm                                                                                                           100% 1013     1.0KB/s   00:00
proc.MYI                                                                                                                           100% 2048     2.0KB/s   00:00
time_zone.MYI                                                                                                                      100% 1024     1.0KB/s   00:00
servers.MYI                                                                                                                        100% 1024     1.0KB/s   00:00
proc.MYD                                                                                                                           100%    0     0.0KB/s   00:00
innodb_index_stats.ibd                                                                                                             100%  128KB 128.0KB/s   00:00
help_topic.frm                                                                                                                     100% 1776     1.7KB/s   00:00
slow_log.frm                                                                                                                       100% 2339     2.3KB/s   00:00
servers.frm                                                                                                                        100% 2703     2.6KB/s   00:00
tables_priv.MYD                                                                                                                    100%    0     0.0KB/s   00:00
db.MYD                                                                                                                             100% 1264     1.2KB/s   00:00
time_zone.MYD                                                                                                                      100%    0     0.0KB/s   00:00
proxies_priv.frm                                                                                                                   100% 2839     2.8KB/s   00:00
time_zone_transition.MYD                                                                                                           100%    0     0.0KB/s   00:00
plugin.frm                                                                                                                         100% 1518     1.5KB/s   00:00
procs_priv.MYD                                                                                                                     100%    0     0.0KB/s   00:00
gtid_slave_pos.ibd                                                                                                                 100%   96KB  96.0KB/s   00:00
time_zone_transition_type.MYI                                                                                                      100% 1024     1.0KB/s   00:00
table_stats.MYI                                                                                                                    100% 2048     2.0KB/s   00:00
event.MYD                                                                                                                          100%    0     0.0KB/s   00:00
index_stats.frm                                                                                                                    100% 1629     1.6KB/s   00:00
host.frm                                                                                                                           100% 1897     1.9KB/s   00:00
roles_mapping.MYI                                                                                                                  100% 4096     4.0KB/s   00:00
slow_log.CSM                                                                                                                       100%   35     0.0KB/s   00:00
time_zone_leap_second.frm                                                                                                          100%  971     1.0KB/s   00:00
host.MYD                                                                                                                           100%    0     0.0KB/s   00:00
plugin.MYI                                                                                                                         100% 1024     1.0KB/s   00:00
time_zone_leap_second.MYD                                                                                                          100%    0     0.0KB/s   00:00
columns_priv.frm                                                                                                                   100% 2110     2.1KB/s   00:00
help_category.frm                                                                                                                  100% 1706     1.7KB/s   00:00
gtid_slave_pos.frm                                                                                                                 100% 1024     1.0KB/s   00:00
plugin.MYD                                                                                                                         100%    0     0.0KB/s   00:00
help_relation.MYD                                                                                                                  100% 9252     9.0KB/s   00:00
index_stats.MYI                                                                                                                    100% 4096     4.0KB/s   00:00
help_keyword.MYI                                                                                                                   100%   16KB  16.0KB/s   00:00
user.MYI                                                                                                                           100% 4096     4.0KB/s   00:00
time_zone_name.frm                                                                                                                 100% 1146     1.1KB/s   00:00
event.MYI                                                                                                                          100% 2048     2.0KB/s   00:00
func.frm                                                                                                                           100% 1582     1.5KB/s   00:00
user.MYD                                                                                                                           100%  440     0.4KB/s   00:00
procs_priv.MYI                                                                                                                     100% 4096     4.0KB/s   00:00
help_category.MYD                                                                                                                  100% 1092     1.1KB/s   00:00
columns_priv.MYD                                                                                                                   100%    0     0.0KB/s   00:00
column_stats.frm                                                                                                                   100% 3624     3.5KB/s   00:00
index_stats.MYD                                                                                                                    100%    0     0.0KB/s   00:00
time_zone_name.MYD                                                                                                                 100%    0     0.0KB/s   00:00
time_zone_leap_second.MYI                                                                                                          100% 1024     1.0KB/s   00:00
help_relation.MYI                                                                                                                  100%   19KB  19.0KB/s   00:00
help_category.MYI                                                                                                                  100% 3072     3.0KB/s   00:00
column_stats.MYI                                                                                                                   100% 4096     4.0KB/s   00:00
help_topic.MYI                                                                                                                     100%   20KB  20.0KB/s   00:00
columns_priv.MYI                                                                                                                   100% 4096     4.0KB/s   00:00
innodb_table_stats.frm                                                                                                             100% 1503     1.5KB/s   00:00
time_zone_transition.MYI                                                                                                           100% 1024     1.0KB/s   00:00
general_log.frm                                                                                                                    100%  804     0.8KB/s   00:00
host.MYI                                                                                                                           100% 2048     2.0KB/s   00:00
roles_mapping.MYD                                                                                                                  100%    0     0.0KB/s   00:00
ib_logfile1                                                                                                                        100%   32MB  32.0MB/s   00:00
ibdata1                                                                                                                            100%   76MB  38.0MB/s   00:02
[root@Legion100 innobackupex]# 
#Slave节点上执行
[root@Legion101 data]# pwd
/data
[root@Legion101 data]# ls -l
总用量 24
drwxr-xr-x  3 root  root   4096 7月  10 23:42 backup
drwx------. 2 root  root  16384 6月   4 12:10 lost+found
drwxr-xr-x  4 mysql mysql  4096 7月  10 23:43 mariadb
[root@Legion101 data]# cp -a mariadb mariadb-`date +%F`-backup
[root@Legion101 data]# ls
backup  lost+found  mariadb  mariadb-2015-07-10-backup
[root@Legion101 data]# ls -l backup/
总用量 4
drwxr-xr-x 5 root root 4096 7月  10 23:42 2015-07-10_22-57-51
[root@Legion101 data]# mv /data/backup/2015-07-10_22-57-51 /data/mariadb
[root@Legion101 data]# chown -R mysql.mysql /data/mariadb/*
[root@Legion101 data]# 

7、启动Slave节点上MariaDB

[root@Legion101 data]# service mysqld start
Starting MySQL. SUCCESS!
[root@Legion101 data]# ps -ef |grep mysqld
root       4865      1  0 23:50 pts/1    00:00:00 /bin/sh /usr/local/mariadb/bin/mysqld_safe --datadir=/data/mariadb --pid-file=/data/mariadb/mysql
mysql      5117   4865  0 23:50 pts/1    00:00:00 /usr/local/mariadb/bin/mysqld --basedir=/usr/local/mariadb --datadir=/data/mariadb --plugin-dir=/lib/plugin --user=mysql --log-error=/data/mariadb/mysql-error.log --open-files-limit=65535 --pid-file=/data/mariadb/mysql.pid --socket=/tmp/mysql.s
root       5161   2386  0 23:50 pts/1    00:00:00 grep --color=auto mysqld
[root@Legion101 data]# date
2015年 07月 10日 星期五 23:51:47 CST
[root@Legion101 data]# tail -30 /data/mariadb/mysql-error.log #结合上面的时间看看mysql-err的日志
Version: '10.0.20-MariaDB-log'  socket: '/tmp/mysql.sock'  port: 3306  MariaDB Server
150710 23:43:32 [Note] /usr/local/mariadb/bin/mysqld: Normal shutdown

150710 23:43:32 [Note] Event Scheduler: Purging the queue. 0 events
150710 23:43:32 [Note] InnoDB: FTS optimize thread exiting.
150710 23:43:32 [Note] InnoDB: Starting shutdown...
150710 23:43:34 [Note] InnoDB: Shutdown completed; log sequence number 1616757
150710 23:43:34 [Note] /usr/local/mariadb/bin/mysqld: Shutdown complete

150710 23:43:34 mysqld_safe mysqld from pid file /data/mariadb/mysql.pid ended
150710 23:50:36 mysqld_safe Starting mysqld daemon with databases from /data/mariadb
150710 23:50:36 [Note] /usr/local/mariadb/bin/mysqld (mysqld 10.0.20-MariaDB-log) starting as process 5117 ...
150710 23:50:36 [Note] InnoDB: Using mutexes to ref count buffer pool pages
150710 23:50:36 [Note] InnoDB: The InnoDB memory heap is disabled
150710 23:50:36 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
150710 23:50:36 [Note] InnoDB: Memory barrier is not used
150710 23:50:36 [Note] InnoDB: Compressed tables use zlib 1.2.3
150710 23:50:36 [Note] InnoDB: Using Linux native AIO
150710 23:50:36 [Note] InnoDB: Using CPU crc32 instructions
150710 23:50:36 [Note] InnoDB: Initializing buffer pool, size = 128.0M
150710 23:50:36 [Note] InnoDB: Completed initialization of buffer pool
150710 23:50:36 [Note] InnoDB: Highest supported file format is Barracuda.
150710 23:50:36 [Note] InnoDB: 128 rollback segment(s) are active.
150710 23:50:36 [Note] InnoDB: Waiting for purge to start
150710 23:50:36 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.24-72.2 started; log sequence number 1616757
150710 23:50:36 [Note] Plugin 'FEEDBACK' is disabled.
150710 23:50:36 [Note] Server socket created on IP: '0.0.0.0'.
150710 23:50:36 [Note] Event Scheduler: Loaded 0 events
150710 23:50:36 [Note] /usr/local/mariadb/bin/mysqld: ready for connections.
Version: '10.0.20-MariaDB-log'  socket: '/tmp/mysql.sock'  port: 3306  MariaDB Server
[root@Legion101 data]# 

8、在slave上执行change master设置主服务器复制信息

#查看Master的binlog信息
[root@Legion101 ~]# ssh root@172.16.6.100 "cat /data/backup/innobackupex/2015*/xtrabackup_binlog_info"
root@172.16.6.100's password:
mysql-bin.000003        3025154
[root@Legion101 ~]# mysql -uroot -plookback
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.20-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> change master to master_host='172.16.6.100', master_user='legion',master_password='legionpasswd',master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=3025154;
Query OK, 0 rows affected (0.03 sec)

MariaDB [mysql]> start slave;  #在slave上启动复制
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> \q
Bye
[root@Legion101 ~]# 

9、检查主从复制是否正常

[root@Legion101 ~]# mysql -uroot -plookback
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.0.20-MariaDB-log MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.6.100
                  Master_User: legion
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 3025501
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 882
        Relay_Master_Log_File: mysql-bin.000003
             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: 3025501
              Relay_Log_Space: 1179
              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: 1
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
1 row in set (0.00 sec)

MariaDB [mysql]> \q
Bye
[root@Legion101 ~]# 

Linux之MariaDB/MySQL主从复制之借助Percona的XtraBackup实现不锁表不停库热同步

lookback

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: