MySQL カスケード レプリケーションで大きなテーブルのフィールドを拡張する方法

MySQL カスケード レプリケーションでの大きなテーブルのフィールド拡張

1. 背景

顧客のビジネスには、約 4 億行のテーブルがあります。ビジネスの拡張により、テーブルの open_id varchar (50) は varchar(500) に拡張する必要があります。
変更中は、メイン ライブラリへの影響を最小限に抑えるようにしてください (影響を与えないことが最善です -> 最終的には 4 を目指しました) -時間枠期間)。

2. データベース テーブル情報

環境: Mysql 8.0.22
1 Gtid に基づくマスター 1 スレーブ レプリケーション

1. 最初の質問、これは大きな質問です。 pic テーブル? はい、このテーブルの ibd ファイルを見てください。280G カウントが長い間返されていません。スタンバイ データベースを使用して行数 > 4 億を確認してください。

以下语句也可以查看：
show table status from dbname like 'tablename'\G # Rows 的值不准，有时误差有2倍

SELECT a.table_schema,a.table_name,concat(round(sum(DATA_LENGTH/1024/1024)+sum(INDEX_LENGTH/1024/1024),2) ,'MB')total_size,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') AS data_size,concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') AS index_size FROM information_schema.TABLES a WHERE a.table_schema = 'dbname' AND a.table_name = 'tablename'; #看下此表的数据量

Big tableなのでどのような方法で変更すればよいでしょうか？

3. ソリューションの選択

次の M はメイン データベースを表し、S1 はスレーブ 1、S2 はスレーブ 2

方法オンラインDDL Gh-ostPt-osc##M-S1-S2時間は推定可能ですカスケード レプリケーション、手動操作1 つ星 #最初の 3 つのオプションを選択しなかったのはなぜですか?

利点	欠点	実現可能性
ネイティブ、中間一時テーブル	ALGORITHM=COPYを使用すると、DMLがブロックされます。推奨バージョン>MySQL5.7	5つ星
トリガーの代わりにバイナリログ再生スレッドを使用します	サードパーティ ツール。これにより、さまざまなパラメーターに応じて実行時間が長くなります	星 4 つ
良好なバージョン互換性、トリガーを使用してメイン テーブルとセカンダリ テーブルの一貫性を維持	サードパーティ ツール、および使用に関する追加の制限	3 つ星

実態評価に基づく、今回のビジネス側の要求は

このテーブルには 24 時間ビジネス トラフィックがあり、4 時間を超えるビジネスの停止は許容されません

OnlineDDL メソッドでは、ALGORITHM=COPY の場合、DML は一定期間ブロックされます (読み取り専用)。最後に、メイン テーブルとセカンダリ テーブルの名前変更操作中 (読み取りおよび書き込み不可)、DDL までブロックされます。完了（所要時間は不明）。

スレーブ ライブラリに Gh-ost モードで接続して変換することを推奨します。このモードはメイン ライブラリへの影響が最も少なく、パラメータを通じてフロー制御を設定できます。このツールの致命的な弱点は変更に時間がかかりすぎることで、4億件のデータを持つテーブルの場合、テスト環境では70時間かかりました。最後に、切り替えコマンドを発行し、中間テーブル *_del を手動で削除する必要もあります。このソリューションの利点は、データのセキュリティを確保するためのスレーブ データベースがあることです。そのため、1 マスター 2 スレーブ アーキテクチャを使用する場合は、このソリューションの方が推奨されます。

Pt-osc と Gh-ost はどちらもサードパーティに属しており、Pt-osc による大きなテーブルの操作と OnlineDDL には、失敗時のロールバックのコストが非常に高いという共通の欠点があります。

MySQL <5.7 などの下位バージョンであれば使用できますが、理論上は MySQL5.6.7 から OnlineDDL がサポートされます。最初はサポートがあまり良くないので、選択できます。適当に。

最後に、DBA が好む (xin ku) 方法を選択しました。これは、M-S1-S2 カスケード レプリケーションで実行されます。

4. 操作方法

新しい S1 スレーブ ライブラリを作成し、M-S1-S2 カスケード レプリケーションを構築します

• # # OnlineDDL を使用して S2 でフィールド拡張を実行します (利点は、その間 M-S1 のマスターが影響を受けないことです)

• 拡張完了後、M の遅延同期を待ちます。 -S1-S2 (M との S2 データの差を減らし、データ検証を実行します)

• S1 を削除し、M-S2 間にマスター/スレーブ関係を確立します (S2 が M のデータの同期を継続できるようにします) )

• S2 をバックアップして S1 を復元し、M-S2-S1 カスケード レプリケーションを確立します

• アプリケーションを停止し、マスター/スレーブ データを待ちます一貫性を保つため (利点は、異なるデータ量の同期時間が非常に短いことです)

