MySQLクラスタ構築(1)-プライマリ・プロビジョニング構築

15065 ワード

データベースは、どのビジネスにおいても最も重要な一環であり、データベース・アーキテクチャに対する高い要求です.単一のデータベースは常に本番環境に存在するべきではありません.単一のデータベース、バックアップの欠落による損失が多すぎることを目撃しています.そのため、高可用性MySQLクラスタを構築する必要があります.
クラスタの構築には、次のメリットがあります.

高可用性、マスター・セクションの失効時に自動的に切り替え、技術者緊急処理

を必要としない

高スループットは、複数のノードが同時にデータの読み取りサービスを提供し、メインノードの負荷を低減し、高スループット

を実現することができる.

拡張性が高く、オンライン拡張をサポート

バックアップに影響なし、バックアップノードでのバックアップ操作はビジネスに影響しない

欠点といえば、以下の点があります.

アーキテクチャが複雑で、配置、管理の面で技術者に要求がある

スタンバイノードがプライマリノードログをプルすると、プライマリノードサーバのパフォーマンスに一定の影響があります

半同期レプリケーションが構成されている場合、トランザクションのコミットに少し影響します

総じて言えば、クラスタは必ず構築されなければならないので、誰が自分のデータをいつでもリスクのあるデータベースに走ることができますか.次に、単純なプライマリ・プロビジョニング・モードから高可用性アーキテクチャへのアプローチについていくつかの記事で説明します.このセクションでは、MySQLのマスターを構築する方法について説明します.操作を重視し、理論的な説明はあまりありません.
1環境準備
1.1データベースの起動
2台のマシンでそれぞれMySQLインスタンスを起動し、MySQL構築方式はMySQLインストール(バイナリ版)を参考にすることができる.
IP
システム
ポート
MySQLバージョン
ノード
192.168.41.83
Centos6.8
3306
5.7.20
Master
192.168.41.72
Centos6.8
3306
5.7.20
Salve
重要な構成:
Master:

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 833306

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

Salve

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 723306
read_only=1

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

構成の解析

datadir,basedir,tmpdirはそれぞれデータファイル位置、データベースプログラムインストール位置、一時ファイル位置

server_idインスタンスid,注意,同一クラスタマシンのserver_idは同じではありません

read_only読み取り専用かどうかは、一般的に備庫設定

log_bin,log_bin_indexバイナリ・ログ・ロケーション、バイナリ・ログ・インデックス・ファイル・ロケーション

binlog_formatバイナリログフォーマット、rowは各データの変化状況を記録することを表し、statementは関連sql文を記録することを表し、mixedは2種類の混用を表し、クラスタを構築する際にrowフォーマットを使用することを提案し、sql文でデータを同期するとデータの不一致が発生しやすい場合

relay_log_recoveryslaveダウンタイム後、中継ログが破損した場合は、ログを再プルします.中継ログの完全性を保証するために、

を開くことをお勧めします.

relay_log,relay_log_index中継ログおよび中継ログインデックスファイル位置

log_errorエラーログ位置

replicate_wild_ignore_table同期時に無視する必要があるテーブルです.ここではシステム統計テーブルを無視します.奇妙な同期失敗が発生した場合は、

をオンにしてみてください.

plugin_dirカード位置

plugin_load起動時にロードするプラグイン

loose_rpl_semi_sync_master_enabled無損半同期レプリケーション-プライマリ・ライブラリを開くかどうか(プライマリ・スタンバイがすべて開くことを推奨し、プライマリ・スタンバイの切り替えを容易にする)

loose_rpl_semi_sync_slave_enabled 無損半同期レプリケーション-スタンバイをオープンするかどうか(プライマリ・スタンバイがすべてオープンすることを推奨し、プライマリ・スタンバイの切り替えを容易にする)

1.2データの挿入
Masterが使用中のデータベースであると仮定し、オンラインでバックアップを構築し、テストデータをMasterノードに挿入します.

[mysql@mysql-test-83 ~]$ mydb-test_seg
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

db83-3306>>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

db83-3306>>create database mytest;
Query OK, 1 row affected (0.00 sec)

db83-3306>>use mytest;
Database changed
db83-3306>>create table test1(
    -> id int not null primary key auto_increment,
    -> name varchar(16) not null default '',
    -> age int not null default 0
    -> ) engine = InnoDb charset = utf8;
Query OK, 0 rows affected (0.01 sec)
db83-3306>>insert into test1 values (0, 'a', 16), (0, 'b', 17), (0, 'c', 18), (0, 'd', 19);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

db83-3306>>select * from test1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  16 |
|  2 | b    |  17 |
|  3 | c    |  18 |
|  4 | d    |  19 |
+----+------+-----+
4 rows in set (0.00 sec)

2オンライン構築主従
現在、私たちの環境は以下の通りです.

192.168.41.83:3306 Masterノード、使用中

192.168.41.72:3306新しくデータベースを建てて、上で192.168.41.83の備庫

をします
2.1同期ユーザの作成
同期用の権限を付与するreplのユーザー名を作成しました.

db83-3306>>CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (5.01 sec)

db83-3306>>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)

db83-3306>>flush privileges;
Query OK, 0 rows affected (0.00 sec)

2.2データのバックアップ
よく使うバックアップデータの方式はREPLICATION SLAVEとinnobackupexで、ここでデータ量は少なくて、私達はmysqldumpで全備を行います

[mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysqldump -S /data/mysql_db/mysql_seg_3306/mysql.sock -F --opt -R --single-transaction --master-data=2 --default-character-set=utf8 -A > mysql_backup_full.sql

パラメータ解析:

mysqldumpsocketファイルを選択し、ネイティブ接続データベースはこの方法でもip、ポートを指定して接続する

-Sエクスポート開始前にログをリフレッシュ

-Fこのパラメータがmysqldumpコマンドを同時にアクティブにしたquick,add-drop-table,add-locks,extended-insert,lock-tablesパラメータを表す場合

--optはバッファ出力を無視することを意味し、mysqldumpコマンドは指定したSQLファイル

に直接データをエクスポートします.

--quickは、各--add-drop-tableコマンドの前にCREATE TABEL文を追加し、データテーブルの重複を防止する

である.

DROP-TABLE IF EXISTSINSERTデータの前後で対応するデータテーブル

をロックおよびロック解除する

--add-locks複数行挿入可能

--extended-insertストアド・プロシージャおよびカスタム関数をエクスポートし、ストアド・プロシージャに役立つ場合は、このパラメータ

を追加する必要があります.

-R(innodb)トランザクションの独立性レベルを繰り返し可能、すなわち--single-transactionに設定します.これにより、1つのトランザクションで同じクエリーが同じデータをすべて読み込むことが保証され、すべてのテーブルがInnoDBであればこのパラメータが付き、データの一貫性が保証され、バックアップ時にテーブルがロックされません.MyISAMのテーブルがある場合は、データの整合性を保証するためにテーブルバックアップをロックする必要があります

REPEATABLE READバックアップ・プロシージャの読み込みロック、--lock-all-tablesオプションおよびsingle-transactionオプションは、2つ選択されています

lock-all-tables現在のバイナリ・ログの位置を記録し、--master-data=21と2の違いを取るが、後者はmaster_dataコマンドを注釈した

change master ...符号化を選択することは非常に重要であり、符号化の選択が間違っているか、設定されていないと文字化しやすい

--default-character-setすべてのライブラリをバックアップ

データのバックアップが完了したら、Slaveマシンに直接データファイルを転送します.

[mysql@mysql-test-83 ~]$ ll
total 772
-rw-rw-r-- 1 mysql mysql 786921 Nov 17 10:38 mysql_backup_full.sql
[mysql@mysql-test-83 ~]$ rsync -avzP mysql_backup_full.sql 192.168.41.72:/home/mysql/

2.3データ復旧
Slaveマシン上で直接sqlファイルインポートデータを実行する

[mysql@mysql-test-72 ~]$ ll mysql_backup_full.sql 
-rw-rw-r-- 1 mysql mysql 786921 Nov 17 10:38 mysql_backup_full.sql
[mysql@mysql-test-72 ~]$ /usr/local/mysql57/bin/mysql -S /data/mysql_db/mysql_seg_3306/mysql.sock < mysql_backup_full.sql

インポートが完了すると、データとMasterのバックアップデータが一致していることがわかります.

db72-3306>>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

db72-3306>>use mytest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
db72-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| test1            |
+------------------+
1 row in set (0.00 sec)

db72-3306>>select * from test1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  16 |
|  2 | b    |  17 |
|  3 | c    |  18 |
|  4 | d    |  19 |
+----+------+-----+
4 rows in set (0.00 sec)

2.4同期のオン
バックアップ・ファイルに戻り、Masterバックアップ・ポイントのバイナリ・ログの場所をヘッダーから見つけます.

[mysql@mysql-test-72 ~]$ head -30 mysql_backup_full.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154;

-AおよびMASTER_LOG_FILEは、MasterがMASTER_LOG_POSを実行して得られるバイナリ位置情報である.同期コマンドを実行します

--     
-- reset slave;

--     
CHANGE MASTER TO
MASTER_HOST='192.168.41.83',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=154;

--     
start slave

実際の実行結果は以下の通りです.

db72-3306>>CHANGE MASTER TO
    -> MASTER_HOST='192.168.41.83',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.20 sec)

db72-3306>>start slave;
Query OK, 0 rows affected (0.01 sec)

同期ステータスの表示

db72-3306>>show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.83
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 833306
                  Master_UUID: 15958368-e9a0-11e8-a98c-ecb1d77febe4
             Master_Info_File: /data/mysql_db/mysql_seg_3306/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

show master status,Slave_IO_Running,Slave_SQL_Running,Seconds_Behind_Masterの3つのパラメータから同期状態が正常かどうかを判断することができる.

Slave_IO_RunningMasterログのスレッドを取り、Yesは実行中

Slave_SQL_Runningログからデータをリカバリするスレッド、Yesは実行中

Seconds_Behind_Master現在のデータベースは、プライマリ・ライブラリのデータに対して遅延しており、この値は、バイナリ・ログのタイムスタンプに基づいて計算される(秒)

出力結果から同期が正常であることがわかりますので、テストしてみましょう.
2.5同期テスト
Masterノードに新しいデータを挿入

db83-3306>>insert into test1 values(0, 'chengqm', 24);
Query OK, 1 row affected (0.00 sec)

db83-3306>>select * from test1;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | a       |  16 |
|  2 | b       |  17 |
|  3 | c       |  18 |
|  4 | d       |  19 |
|  5 | chengqm |  24 |
+----+---------+-----+
5 rows in set (0.00 sec)

スタンバイノードデータ同期状態の確認

db72-3306>>select * from test1;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | a       |  16 |
|  2 | b       |  17 |
|  3 | c       |  18 |
|  4 | d       |  19 |
|  5 | chengqm |  24 |
+----+---------+-----+
5 rows in set (0.00 sec)

データがスタンバイノードに同期されていることがわかり、今回のプライマリ・スタンバイが完了しました.

接続追跡のZONE

mysql再整理--ストレージエンジン[3]