Create MySQL database replication to scale-out website
-------------------------------------------------------------------

1. INTRODUCTION
-------------------------------------------------------------------
If you have a big website that has traffic volume that not can be
handled by only one MySQL database server then you can balance the
load on many database servers to better handle the high load. It is
easier to balance traffic load between many webservers than to 
balance traffic load between many database servers because the code
on one webserver does not change as frequently as the data in a 
database.

Microsoft Web Farm Framework for webservers makes it possible for
you to have automatic load balancing between many webservers. You
need to install Microsoft Web Farm Framework and ARR on your master 
webserver and configure it. The Web Farm Framework replicate the 
website to the webservers in the web farm and ARR takes care of 
the load balancing between webservers.

Database replication is used to make it possible to load balance
between several database servers. Database replication means that
we copy the structure and the content from a master (publisher) 
database to one or more slave (subscribers) database(s) so that
we can have the same content on many database servers. Microsoft 
SQL Server Standard and Enterprise supports "Merge Replication" 
that makes it possible to have all database
servers to write to each other (all servers is publishers). "Merge
replication" is not supported in MySQL Community Server.

MySQL Community Server supports one-way replication from master to 
slaves, but one slave can be the master for another slave. One other 
possibility to increase performance for inserts, updates and selects
in MySQL Community Server is with partitioning or sharding, with 
partitioning and sharding you split tables in different parts on the
same computer (partitioning) or on separate database servers 
(sharding).
-------------------------------------------------------------------

2. INSTALLATION
-------------------------------------------------------------------
If you not have done this before you have to download and install 
MySQL Community Server (www.mysql.com) on the computer that should 
act as a database server, we downloaded and installed the 5.1.51 
version. Download and install MySQL Connector Net on your webserver 
and your developing computer to be able to connect to a MySQL database 
from code. You should also download and install MySQL Workbench 
(GUI Tool) that are replacing MySQL Query Browser and MySQL 
Administrator (older MySQL tools), we downloaded and installed the 
5.2.28 version of MySQL Workbench.
-------------------------------------------------------------------

3. PREPARATION
-------------------------------------------------------------------
We are going to use three different database servers for our 
replication but we only have one computer so we have to create three
different MySQL Server Instances on our computer. One of the server
instances are going to be the master server and the database on the
master server are going to be replicated to the two slave servers. 
The master server should be used for insert and updates and the
master server should also send data to the slave servers for 
replication. The slave servers should be used for reading so we 
have to redirect the reads to the slave servers.
-------------------------------------------------------------------

4. SET UP THREE MySQL SERVER INSTANCES
-------------------------------------------------------------------
First we need to have three different data directories and we 
therefore browse to "C:\ProgramData\MySQL\MySQL Server 5.1". We
add a folder called "masterdata", add a second folder called
"slaveonedata" and add a third folder called "slavetwodata".
There is now four folders in "C:\ProgramData\MySQL\MySQL Server 5.1",
data, masterdata, slaveonedata and slavetwodata. Copy the "mysql"
folder in the data folder to all of the newly created folders so
that we have a system database in each database folder.

We also need to have 3 different folders for InnoDB and we therefore
browse to "C:\MySQL Datafiles" and add one folder called "master",
one called "slaveone" and one called "slavetwo". If you dont want
to use InnoDB you need to include "skip-innodb" in the 
configuration file for each server instance. It is important that
you add these folders because you will not be able to start 
Windows services for MySQL if you dont do this (i had to struggle
some hours with this). 

Secondly we need to have three different configuration files so we 
copy the my.ini file in "C:\Program Files\MySQL\MySQL Server 5.1" 
and rename the copy to "master.ini", we make a second copy
of my.ini and set the name to "slave1.ini" and finally we
make a third copy of my.ini and set the name to "slave2.ini". 
If you want remote access to the server instances you have to 
create exceptions for port 3307, 3308 and 3309 in Windows Firewall. 
Edit each configuration file so that each file has different ports 
(3307, 3308 and 3309) and data directories.

