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

转载于:https://www.cnblogs.com/yangke687/p/4388338.html