MariaDB/MySQL主從復制之借助Percona的XtraBackup實現不鎖表不停庫熱同步

本文章為各位同學介紹一篇關於　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、檢查下主、從庫的情況

 

上面兩種動態圖可以看出我們的從庫是新庫，而主庫是一個有數據不能鎖表不能停庫的(模擬環境)。下面我們就開始做具體配置了

 

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

 

[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 [email protected]'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 [email protected] "cat /data/backup/innobackupex/2015*/xtrabackup_binlog_info" [email protected]'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 ~]#