réplication maître-esclave MySQL

Tutoriel vidéo MySQLLa colonne présente la réplication maître-esclave

Recommandations d'apprentissage gratuites associées : Tutoriel vidéo MySQL

La réplication de base de données joue un rôle très important dans l'amélioration de la haute disponibilité et des hautes performances du système. Cet article résume les connaissances pertinentes impliquées dans la réplication maître-esclave MySQL. Si vous travaillez dans ce domaine, je. j'espère que vous pourrez en savoir plus à ce sujet. Vous avez aidé.

1 Configuration de la bibliothèque principale

1.1 Configuration my.cnf :

Configurez la configuration de base suivante dans le fichier de configuration de la bibliothèque principale 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每次在事务提交前会将二进制日志同步到磁盘上，保证在服务器崩溃时不会丢失事件。

Par défaut Copier toutes les bases de données Si vous devez spécifier une base de données, veuillez vous référer à la section 7 (Filtrage de copie).

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

1.2 Ajouter un compte de copie :

Ajouter un compte de copie et définir les autorisations :

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 Configuration de la base de données en veille

Dans le fichier de configuration de la base de données en veille my.cnf Effectuez la configuration de base suivante dans :

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

Vous pouvez également filtrer la base de données ou la table à synchroniser, merci de vous référer à la section sur le filtrage de réplication.

3 Sauvegarde à distance de la base de données

La sauvegarde à distance de la base de données peut choisir mysqldump (sauvegarde logique) pour la sauvegarde à chaud, mais elle sera plus lente lorsque la quantité de données est importante. Xtrabackup (sauvegarde physique) peut également le faire. effectuer une sauvegarde à chaud sur mysql La base de données effectue une sauvegarde à chaud (innobackupex-1.5.1 est utilisé ici). Xtrabackup peut réaliser une sauvegarde en ligne de bases de données telles que innoDB, ce qui est rapide et n'affecte pas la lecture et l'écriture normales. Sauvegardez toute la base de données ici.

3.1 Créer un compte de sauvegarde

Créez une sauvegarde utilisateur sur le serveur principal (en utilisant les autorisations minimales) pour la sauvegarde de la base de données.

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

3.2 Sauvegarde complète de la base de données

Les deux étapes de sauvegarde complète et de préparation à la récupération sont effectuées sur le serveur de base de données principal.

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

3.3 Préparation de la récupération

Généralement, une fois la sauvegarde terminée, les données ne peuvent pas encore être utilisées pour les opérations de récupération, car les données sauvegardées peuvent contenir des transactions qui n'ont pas encore été validées ou qui ont été validé mais pas encore synchronisé avec les transactions dans les fichiers de données. Par conséquent, les fichiers de données sont toujours confrontés à un état incohérent à l’heure actuelle. La fonction principale de la « préparation » est de rendre les fichiers de données dans un état cohérent en annulant les transactions non validées et en synchronisant les transactions validées avec les fichiers de données.
L'option --apply-log de la commande innobakupex peut être utilisée pour implémenter les fonctions ci-dessus. Par exemple, la commande suivante :

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!

Dans le processus d'implémentation de la "préparation", innobackupex peut généralement également utiliser l'option --use-memory pour spécifier la taille de la mémoire qu'il peut utiliser. La valeur par défaut est généralement. 100M. Si suffisamment de mémoire est disponible, vous pouvez allouer plus de mémoire au processus de préparation pour augmenter sa vitesse d'exécution.

3.4 Copie des données

Copiez la base de données préparée sur le serveur maître vers le serveur esclave. (Bien sûr, vous pouvez également empaqueter puis copier)

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

3.5 Récupération de données

Avant la récupération des données, fermez d'abord le service mysql du serveur esclave et obtenez-le à partir du fichier xtrabackup_binlog_info dans le dossier de sauvegarde Le fichier journal binaire actuellement utilisé et l'emplacement où les événements du journal binaire jusqu'à présent sont sauvegardés. Si le répertoire datadir n'est pas vide, vous devez également vider le répertoire datadir.
L'option --copy-back de la commande innobackupex est utilisée pour effectuer une opération de récupération. Elle effectue le processus de récupération en copiant tous les fichiers liés aux données dans le répertoire datadir du serveur mysql. innobackupex obtient des informations pertinentes sur le répertoire datadir via backup-my.cnf (vous pouvez également spécifier le répertoire my.cnf via --defaults-file, et assurez-vous que le chemin datadir est vide)

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!

Veuillez vous assurer la dernière partie des informations ci-dessus est Une ligne « innobackupex : terminé OK ! » apparaît.

Une fois les données restaurées dans le répertoire datadir, vous devez vous assurer que les propriétaires et les groupes de tous les fichiers de données sont les bons utilisateurs, comme mysql. Sinon, vous devez modifier les propriétaires des fichiers de données. avant de démarrer mysqld et group. Par exemple :

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

4 Connexion maître-esclave

4.1 Ouvrir la base de données esclave

service mysql start

Si l'ouverture de MySQL échoue, vous pouvez trouver la raison de l'échec en vérifiant l'erreur enregistrer.

4.2 Etablir une connexion maître-esclave

La base de données esclave se connecte à la base de données maître via le compte de réplication : (l'esclave doit être en état d'arrêt pour que la connexion suivante prenne effet)

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（备份时得到的活动日志中事件的位置）;

Remarque :Si le maître ne peut pas être connecté à la connexion maître-esclave ici, une raison possible est que le fichier de configuration my.cnf est lié à la machine locale, c'est-à-dire bind-address = 127.0.0.1. Ce que nous devons faire Il suffit de le commenter, sinon les machines externes ne pourront pas y accéder.

Activer l'esclave :

mysql> start slave;

Vérifiez l'état de l'esclave, vous pouvez constater que le thread IO et le thread SQL sont déjà à l'état ouvert, et il existe de nombreuses variables qui représentent l'état de connexion du bibliothèque esclave (ces variables peuvent également être utilisées pour paramétrer la surveillance maître-esclave), nous ne les présenterons pas une par une ici.

mysql> show slave status;

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

4.3 Commandes de surveillance communes

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 Le délai de la bibliothèque esclave est important

Si vous constatez que le délai de la bibliothèque esclave est important , vous devez trouver la raison du retard important. Le paramètre innodb_flush_log_at_trx_commit a un plus grand impact sur l'efficacité d'écriture de mysql et a trois valeurs :

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培训栏目！

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!