/////////// Changes in the master.ini file /////////////
[client]
port=3307

# The TCP/IP Port the MySQL Server will listen on
port = 3307

#Path to the database root
datadir = "C:/ProgramData/MySQL/MySQL Server 5.1/masterdata"

#*** INNODB Specific options ***
innodb_data_home_dir="C:/MySQL Datafiles/master"

/////////// Changes in the slave1.ini file /////////////
[client]
port=3308

# The TCP/IP Port the MySQL Server will listen on
port = 3308

#Path to the database root
datadir = "C:/ProgramData/MySQL/MySQL Server 5.1/slaveonedata"

#*** INNODB Specific options ***
innodb_data_home_dir="C:/MySQL Datafiles/slaveone"

/////////// Changes in the slave2.ini file /////////////
[client]
port=3309

# The TCP/IP Port the MySQL Server will listen on
port = 3309

#Path to the database root
datadir = "C:/ProgramData/MySQL/MySQL Server 5.1/slavetwodata"

#*** INNODB Specific options ***
innodb_data_home_dir="C:/MySQL Datafiles/slavetwo"

To be able to edit the files you must first copy them to the
desktop or one folder where you have permission to save the
edited files.

Finally you have to install three windows services for MySQL so
that you can run three MySQL server instances on the computer. Make
sure that you have stopped the first service "MySQL" before you
install the new ones. Open the "command prompt as a administrator 
to have the neccessary permissions to install a windows service.

C:\Windows\system32>cd..
C:\Windows>cd..
C:\>cd program files
C:\Program Files>cd MySQL
C:\Program Files\MySQL>cd MySQL Server 5.1
C:\Program Files\MySQL\MySQL Server 5.1>cd bin

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld --install MySQL1 --defaults-file="C:\Program Files\MySQL\MySQL Server 5.1\master.ini"
Service successfully installed.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld --install MySQL2 --defaults-file="C:\Program Files\MySQL\MySQL Server 5.1\slave1.ini"
Service successfully installed.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld --install MySQL3 --defaults-file="C:\Program Files\MySQL\MySQL Server 5.1\slave2.ini"
Service successfully installed.

If you want to remove (uninstall) a windows service you can type:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld --remove MySQL2
Service successfully removed.

When you have installed these services go to Control Panel>
Administrative Tools>Services and start the three windows services 
(MySQL1, MySQL2 and MySQL3) that you have installed.

5. CREATE THREE MySQL SERVER INSTANCES IN MySQL WORKBENCH
-------------------------------------------------------------------
Open "MySQL Workbench" and click on "New Server Instance" under 
"Server Administration. In the wizard for "Create New Server Instance"
select "localhost" and click next, give the new connection a 
"Connection Name" (masterconnection), we set the "Hostname" to 
"localhost", let the "Port" be 3307, let the "Username" be "root"and 
then click on "Next". In the next step there is a test for the database 
connection, we entered the password for the root user, succeded with the 
test and clicked "Next". The check for "MySQL configuration file" returned 
a error that the file doesnt exist but we ignored this and clicked "Next" 
until we reached the page to "Create the Instance Profile". We entered the 
"Server Instance Name" as "mysqlmaster@localhost".

Change name for the connections in "Manage Connections" under SQL Development
because the name is always set to localhost when we create a new MySQL
Server Instance. Repeat the step to create a MySQL Server Instance two times
more but give every instance a different connection (masterconnection, 
slaveoneconnection, slavetwoconnection), a different port (3307,3308,3309) and
a different name (mysqlmaster@localhost, mysqlslaveone@localhost, 
mysqlslavetwo@localhost).

