Mysql Master-Slave configuration

网络环境

主服务器：server1, 192.168.2.231

备份服务器：server2, 192.168.1.78

配置主备数据库

创建同步用户

在主数据库上创建同步用户，以便该帐号可以访问主数据库。

e.g.

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.1.78′ IDENTIFIED BY ’123456′;

验证方法，可以登录到备份服务器使用下面的命令访问主服务器，如果可以访问说明配置正确：

# mysql –u repl –p –h 192.168.2.231

修改数据库配置文件

注意：所有服务器的server-id不能相同！！！

1. 修改主配置文件

# vi /etc/my.cnf

[mysqld]

log-bin=mysql-bin

server-id=1   # 1 – 2^32-1

2. 修改备配置文件

# vi /etc/my.cnf

[mysqld]

server-id=101      # 1 – 2^32-1

3. 重启主备数据库

# service mysqld restart

备份主数据库文件并导入备份数据库（方法一）

注意：确认主数据库不被修改！！！

1. 主服务器上操作：

备份数据库

# mysqldump –all-databases > /root/mysql-backup.sql

打包备份文件

# tar czvf /root/mysql-backup.tar.gz /root/mysql-backup.sql

复制到备份服务器

# scp /root/mysql-backup.tar.gz root@server2:/root/

2. 备份服务器上操作：

解压备份文件

# cd /root

# tar zxvf /root/mysql-backup.tar.gz

导入到备份数据库

# mysql –u roo –p < /root/mysql-backup.sql

备份主数据库文件并导入备份数据库（方法二）

1. 主服务器上操作：

锁定数据库只读

mysql> FLUSH TABLES WITH READ LOCK;

不要退出上面的命令，备份数据库文件

# tar zcvf /root/backup.tar.gz /var/lib/mysql

复制到备份服务器

# scp /root/ backup.tar.gz root@server2:/root/

记录master状态

mysql> SHOW MASTER STATUS;

释放数据库锁

mysql> UNLOCK TABLES;

2. 备份服务器上操作：

停止mysql服务

# service mysqld stop

解压备份文件

# cd /var/lib/mysql

# tar zxvf /root/mysql-backup.tar.gz

启动mysql服务

# service mysqld start

配置主备同步

查看主数据库状态

查看master状态，记录下File和Position

mysql> show master status;

| File              | Position  | Binlog_Do_DB | Binlog_Ignore_DB |

| mysql-bin.000001 | 72267996 |              |                  |

设置Master，查看备份数据库状态

1. 设置Master

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.2.231′, MASTER_USER=’repl’, MASTER_PASSWORD=’123456′, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=72267996;

2. 查看slave状态

mysql> show slave status;

| Connecting to master | 192.168.2.231 | repl        |        3306 |            60 | mysql-bin.000001 |            72267996 | mysqld-relay-bin.000001 |            98 | mysql-bin.000001      | No               | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |            72267996 |              98 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL |

3. 启动slave

mysql> start slave;

4. 验证同步状态

检查主数据库进程状态，共1个同步进程

mysql> show processlist;

110346 | repl       | 192.168.1.78:45597 | NULL       | Binlog Dump | 59523 | Has sent all binlog to slave; waiting for binlog to be updated | NULL

检查备份数据库进程状态，共2个同步进程

mysql> show processlist;

|    1 | system user |                 | NULL       | Connect | 59472 | Waiting for master to send event                                      | NULL |

|    2 | system user |                 | NULL       | Connect |    35 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |

修改主数据库数据，然后在备份数据库查看备份数据库是否也修改成功。

FAQ

Q1：同步数据库启动失败？

# tail -f /var/log/mysqld.log

101117 12:52:25  mysqld started

101117 12:52:25  InnoDB: Started; log sequence number 0 8769907

101117 12:52:25 [Note] /usr/libexec/mysqld: ready for connections.

Version: ’5.0.22′  socket: ‘/var/lib/mysql/mysql.sock’  port: 3306  Source distribution

101117 12:56:50 [Warning] Neither –relay-log nor –relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use ‘–relay-log=hzserver2-relay-bin’ to avoid this problem.

101117 13:38:18 [Note] Slave SQL thread initialized, starting replication in log ‘FIRST’ at position 0, relay log ‘./hzserver2-relay-bin.000001′ position: 4

101117 13:38:18 [Note] Slave I/O thread: connected to master ‘repl@hzserver1:3306′,  replication started in log ‘FIRST’ at position 4

101117 13:41:39 [Note] /usr/libexec/mysqld: Normal shutdown

101117 13:41:39 [Note] Slave I/O thread killed while reading event

101117 13:41:39 [Note] Slave I/O thread exiting, read up to log ‘hzserver1-bin.000001′, position 98

101117 13:41:39 [Note] Error reading relay log event: slave SQL thread was killed

101117 13:41:39  InnoDB: Starting shutdown…

101117 13:41:42  InnoDB: Shutdown completed; log sequence number 0 8769907

101117 13:41:42 [Note] /usr/libexec/mysqld: Shutdown complete

—————–

A1：修改启动脚本

增加启动项：’–relay-log=hzserver2-relay-bin’

# vi /etc/init.d/mysqld

/usr/bin/mysqld_safe  –defaults-file=/etc/my.cnf –pid-file=”$mypidfile” –log-error=”$errlogfile” –relay-log=hzserver2-relay-bin>/dev/null 2>&1 &

参考资料

官方资料：

http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

如何设置MySQL同步(Replication)

http://blog.chinaitlab.com/html/30/104830-161852.html

MYSQL主从复制或双机互备

http://hi.baidu.com/ultimated_fantasy/blog/item/f6752d1843f9450034fa4148.html