使用meb工具在线热备，将单实例切换为主从

主机配置说明：
192.168.30.120  master
192.168.30.121  slave


meb使用说明：
mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/data/backup/mysql-full-$(date +%F) backup-and-apply-log
mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/data/backup/mysql-full-$(date +%F) backup
mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/data/backup/mysql-full-$(date +%F) apply-log


====================================================================================================================
使用meb工具在线热备，将单实例切换为主从

1. 先在要作为主的服务器上查看
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 217
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysql> exit


2. 在要作为主的服务器上执行meb在线热备
[root@node1 data]# mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/data/backup/mysql-full-$(date +%F) --slave-info backup
MySQL Enterprise Backup version 3.9.0 [2013/08/23]
Copyright (c) 2003, 2013, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 mysqlbackup --defaults-file=/etc/my.cnf
        --backup-dir=/data/backup/mysql-full-2014-03-07 backup

 mysqlbackup: INFO: MySQL server version is '5.6.14-log'.
 mysqlbackup: INFO: Got some server configuration information from running server.

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup' run mysqlbackup
           prints "mysqlbackup completed OK!".

140307 11:58:11 mysqlbackup: INFO: MEB logfile created at /data/backup/mysql-full-2014-03-07/meta/MEB_2014-03-07.11-58-11_backup.log

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir = /data/mysql/data/
  innodb_data_home_dir =
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = /data/mysql/data/
  innodb_log_files_in_group = 2
  innodb_log_file_size = 134217728
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb
  innodb_undo_directory = /data/mysql/data/
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = /data/backup/mysql-full-2014-03-07/datadir
  innodb_data_home_dir = /data/backup/mysql-full-2014-03-07/datadir
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = /data/backup/mysql-full-2014-03-07/datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 134217728
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb
  innodb_undo_directory = /data/backup/mysql-full-2014-03-07/datadir
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

 mysqlbackup: INFO: Unique generated backup id for this is 13941646917706914

 mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
140307 11:58:13 mysqlbackup: INFO: Full Backup operation starts with following threads
        1 read-threads    6 process-threads    1 write-threads
140307 11:58:13 mysqlbackup: INFO: System tablespace file format is Antelope.
140307 11:58:13 mysqlbackup: INFO: Starting to copy all innodb files...
140307 11:58:13 mysqlbackup: INFO: Found checkpoint at lsn 1632059.
140307 11:58:13 mysqlbackup: INFO: Copying /data/mysql/data/ibdata1 (Antelope file format).
140307 11:58:13 mysqlbackup: INFO: Starting log scan from lsn 1631744.
140307 11:58:13 mysqlbackup: INFO: Copying log...
140307 11:58:13 mysqlbackup: INFO: Log copied, lsn 1632059.
140307 11:58:13 mysqlbackup: INFO: Copying /data/mysql/data/mysql/innodb_index_stats.ibd (Antelope file format).
140307 11:58:13 mysqlbackup: INFO: Copying /data/mysql/data/mysql/innodb_table_stats.ibd (Antelope file format).
140307 11:58:13 mysqlbackup: INFO: Copying /data/mysql/data/mysql/slave_master_info.ibd (Antelope file format).
140307 11:58:13 mysqlbackup: INFO: Copying /data/mysql/data/mysql/slave_relay_log_info.ibd (Antelope file format).
140307 11:58:13 mysqlbackup: INFO: Copying /data/mysql/data/mysql/slave_worker_info.ibd (Antelope file format).
140307 11:58:13 mysqlbackup: INFO: Copying /data/mysql/data/test/t1.ibd (Antelope file format).
140307 11:58:13 mysqlbackup: INFO: Completing the copy of innodb files.
140307 11:58:14 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
140307 11:58:15 mysqlbackup: INFO: Starting to lock all the tables...
140307 11:58:15 mysqlbackup: INFO: All tables are locked and flushed to disk
140307 11:58:15 mysqlbackup: INFO: Opening backup source directory '/data/mysql/data/'
140307 11:58:15 mysqlbackup: INFO: Starting to backup all non-innodb files in
    subdirectories of '/data/mysql/data/'
140307 11:58:15 mysqlbackup: INFO: Copying the database directory 'mysql'
140307 11:58:15 mysqlbackup: INFO: Copying the database directory 'performance_schema'
140307 11:58:15 mysqlbackup: INFO: Copying the database directory 'test'
140307 11:58:15 mysqlbackup: INFO: Completing the copy of all non-innodb files.
140307 11:58:15 mysqlbackup: INFO: A copied database page was modified at 1632059.
          (This is the highest lsn found on page)
          Scanned log up to lsn 1637723.
          Was able to parse the log up to lsn 1637723.
          Maximum page number for a log record 319
140307 11:58:15 mysqlbackup: INFO: All tables unlocked
140307 11:58:15 mysqlbackup: INFO: All MySQL tables were locked for 0.675 seconds.
140307 11:58:15 mysqlbackup: INFO: Reading all global variables from the server.
140307 11:58:15 mysqlbackup: INFO: Completed reading of all global variables from the server.
140307 11:58:15 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/backup/mysql-full-2014-03-07
140307 11:58:16 mysqlbackup: INFO: Full Backup operation completed successfully.
140307 11:58:16 mysqlbackup: INFO: Backup created in directory '/data/backup/mysql-full-2014-03-07'
140307 11:58:16 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000003, position 2299