Click on "Manage Server Instances", select the mysqlmaster@localhost instance,
click on the "System Profile" tab, set the "Installation Type" to Custom, set
the "Configuration File" to "C:\Program Files\MySQL\MySQL Server 5.1\master.ini",
set the value for "Configuration File Section" to "mysqld" and set the "Windows
Service Name" to "MySQL1". Do the same thing for the other two MySQL Server Instances 
and select the "slave1.ini" file for slave one and "slave2.ini" file for slave
two. Set the "Windows Service Name" to "MySQL2" for slave one and to "MySQL3"
for slave 2.
-------------------------------------------------------------------

6. CONFIGURE THE MASTER SERVER
-------------------------------------------------------------------
Replication in MySQL is based on the binary logging mechanism. The
master server writes updates and changes as events to the binary
log and slaves are configured to read from the binary log. If you 
are establishing a new MySQL master and one or more slaves, you need 
only set up the configuration, as you have no data to exchange. We 
are starting from scratch with a new master without a database.

Stop all MySQL Windows Services in Control Panel>Administrative Tools>
Services. Open "MySQL Workbench", double-click on "mysqlmaster@localhost 
under "Server Administration" and enter the password for the "root" 
user. Select "Configuration", click on the "Replication" tab, check the
checkbox for "server-id" and enter 1 as the id number. Select the
"Log Files" tab, check the checkbox before "log-bin" and enter
"master-bin.log" in the textbox. Check the checkbox before "binlog-
format" and select "MIXED", check the checkbox before "sync_binlog" 
and enter 1 as the value. Select the "InnoDB Parameters" tab, check 
the checkbox before "innodb_flush_log_at_trx_commit" and enter 1 
as the value. Select the "General" tab and make sure the checkbox 
before "skip-networking" is unchecked. Click on "Apply..." and then 
restart the windows service for the master server to activate binary 
logging.

The master-bin.log file will be saved in the datadirectory for the
master server, "C:/ProgramData/MySQL/MySQL Server 5.1/masterdata".
-------------------------------------------------------------------

7. CREATE A REPLICATION USER FOR THE MASTER SERVER
-------------------------------------------------------------------
Double-click on "mysqlmaster@localhost under "Server Administration" 
and enter the password for the "root" user. Click on "Accounts", 
click on "Add Account", set the "Login Name" to "replication, enter
a password and confirm the password, enter % after "Limit Connectivity
to Hosts Matching" to allow for remote access. Click on the 
"Administrative Roles" tab, check the checkbox for "ReplicationAdmin"
and click on the "Apply" button.
-------------------------------------------------------------------

8. CONFIGURE SLAVE SERVERS
-------------------------------------------------------------------
Double-click on mysqlserverone@localhost under "Server Administration" 
in "MySQL Workbench" and enter the password for the root user. Click 
on "Configuration", select the "Replication" tab, check the checkbox 
before "server-id" and enter 2 as the value. Scroll down to "Slave 
default connection values", check the checkbox before "master-host" 
and enter the Host name or IP adress for the master server
(localhost in our case). Check the checkbox before "master-info-file"
and enter "master.info" in the textbox, check the checkbox before
"master-password" and enter the password for the "replication" user,
check the checkbox before "master-port" and enter 3307 as the 
master servers port, check the checkbox before "master-user" and
enter replication (login name for the replication user) in the textbox
and then click on the "Apply..." button.

Double-click on mysqlservertwo@localhost under "Server Administration" 
in "MySQL Workbench" and enter the password for the root user. Click on 
"Configuration", select the "Replication" tab, check the checkbox before 
"server-id" and enter 3 as the value. Scroll down to "Slave default 
connection values", check the checkbox before "master-host" and enter 
the Host name or IP adress for the master server (localhost in our case). 
Check the checkbox before "master-info-file" and enter "master.info" in 
the textbox, check the checkbox before "master-password" and enter the 
password for the "replication" user, check the checkbox before "master-port" 
and enter 3307 as the master servers port, check the checkbox before 
"master-user" and enter replication (login name for the replication user) 
in the textbox and then click on the "Apply..." button.
-------------------------------------------------------------------

