====== MySQL Proxy mit Load Balancing ======
//(Dieses Tutorial muss noch fertig übersetzt werden)//

Der MySQL-Proxy ist ein Mitelsmann, der zwischen [[software:datenbanken:mysql|MySQL]]-Konsole und Server arbeitet und hier verschiedene Aufgaben ausführen kann. Nähere Infos unter: 
  * http://www.oracle.com/technetwork/articles/dsl/white-php-part1-355135.html
  * http://www.oreillynet.com/pub/a/databases/2007/07/12/getting-started-with-mysql-proxy.html?page=4

==== Installation ====
Die Installation leht sich an die Anleitung von AgileTesting ((http://agiletesting.blogspot.se/2009/04/mysql-load-balancing-and-read-write.html, accessed 2012/11/02)) an:

=== Schritt 1: Download latest version ===
Die neuste Version kann unter http://dev.mysql.com/downloads/mysql-proxy/#downloads heruntergeladen werden. Obwohl noch Alpha, ist die Version gut nutzbar.

=== Schritt 2: Installation ===

Unpack the .tar.gz file in the directory ''/usr/local'' which is the ''ROOT_DIR'' in the Tutorial and set a symbolic link ''mysql-proxy -> mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit'' while using ''ln -sd''

<code bash>
ln -sd mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit mysql-proxy
</code>

=== Schritt 3: Konfiguration ===

create a bash script to start the MySQL proxy:

<code bash>
#!/bin/bash

#Addresses with ports of MySQL servers 
PROXY_ADDRESS=10.208.10.2:4040
MASTERDB=10.208.10.2:3306
SLAVEDB01=10.208.9.2:3306
SLAVEDB02=10.208.9.3:3306

#directory of the proxy binaries
ROOT_DIR=/usr/local/mysql-proxy

LUA_PATH="$ROOT_DIR/share/mysql-proxy/?.lua" $ROOT_DIR/bin/mysql-proxy \
	--daemon \
	--proxy-address=$PROXY_ADDRESS \
	--proxy-backend-addresses=$MASTERDB \
	--proxy-read-only-backend-addresses=$SLAVEDB01 \
	--proxy-lua-script=$ROOT_DIR/share/doc/mysql-proxy/rw-splitting.lua
	--log-file=/var/log/mysql-proxy.log \
	--log-level=message
</code>

=== Step 4: Rechte überprüfen ===

Because of the request is not executed from the webserver, but rather from the MySQL-Proxy we have to grant the user from all the hosts the right to access the database. This query must be executed on the master, on the slave it will be replicated.

<code mysql>
GRANT ALL ON vilbeforums.* TO 'vilbeforums'@'%' IDENTIFIED '***';
</code>

==== MySQL-Proxy Startoptionen====
<code>
Usage:
  mysql-proxy [OPTION...] - MySQL Proxy

Help Options:
  -?, --help                                      Show help options
  --help-all                                      Show all help options
  --help-proxy                                    Show options for the proxy-module

Application Options:
  -V, --version                                   Show version
  --defaults-file=<file>                          configuration file
  --verbose-shutdown                              Always log the exit code when shutting down
  --daemon                                        Start in daemon-mode
  --user=<user>                                   Run mysql-proxy as user
  --basedir=<absolute path>                       Base directory to prepend to relative paths in the config
  --pid-file=<file>                               PID file in case we are started as daemon
  --plugin-dir=<path>                             path to the plugins
  --plugins=<name>                                plugins to load
  --log-level=(error|warning|info|message|debug)  log all messages of level ... or higher
  --log-file=<file>                               log all messages in a file
  --log-use-syslog                                log all messages to syslog
  --log-backtrace-on-crash                        try to invoke debugger on crash
  --keepalive                                     try to restart the proxy if it crashed
  --max-open-files                                maximum number of open files (ulimit -n)
  --event-threads                                 number of event-handling threads (default: 1)
  --lua-path=<...>                                set the LUA_PATH
  --lua-cpath=<...>                               set the LUA_CPATH

proxy-module
  -P, --proxy-address=<host:port>                 listening address:port of the proxy-server (default: :4040)
  -r, 
  --proxy-read-only-backend-addresses=<host:port> address:port of the remote slave-server (default: not set)
  -b, --proxy-backend-a ddresses=<host:port>      address:port of the remote backend-servers (default: 127.0.0.1:3306)
  --proxy-skip-profiling                          disables profiling of queries (default: enabled)
  --proxy-fix-bug-25371                           fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
  -s, --proxy-lua-script=<file>                   filename of the lua script (default: not set)
  --no-proxy                                      don't start the proxy-module (default: enabled)
  --proxy-pool-no-change-user                     don't use CHANGE_USER to reset the connection coming from the pool (default: enabled)
  --proxy-connect-timeout                         connect timeout in seconds (default: 2.0 seconds)
  --proxy-read-timeout                            read timeout in seconds (default: 8 hours)
  --proxy-write-timeout                           write timeout in seconds (default: 8 hours)
</code>