mysql 마스터-슬레이브 복제

mysql 비디오 튜토리얼이 칼럼에서는 마스터-슬레이브 복제를 소개합니다

관련 무료 학습 권장사항: mysql 비디오 튜토리얼

데이터베이스 복제는 시스템의 고가용성과 성능을 향상시키는 데 매우 중요한 역할을 합니다. 이 기사는 Mysql 마스터-슬레이브 복제에 관한 관련 지식을 요약한 것입니다. 이 분야에서 일하게 된다면 이 기사가 도움이 되기를 바랍니다.

1 메인 라이브러리 구성

1.1 my.cnf 구성:

메인 라이브러리 구성 파일 my.cnf에서 다음과 같은 기본 구성을 만듭니다:

log-bin  =  mysql-bin　//二进制日志文件名称主体
log-bin-index  =  mysql-bin.index　//二进制日志文件索引文件
server-id  =  1　//唯一的服务器ID，为了保持唯一性，可以去ip的尾部
binlog-format  =  mixed　//控制复制基于的方式，有基于语句(statement)，基于行(row)，混合(mixed)，**主从库需要保持一致**
#sync_binlog=1 //推荐配置，开启该选项，mysql每次在事务提交前会将二进制日志同步到磁盘上，保证在服务器崩溃时不会丢失事件。

기본적으로 모든 데이터베이스를 복사합니다. 섹션 7(필터 복사)을 참조하세요.

比如说要指定db1和db2两个数据库进行主从复制：
binlog-do-db = db1
binlog-do-db = db2

1.2 복제 계정 추가:

복제 계정 및 권한 설정 추가:

mysql> grant replication slave, replicatin client on \*.\* to repl@'172.16.226.192' identified by 'repl123456';　//其中repl是用户名，repl123456为账户密码，172.16.226.168为备库的地址。
mysql> flush privileges;　//在不重启mysql服务的情况下完成权限的更新

2 대기 데이터베이스 구성

대기 데이터베이스 구성 파일 my.cnf에서 다음과 같은 기본 구성을 만듭니다.

relay-log  =  slave-relay-bin　//中继日志文件名称主体
relay-log-index  =  slave-relay-bin.index　//中继日志文件索引文件
server-id  =  2　//唯一的服务器ID，必须要异于主库
#read_only = 1　//限制备库为只读，可选
log_slave_updates = 1　//控制是否在中继日志执行之后，将同步过来的数据添加到自己的binlog中去，1代表是
skip_slave_start // 该选项能够阻止备库在崩溃后自动启动复制，建议开启
即使开启了建议的选项，备库仍然可能在崩溃后被中断，因为master.info和中级日志文件都不是崩溃安全的，所以建议开启一下选项：
sync_master_info     = 1
sync_relay_log       = 1
sync_relay_log_info  = 1

데이터베이스를 필터링할 수도 있습니다. 동기화하거나 테이블을 만들려면 복사 필터링 섹션을 참조하세요.

3 데이터베이스 원격 백업

데이터베이스 원격 백업의 경우 mysqldump(논리 백업)을 선택하여 핫 백업할 수 있지만, 데이터 양이 많을 경우 속도가 느려집니다. Xtrabackup(물리적 백업)도 핫 백업을 수행할 수 있습니다. mysql 데이터베이스(여기서는 innobackupex가 사용됨) -1.5.1), Xtrabackup은 innoDB와 같은 데이터베이스의 온라인 백업을 실현할 수 있으며 이는 빠르고 정상적인 읽기 및 쓰기에 영향을 주지 않습니다. 여기에서 전체 데이터베이스를 백업하세요.

3.1 백업 계정 만들기

데이터베이스 백업을 위해 메인 서버에 사용자 백업을 만듭니다(최소 권한 사용).

mysql> grant reload, lock tables, replication client on \*.\* to backup@'%' identified by 'backup123';
mysql> flush privileges;　//在不重启mysql服务的情况下完成权限的更新

3.2 전체 데이터베이스 백업

전체 백업과 복구 준비의 두 단계는 모두 기본 데이터베이스 서버에서 완료됩니다.

innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --user=backup --password=backup123  /mysqlbackup
--defaults-file：选择默认的配置文件
--user和--password：分别为进行备份的用户名和密码
/mysqlbackup：目标目录

3.3 복구 준비

일반적으로 백업이 완료된 후에는 해당 데이터를 아직 복구 작업에 사용할 수 없습니다. 백업된 데이터에는 커밋되지 않은 트랜잭션이나 커밋되었지만 아직 데이터와 동기화되지 않은 트랜잭션이 포함될 수 있기 때문입니다. 파일. 따라서 현재 데이터 파일은 여전히 ​​일관성 없는 상태를 처리하고 있습니다. "준비"의 주요 기능은 커밋되지 않은 트랜잭션을 롤백하고 커밋된 트랜잭션을 데이터 파일에 동기화하여 데이터 파일을 일관된 상태로 만드는 것입니다.
innobakupex 명령의 --apply-log 옵션을 사용하여 위 기능을 구현할 수 있습니다. 다음 명령과 같습니다.

