mysqlbackup線上配置Mysql主從架構

資料庫

主機名

IP地址

同步使用者

備份位置

主資料庫

Mysql01

192.168.47.152

server01

/backup

從資料庫

Mysql02

192.168.47.151

mysqlbackup --user=root --password --backup-dir=/backup backup-and-apply-log

備份的目錄為/backup，請確認這個目錄的存在；

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 107 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 500 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

[root@mysql01 backup]# service iptables stop

iptables：清除防火牆規則： [確定]

iptables：將鏈設定為政策 ACCEPT：filter [確定]

iptables：正在解除安裝模組： [確定]

root@mysql01 backup]# scp bak.tar root@mysql02:/backup/*

Warning: Permanently added the RSA host key for IP address '192.168.47.151' to the list of known hosts.

root@mysql02's password:

bak.tar 100% 69MB 23.1MB/s 00:03

需要先關閉防火牆

[root@mysql01 backup]# mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ copy-back

MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2014/08/26]

Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.

mysqlbackup: INFO: Starting with following command line ...

mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql

--backup-dir=/backup/ copy-back

mysqlbackup: INFO:

IMPORTANT: Please check that mysqlbackup run completes successfully.

At the end of a successful 'copy-back' run mysqlbackup

prints "mysqlbackup completed OK!".

141118 16:19:35 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2014-11-18.16-19-35_copy_back.log

--------------------------------------------------------------------

Server Repository Options:

--------------------------------------------------------------------

datadir = /data/mysql

innodb_data_home_dir = /data/mysql

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /data/mysql/

innodb_log_files_in_group = 2

innodb_log_file_size = 5242880

innodb_page_size = Null

innodb_checksum_algorithm = none

--------------------------------------------------------------------

Backup Config Options:

--------------------------------------------------------------------

datadir = /backup/datadir

innodb_data_home_dir = /backup/datadir

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /backup/datadir

innodb_log_files_in_group = 2

innodb_log_file_size = 5242880

innodb_page_size = 16384

innodb_checksum_algorithm = none

mysqlbackup: INFO: Creating 14 buffers each of size 16777216.

141118 16:19:35 mysqlbackup: INFO: Copy-back operation starts with following threads

        1 read-threads 1 write-threads

mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with --log-bin.

        Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible.

141118 16:19:35 mysqlbackup: INFO: Copying /backup/datadir/ibdata1.

141118 16:19:37 mysqlbackup: INFO: Copying the database directory 'john'

141118 16:19:37 mysqlbackup: INFO: Copying the database directory 'mysql'

141118 16:19:37 mysqlbackup: INFO: Copying the database directory 'performance_schema'

141118 16:19:37 mysqlbackup: INFO: Completing the copy of all non-innodb files.

141118 16:19:37 mysqlbackup: INFO: Copying the log file 'ib_logfile0'

141118 16:19:37 mysqlbackup: INFO: Copying the log file 'ib_logfile1'

141118 16:19:39 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql

141118 16:19:39 mysqlbackup: INFO: Copy-back operation completed successfully.

141118 16:19:39 mysqlbackup: INFO: Finished copying backup files to '/data/mysql'

[root@mysql02 data]# chmod -R 777 mysql

[root@mysql02 data]# service mysqld start

Starting MySQL [確定]

mysql> CHANGE MASTER TO MASTER_HOST='mysql01',MASTER_USER='server01',MASTER_PASSWORD='server01', MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=107;

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: mysql01

Master_User: server01

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 1082

Relay_Log_File: mysql02-relay-bin.000003

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: john

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: 1082

Relay_Log_Space: 1532

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

1 row in set (0.00 sec)

mysql> select * from wzq;

+-------+

| name |

+-------+

| qiang |

| wu |

| zhi |

| 1 |

| 2 |

| 3 |

+-------+

8 rows in set (0.00 sec)