Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.1.18
-
3 nodes multimaster(galera cluster) on Vmware environment with Oracle Linux 7
And 1 node slave on Vmware environment with Oracle Linux 7
Description
Hi,
I've this problem, the slave node replica(gtid mode) have status is uptodate :
[OK] This replication slave is running with the read_only option enabled.
[OK] This replication slave is uptodate with master.
But a level row is inconsistent :
DWHS (replica server have this tables ) :
SOURCE_XXX_LETTURE : row 3460
SOURCE_XXX_GESTORE: row 10685
DWH1,DWH2,DWH3 (Master nodes) :
SOURCE_XXX_LETTURE : row 5039
SOURCE_XXX_GESTORE: row 14.223
Actually the replica master has connect to :
2016-11-02 10:55:48 139671523392256 [Note] Slave I/O thread: connected to master 'repl_user@dwh2:3306',replication starts at GTID position '1-101-42535'
but i create a new table on dwh2.gamenet.psm the replication automatically creates the table created on the server dwh2, but if create a new table on dwh1 the table is created on dwh 2 server but but it is not applied on dwhs node.
but if on the node dwh1 insert a row on the new table created, the replica goes in the error state because in the replica slave the new table not exist and insert is lost.
All master server have the option log_slave_updates =1 on server.cnf.
The actual configuration of server.cnf on all master and slave :
|
DWH1 |
#
|
# These groups are read by MariaDB server.
|
# Use it for options that only the server (but not clients) should see
|
#
|
# See the examples of server my.cnf files in /usr/share/mysql/
|
#
|
# this is read by the standalone daemon and embedded servers
|
[server]
|
# this is only for the mysqld standalone daemon
|
[mysqld]
|
# * SLAVE CONFIGURATION
|
# #
|
gtid-domain-id = 1
|
server_id = 101
|
log_slave_updates = 1
|
log_bin = binlog
|
binlog_cache_size = 16M
|
#
|
# * Galera-related settings
|
#
|
large-pages
|
# BINARY LOGGING #
|
expire-logs-days = 14
|
sync-binlog = 1
|
|
|
# CACHES AND LIMITS #
|
tmp-table-size = 128M
|
max-heap-table-size = 128M
|
query-cache-type = 1
|
query-cache-size = 128M
|
query_cache_limit = 1M
|
max-connections = 150
|
thread-cache-size = 50
|
table-definition-cache = 50
|
table-open-cache = 1250 # table_open_cache is same as table_cache
|
sort_buffer_size = 16M # 16M per connessione
|
max_allowed_packet = 16M
|
join_buffer_size = 8M
|
# # INNODB #
|
innodb-flush-method = O_DIRECT
|
innodb-log-files-in-group = 2
|
innodb-log-file-size = 1G
|
innodb-flush-log-at-trx-commit = 1
|
innodb-file-per-table = 1
|
innodb-buffer-pool-size = 4G
|
innodb_file_format = barracuda
|
innodb_large_prefix = 1
|
log_bin_trust_function_creators = ON
|
innodb_file_per_table = 1
|
innodb_buffer_pool_instances = 4
|
# # MyISAM #
|
key_buffer_size = 1M
|
|
|
#
|
# # LOGGING #
|
log-output = FILE
|
# general_log
|
# general_log_file = /var/log/mysql/mysql_query.log
|
log-error = /var/log/mysql/mysql-error.log
|
log-queries-not-using-indexes = 1
|
slow-query-log = 1
|
slow-query-log-file = /var/log/mysql/mysql-slow.log
|
|
|
[galera]
|
# Mandatory settings
|
wsrep_on =ON
|
wsrep_provider =/usr/lib64/galera/libgalera_smm.so
|
wsrep_cluster_address =gcomm://172.16.31.15,172.16.31.16,172.16.31.17
|
wsrep_provider_options = "gmcast.mcast_addr=239.192.0.11"
|
#wsrep_cluster_address=gcomm://239.192.0.11
|
binlog_format = ROW
|
wsrep_forced_binlog_format = ROW
|
wsrep_gtid_domain_id = 1
|
wsrep_gtid_mode = 1
|
default_storage_engine = InnoDB
|
innodb_autoinc_lock_mode = 2
|
wsrep_sst_auth =repl_user:Replication$$2016!
|
wsrep_debug =OFF
|
wsrep_log_conflicts = 1
|
wsrep_slave_threads =4
|
#
|
# Allow server to accept connections on all interfaces.
|
#
|
bind-address=0.0.0.0
|
#
|
# Optional setting
|
|
|
wsrep_cluster_name="DWH_CLUSTER"
|
wsrep_node_address="172.16.31.15"
|
wsrep_node_name="dwh1"
|
wsrep_node_incoming_address="172.16.33.15"
|
wsrep_sst_method=rsync
|
wsrep_slave_threads=4
|
|
|
|
|
# this is only for embedded server
|
[embedded]
|
|
|
# This group is only read by MariaDB servers, not by MySQL.
|
# If you use the same .cnf file for MySQL and MariaDB,
|
# you can put MariaDB-only options here
|
[mariadb]
|
|
|
# This group is only read by MariaDB-10.1 servers.
|
# If you use the same .cnf file for MariaDB of different versions,
|
# use this group for options that older servers don't understand
|
[mariadb-10.1]
|
|
DWH2 |
#
|
# These groups are read by MariaDB server.
|
# Use it for options that only the server (but not clients) should see
|
#
|
# See the examples of server my.cnf files in /usr/share/mysql/
|
#
|
|
|
# this is read by the standalone daemon and embedded servers
|
[server]
|
|
|
# this is only for the mysqld standalone daemon
|
[mysqld]
|
# * SLAVE CONFIGURATION
|
# #
|
gtid-domain-id = 1
|
server_id = 201
|
log_slave_updates = 1
|
log_bin = binlog
|
binlog_cache_size = 16M
|
#
|
# * Galera-related settings
|
#
|
large-pages
|
# BINARY LOGGING #
|
expire-logs-days = 14
|
sync-binlog = 1
|
# CACHES AND LIMITS #
|
tmp-table-size = 128M
|
max-heap-table-size = 128M
|
query-cache-type = 1
|
query-cache-size = 128M
|
max-connections = 150
|
thread-cache-size = 50
|
table-definition-cache = 50
|
table-open-cache = 1250
|
sort_buffer_size = 16M
|
max_allowed_packet = 16M
|
join_buffer_size = 8M
|
# # INNODB #
|
innodb-flush-method = O_DIRECT
|
innodb-log-files-in-group = 2
|
innodb-log-file-size = 1G
|
innodb-flush-log-at-trx-commit = 1
|
innodb-file-per-table = 1
|
innodb-buffer-pool-size = 4G
|
innodb_file_format = barracuda
|
innodb_large_prefix = 1
|
log_bin_trust_function_creators = ON
|
innodb_file_per_table = 1
|
innodb_buffer_pool_instances = 4
|
#
|
# # LOGGING #
|
log-error = /var/log/mysql/mysql-error.log
|
log-queries-not-using-indexes = 1
|
slow-query-log = 1
|
slow-query-log-file = /var/log/mysql/mysql-slow.log
|
|
|
[galera]
|
# Mandatory settings
|
wsrep_on = ON
|
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
|
wsrep_cluster_address = gcomm://172.16.31.15,172.16.31.16,172.16.31.17
|
wsrep_provider_options = "gmcast.mcast_addr=239.192.0.11"
|
#wsrep_cluster_address=gcomm://239.192.0.11
|
binlog_format = ROW
|
wsrep_forced_binlog_format = ROW
|
wsrep_gtid_mode = 1
|
wsrep_gtid_domain_id = 1
|
default_storage_engine = InnoDB
|
innodb_autoinc_lock_mode = 2
|
wsrep_sst_auth = repl_user:Replication$$2016!
|
wsrep_slave_threads = 4
|
#
|
# Allow server to accept connections on all interfaces.
|
#
|
bind-address=0.0.0.0
|
#
|
# Optional setting
|
|
|
wsrep_cluster_name="DWH_CLUSTER"
|
wsrep_node_address="172.16.31.16"
|
wsrep_node_name="dwh2"
|
wsrep_node_incoming_address="172.16.33.16"
|
wsrep_sst_method=rsync
|
wsrep_slave_threads=4
|
|
|
|
|
# this is only for embedded server
|
[embedded]
|
|
|
# This group is only read by MariaDB servers, not by MySQL.
|
# If you use the same .cnf file for MySQL and MariaDB,
|
# you can put MariaDB-only options here
|
[mariadb]
|
|
|
# This group is only read by MariaDB-10.1 servers.
|
# If you use the same .cnf file for MariaDB of different versions,
|
# use this group for options that older servers don't understand
|
[mariadb-10.1]
|
|
DWH3 |
#
|
# These groups are read by MariaDB server.
|
# Use it for options that only the server (but not clients) should see
|
#
|
# See the examples of server my.cnf files in /usr/share/mysql/
|
#
|
|
|
# this is read by the standalone daemon and embedded servers
|
[server]
|
|
|
# this is only for the mysqld standalone daemon
|
[mysqld]
|
# * SLAVE CONFIGURATION
|
# # #
|
gtid-domain-id = 1
|
server_id = 301
|
log_slave_updates = 1
|
log_bin = binlog
|
binlog_cache_size = 16M
|
#
|
# * Galera-related settings
|
#
|
large-pages
|
# BINARY LOGGING #
|
expire-logs-days = 14
|
sync-binlog = 1
|
|
|
# CACHES AND LIMITS #
|
tmp-table-size = 128M
|
max-heap-table-size = 128M
|
query-cache-type = 1
|
query-cache-size = 512M
|
max-connections = 150
|
thread-cache-size = 50
|
table-definition-cache = 50
|
table-open-cache = 1250
|
sort_buffer_size = 16M
|
max_allowed_packet = 16M
|
join_buffer_size = 8M
|
# # INNODB #
|
innodb-flush-method = O_DIRECT
|
innodb-log-files-in-group = 2
|
innodb-log-file-size = 1G
|
innodb-flush-log-at-trx-commit = 1
|
innodb-file-per-table = 1
|
innodb-buffer-pool-size = 4G
|
innodb_file_format = barracuda
|
innodb_large_prefix = 1
|
log_bin_trust_function_creators = ON
|
innodb_file_per_table = 1
|
innodb_buffer_pool_instances = 4
|
#
|
# # LOGGING #
|
log-error = /var/log/mysql/mysql-error.log
|
log-queries-not-using-indexes = 1
|
slow-query-log = 1
|
slow-query-log-file = /var/log/mysql/mysql-slow.log
|
|
|
[galera]
|
# Mandatory settings
|
wsrep_on = ON
|
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
|
wsrep_cluster_address = gcomm://172.16.31.15,172.16.31.16,172.16.31.17
|
wsrep_provider_options = "gmcast.mcast_addr=239.192.0.11"
|
#wsrep_cluster_address=gcomm://239.192.0.11
|
binlog_format = ROW
|
wsrep_forced_binlog_format = ROW
|
wsrep_gtid_mode = 1
|
wsrep_gtid_domain_id = 1
|
default_storage_engine = InnoDB
|
innodb_autoinc_lock_mode = 2
|
wsrep_sst_auth = repl_user:Replication$$2016!
|
wsrep_slave_threads = 4
|
#
|
# Allow server to accept connections on all interfaces.
|
#
|
bind-address =0.0.0.0
|
#
|
# Optional setting
|
|
|
wsrep_cluster_name ="DWH_CLUSTER"
|
wsrep_node_address ="172.16.31.17"
|
wsrep_node_name ="dwh3"
|
wsrep_node_incoming_address ="172.16.33.17"
|
wsrep_sst_method =rsync
|
|
|
|
|
# this is only for embedded server
|
[embedded]
|
|
|
# This group is only read by MariaDB servers, not by MySQL.
|
# If you use the same .cnf file for MySQL and MariaDB,
|
# you can put MariaDB-only options here
|
[mariadb]
|
|
|
# This group is only read by MariaDB-10.1 servers.
|
# If you use the same .cnf file for MariaDB of different versions,
|
# use this group for options that older servers don't understand
|
[mariadb-10.1]
|
|
DWHS1 |
#
|
# These groups are read by MariaDB server.
|
# Use it for options that only the server (but not clients) should see
|
#
|
# See the examples of server my.cnf files in /usr/share/mysql/
|
#
|
|
|
# this is read by the standalone daemon and embedded servers
|
[server]
|
|
|
# this is only for the mysqld standalone daemon
|
[mysqld]
|
# * SLAVE CONFIGURATION
|
# #
|
gtid-domain-id = 1
|
server_id = 1001
|
log_slave_updates = 1
|
log_bin = binlog
|
relay-log = DWHS1-relay-bin
|
read_only
|
#
|
# * Galera-related settings
|
#
|
large-pages
|
# BINARY LOGGING #
|
expire-logs-days = 14
|
sync-binlog = 1
|
binlog_cache_size = 128M
|
# CACHES AND LIMITS #
|
tmp-table-size = 1G
|
max-heap-table-size = 512M
|
query-cache-type = 1
|
query-cache-size = 4G
|
query_cache_limit = 1M
|
max-connections = 40
|
thread-cache-size = 100
|
table-definition-cache = 100
|
table-open-cache = 1250 # table_open_cache is same as table_cache
|
sort_buffer_size = 64M # 32M per connessione
|
max_allowed_packet = 32M
|
join_buffer_size = 16M
|
# # INNODB #
|
innodb-flush-method = O_DIRECT
|
innodb-log-files-in-group = 2
|
innodb-log-file-size = 1G
|
innodb-flush-log-at-trx-commit = 1
|
innodb-file-per-table = 1
|
innodb-buffer-pool-size = 4G
|
innodb_file_format = barracuda
|
innodb_large_prefix = 1
|
log_bin_trust_function_creators = ON
|
innodb_file_per_table = 1
|
innodb_buffer_pool_instances = 4
|
# # MyISAM #
|
key_buffer_size = 2M
|
|
|
#
|
# # LOGGING #
|
log-output = FILE
|
# general_log
|
# general_log_file = /var/log/mysql/mysql_query.log
|
log-error = /var/log/mysql/mysql.log
|
log-queries-not-using-indexes = 1
|
slow-query-log = 1
|
slow-query-log-file = /var/log/mysql/mysql-slow.log
|
|
|
[galera]
|
# Mandatory settings
|
#wsrep_on=ON
|
#wsrep_provider=/usr/lib64/galera/libgalera_smm.so
|
#wsrep_cluster_address=gcomm://172.16.31.15,172.16.31.16,172.16.31.17
|
#wsrep_provider_options = "gmcast.mcast_addr=239.192.0.11"
|
#wsrep_cluster_address=gcomm://239.192.0.11
|
binlog_format=row
|
default_storage_engine=InnoDB
|
#innodb_autoinc_lock_mode=2
|
#wsrep_sst_auth=repl_user:Replication$$2016!
|
#wsrep_debug=OFF
|
#wsrep_log_conflicts = 1
|
#
|
# Allow server to accept connections on all interfaces.
|
#
|
bind-address=0.0.0.0
|
#
|
# Optional setting
|
|
|
#wsrep_cluster_name="DWH_CLUSTER"
|
#wsrep_node_address="172.16.31.15"
|
#wsrep_node_name="dwh1"
|
#wsrep_node_incoming_address="172.16.33.15"
|
#wsrep_sst_method=rsync
|
#wsrep_slave_threads=4
|
|
|
|
|
# this is only for embedded server
|
[embedded]
|
|
|
# This group is only read by MariaDB servers, not by MySQL.
|
# If you use the same .cnf file for MySQL and MariaDB,
|
# you can put MariaDB-only options here
|
[mariadb]
|
|
|
# This group is only read by MariaDB-10.1 servers.
|
# If you use the same .cnf file for MariaDB of different versions,
|
# use this group for options that older servers don't understand
|
[mariadb-10.1]
|
sorry for my bad english.
Thanks.
Regards.