There are good materials around for setting up multiple MySQL instance on local machine for data replication. But, the fact is that MySQL server only supports offline backup for INNODB type database. For online backup, Slave MySQL instance can be setup separately for backup purposes while Master instance can remain online to serve the users. Once Slave instance finishes its job, it can be startup again and synchronize with Master instance during the black-out period. This is a cost-effective solution for real-time MySQL backup.
The official manual have the details about this:
http://dev.mysql.com/doc/refman/5.5/en/replication.html
For basic tutorials, you may find it here:
http://forge.mysql.com/wiki/Replication/Tutorial
I love the presentation which bring a brief but clear information about what have to be done:
http://assets.en.oreilly.com/1/event/2/MySQL%20Replication%20Tutorial%20Presentation%202.pdf
To setup data replication on existing MySQL server, it is good to have a copy of /data directory first and then rename it to something like /data_slave and hook it up under the same parent folder of /data. This ensures a good start for setting up new database instance from the grounds up.
You may need to make a duplicate of my.ini which has already been used by the master MySQL instance. Rename that duplicate to something like, my2.ini and keep it in the same folder as the master my.ini file. Regarding the content of my2.ini, I suggest it is good to change the parameters first before starting those instances.
In my.ini, we have some basic settings like these:
[mysqld]
server-id=1
port = 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir="C:/xampp/mysql"
tmpdir="C:/xampp/tmp"
datadir="C:/xampp/mysql/data"
pid_file="mysql.pid"
innodb_data_home_dir = "C:/xampp/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "C:/xampp/mysql/data"
#innodb_log_arch_dir = "C:/xampp/mysql/data"
#Important setting for error prone builtin innodb plugin
innodb_use_sys_malloc = 0
innodb = ON
#Necessary for master instance
log-bin = mysql-bin
log_error="mysql_error.log"
In my2.ini, we should have changed those to:
[mysqld]
server-id=2
port = 3307
socket = "C:/xampp/mysql/mysql2.sock"
basedir="C:/xampp/mysql"
tmpdir="C:/xampp/tmp"
datadir="C:/xampp/mysql/data2"
pid_file="mysql2.pid"
innodb_data_home_dir = "C:/xampp/mysql/data2"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "C:/xampp/mysql/data2"
#innodb_log_arch_dir = "C:/xampp/mysql/data2"
#Important setting for error prone builtin innodb plugin
innodb_use_sys_malloc = 0
innodb = ON
#Necessary for slave instance
relay-log = mysqld-relay-bin
log_error="mysql_error.log"
The concept behind this is that we are going to setup two MySQL instances on the same machine, with the master instance using port 3306 and the slave instance using port 3307, for communication. For your convenience, you may change it to any other port available on the server.
Supposing we have already have the master instance of MySQL started and running, now we need another one acting as a slave instance for backup purposes.
In Windows environment, it is necessary to setup a new Windows service running at the background for better system management.
Before this, you may want to test if my2.ini ready to be a running service. To test this in a debugging mode, it is good to use mysqld-debug.exe to start a debugging process with the target configuration file (my2.ini).
mysqld-debug.exe --defaults-file="c:\mysql\bin\my2.ini"
Take a look at the error log file (
mysql_error.log) when anything goes wrong, you won't miss it.
When the test is finished and you are satisfied, you can create a new Windows service called mysql2 running in the background for easy management.
c:\mysql\bin\mysqld --install mysql2 --defaults-file="c:\mysql\bin\my2.ini"
After that, you may start this Windows service.
net start mysql2
It is recommended to use MySQL Workbench to manage those master and slave instance. For details, please read their manual.
You can download a copy of MySQL Workbench via:
http://www.mysql.com/downloads/workbench/
It is also recommended to download the no-install.zip copy on Windows platform. The reason is that .msi installer file will require an pre-installation of .NET framework 3.5 or above which may install extra pieces of software like IIS web server while you might already have one instance of Apache server installed on the machine. They both race for port 80, anyway.
Once you can start both MySQL instances, you may start issuing commands in MySQL consoles to make data replication happens among them.
It's good to take a look at this first:
http://dev.mysql.com/doc/refman/5.5/en/replication-howto-slaveinit.html
You may also check out the steps here:
http://www.howtoforge.com/mysql_database_replication_p2
During this time, you may want to dump the data out of master instance and then feed it into slave instance for easy startup. Take a look at this:
http://dev.mysql.com/doc/refman/5.5/en/replication-howto-masterstatus.html
To check the replication status on master instance, try the following commands in MySQL console:
mysql>unlock tables;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000020 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
| 2 | | 3306 | 1 |
+-----------+------+------+-----------+
1 row in set (0.00 sec)
Once the replication happens, it will continue even after a system restart on the server itself. Sounds like a magic!