• #最終的に、S2 がメイン ライブラリになり、S1 がスレーブ ライブラリになります (アプリケーションはフロント ライブラリを変更する必要があります)。接続情報の終了)

##リグレッション検証の申請
• 上記の内容は複雑に見えるかもしれませんが、本質的にはバックアップとリカバリです。読者はこれを代替案として検討できます。具体的な手順を共有しますか?

  环境装备:开启Gtid,注意M,S1 binlog保存时长,磁盘剩余空间大于待变更表的2倍
  show global variables like 'binlog_expire_logs_seconds'; # 默认604800
  set global binlog_expire_logs_seconds=1209600; # 主库和级联主库都需要设置
  1.搭建 1主2从的级联复制，M -> S1 -> S2 ，安装MySQL注意本次环境lower_case_table_names = 0 
  2.在S2 上做字段扩容。 预估 10个小时
  `参数设置:`
  set global slave_type_conversions='ALL_NON_LOSSY'; # 防止复制报错SQL_Errno: 13146，属于字段类型长度不一致无法回放
  set global interactive_timeout=144000;set global wait_timeout =144000;
  `磁盘IO参数设置:`
  set global innodb_buffer_pool_size=32*1024*1024*1024;# 增加buffer_pool 防止Error1206The total number of locks exceeds the lock table size 资源不足
  set global sync_binlog=20000;set global innodb_flush_log_at_trx_commit=2;
  set global innodb_io_capacity=600000;set global innodb_io_capacity_max=1200000; # innodb_io_capacity需要设置两次
  show variables like '%innodb_io%'; # 验证以上设置
  screen 下执行:
  time mysql -S /data/mysql/3306/data/mysqld.sock -p'' dbname -NBe "ALTER TABLE tablename MODIFY COLUMN open_id VARCHAR(500) NULL DEFAULT NULL COMMENT 'Id' COLLATE 'utf8mb4_bin';"
  查看DDL进度:
  SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED  FROM performance_schema.events_stages_current;
  3.扩容完成后，等待延迟同步M-S1-S2 
  数据同步至主从一致,对比主从Gtid
  4.移除S1,建立M-S2的主从关系
  S1 (可选)
  stop slave;
  reset slave all;
  systemctl stop mysql_3306
  S2
  stop slave;
  reset slave all;
  # MASTER_HOST='M主机IP'  
  CHANGE MASTER TO
    MASTER_HOST='',
    MASTER_USER='',
    MASTER_PASSWORD=',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION=1,
    MASTER_CONNECT_RETRY=10;
  start slave; (flush privileges;# 验证数据可正常同步)
  5.备份S2恢复S1，建立M-S2-S1级联复制
  物理备份S2,重做S2->S1 级联主从
  rm -rf binlog/*
  rm -rf redolog/*
  xtrabackup --defaults-file=/data/mysql/3306/my.cnf.3306 --move-back --target-dir=/data/actionsky/xtrabackup_recovery/data
  chown -R mysql. data/
  chown -R mysql. binlog/*
  chown -R mysql. redolog/*
  systemctl start mysql_3306
  set global gtid_purged='';
  reset slave all;
  # MASTER_HOST='S2主机IP'  ,已扩容变更完的主机
  CHANGE MASTER TO
    MASTER_HOST='',
    MASTER_USER='',
    MASTER_PASSWORD='',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION=1,
    MASTER_CONNECT_RETRY=10;
  `MySQL8.0版本需要在上面语句中添加 GET_MASTER_PUBLIC_KEY=1; #防止 Last_IO_Errno: 2061 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.`
  start slave;
  6.应用停服，等待主从数据一致
  主库停服+可设置read_only+flush privileges，对比主从Gtid
  7.最终S2成为主库，S1为从库
  应用更改配置连接新主库。
  S2上:
  stop slave;reset slave all;
  set global read_only=0;set global super_read_only=0;
  `show master status\G 观察是否有新事务写入`
  
  收尾：还原第2步的参数设置。
  set global interactive_timeout=28800;set global wait_timeout =28800;
  set global innodb_buffer_pool_size=8*1024*1024*1024;
  set global slave_type_conversions='';
  set global sync_binlog=1;set global innodb_flush_log_at_trx_commit=1;
  set global innodb_io_capacity=2000;set global innodb_io_capacity_max=4000;

  補足シナリオ: ディスク IO 機能に基づいたテスト
  トラフィックを発生させずにメイン データベースで直接変更するケース:

  シナリオ 1、ディスクは NVME の物理マシンであり、4 億のデータを処理するのに約 5 時間かかります (ディスク パフォーマンス 1G/秒)。

  シナリオ 2、ディスクがメカニカル ディスクである仮想マシン、この量のデータには約 40 時間かかります (ディスク パフォーマンス 100M/秒)。

  以上がMySQL カスケード レプリケーションで大きなテーブルのフィールドを拡張する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。