innobackupex-1.5.1 --apply-log --user=backup --password=backup123  /mysqlbackup/2017-01-11_21-20-57
如果执行正确，其最后输出的几行信息通常如下：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120407  9:01:36  InnoDB: Starting shutdown...
120407  9:01:40  InnoDB: Shutdown completed; log sequence number 92036620
120407 09:01:40  innobackupex: completed OK!

"준비"를 구현하는 과정에서 innobackupex는 일반적으로 --use-memory 옵션을 사용하여 사용할 수 있는 메모리 크기를 지정할 수도 있습니다. 기본값은 일반적으로 100M입니다. 사용 가능한 메모리가 충분한 경우 준비 프로세스에 더 많은 메모리를 할당하여 완료 속도를 높일 수 있습니다.

3.4 데이터 복사

마스터 서버에 준비된 데이터베이스를 슬레이브 서버에 복사합니다. (물론 패키징한 후 복사도 가능합니다)

scp -r /mysqlbackup/ copyer@192.168.1.192:/data/

3.5 데이터 복구

데이터 복구 전 먼저 슬레이브 서버인 mysql 서비스를 종료하고, 백업 폴더에 있는 xtrabackup_binlog_info 파일에서 현재 사용되는 바이너리 로그 파일을 구하고, 백업도 포함됩니다. 지금까지의 바이너리 로그 이벤트 위치입니다. datadir 디렉터리가 비어 있지 않으면 datadir 디렉터리도 지워야 합니다.
innobackupex 명령의 --copy-back 옵션은 복구 작업을 수행하는 데 사용되며 모든 데이터 관련 파일을 mysql 서버의 datadir 디렉터리에 복사하여 복구 프로세스를 수행합니다. innobackupex는 backup-my.cnf를 통해 datadir 디렉터리에 대한 관련 정보를 얻습니다. (--defaults-file을 통해 my.cnf 디렉터리를 지정할 수도 있고 datadir 경로가 비어 있는지 확인할 수도 있습니다.)

innobackupex-1.5.1 --copy-back  /mysqlbackup
如果执行正确，其输出信息的最后几行通常如下：
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/backup/2012-04-07_08-17-03'
innobackupex: back to original InnoDB log directory '/mydata/data'
innobackupex: Finished copying back files.
120407 09:36:10  innobackupex: completed OK!

"innobackupex"가 있는지 확인하세요. 위 정보의 마지막 줄에 나타납니다. : Completed OK!".

데이터가 datadir 디렉터리에 복원된 후 모든 데이터 파일의 소유자 및 그룹이 mysql과 같은 올바른 사용자인지 확인해야 합니다. 그렇지 않으면 시작하기 전에 데이터 파일의 소유자 및 그룹을 수정해야 합니다. mysqld. 예:

chown -R  mysql:mysql  /var/lib/mysql/

4 마스터-슬레이브 연결

4.1 슬레이브 데이터베이스 열기

service mysql start

mysql 열기에 실패하면 오류 로그를 확인하여 실패 이유를 찾을 수 있습니다.

4.2 마스터-슬레이브 연결 설정

슬레이브 라이브러리는 복제 계정을 통해 마스터 라이브러리에 연결됩니다. (다음 연결이 적용되려면 슬레이브가 중지 상태여야 합니다.)

mysql> change master to master_host='192.168.1.208'，master_user='repl'，
master_password='repl123456'，master_log_file='mysql-bin.000001'（备份时得到的活动日志）,master_log_pos=0（备份时得到的活动日志中事件的位置）;

참고: 마스터- 슬레이브 연결이 여기에 연결되어 있지 않습니다. 마스터에서 가능한 이유 중 하나는 my.cnf 구성 파일이 로컬 시스템에 바인딩되어 있기 때문입니다. 즉, 바인드 주소 = 127.0.0.1 우리가 해야 할 일은 주석 처리뿐입니다. 외부 컴퓨터에서는 액세스할 수 없습니다.

오픈 슬레이브:

mysql> start slave;

슬레이브 상태를 확인하면 IO 스레드와 SQL 스레드가 이미 오픈 상태인 것을 확인할 수 있습니다. 슬레이브 라이브러리의 연결 상태를 나타내는 변수가 많이 있습니다(이러한 변수도 사용할 수 있습니다). 마스터-슬레이브 모니터링 설정) 여기서는 설명하지 않습니다.

mysql> show slave status;

Slave_IO_Running: Yes  //表示IO线程运行正常
Slave_SQL_Running: Yes  //表示SQL线程运行正常
Seconds_Behind_Master: 0  //表示在网络条件较好的情况下，从库能够及时同步上主库

