MySQL Replication M-S模型配置一则
STEP1
Slave是通过MySQL登录到Master上读取二进制日志的，因此需要在Master上给Slave配置权限。
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'db2' IDENTIFIED BY 'password';
mysql> flush privileges;
STEP2
在Master上需要打开二进制日志，并标识server-id。server-id用于唯一的标识主机，数字取值范围1 - (2的32次方-1)
#vi /etc/mysql/my.cnf
[mysqld]
log-bin
binglog-format=row
sync-binlog=1
server-id=1
#service mysql restart
STEP3
制作一个Master的完整备份，并且执行prepare
#innobackupex --user=root --password=toor /var/lib/backup
#innobackupex --use-memory=500m --apply-log /var/lib/backup/2015-04-02_21-58-57/
STEP4
将备份拷贝至Slave, 并放入数据文件目录
-
in Master
scp -r /var/lib/backup/2015-04-02_21-58-57/ db02:/var/lib/backup
-
in Slave
cd /var/lib/backup/2015-04-02_21-58-57/ db02:/var/lib/backup
mv * /var/lib/mysql
chown -R mysql.mysql /var/lib/mysql
STEP5
在Slave上配置server-id,标识服务器
# vi /etc/my.cnf
[mysqld]
server-id = 2
#service mysql restart
STEP6
查看并记录二进制日志中的position ID
# cd /var/lib/mysql
# cat xtrabackup_binlog_info
db01-bin.000007 183
STEP7
配置Slave Replication ( in slave )
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.92',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='db01-bin.000007',
-> MASTER_LOG_POS=183;
STEP8
启动 Replication , 并检查结果
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
附件:
-
master my.cnf
[mysqld]
log-bin
binlog-format = row
sync-binlog = 1
server-id = 1
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
socket = /var/run/mysqld/mysqld.sock
port = 3306
user = mysql
symbolic-links = 0
bind-address = 192.168.1.92
innodb_log_file_size = 5242880[mysqld_safe]
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid -
slave my.cnf
[mysqld]
server-id = 2
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
socket = /var/run/mysqld/mysqld.sock
port = 3306
user = mysql
symbolic-links = 0
bind-address = 192.168.1.93
innodb_log_file_size = 5242880[mysqld_safe]
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
