README-wsrep 19.1 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
Codership Oy
http://www.codership.com
<info@codership.com>

DISCLAIMER

THIS SOFTWARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
IN NO EVENT SHALL CODERSHIP OY BE HELD LIABLE TO ANY PARTY FOR ANY DAMAGES
RESULTING DIRECTLY OR INDIRECTLY FROM THE USE OF THIS SOFTWARE.

Trademark Information.

MySQL is a trademark or registered trademark of Oracle and/or its affiliates.
Other trademarks are the property of their respective owners.

Licensing Information.

Please see file COPYING that came with this distribution

Source code can be found at
wsrep API:    https://launchpad.net/wsrep
MySQL patch:  https://launchpad.net/codership-mysql


ABOUT THIS DOCUMENT

This document covers installation and configuration issues specific to this
wsrep-patched MySQL distribution by Codership. It does not cover the use or
administration of MySQL server per se. The reader is assumed to know how to
install, configure, administer and use standard MySQL server version 5.1.xx.


                        MYSQL-5.5.x/wsrep-23.x

CONTENTS:
=========
1. WHAT IS WSREP PATCH FOR MYSQL
2. INSTALLATION
3. FIRST TIME SETUP
   3.1 CONFIGURATION FILES
   3.2 DATABASE PRIVILEGES
   3.3 CHECK AND CORRECT FIREWALL SETTINGS
   3.4 SELINUX
   3.5 APPARMOR
   3.6 CONNECT TO CLUSTER
4. UPGRADING FROM MySQL 5.1.x
5. CONFIGURATION OPTIONS
   5.1 MANDATORY MYSQL OPTIONS
   5.2 WSREP OPTIONS
6. ONLINE SCHEMA UPGRADE
   6.1 TOTAL ORDER ISOLATION (TOI)
   6.2 ROLLING SCHEMA UPGRADE (RSU)
7. LIMITATIONS


1. WHAT IS WSREP PATCH FOR MYSQL/INNODB

Wsrep API developed by Codership Oy is a modern generic (database-agnostic)
replication API for transactional databases with a goal to make database
replication/logging subsystem completely modular and pluggable. It is developed
63
with flexibility and completeness in mind to satisfy a broad range of modern
64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89
replication scenarios. It is equally suitable for synchronous and asynchronous,
master-slave and multi-master replication.

wsrep stands for Write Set REPlication.

Wsrep patch for MySQL/InnoDB allows MySQL server to load and use various wsrep
API implementations ("wsrep providers") with different qualities of service.
Without wsrep provider MySQL-wsrep server will function like a regular
standalone server.


2. INSTALLATION

In the examples below mysql authentication options are omitted for brevity.

2.1 Download and install mysql-wsrep package.

Download binary package for your Linux distribution from
https://launchpad.net/codership-mysql/

2.1.1 On Debian and Debian-derived distributions.

Upgrade from mysql-server-5.0 to mysql-wsrep is not supported yet, please 
upgrade to mysql-server-5.1 first.

If you're installing over an existing mysql installation, mysql-server-wsrep
90
will conflict with the mysql-server-5.1 package, so remove it first:
91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107

$ sudo apt-get remove mysql-server-5.1 mysql-server-core-5.1

mysql-server-wsrep requires psmisc and mysql-client-5.1.47 (or later).
MySQL 5.1 packages can be found from backports repositories.
For further information about configuring and using Debian or Ubuntu
backports, see:

* http://backports.debian.org

* https://help.ubuntu.com/community/UbuntuBackports

For example, installation of required packages on Debian Lenny:

$ sudo apt-get install psmisc
$ sudo apt-get -t lenny-backports install mysql-client-5.1

108
Now you should be able to install the mysql-wsrep package:
109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139

$ sudo dpkg -i <mysql-server-wsrep DEB>

2.1.2 On CentOS and similar RPM-based distributions.

