一、准备工作
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 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 ~]#
您可以选择一种方式赞助本站
支付宝扫一扫赞助
微信钱包扫描赞助
赏