4.3 일반 모니터링 명령

mysql> show processlist\G;　//查看数据库服务线程情况
mysql> show master/slave status\G;　//查看主备库状态
mysql> flush logs;　//强制轮换(rotate)二进制日志，从而得到一个完整的二进制日志文件
mysql> show binlog events in '指定二进制日志文件名称'  from （从指定位置开始显示） limit （需要显示的事件数量）\G;　//查看binlog中事件
mysql> show binary logs;　//显示所有的binlogs
mysql> reset master;　//删除所有二进制日志文件并清空索引文件
mysql> reset slave;　//删除slave上复制用的所有文件重新开始
mysql> show slave hosts;  //查看主库所拥有的从库信息

5 슬레이브 라이브러리 지연이 큽니다

슬레이브 라이브러리 지연이 크다면 지연이 큰 이유를 찾아야 합니다. innodb_flush_log_at_trx_commit 매개변수는 mysql의 쓰기 효율성에 더 큰 영향을 미치며 다음 세 가지 값을 갖습니다.

0：每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；
1：每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上；
2：每事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度；

取1时的IO耗费最大，虽然一致性和完整性方面效果最好，但是写入效率最低，而这也是导致从库延迟较大的原因（如果服务器配置较高或许会好些）。取0时mysql写入性能很好，但如果 mysqld 进程崩溃，通常会导致最后 1s 的日志丢失 。取2时的写入性能也很好，每次事务提交会写入日志文件，但并不会立即刷写到磁盘，日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃，由于日志已经写入到系统缓存，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后 1s 的日志丢失。

6 混合模式复制

正常情况下使用使用基于语句的复制，而对不安全的语句则切换到基于行的复制。主要有以下几种情况：

1. 该语句调用了：
   • UUID函数
   • 用户自定义函数
   • CURRENT_USER或USER函数
   • LOAD_FILE函数
2. 一个语句同时更新了两个或者两个以上含有AUTO_INCREMENT列的表
3. 语句使用了服务器变量
4. 存储引擎不允许使用基于语句的复制，例如，mysql cluster引擎

7 复制过滤

有时候我们不需要对数据库中所有的库进行复制，或者不想对指定库中的某些表进行复制操作，那么我们就需要对复制进行一定的过滤配置，以达到更合理的复制效果。

1. 基于master

**binlog-do-db=mysql**：主库只是将指定库(mysql)发生的变化记录到二进制日志中。
**binlog-ignore-db=mysql**：主库取消将指定库(mysql)发生的变化记录到二进制日志中。

2. 基于slave

针对数据库进行的过滤：

**replicate-do-db=mysql**：从库只是将指定库(mysql)发生的变化进行重现。
**replicate-ignore-db=mysql**：从库取消将指定库(mysql)发生的变化进行重现。
针对表进行的过滤：
**replicate-wild_do-table=mysql.learn**：从库只是将指定库(mysql)中指定表(learn)发生的变化进行重现。
**replicate-wild_ignore-table=mysql.learn**：从库取消将指定库(mysql)中指定表(learn)发生的变化进行重现。

以上复制过滤方式乍一看没有问题，其实还是有需要注意的地方。因为这些过滤方式的效果与复制方式有关系。如果是基于语句的复制，binlog-do-db、binlog-ignore-db、replicate-do-db、replicate-ignore-db与跨库(如use库内和use外)有关系，这一点需要注意。

8 日志清理

暴力清理：（没有主从复制的情况下）

1、重启mysql服务器即可关闭bin日志的记录
2、通过reset master命令进行清理

条件清理

如果存在主从复制关系，则应当使用purge的方式来清理bin日志，语法如下：

purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'

用户删除列于在指定的日志或日期之前的日志索引中的所有二进制日志，同时这些日志也会从日志索引文件的清单中删除。

eg.
purge master logs to 'mysql-bin.000005';
purge master logs before '2014-08-30 00:00:00';//清除指定日期之前的日志
purge master logs before date_sub(now(),Interval 3 day);清除三天前的日志

定时清理

参数：expire_logs_days
说明：二进制日志自动删除/过期的天数。默认值为'0'，即没有过期的
示例：expire_logs_days = 5，代表日志的有效时间为5天
什么时候会删除过期日志？

每次进行log flush的时候会自动删除过期的日志

什么时候会触发log flush？

1、重启
2、binlog文件的大小达到了最大限制
3、手动执行flush logs命令

写在最后

本文只是结合自己的学习以及实践过程进行了相关总结，如有不妥之处望您批评指正。推荐大家学习《高可用MYSQL》、《高性能MYSQL》两本书，最重要的还是实践实践再实践，欢迎交流，共同进步。

想了解更多编程学习，敬请关注php培训栏目！

위 내용은 mysql 마스터-슬레이브 복제의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!