検索

ホームページ >データベース >mysql チュートリアル >Centos7 で MySQL の複数のマスターと 1 つのスレーブを構成する方法

Centos7 で MySQL の複数のマスターと 1 つのスレーブを構成する方法

PHPz
PHPz転載
2023-06-01 11:19:051273ブラウズ

ビジネス シナリオ:

会社のいくつかの主要なビジネスは独立しており、異なるデータベース サーバーに配置されていますが、共同クエリ統計のために 1 つのビジネスを複数のビジネス ライブラリに関連付ける必要があります。 。このとき、異なる業務データベースのデータを統計用のスレーブデータベースに同期する必要があります。 mysql のマスター/スレーブ同期原則に従って、複数のスレーブと 1 つのマスターのソリューションが使用されます。メイン ライブラリは innodb エンジンを使用し、スレーブ ライブラリは myisam エンジンを使用して複数のインスタンスを開き、複数のインスタンスのデータを同じディレクトリに同期し、フラッシュ テーブルを通じて 1 つのインスタンス内の他のインスタンスのデータにアクセスします。

解決策:

1. メイン データベースに innodb エンジンを使用し、sql_mode を no_auto_create_user に設定します
2. スレーブ ライブラリから複数のインスタンスを開いて結合します。複数のマスター ライブラリ 内部のデータは、マスター/スレーブ レプリケーションを通じて同じデータ ディレクトリに同期されます。スレーブ ライブラリの各インスタンスはマスター ライブラリに対応します。複数のインスタンスが同じデータ ディレクトリを使用します。
3. データベースから myisam エンジンを使用し、データベースからデフォルトの innodb エンジンを閉じる myisam エンジンは、同じデータ ディレクトリ内の他のインスタンスのテーブルにアクセスできます。
4. スレーブ ライブラリの各インスタンスは、他のインスタンス テーブルのデータ変更を確認するためにフラッシュ テーブルを実行する必要があります。デフォルトのインスタンスが接続されるように、最初のインスタンスのテーブルを 1 分ごとに更新するように crontab タスク スケジュールを設定できます。プログラムはテーブルのリアルタイムの変更を確認できます。
5. メイン ライブラリとスレーブ ライブラリの両方の sql_mode を no_auto_create_user に設定するこの方法でのみ、メイン ライブラリの innodb エンジンの SQL をスレーブ ライブラリに同期して正常に実行できます。

スキーム アーキテクチャ図:

Centos7 で MySQL の複数のマスターと 1 つのスレーブを構成する方法

##環境の説明:

メイン ライブラリ-1: 192.168.1.1

メイン ライブラリ-2: 192.168.1.2
スレーブ ライブラリ-3: 192.168.1.3
スレーブ ライブラリ-3: 192.168.1.4
スレーブ ライブラリ-3: 192.168. 1.5

実装手順: (mysql のインストール手順はここでは説明しません)

1. メイン データベース構成ファイル. 複数のメイン データベース構成ファイルは、server-id を除いて同じにすることはできません。 それは同じだ。 

