数据库主从复制的原理|Mysql数据库主从复制相关介绍

【www.bbyears.com--php安全】

公司使用master-slave架构，就具体学习了下，这里记录下相关内容。

Replication的好处

读写分离：主数据库负责写和update数据，从数据库负责读操作
数据安全：由于数据复制到从库，并且可以对从库的复制进程进行停止操作，所以可以在从库上进行备份服务而不需要对主服务器进行中断操作
可分析：在线数据可以在master数据库上创建，然后在slave数据库上进行分析，且不会对master数据库造成性能影响
长数据：如果一个分支机构需要一份主要数据的copy，可以使用复制来创建一份本地备份。
Replication类型：

Statement Based Replication （SBR）：二进制日志保存为SQL语句，通过SQL在slave上执行达到同步效果
Row Based Replication（RBR）：只复制改变ROW
Mixed Based Replication（MBR）：混合型
NOTICE: 此博客使用SBR（默认日志格式）方式进行主从复制。

主备设置注意事项：

在master上，必须使用二进制日志，且设置唯一ID号。
在每个slave服务器上，为了与maste服务器进行连接，必须设置唯一ID号
（可选）创建一个独立用户，用来认证主服务器，并通过访问二进制日志来达到复制
在创建数据快照或启动复制进程前，需要记录master服务器的二进制日志位置。此信息在后期slave启动时需要
如果master服务器上已经存在数据，且需要同步，需要创建数据快照。（可通过mysqldump或直接拷贝文件）
需要配置slave服务器，填写master服务器相关信息。
一些选择：

如果是新的master和slaves，则只需要配置文件
如果数据库已经在运行，则需要先进行数据同步
如果增加slaves到一个已经存在主备的环境，只需要设置slaves（通过对slave数据拷贝到新slave方式，达到数据同步效果）
mysql数据库相关操作

master服务器配置

1. my.cnf配置

修改my.cnf文件，启用二进制日志记录，且设置server-id为唯一数字，不要跟从slave重复

[mysqld]
log-bin=mysql-bin
server-id=1
NOTICE：
为了良好的一致性，可以设置innodb_flush_log_at_trx_commit=1和sync_binlog=1，但是这样会存在一定的性能影响。

2. 为主从创建专用账户

每个从服务器与主服务器连接，都是使用mysql用户和密码，为安全起见，创建专门的主从账号。
在master服务器上创建用户，复制专用用户需要具有REPLICATION SLAVE权限。

mysql>create user "username"@"address" identified by "slavepass";
mysql>grant replication slave on *.* to "username"@"address";
3. 获取mater的二进制日志位置

获取master的当前二进制日志位置，使slave从当前位置开始复制

连接主数据库，执行flush tables with read lock命令
mysql> FLUSH TABLES WITH READ LOCK;
另打开一个回话，使用show master status确定当前二进制日志的文件名和位置（使用同一会话也可以）

mysql>show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+
4. slave获取同步点

如果数据库以前存在数据，则需要先用mysqldump或数据快照进行数据库复制

mysqldump方式创建数据快照

shell> mysqldump –all-databases –master-data > dbdump.db
使用–master-data会自动锁定表，并且会导出位置，打开导出文件，可以看到里面有CHANGE MASTER TO MASTER_LOG_FILE="mysql-bin.000001", MASTER_LOG_POS=3254350;这样的位置语句，就不需要执行手动查看master的二进制日志位置了。

如果不使用–master-data，则需要另开启一个终端，输入FLUSH TABLES WITH READ LOCK，进行锁表操作，mysqldump结束后输入

UNLOCK TABLES
data目录拷贝方式

当数据量大的时候，使用mysqldump效率比较低，推荐复制原始数据文件进行迁移
为了获取数据一致性，受限需要关闭数据库

shell>mysqladmin shutdown
复制Mysql的data文件
shell>tar cf /tmp/db.tar ./data
slave设置

修改my.cnf文件，设置server-id为唯一数字

[mysqld]
server-id=2
slave服务器不需要强制使用二进制日志，但是推荐启用二进制日志，这样可以进行更多其他操作（数据库备份、作为其他slave的主库）

主从复制具体操作