-------------------------------------------------------------
   Parameters Summary         
-------------------------------------------------------------
   Start LSN                  : 1631744
   End LSN                    : 1637723
-------------------------------------------------------------

mysqlbackup completed OK!

记下日志文件及日志文件位置：mysql-bin.000003, position 2299



3. 准备备份，将日志应用到数据
[root@node1 data]# mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/data/backup/mysql-full-$(date +%F) apply-log
MySQL Enterprise Backup version 3.9.0 [2013/08/23]
Copyright (c) 2003, 2013, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 mysqlbackup --defaults-file=/etc/my.cnf
        --backup-dir=/data/backup/mysql-full-2014-03-07 apply-log

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".

140307 12:43:13 mysqlbackup: INFO: MEB logfile created at /data/backup/mysql-full-2014-03-07/meta/MEB_2014-03-07.12-43-13_apply_log.log

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = /data/backup/mysql-full-2014-03-07/datadir
  innodb_data_home_dir = /data/backup/mysql-full-2014-03-07/datadir
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = /data/backup/mysql-full-2014-03-07/datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 134217728
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb

 mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
 mysqlbackup: INFO: Creating 14 buffers each of size 65536.
140307 12:43:13 mysqlbackup: INFO: Apply-log operation starts with following threads
        1 read-threads    1 process-threads
140307 12:43:13 mysqlbackup: INFO: ibbackup_logfile's creation parameters:
          start lsn 1631744, end lsn 1637723,
          start checkpoint 1632059.
InnoDB: Doing recovery: scanned up to log sequence number 1637723
 mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
 mysqlbackup: INFO: InnoDB: Setting log file size to 134217728
InnoDB: Progress in MB: 100
 mysqlbackup: INFO: InnoDB: Setting log file size to 134217728
InnoDB: Progress in MB: 100
140307 12:43:15 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
          lsn 1637723.
 mysqlbackup: INFO: Last MySQL binlog file position 0 2214, file name mysql-bin.000003
140307 12:43:15 mysqlbackup: INFO: The first data file is '/data/backup/mysql-full-2014-03-07/datadir/ibdata1'
          and the new created log files are at '/data/backup/mysql-full-2014-03-07/datadir'
140307 12:43:15 mysqlbackup: INFO: Apply-log operation completed successfully.
140307 12:43:15 mysqlbackup: INFO: Full backup prepared for recovery successfully.

mysqlbackup completed OK!
[root@node1 data]#


4. 将恢复好的数据打包并传到从库服务器上，并且打包数据库安装二进制程序也打包传送
[root@node1 mysql]# cd /data/backup/
[root@node1 backup]# tar cvzf mysql-full-2014-03-07.tar.gz mysql-full-2014-03-07
[root@node1 backup]# scp -P 10007 mysql-full-2014-03-07.tar.gz root@192.168.30.121:/data/
[root@node1 backup]# cp /etc/my.cnf /usr/local/mysql/
[root@node1 backup]# cp /etc/init.d/mysqld /usr/local/mysql/
[root@node1 mysql]# cd /usr/local/
[root@node1 backup]# tar cvzf mysql-bin.tar.gz mysql
[root@node1 backup]# scp -P 10007 mysql-bin.tar.gz root@192.168.30.121:/usr/local/

5. 主库上创建复制用户
mysql> grant replication slave on *.* to repl@'192.168.30.121' identified by '123456';
mysql> flush privileges;


6. 登录到从库服务器上，解压安装二进制程序、创建目录、拷回数据文件
[root@node2 local]# tar xvf mysql-bin.tar.gz
[root@node2 local]# mkdir /data/mysql -p
[root@node2 local]# useradd -s /sbin/nologin -M mysql
[root@node2 local]# cd /data/
[root@node2 data]# tar xvf mysql-full-2014-03-07.tar.gz
[root@node2 data]# mv mysql-full-2014-03-07/datadir /data/mysql/
[root@node2 data]# cd mysql
[root@node2 data]# mv datadir data
[root@node2 data]# cp /usr/local/mysql/mysqld /etc/init.d/
[root@node2 data]# cp /usr/local/mysql/my.cnf /etc/my.cnf
[root@node2 data]#  vim /etc/my.cnf    ------------------------>修改server_id等
[root@node2 data]# chown -R mysql.mysql /data/mysql


7. 从库服务器上，修改/etc/profile
[root@node2 data]# export PATH=/usr/local/mysql/bin:$PATH
[root@node2 data]# source !$

8. 在从库服务器上验证复制用户是否可以连接
[root@node2 data]# mysql -u repl -p -h 192.168.30.120


9. 从库服务器上启动从库，注意server_id的值，不要跟master的server_id一样，否则要set global server_id。server_id不一样才能change master to
[root@node2 data]# /etc/init.d/mysqld start
mysql> select @@server_id;
mysql> change master to
    -> master_host='192.168.30.120',
    -> master_user='repl',
    -> master_password='123456',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=2299;
mysql>
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.30.120
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000005
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


10. 测试之：在主库上新建表
mysql> use test
mysql> create table test(id int);

11. 测试之：在从库上查看表是否有同步过来
mysql> use test
mysql> show tables;