[root@masterdb01 ~]#cat /etc/my.cnf
[client]
port= 3306
socket= /tmp/mysql.sock
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
character-set-server = utf8mb4
default-storage-engine = innodb
socket = /tmp/mysql.sock
skip-name-resolv = 1
open_files_limit = 65535 
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 2048
tmp-table-size  = 32m
max-heap-table-size = 32m
#query-cache-type = 0
query-cache-size = 0
external-locking = false
max_allowed_packet = 32m
sort_buffer_size = 2m
join_buffer_size = 2m
thread_cache_size = 51
query_cache_size = 32m
tmp_table_size = 96m
max_heap_table_size = 96m
query_cache_type=1
log-error=/data/logs/mysqld.log
slow_query_log = 1
slow_query_log_file = /data/logs/slow.log
long_query_time = 0.1
# binary logging #
server-id = 1
log-bin     = /data/binlog/mysql-bin
log-bin-index  =/data/binlog/mysql-bin.index
expire-logs-days = 14
sync_binlog = 1
binlog_cache_size = 4m
max_binlog_cache_size = 8m
max_binlog_size = 1024m
log_slave_updates
#binlog_format = row 
binlog_format = mixed  //这里使用的混合模式复制
relay_log_recovery = 1
#不需要同步的表
replicate-wild-ignore-table=mydb.sp_counter
#不需要同步的库
replicate-ignore-db = mysql,information_schema,performance_schema
key_buffer_size = 32m
read_buffer_size = 1m
read_rnd_buffer_size = 16m
bulk_insert_buffer_size = 64m
myisam_sort_buffer_size = 128m
myisam_max_sort_file_size = 10g
myisam_repair_threads = 1
myisam_recover
transaction_isolation = repeatable-read
innodb_additional_mem_pool_size = 16m
innodb_buffer_pool_size = 5734m
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1024m:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32m
innodb_log_file_size = 2g
innodb_log_files_in_group = 2
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_max_dirty_pages_pct = 50
innodb_flush_method = o_direct
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 0
[mysqldump]
quick
max_allowed_packet = 32m

2. スレーブ ライブラリ設定ファイル。複数のスレーブ設定ファイルは、サーバー ID を除いて同じである必要があります。 