9. START THE REPLICATION PROCESS
-------------------------------------------------------------------
Open the Microsoft Management Console that you can find when browsing
to Control Panel>Administrative Tools>Services and start all the 
MySQL windows services (MySQL1, MySQL2, MySQL3) that are included
in the replication setup. When you start all the Windows services
you can see that log-files have been added in each datadirectory.
If you want to restart the replication process you can stop all
Windows Services for MySQL and then delete all binary log files
in the datadirectories for masterdata, slaveonedata and slavetwodata.
-------------------------------------------------------------------

10. CREATE A DATABASE ON THE MASTER SERVER
-------------------------------------------------------------------
Create a new database or import a existing database to the master
server. We created a new database by double-clicking on 
"masterconnection" under "SQL Development", enter the password for
the root user and "Create a new Schema". 

Now when the replication process is in action you will see that
everything you do regarding databases on the master server will
be replicated to the slave servers.
-------------------------------------------------------------------

11. REMOTE ACCESS TO DATABASE SERVERS, LOAD BALANCING AND FAILOVER
-------------------------------------------------------------------
To have remote access to your MySQL database servers you need to add 
one account for each MySQL database server that have the permission 
to connect from a remote host (computer). In MySQL Workbench you also
need to make sure that "skip-networking" is unchecked under the
"General" tab in "Configuration". It is also important that you add
exceptions in the Firewall for the ports that are used for the
MySQL Server Instances on the database server.

In MySQL Workbench we double-click on one "MySQL Server Instance" 
under "Server Administration" and enter the password for the root 
user. We click on "Accounts" and then on the button "Add Account", 
enter a "Login Name" and a "Password". In the textbox after "Limit 
Connectivity to Hosts Matching" you can restrict which computers 
(IP, Host name) that should be able to have remote access to the
MySQL Server Instance but we entered the % sign to not limit 
access, we clicked on the "Administrative Roles" tab, checked the 
checkbox before "DBA" and clicked on the "Apply" button. We did not
restrict remote access to certain computers in "Limit Connectivity 
to Hosts Matching" and therefore left the "%" sign in this textbox 
unchanged, you can limit access to the database server in the Firewall 
instead.

When you have more than one database server you need to implement
some rules to redirect database connections from your webservers
to different database servers. The rules to redirect a connection
to different database servers are called load balancing and failover. 
You can code the rules for database connections in your application by 
having multiple connection strings to select from but the 
recommended way to handle load balancing for database connections
is to use a software that handles automatic load balancing because
it gives you the greatest flexibility when you want to scale out
your website.

When you are using a software for automatic load balancing you will
have one connection string in your application to this load balancing
software and then there are rules in the load balancing software to
redirect the connection to different database servers. If you have
Windows Server on your webservers then you can use the Network Load
Balancing (NLB) application for database load balancing.

We dont have Windows Server on our computer so we are going to test
MySQL Proxy that is a program that sits between your client and MySQL 
server(s) that can monitor, analyze or transform their communication.
MySQL Proxy is a new software released in a alpha version and therefore
not recommended to use in a production environment.
-------------------------------------------------------------------

12. USE MySQL PROXY FOR LOAD BALANCING AND FAILOVER
-------------------------------------------------------------------
Download MySQL Proxy from the official MySQL website (www.mysql.com)
and extract the zip-package on your computer. We downloaded the
0.8.1 alpha version of MySQL Proxy.

The first thing to do is to create a configuration file for MySQL 
Proxy because this gives you the greatest amount of flexibility
to run MySQL Proxy on your webserver. The configuration file is
then used as the defaults file for a Windows Service that starts
automatically and that makes it possible for you to do changes
in the configuration of MySQL Proxy by doing changes in the 
configuration file. When you do changes in the configuration file
you need to restart the Windows service for MySQL Proxy so that
the service can use the new values. 

We created a configuration file that we named "MySQL-Proxy-config.ini". 
In the configuration file we declared settings for a log-file, the 
addresses to our three MySQL server instances and some other settings
that you can see in the configuration file. We did not find the 
"admin.lua" file in the installed package but we found this file in 
the source package and copied it to the folder according to the path 
in the configuration file.