If you're migrating from existing MySQL installation, there are two variants:

  a) If you're already using official MySQL-server-community 5.1.x RPM from
     Oracle:

     # rpm -e mysql-server

  b) If you're upgrading from the stock mysql-5.0.77 on CentOS:

     1) Make sure that the following packages are not installed:
     # rpm --nodeps --allmatches -e mysql-server mysql-test mysql-bench

     2) Install *official* MySQL-shared-compat-5.1.x from
        http://dev.mysql.com/downloads/mysql/5.1.html

Actual installation:

   # rpm -Uvh <MySQL-server-wsrep RPM>

   If this fails due to unsatisfied dependencies, install missing packages
   (e.g. yum install perl-DBI) and retry.

Additional packages to consider (if not yet installed):
   * galera (multi-master replication provider, https://launchpad.net/galera)
   * MySQL-client-community (for connecting to server and mysqldump-based SST)
   * rsync (for rsync-based SST)
140
   * mariabackup and nc (for mariabackup-based SST)
141 142 143 144 145 146 147 148 149 150 151 152

2.2 Upgrade system tables.

If you're upgrading a previous MySQL installation, it might be advisable to
upgrade system tables. To do that start mysqld and run mysql_upgrade command.
Consult MySQL documentation in case of errors. Normally they are not critical
and can be ignored unless specific functionality is needed.


3. FIRST TIME SETUP

Unless you're upgrading an already installed mysql-wsrep package, you will need
153
to set up a few things to prepare the server for operation.
154 155 156 157 158 159 160 161 162 163 164

3.1 CONFIGURATION FILES

* Make sure system-wide my.cnf does not bind mysqld to 127.0.0.1. That is, if
  you have the following line in [mysqld] section, comment it out:

  #bind-address = 127.0.0.1

* Make sure system-wide my.cnf contains "!includedir /etc/mysql/conf.d/" line.

* Edit /etc/mysql/conf.d/wsrep.cnf and set wsrep_provider option by specifying
165
  a path to the provider library. If you don't have a provider, leave it as it is.
166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269

* When a new node joins the cluster it'll have to receive a state snapshot from
  one of the peers. This requires a privileged MySQL account with access from
  the rest of the cluster. Edit /etc/mysql/conf.d/wsrep.cnf and set mysql
  login/password pair for SST, for example:

  wsrep_sst_auth=wsrep_sst:wspass

* See CONFIGURATION section below about other configuration parameters that you
  might want to change at this point.

3.2 DATABASE PRIVILEGES

Restart MySQL server and connect to it as root to grant privileges to SST
account (empty users confuse MySQL authentication matching rules, we need to
delete them too):

$ mysql -e "SET wsrep_on=OFF; DELETE FROM mysql.user WHERE user='';"
$ mysql -e "SET wsrep_on=OFF; GRANT ALL ON *.* TO wsrep_sst@'%' IDENTIFIED BY 'wspass'";

3.3 CHECK AND CORRECT FIREWALL SETTINGS.

MySQL-wsrep server needs to be accessible from other cluster members through
its client listening socket and through wsrep provider socket. See your
distribution and wsrep provider documentation for details. For example on
CentOS you might need to do something along these lines:

# iptables --insert RH-Firewall-1-INPUT 1 --proto tcp --source <my IP>/24 --destination <my IP>/32 --dport 3306 -j ACCEPT
# iptables --insert RH-Firewall-1-INPUT 1 --proto tcp --source <my IP>/24 --destination <my IP>/32 --dport 4567 -j ACCEPT

If there is a NAT firewall between the nodes, it must be configured to allow
direct connections between the nodes (e.g. via port forwarding).

3.4 SELINUX

If you have SELinux enabled, it may block mysqld from doing required operations.
You'll need to either disable it or configure to allow mysqld to run external
programs and open listen sockets at unprivileged ports (i.e. things that
an unprivileged user can do). See SELinux documentation about it.

To quickly disable SELinux:
1) run 'setenforce 0' as root.
2) set 'SELINUX=permissive' in  /etc/selinux/config