[root@slavedb01 ~]# cat /etc/my.cnf
[client]
port= 3306
socket= /tmp/mysql.sock
[mysqld_multi]
# 指定相关命令的路径
mysqld   = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
##复制主库1的数据##
[mysqld2]
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
character-set-server = utf8mb4
#指定实例1的sock文件和pid文件
socket = /tmp/mysql.sock
pid-file=/data/mysql/mysql.pid
skip-name-resolv = 1
open_files_limit = 65535 
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 2048
tmp-table-size  = 32m
max-heap-table-size = 32m
query-cache-size = 0
external-locking = false
max_allowed_packet = 32m
sort_buffer_size = 2m
join_buffer_size = 2m
thread_cache_size = 51
query_cache_size = 32m
tmp_table_size = 96m
max_heap_table_size = 96m
query_cache_type=1
#指定第一个实例的错误日志和慢查询日志路径
log-error=/data/logs/mysqld.log
slow_query_log = 1
slow_query_log_file = /data/logs/slow.log
long_query_time = 0.1
# binary logging#
# 指定实例1的binlog和relaylog路径为/data/binlog目录
# 每个从库和每个实例的server_id不能一样。
server-id = 2
log-bin     = /data/binlog/mysql-bin
log-bin-index  =/data/binlog/mysql-bin.index
relay_log = /data/binlog/mysql-relay-bin
relay_log_index = /data/binlog/mysql-relay.index
master-info-file = /data/mysql/master.info
relay_log_info_file = /data/mysql/relay-log.info
read_only = 1
expire-logs-days = 14
sync_binlog = 1
#需要同步的库，如果不设置，默认同步所有库。
#replicate-do-db = xxx
#不需要同步的表
replicate-wild-ignore-table=mydb.sp_counter
#不需要同步的库
replicate-ignore-db = mysql,information_schema,performance_schema
binlog_cache_size = 4m
max_binlog_cache_size = 8m
max_binlog_size = 1024m
log_slave_updates =1
#binlog_format = row 
binlog_format = mixed
relay_log_recovery = 1
key_buffer_size = 32m
read_buffer_size = 1m
read_rnd_buffer_size = 16m
bulk_insert_buffer_size = 64m
myisam_sort_buffer_size = 128m
myisam_max_sort_file_size = 10g
myisam_repair_threads = 1
myisam_recover
#设置默认引擎为myisam，下面这些参数一定要加上。
default-storage-engine=myisam
default-tmp-storage-engine=myisam
#关闭innodb引擎
skip-innodb
innodb = off
disable-innodb
#设置sql_mode模式为no_auto_create_user
sql_mode = no_auto_create_user
#关闭innodb引擎
loose-skip-innodb
loose-innodb-trx=0 
loose-innodb-locks=0 
loose-innodb-lock-waits=0 
loose-innodb-cmp=0 
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0 
loose-innodb-cmpmem=0 
loose-innodb-cmpmem-reset=0 
loose-innodb-buffer-page=0 
loose-innodb-buffer-page-lru=0 
loose-innodb-buffer-pool-stats=0 
loose-innodb-metrics=0 
loose-innodb-ft-default-stopword=0 
loose-innodb-ft-inserted=0 
loose-innodb-ft-deleted=0 
loose-innodb-ft-being-deleted=0 
loose-innodb-ft-config=0 
loose-innodb-ft-index-cache=0 
loose-innodb-ft-index-table=0 
loose-innodb-sys-tables=0 
loose-innodb-sys-tablestats=0 
loose-innodb-sys-indexes=0 
loose-innodb-sys-columns=0 
loose-innodb-sys-fields=0 
loose-innodb-sys-foreign=0 
loose-innodb-sys-foreign-cols=0
 
 
##复制主库2的数据##
[mysqld3]
port = 3307
basedir = /usr/local/mysql
datadir = /data/mysql
character-set-server = utf8mb4
#指定实例2的sock文件和pid文件
socket = /tmp/mysql3.sock
pid-file=/data/mysql/mysql3.pid
skip-name-resolv = 1
open_files_limit = 65535 
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 2048
tmp-table-size  = 32m
max-heap-table-size = 32m
query-cache-size = 0
external-locking = false
max_allowed_packet = 32m
sort_buffer_size = 2m
join_buffer_size = 2m
thread_cache_size = 51
query_cache_size = 32m
tmp_table_size = 96m
max_heap_table_size = 96m
query_cache_type=1
log-error=/data/logs/mysqld3.log
slow_query_log = 1
slow_query_log_file = /data/logs/slow3.log
long_query_time = 0.1
# binary logging #
# 这里一定要注意，不能把两个实例的binlog和relaylog放到同一个目录，
# 这里指定实例2的binlog日志为/data/binlog2目录
# 每个从库和每个实例的server_id不能一样。
server-id = 22
log-bin     = /data/binlog2/mysql-bin
log-bin-index  =/data/binlog2/mysql-bin.index
relay_log = /data/binlog2/mysql-relay-bin
relay_log_index = /data/binlog2/mysql-relay.index
master-info-file = /data/mysql/master3.info
relay_log_info_file = /data/mysql/relay-log3.info
read_only = 1
expire-logs-days = 14
sync_binlog = 1
#不需要复制的库
replicate-ignore-db = mysql,information_schema,performance_schema
binlog_cache_size = 4m
max_binlog_cache_size = 8m
max_binlog_size = 1024m
log_slave_updates =1
#binlog_format = row 
binlog_format = mixed
relay_log_recovery = 1
key_buffer_size = 32m
read_buffer_size = 1m
read_rnd_buffer_size = 16m
bulk_insert_buffer_size = 64m
myisam_sort_buffer_size = 128m
myisam_max_sort_file_size = 10g
myisam_repair_threads = 1
myisam_recover
#设置默认引擎为myisam
default-storage-engine=myisam
default-tmp-storage-engine=myisam
#关闭innodb引擎
skip-innodb
innodb = off
disable-innodb
#设置sql_mode模式为no_auto_create_user
sql_mode = no_auto_create_user
#关闭innodb引擎，下面这些参数一定要加上。
loose-skip-innodb
loose-innodb-trx=0 
loose-innodb-locks=0 
loose-innodb-lock-waits=0 
loose-innodb-cmp=0 
loose-innodb-cmp-per-index=0
loose-innodb-cmp-per-index-reset=0
loose-innodb-cmp-reset=0 
loose-innodb-cmpmem=0 
loose-innodb-cmpmem-reset=0 
loose-innodb-buffer-page=0 
loose-innodb-buffer-page-lru=0 
loose-innodb-buffer-pool-stats=0 
loose-innodb-metrics=0 
loose-innodb-ft-default-stopword=0 
loose-innodb-ft-inserted=0 
loose-innodb-ft-deleted=0 
loose-innodb-ft-being-deleted=0 
loose-innodb-ft-config=0 
loose-innodb-ft-index-cache=0 
loose-innodb-ft-index-table=0 
loose-innodb-sys-tables=0 
loose-innodb-sys-tablestats=0 
loose-innodb-sys-indexes=0 
loose-innodb-sys-columns=0 
loose-innodb-sys-fields=0 
loose-innodb-sys-foreign=0 
loose-innodb-sys-foreign-cols=0
[mysqldump]
quick
max_allowed_packet = 32m
```

3. メイン ライブラリ sql_mode を設定します。デフォルトでは、mysql5.6 を有効にするには、スタートアップ ファイルに sql_mode を設定する必要があります。 

# cat /etc/init.d/mysqld
#other_args="$*"  # uncommon, but needed when called from an rpm upgrade action
      # expected: "--skip-networking --skip-grant-tables"
      # they are not checked here, intentionally, as it is the resposibility
      # of the "spec" file author to give correct arguments only.
#将上面默认的#other_args开启后改为
other_args="--sql-mode=no_auto_create_user"

4. メイン データベースとスレーブ データベースを開きます 

#主库
service mysqld start
#开启从库的二个实例
/usr/local/mysql/bin/mysqld_multi start 2
/usr/local/mysql/bin/mysqld_multi start 3

5. 2 つのマスター データベースでそれぞれレプリケーション アカウントを承認します 

#需要授权三个从库的ip可以同步
mysql> grant replication slave on *.* to rep@'192.168.1.3' identified by 'rep123';
mysql> grant replication slave on *.* to rep@'192.168.1.4' identified by 'rep123';
mysql> grant replication slave on *.* to rep@'192.168.1.5' identified by 'rep123';
mysql> flush privileges;

6. 3 つのスレーブでそれらを開きますデータベースをそれぞれ同期します。 

#进入第一个实例执行
$ mysql -s /tmp/mysql.sock
mysql> change master to master_host='192.168.1.1',master_user='rep',master_password='rep123',master_log_file='mysql-bin.000001',master_log_pos=112;
 
#进入第二个实例执行
$ mysql -s /tmp/mysql3.sock
mysql> change master to master_host='192.168.1.2',master_user='rep',master_password='rep123',master_log_file='mysql-bin.000001',master_log_pos=112;

7. データ同期のテスト

2 つのマスター データベースにそれぞれテーブルを作成し、データを挿入します。スレーブ データベースを確認すると、2 つのマスター データベースが同じデータベースに同期していることがわかります。スレーブデータベースのデータ。

8. 各スレーブ サーバーでタスク スケジュールを設定し、最初のインスタンスのテーブルを毎分更新します


# crontab -l
*/1 * * * * mysql -s /tmp/mysql.sock -e 'flush tables;'

Mysql5.6 の複数のマスターと 1 つのスレーブの落とし穴#1. mysql5.6 のデフォルト エンジンは innodb です。デフォルトで同期する場合は、マスターとスレーブの sql_mode モードで no_engine_substitution パラメータをオフにする必要があります。スレーブ データベース上の SQL への innodb 同期をオフにしない場合、innodb エンジンが見つからず、同期は失敗します。

2. mysql5.6 で複数のインスタンスが有効になっている場合、最初の起動時にデータベースのインストール ディレクトリ (/usr/local/mysql/) に my.cnf 設定ファイルが生成されます。デフォルトではそれが優先されます データベースのインストールディレクトリにある設定ファイルを読み込みます。その結果、複数のインスタンスは有効になりません。

以上がCentos7 で MySQL の複数のマスターと 1 つのスレーブを構成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。

声明：
この記事はyisu.comで複製されています。侵害がある場合は、admin@php.cn までご連絡ください。
前の記事：Linux で MySql ストレージ エンジンを確認する方法次の記事：Linux で MySql ストレージ エンジンを確認する方法

関連記事

続きを見る