When we have created a configuration file for MySQL Proxy we are ready
to install MySQL Proxy as a Windows Service. We opened the command prompt
as a "Administrator" (run as administrator) to be able to install a
Windows Service and then we typed the rows below and pressed "Enter" 
after each row.

C:\Windows\system32>cd..
C:\Windows>cd..
C:\>sc create "MySqlProxyOne" DisplayName= "MySQL Proxy One" start= "auto" binPath= "\"D:\mysql-proxy-0.8.1-win32-x86\bin\mysql-proxy-svc.exe\" --defaults-file=\"D:\mysql-proxy-0.8.1-win32-x86\MySQL-Proxy-config.ini""
[SC] CreateService SUCCESS

The spaces after each alternative is important and if you dont
have a space after the = sign the Windows service will not be
installed. You can set up multiple MySQL Proxy services on the same 
computer (providing they are configured to listen on different ports 
and/or IP addresses).

If you want to remove a MySQL Proxy service then you can type the 
following in the command prompt (dos-prompt).

C:\>sc delete MySqlProxyOne
[SC] DeleteService SUCCESS

Start the MySQL Proxy One service from the command prompt or by browsing
to Control Panel>Administrative Tools>Services. In the command prompt you
start the service by typing: C:\>net start MySqlProxyOne. You can look
in the log-file (D:\mysql-proxy-0.8.1-win32-x86\mysql-proxy.log) to see 
when MySQL Proxy is started and stopped.

To test MySQL Proxy on one computer we created one website that could
handle inserts and selects and then copied this website 3 times to
get Website1, Website2, Website3 and Website4. In each website we had
a connection string to MySQL Proxy like stated below.

<connectionStrings>
	<add name="ConnectionString" connectionString="server=localhost; port=4040;database=customerdb;uid=firstdbuser;pwd=qqq" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>

To start the test we first opened MySQL Workbench and connected to our
three MySQL server instances under "Server Administration" so that we 
can see every connection under "Connections". Thereafter we opened 
"Microsoft Visual Web Developer Express" four times and opened 
Website1 in the first window, Website2 in the second window, Website3
in the third window and Website4 in the fourth window. We started each
website by clicking on the playbutton (Start Debugging) and this means
that we get four different connections to the database. We was looking
in MySQL Workbench to see to which MySQL server instance each connection
was redirected.

We first tested to use Lua scripts by referencing to them in the 
configuration file but was not able to get any of them to work. We tested
rw-splitting.lua, ro-balance.lua and tutorial-keepalive.lua. When we 
tested tutorial-keepalive.lua we were able to connect to database servers
but the first connection was redirected to master, the second to slave one,
the third to master and thereafter was all connections redirected to the
master server. I would have been great to be able to use read/write 
splitting to automatically redirect inserts, updates and deletes to the
master server and load balance the reads between all three servers.

We gave up to test Lua-scripts and did some test where we commented
out the Lua script row in the configuration file and put all the 
server addresses behind "proxy-backend-addresses=" in the configuration
file. This test turned out to work and the first connection was 
redirected to master, the second connection to slave one, the third
connection to slave two and the fourth connection to master. This 
configuration of MySQL Proxy means that we need to have two 
connection strings in the "web.config" file because we only can do
writes to the master server according to our replication set-up.

<connectionStrings>
	<add name="WriteConnString" connectionString="server=localhost;port=3307;database=customerdb;uid=firstdbuser;pwd=qqq" providerName="MySql.Data.MySqlClient"/>
	<add name="ReadConnString" connectionString="server=localhost;port=4040;database=customerdb;uid=firstdbuser;pwd=qqq" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>

MySQL Proxy is just in a Alpha-stage and it will be more powerful
when its more stable and when we are able to use Lua-scripts to
redirect database connections. You can read more about MySQL Proxy
at http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy.html. 
-------------------------------------------------------------------

