配置mysql数据库主从复制

一、 原理说明

复制有以下三个步骤：

(a)主服务器把数据更改记录到二进制日志中。（这叫做二进制日志事件（binary log events）。）

(b)从服务器把主服务器的二进制日志事件拷贝到自己的中继日志（relay log）中。

(c)从服务器重放中继日志中的事件，把更改用到自己的数据上。

第一步：在主服务器上记录二进制日志。在每个更新数据的事务完成之前，主服务器都会把数据更改记录到二进制日志中。即使事务在执行期间是交错的，mysql也会串行地把事务写入到二进制日志中。在把事件写入到二进制日志之后，主服务器告诉存储引擎提交事务。

第二步：从服务器把主服务器的二进制日志拷贝到自己硬盘上，进入所谓的“中继日志（relay log）中。

它首先启动一个工作线程，叫I/O线程。这个I/O线程开启一个普通的客户端连接，然后启动一个特殊的二进制日志转储进程。这个转储进程从主服务器的二进制日志中读取事件。它不会对事件进行轮询。如果它跟上了主服务器，就会进入休眠状态，并等待有新事件发生时主服务器发出的信号。I/O线程把事件写入从服务器的中继日志中。

第三步：SQL从线程处理了该过程的最后一部分。该线程读取了中继日志，并且重放其中的事件，然后更新从服务器的数据。由于这个线程能跟上I/O线程，所以，中继日志一般都是在操作系统的缓存中，因此中继日志的开销很低。同时SQL线程执行的事件也可以被写入从服务器自己的二进制日志中。

二、 结构说明

l 2服务器mysql版本一致，初始化表，并在后台启动mysql

l 前提：关闭防火墙和selinux

l 可以实现基于实例级，数据库级，表级别的复制， 本实验的是实例级主从复制

l 复制优势：无需共享存储，通过网络传输变化的数据到从库

三、修改主服务器master:

#vi /etc/f //增加以下参数

[mysqld]

log-bin=mysql-bin //[必须]启用二进制日志

server-id=103 //[必须]服务器唯一ID，默认是1，一般取IP最后一段

四、修改从服务器slave:

#vi /etc/f

[mysqld]

log-bin=mysql-bin //[不是必须]启用二进制日志

server-id=104 //[必须]服务器唯一ID，默认是1，一般取IP最后一段

五、重启两台服务器的mysql

/etc/init.d/mysql restart

六、在主服务器上建立帐户并授权slave:

create user 'rep1'@'192.168.0.%' identified by 'rep1234';

GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'192.168.0.%';

//一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全

该用户信息不会复制到从库上

七、 登录主服务器的mysql，查询master的状态

>show master status;

注：执行完此步骤后不要再对主服务器表进行修改，防止主服务器状态值变化

八、 从服务器配置Slave：

>change master to master_host='192.168.0.103',master_port=3306,master_user='rep1',master_password='rep1234',master_log_file='mysql-bin.000001',master_log_pos=350; //注意不要断开，350数字前后无单引号。

Mysql>start slave; //启动从服务器复制功能

九、 检查从服务器复制功能状态：

mysql> show slave status\G

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

Master端查看：

十、 主从服务器测试：

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

mysql> create database hi_db;

Query OK, 1 row affected (0.00 sec)

mysql> use hi_db;

Database changed

mysql> create table hi_tb(id int(3),name char(10));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into hi_tb values(001,'bobu');

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| hi_db |

| mysql |

| test |

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

4 rows in set (0.00 sec)

从服务器Mysql查询：

mysql> show databases;

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

| Database |

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

| information_schema |

| hi_db | //I'M here，大家看到了吧

| mysql |

| test |

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

4 rows in set (0.00 sec)

mysql> use hi_db

Database changed

mysql> select * from hi_tb; //查看主服务器上新增的具体数据

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

| id | name |

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

| 1 | bobu |

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

1 row in set (0.00 sec)

十一、 主从手动切换

1) 从库 停止 IO_THREAD 线程

mysql> stop slave IO_THREAD;

2) 激活从库

root@localhost:francs>stop slave;

root@localhost:francs>reset master;

root@localhost:francs>reset slave;

mysql> show binary logs;

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

| Log_name | File_size |

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

| mysql-bin.000001 | 106 |

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

1 row in set (0.00 sec)

3) 重启从库

[root@mysqlslave ~]# /etc/init.d/mysqld restart

mysql> show slave status\G

Empty set (0.00 sec)

mysql> show master status\G

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

File: mysql-bin.000002

Position: 360

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

4) 从库创建复制用户

create user 'rep2'@'192.168.0.%' identified by 'rep1234';

GRANT REPLICATION SLAVE ON *.* TO 'rep2'@'192.168.0.%';

//一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全

5) 原主库103上操作

将原来主库变为从库

CHANGE MASTER TO

MASTER_HOST='192.168.0.104',

MASTER_PORT=3306,

MASTER_USER='rep2',

MASTER_PASSWORD='rep1234',

MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=360;

mysql> start slave;

mysql> show slave status\G

6) 复制数据测试

在104原从库现主库上插入数据

在103原主库现从库上查询

十二、 主从回切

目的：将之前的手动切换再回切

103回切回主

104回切回从

1) 103停止从库IO线程

mysql> stop slave IO_THREAD;

mysql> show slave status\G

2) 激活103从库

root@localhost:francs>stop slave;

root@localhost:francs>reset master;

root@localhost:francs>reset slave;

mysql> show binary logs;

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

| Log_name | File_size |

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

| mysql-bin.000001 | 106 |

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

1 row in set (0.00 sec)

3) 重启103从库和查询

[root@mysqlserver ~]# /etc/init.d/mysqld restart

mysql> show slave status\G

Empty set (0.00 sec)

mysql> show master status\G

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

File: mysql-bin.000002

Position: 106

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

4) 104上操作

将原来主库变为从库

CHANGE MASTER TO

MASTER_HOST='192.168.0.103',

MASTER_PORT=3306,

MASTER_USER='rep1',

MASTER_PASSWORD='rep1234',

MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=106;

mysql> start slave;

mysql> show slave status\G

5) 测试

在103上更新记录

mysql> insert into hi_tb values ('5','eee');

Query OK, 1 row affected (0.00 sec)

104上查询

十三、 主从宕机测试

1) 模拟主宕机

103宕机

104查看

2) 103恢复

复制恢复

3) 104宕机

103主库不受影响

4) 104恢复

启动104库，复制恢复