Configure MySQL Database parameters for Galera replication in /etc/my.cnf file:
#Defines the path to the Galera Replication Plugin.
wsrep_provider=/usr/lib/libgalera_smm.so
#Semicolon (;) separated list of wsrep options,
#https://mariadb.com/kb/en/library/wsrep_provider_options/
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
# logical name for your cluster. You must use the same name for every node in your cluster.
wsrep_cluster_name="my_cluster"
#IP addresses of nodes in a comma separated list.
wsrep_cluster_address="gcomm://node1,node2,node3"
#method or script the node uses in a State Snapshot Transfer, mysqldump, rsync, #rsync_wan, xtrabackup,wsrep_%.sh For instance, giving the node a transfer method of #MyCustomSST causes it to look for wsrep_MyCustomSST.sh in /usr/bin
wsrep_sst_method=rsync
#logical name for the individual node.
wsrep_node_name=node1
#set the IP address for the individual node.wsrep_node_address="192.168.0.1"
#configuration file includes the conf.d/
!includedir /etc/mysql/conf.d/
#mysqld is not bound to 127.0.0.1. This is IP address for localhost. If the configuration #variable appears in the file, comment it out:
# bind-address = 127.0.0.1 OR bind-address=0.0.0.0
#InnoDB locking mode for generating auto-increment values is set to interleaved lock mode, #which is designated by a 2 value. Other modes may cause INSERT statements on tables #with AUTO_INCREMENT columns to fail.
innodb_autoinc_lock_mode = 2 - do not use table-level AUTO-INC lock, fastest and most #scalable lock mode, good during row based replication
#Ensure that the InnoDB log buffer is written to file once per second, rather than on each #commit, to improve performance.
innodb_flush_log_at_trx_commit=0 - Write contents of the log buffer to log file every #second.
innodb_buffer_pool_size=122M
#set to use row-level replication, as opposed to statement-level replication.
binlog_format=ROW
#Galera Cluster will not work with MyISAM or similar nontransactional storage engines
default_storage_engine=InnoDB
Using auto-increment with replication:
If you are using statement-based replication, set innodb_autoinc_lock_mode to 0 or 1 and use the same value on the master and its slaves.
Auto-increment values are not ensured to be the same on the slaves as on the master if you use innodb_autoinc_lock_mode = 2 (“interleaved”)
or configurations where the master and slaves do not use the same lock mode.
If you are using row-based or mixed-format replication, all of the auto-increment lock modes are safe, since row-based replication is not sensitive to the order of execution of the SQL statements (and the mixed format uses row-based replication for any statements that are unsafe for statement-based replication).
innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
In the case of statement-based replication, this means that when an SQL statement is replicated on a slave server, the same values are used for the auto-increment column as on the master server.
The result of execution of multiple INSERT statements is deterministic, and the slave reproduces the same data as on the master. If auto-increment values generated by multiple INSERT statements were interleaved, the result of two concurrent INSERT statements would be nondeterministic, and could not reliably be propagated to a slave server using statement-based replication.
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
This is the default lock mode. Simply put, this lock mode significantly improves scalability while being safe for use with statement-based replication.
Further, as with “traditional” lock mode, auto-increment numbers assigned by any given statement are consecutive. There is no change in semantics compared to “traditional” mode for any statement that uses auto-increment, with one important exception.
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
In this lock mode, no “INSERT-like” statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time.
This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.
innodb_flush_log_at_trx_commit:
The default value of 1 - the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.
With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once per second and the log file is flushed to disk.
With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second.
innodb_buffer_pool_size=122M
Ref.:
http://galeracluster.com/documentation-webpages/index.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes
https://mariadb.com/kb/en/library/galera-cluster-system-variables/
http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html
#Defines the path to the Galera Replication Plugin.
wsrep_provider=/usr/lib/libgalera_smm.so
#Semicolon (;) separated list of wsrep options,
#https://mariadb.com/kb/en/library/wsrep_provider_options/
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
# logical name for your cluster. You must use the same name for every node in your cluster.
wsrep_cluster_name="my_cluster"
#IP addresses of nodes in a comma separated list.
wsrep_cluster_address="gcomm://node1,node2,node3"
#method or script the node uses in a State Snapshot Transfer, mysqldump, rsync, #rsync_wan, xtrabackup,wsrep_%.sh For instance, giving the node a transfer method of #MyCustomSST causes it to look for wsrep_MyCustomSST.sh in /usr/bin
wsrep_sst_method=rsync
#logical name for the individual node.
wsrep_node_name=node1
#set the IP address for the individual node.wsrep_node_address="192.168.0.1"
#configuration file includes the conf.d/
!includedir /etc/mysql/conf.d/
#mysqld is not bound to 127.0.0.1. This is IP address for localhost. If the configuration #variable appears in the file, comment it out:
# bind-address = 127.0.0.1 OR bind-address=0.0.0.0
#InnoDB locking mode for generating auto-increment values is set to interleaved lock mode, #which is designated by a 2 value. Other modes may cause INSERT statements on tables #with AUTO_INCREMENT columns to fail.
innodb_autoinc_lock_mode = 2 - do not use table-level AUTO-INC lock, fastest and most #scalable lock mode, good during row based replication
#Ensure that the InnoDB log buffer is written to file once per second, rather than on each #commit, to improve performance.
innodb_flush_log_at_trx_commit=0 - Write contents of the log buffer to log file every #second.
innodb_buffer_pool_size=122M
#set to use row-level replication, as opposed to statement-level replication.
binlog_format=ROW
#Galera Cluster will not work with MyISAM or similar nontransactional storage engines
default_storage_engine=InnoDB
Using auto-increment with replication:
If you are using statement-based replication, set innodb_autoinc_lock_mode to 0 or 1 and use the same value on the master and its slaves.
Auto-increment values are not ensured to be the same on the slaves as on the master if you use innodb_autoinc_lock_mode = 2 (“interleaved”)
or configurations where the master and slaves do not use the same lock mode.
If you are using row-based or mixed-format replication, all of the auto-increment lock modes are safe, since row-based replication is not sensitive to the order of execution of the SQL statements (and the mixed format uses row-based replication for any statements that are unsafe for statement-based replication).
innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
In the case of statement-based replication, this means that when an SQL statement is replicated on a slave server, the same values are used for the auto-increment column as on the master server.
The result of execution of multiple INSERT statements is deterministic, and the slave reproduces the same data as on the master. If auto-increment values generated by multiple INSERT statements were interleaved, the result of two concurrent INSERT statements would be nondeterministic, and could not reliably be propagated to a slave server using statement-based replication.
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
This is the default lock mode. Simply put, this lock mode significantly improves scalability while being safe for use with statement-based replication.
Further, as with “traditional” lock mode, auto-increment numbers assigned by any given statement are consecutive. There is no change in semantics compared to “traditional” mode for any statement that uses auto-increment, with one important exception.
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
In this lock mode, no “INSERT-like” statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time.
This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.
innodb_flush_log_at_trx_commit:
The default value of 1 - the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.
With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once per second and the log file is flushed to disk.
With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second.
innodb_buffer_pool_size=122M
Ref.:
http://galeracluster.com/documentation-webpages/index.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes
https://mariadb.com/kb/en/library/galera-cluster-system-variables/
http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html
Comments
Post a Comment