3.5 APPARMOR

AppArmor automatically comes with Ubuntu and may also prevent mysqld to from
opening additional ports or run scripts. See AppArmor documentation about its
configuration. To disable AppArmor for mysqld:

$ cd /etc/apparmor.d/disable/
$ sudo ln -s /etc/apparmor.d/usr.sbin.mysqld
$ sudo service apparmor restart


3.6 CONNECT TO CLUSTER

Now you're ready to connect to cluster by setting wsrep_cluster_address variable
and monitor status of wsrep provider:

mysql> SET GLOBAL wsrep_cluster_address='<cluster address string>';
mysql> SHOW STATUS LIKE 'wsrep%';


4 UPGRADING FROM MySQL 5.1.x

!!! THESE INSTRUCTIONS ARE PRELIMINARY AND INCOMPLETE !!!

1) BEFORE UPGRADE (while running 5.1.x):
   - comment out 'wsrep_provider' setting from configuration files
     (my.cnf and/or wsrep.cnf)
   - If performing a rolling upgrade on a running cluster, set
     wsrep_sst_method=mysqldump.
     You might also need to configure wsrep_sst_receive_address and
     wsrep_sst_auth appropriately. mysqldump is the only way to transfer data
     from 5.1.x to 5.5.x reliably.
   - remove innodb_plugin settings from configuration files.

2) Perform upgrade as usual:
   http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html
   Don't forget to run 'mysql_upgrade' command.

3) AFTER UPGRADING individual node:
   - uncomment 'wsrep_provider' line in configuration file.
   - restart the server and join the cluster.

4) AFTER UPGRADING the whole cluster:
   - revert to usual wsrep SST settings if not 'mysqldump'.


5. CONFIGURATION OPTIONS

5.1 MANDATORY MYSQL OPTIONS

binlog_format=ROW
   This option is required to use row-level replication as opposed to
   statement-level. For performance and consistency considerations don't change
   that. As a side effect, binlog, if turned on, can be ROW only. In future this
   option won't have special meaning.

innodb_autoinc_lock_mode=2
   This is a required parameter. Without it INSERTs into tables with
   AUTO_INCREMENT column may fail.
   autoinc lock modes 0 and 1 can cause unresolved deadlock, and make
270
   the system unresponsive.
271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301

innodb_locks_unsafe_for_binlog=1
   This option is required for parallel applying.

5.2 WSREP OPTIONS

All options are optional except for wsrep_provider, wsrep_cluster_address, and
wsrep_sst_auth.

wsrep_provider=none
   A full path to the library that implements WSREP interface. If none is
   specified, the server behaves like a regular mysqld.

wsrep_provider_options=
   Provider-specific option string. Check wsrep provider documentation or
   http://www.codership.com/wiki

wsrep_cluster_address=
   Provider-specific cluster address string. This is used to connect a node to
   the desired cluster. This option can be given either on mysqld startup or set
   during runtime. See wsrep provider documentation for possible values.

wsrep_cluster_name="my_wsrep_cluster"
   Logical cluster name, must be the same for all nodes of the cluster.

wsrep_node_address=
   An option to explicitly specify the network address of the node in the form
   <address>[:port] if autoguessing for some reason does not produce desirable
   results (multiple network interfaces, NAT, etc.)
   If not explicitly overridden by wsrep_sst_receive_address, the <address> part
   will be used to listen for SST (see below). And the whole <address>[:port]
302
   will be passed to the wsrep provider to be used as a base address in its
303 304 305 306 307 308
   communications.

wsrep_node_name=
   Human readable node name (for easier log reading only). Defaults to hostname.

wsrep_slave_threads=1
309
   The number of threads dedicated to the processing of writesets from other nodes.
310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328
   For best performance should be few per CPU core.