1. 新的主从服务器搭建

配置master数据库属性

[mysqld]
log-bin=mysql-bin
server-id=1
启动master服务器

创建一个主从用户
每个从服务器与主服务器连接，都是使用mysql用户和密码
在master服务器上创建用户
mysql>create user "*username*"@"*address*" identified by "*slavepass*";
mysql>grant replication slave on *.* to "*username*"@"*address*";
获取master数据库相关信息

连接主数据库，执行flush tables with read lock命令

mysql> FLUSH TABLES WITH READ LOCK;
在master服务器上再打开一个回话，执行show master status来确定当前二进制日志文件名和位置

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+
在主服务器上释放read 锁
mysql>unlock tables;

在slave服务器上，配置mysql属性
[mysqld]
server-id=2
启动slave数据库
在slave数据库上执行change master to来设置master主从服务器配置
mysql> CHANGE MASTER TO
-> MASTER_HOST="master_host_name",
-> MASTER_USER="replication_user_name",
-> MASTER_PASSWORD="replication_password",
-> MASTER_LOG_FILE="recorded_log_file_name",
-> MASTER_LOG_POS=recorded_log_position;
NOTE主从配置无法使用Unix socket文件，只能使用TCP/IP

在5.6.5或以前的版本，slave_master_info和
slave_relay_log_info使用的是myiam存储引擎，可使用下列语句修改（不可以在数据库运行阶段修改）

ALTER TABLE mysql.slave_master_info ENGINE=InnoDB;
ALTER TABLE mysql.slave_relay_log_info ENGINE=InnoDB;
查看slave运行状态

show slave status\G
2. 已存在数据的复制操作

创建复制用户
mysql>create user "*username*"@"*address*" identified by "*slavepass*";
mysql>grant replication slave on *.* to "*username*"@"*address*";
如果master数据库未设置server-id和二进制日志，则需要关闭mysql数据库添加相关配置
如果需要关闭数据库，则是一个创建快照的好机会。通过复制原始数据方式进行快照创建。

获取data目录位置
关闭数据库
service mysql stop
对data目录打包
tar cf /tmp/tmm/db.tar ./mysql
如果已设置，则获取日志位置，然后使用mysqldump或原始数据复制的方式进行数据导出
可以使用“获取mater的二进制日志位置”介绍的方式获取二进制位置，然后使用以下命令来导出数据库：

 >mysqldump --all-databases >db.dump
或者直接使用mysqldump –all-databases –master-data > dbdump.db，此命令不需要获取日志位置信息，直接包含在文件中。

设置slvae（具体可参看前面介绍）
这一步根据创建快照方式不同而不同
mysqldump方式：

启动slave，使用–skip-slave-start选项使replication不在启动数据库的时候启动
导入数据
mysql Raw data files方式：

解压缩数据
tar xvf dbdump.tar
chown -R mysql:mysql mysql/
对解压后的目录需要设置为mysql用户权限，使mysql能连接和修改这些文件
启动slave，使用–skip-slave-start选项
在slave上执行CHANGE MASTER TO语句，设置连接

启动slave线程
mysql 启动成功后不要忘记删掉my.cnf中的skip-slave-start选项，如果是通过命令行添加参数的，则可以忽略。

3. 增加slave

复制一个存在的slave

关闭slave服务
shell> mysqladmin shutdown
复制data目录（进行raw形式的组从配置）
tar cf /tmp/fulldump.dump ./mysql
在增加的slave上解压缩目录
tar xvf fulldump.dump
修改目录权限：
chown -R mysql:mysql mysql/
在my.cnf中添加2个参数，手动指定新slave的relay-log前缀格式，不使用主机名作为格式：

relay-log=f8392e8f9f63-relay-bin
relay-log-index=f8392e8f9f63-relay-bin
如果不修改relay-log格式为拷贝过来的slave-relay-log格式，则mysql会自动使用主机名作为新的slave-relay-log格式，启动会报错：

读写分离就是通过主从复制实现的，主数据库负责写，从数据库负责读，达到读写分离的目的。曾经认为高大上的东西，其实一层层剥离后也就那样，继续学习。

本文来源：http://www.bbyears.com/jiaocheng/86830.html