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

 

 

 

 

<pre title="">[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</pre>

 

<pre title="">[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</pre>

 

 

 

 

<pre title="">[root@Legion100 ~]# mysql -uroot -plookback -e "show global variables like 'log_bin';" --------------- ------- | Variable_name | Value | --------------- ------- | log_bin | ON | --------------- ------- [root@Legion100 ~]#</pre> <pre title="">[root@Legion101 ~]# mysql -uroot -plookback -e "show global variables like 'log_bin';" --------------- ------- | Variable_name | Value | --------------- ------- | log_bin | ON | --------------- ------- [root@Legion101 ~]#</pre>

 

<pre title="">[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</pre>

 

<pre title="">[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 ~]#</pre>

 

<pre title="">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)]></pre>

 

<pre title="">#是在Slave节点上执行哦 [root@Legion101 ~]# mysql -ulegion -plegionpasswd -h172.16.6.100 -e 'show databases;' -------------------- | Database | -------------------- | information_schema | -------------------- [root@Legion101 ~]#</pre>

 

<pre title="">#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]#</pre> <pre title="">#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]#</pre> <pre title="">#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]#</pre>

 

<pre title="">[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]#</pre>

 

<pre title="">#查看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 ~]#</pre>

 

<pre title="">[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 ~]#</pre>

 

您可能感兴趣的文章：
MariaDB/MySQL主从复制之借助Percona的XtraBackup实现不锁表不停库热同步
使用 Percona XtraBackup 进行 mysql热备之安装
Ubuntu 14.04 安装部署 MariaDB 数据库步骤
mysql之percona-toolkit安装与主从复制
分析MySQL数据库物理备份死锁
Mariadb/MySQL 安装及配置在Linux系统中
学习mysql常用的三种备份方法
MySQL之MariaDB启用审计插件配置详解
使用Percona XtraBackup进行mysql热备之测试
解决mysql主从复制数据库不同步的2种方法