wsrep_dbug_option
   Options for the built-in DBUG library (independent from what MySQL uses).
   Empty by default. Not currently in use.

wsrep_debug=0
   Enable debug-level logging.

wsrep_convert_LOCK_to_trx=0
   Implicitly convert locking sessions into transactions inside mysqld. By
   itself it does not mean support for locking sessions, but it prevents the
   database from going into logically inconsistent state. Note however, that
   loading large database dump with LOCK statements might result in abnormally
   large transactions and cause an out-of-memory condition

wsrep_retry_autocommit=1
   Retry autocommit queries and single statement transactions should they fail
   certification test. This is analogous to rescheduling an autocommit query
329
   should it go into a deadlock with other transactions in the database lock
330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359
   manager.

wsrep_auto_increment_control=1
   Automatically adjust auto_increment_increment and auto_increment_offset
   variables based on the number of nodes in the cluster. Significantly reduces
   certification conflict rate for INSERTS.

wsrep_drupal_282555_workaround=1
   MySQL seems to have an obscure bug when INSERT into table with
   AUTO_INCREMENT column with NULL value for that column can fail with a 
   duplicate key error. When this option is on, it retries such INSERTs.
   Required for stable Drupal operation. Documented at:
      http://bugs.mysql.com/bug.php?id=41984
      http://drupal.org/node/282555

wsrep_causal_reads=0
   Enforce strict READ COMMITTED semantics on reads and transactions. May
   result in additional latencies. It is a session variable.

wsrep_OSU_method=TOI
   Online Schema Upgrade (OSU) can be performed with two alternative  methods:
   Total Order Isolation (TOI) runs DDL statement in all cluster nodes in
   same total order sequence locking the affected table for the duration of the
   operation. This may result in the whole cluster being blocked for the
   duration of the operation.
   Rolling Schema Upgrade (RSU) executes the DDL statement only locally, thus
   blocking only one cluster node. During the DDL processing, the node
   is not replicating and may be unable to process replication events (due to
   table lock). Once DDL operation is complete, the node will catch up and sync
   with the cluster to become fully operational again. The DDL statement or
360
   its effects are not replicated, so it is the user's responsibility to manually
361 362 363 364 365 366 367 368
   perform this operation on each of the nodes.

wsrep_forced_binlog_format=none
   Force every transaction to use given binlog format. When this variable is 
   set to something else than NONE, all transactions will use the given forced
   format, regardless of what the client session has specified in binlog_format.
   Valid choices for wsrep_forced_binlog_format are: ROW, STATEMENT, MIXED and
   special value NONE, meaning that there is no forced binlog format in effect.
369
   This variable was introduced to support STATEMENT format replication during
370 371 372 373 374 375 376 377 378 379 380 381 382
   rolling schema upgrade processing. However, in most cases ROW replication 
   is valid for asymmetrict schema replication.

State snapshot transfer options.

When a new node joins the cluster it has to synchronize its initial state with
the other cluster members by transferring state snapshot from one of them.
The options below govern how this happens and should be set up before attempting
to join or start a cluster.

wsrep_sst_method=rsync
   What method to use to copy database state to a newly joined node. Supported
   methods:
383 384 385 386 387 388 389 390
   - mysqldump:   slow (except for small datasets) but allows for upgrade
                  between major MySQL versions or InnoDB features.
   - rsync:       much faster on large datasets (default).
   - rsync_wan:   same as rsync but with deltaxfer to minimize network traffic.
   - mariabackup: very fast and practically non-blocking SST method based on
                  mariabackup tool (enhanced version of Percona's xtrabackup).

   (for mariabackup to work the following settings must be present in my.cnf
391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414
    on all nodes:
      [mysqld]
      wsrep_sst_auth=root:<root password>
      datadir=<path to data dir>
      [client]
      socket=<path to socket>
   )

wsrep_sst_receive_address=
   Address (hostname:port) at which this node wants to receive state snapshot.
   Defaults to mysqld bind address, and if that is not specified (0.0.0.0) -
   to the first IP of eth0 + mysqld bind port.
   NOTE: check that your firewall allows connections to this address from other
         cluster nodes.

wsrep_sst_auth=
   Authentication information needed for state transfer. Depends on the state
   transfer method. For mysqldump-based SST it is
   <mysql_root_user>:<mysql_root_password>
   and should be the same on all nodes - it is used to authenticate with both
   state snapshot receiver and state snapshot donor.

wsrep_sst_donor=
   A name of the node which should serve as state snapshot donor. This allows
415 416
   controlling which node will serve the state snapshot request. By default the
   most suitable node is chosen by the wsrep provider. This is the same as given in
417 418 419 420 421 422 423 424 425
   wsrep_node_name.


6. ONLINE SCHEMA UPGRADE

   Schema upgrades mean any data definition statements (DDL statemnents) run
   for the database. They change the database structure and are non-
   transactional.

426
   Release 22.3 brings a new method for performing schema upgrades. A user can
427 428 429 430 431 432 433 434 435 436 437 438 439 440 441
   now choose whether to use the traditional total order isolation or new
   rolling schema upgrade method. The OSU method choice is done by global
   parameter: 'wsrep_OSU_method'.

6.1 Total Order Isolation (TOI)

   With earlier releases, DDL processing happened always by Total Order
   Isolation (TOI) method. With TOI, the DDL was scheduled to be processed in
   same transaction seqeuncing 'slot' in each cluster node.
   The processing is secured by locking the affected table from any other use.
   With TOI method, the whole cluster has part of the database locked for the
   duration of the DDL processing.

6.2 Rolling Schema Upgrade (RSU)

442
   Rolling schema upgrade is a new DDL processing method, where DDL will be
443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470
   processed locally for the node. The node is disconnected of the replication
   for the duration of the DDL processing, so that there is only DDL statement
   processing in the node and it does not block the rest of the cluster. When
   the DDL processing is complete, the node applies delayed replication events
   and synchronizes back with the cluster.
   The DDL can then be executed cluster-wide by running the same DDL statement
   for each node in turn. When this rolling schema upgrade proceeds, part of
   the cluster will have old schema structure and part of the cluster will have
   new schema structure.


7. LIMITATIONS

1) Currently replication works only with InnoDB storage engine. Any writes to 
   tables of other types, including system (mysql.*) tables are not replicated. 
   However, DDL statements are replicated in statement level, and changes
   to mysql.* tables will get replicated that way.
   So, you can safely issue: CREATE USER...,
   but issuing: INSERT INTO mysql.user..., will not be replicated.

2) DELETE operation is unsupported on tables without primary key. Also rows in
   tables without primary key may appear in different order on different nodes.
   As a result SELECT...LIMIT... may return slightly different sets.

3) Unsupported queries:
    * LOCK/UNLOCK TABLES cannot be supported in multi-master setups.
    * lock functions (GET_LOCK(), RELEASE_LOCK()... )

471
4) Query log cannot be directed to a table. If you enable query logging,
472 473 474 475 476 477 478 479 480 481 482
   you must forward the log to a file:
       log_output = FILE
   Use general_log and general_log_file to choose query logging and the 
   log file name

5) Maximum allowed transaction size is defined by wsrep_max_ws_rows and
   wsrep_max_ws_size. Anything bigger (e.g. huge LOAD DATA) will be rejected.

6) Due to cluster level optimistic concurrency control, transaction issuing
   COMMIT may still be aborted at that stage. There can be two transactions.
   writing to same rows and committing in separate cluster nodes, and only one
483
   of them can successfully commit. The failing one will be aborted.
484 485 486 487 488
   For cluster level aborts, MySQL/galera cluster gives back deadlock error.
   code (Error: 1213 SQLSTATE: 40001  (ER_LOCK_DEADLOCK)).

7) XA transactions can not be supported due to possible rollback on commit.