This guide will walk you through creating MySQL replication on existing live MySQL servers on Windows. While some downtime is required, we try to keep this to a minimum. We’ll start by creating a simple master-slave relationship, however this can be enabled for multiple slaves.
Important:
Make sure you have a good backup of your database and/or server
This guide assumes you have a functional MySQL Server (master) and an empty slave with MySQL installed, configured and running.
I’m using F: as a logging destination, however you may be using a different drive or default logging location.
This guide will include options for exporting data with and without taking a non-locking dump of the database. By taking a non-locking dump, you allow the current database to stay operational (less downtime) but run the risk of missing data if you don’t act quickly. By taking a locking dump of the database, you ensure no new data is written to the database but any service that requires writes to the database will not work.
1) On the master, you must enable binary logging and configure a unique server ID. This might require a service restart. Binary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves. If binary logging is not enabled, replication will not be possible. Each server within a replication group must be configured with a unique server ID. This ID is used to identify individual servers within the group.
Stop the MySQL service
Create a backup of my.ini (Windows default location: C:/ProgramData/MySQL)
Add the following options to the configuration file under the [mysqld] section.
[mysqld]
log-bin="F:/mysql_logs/servername-bin.log"
server-id=1For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should set the following
innodb_flush_log_at_trx_commit=1
sync_binlog=1If you plan on taking a non-locking dump of the database for minimal downtime during slave creation
binlog-format=mixedEnsure that skip-networking is not enabled in my.ini
Start the MySQL service
2) On each slave that you want to connect to the master, you must configure a unique server ID that is higher than the master’s ID.
Stop the MySQL service
Create a backup of my.ini (Windows Default Location: C:/ProgramData/MySQL)
Set a unique server ID, different from the master or any other slave
[mysqld]
server-id=2You do not have to enable binary logging on the slave for replication to be enabled. However, if you enable binary logging on the slave you can use the binary log for data backups and crash recovery on the slave. If the slave ever gets promoted to master, this will allow for other slaves to be created based off the new master.
innodb_flush_log_at_trx_commit=1
sync-binlog=1For minimal downtime, enable mixed binary logging and relay logging. The read-only option is turned on here to prevent accidental writes to the slave (optional). In the event this slave ever becomes a master this will need to be removed.
binlog-format=mixed
relay-log="F:/mysql_logs/servername-relay.log"
log-slave-updates=1
read-only=1Ensure that skip-networking is not enabled in my.ini
Start the MySQL service
3) You’ll want to create a separate user that will be used by your slaves to authenticate with the master to read the binary log for replication. Even though this is optional, it’s strongly recommended, each slave must connect to the master using a MySQL user name and password. There must be a user account on the master that the slave can use to connect. Any account can be used for this operation, providing it has been granted the REPLICATION SLAVE privilege.
Create MySQL User
mysql> CREATE USER 'srv-repl'@'%' IDENTIFIED BY 'slavepass';Grant permissions to User
mysql> GRANT REPLICATION SLAVE ON *.* TO 'srv-repl'@'%';Verify user authentication by logging in
Locally: mysql –defaults-file=E:\DATA\my.ini -u -p
Remotely: mysql –user= –host= -p
4) Skip this step if you plan on exporting data without locking tables. If you have existing data on your master that you want to synchronize on your slaves before starting the replication process, you must stop processing statements on the master. Before permitting the master to continue executing statements, obtain its current binary log coordinates and dump its data. If you do not stop the execution of statements, the data dump and the master status information that you use will not match and you will end up with inconsistent or corrupted databases on the slaves. You will need this information when configuring the slave so that the slave knows where within the binary log to start executing events.
Flushing tables with read lock will block all write/commit statements, close open tables and flush the query cache
Flush tables with read lock.
mysql> FLUSH TABLES WITH READ LOCK;In a different session on the master
mysql> SHOW MASTER STATUS;The File column shows the name of the log file and the Position shows the position within the file. For example, the binary log file would look like mysql-bin.000003 and the log position could be 73. Record these values.
Note: If the master has been running previously without binary logging enabled, the log file name and position values will be empty. In that case, the values that you need to use later when specifying the slave’s log file and position are the empty string (”) and 4.
5) One way to create a snapshot of the data in an existing master database is to use the mysqldump tool to create a dump of all the databases you want to replicate. Once the data dump has been completed, you can then import this data into the slave before starting the replication process. This dumps all databases to a file named dbdump.db, and includes the –master-data option which automatically appends the CHANGE MASTER TO statement required on the slave to start the replication process:
Dump tables with lock (will result in DB downtime)
cmd> mysqldump --all-databases --master-data > dbdump.dbDump without locking tables (will not result in downtime)
cmd> mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A -uroot -p > C:\dump.sqlMake sure to examine the head of the file and record the values for MASTER_LOG_FILE and MASTER_LOG_POS you will need them for later
PowerShell> Get-Content C:\dump.sql -TotalCount 506) On the slave, you’ll need to configure settings for connecting to the master, such as the host name, login credentials, and binary log file name and position.
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;Stop the MySQL service
Start the MySQL service using the –skip-slave-start (this may have to be done through the command line, you may also need to leave out the MySQL Service Name)
cmd> mysqld.exe --defaults-file="C:\ProgramData\MySQL\my.ini" --skip-slave-start Import the dump file
cmd> mysql -u root -p < dump.db dump.sqlShutdown the service
cmd> mysqld -shutdownYou may need to recreate the service if it doesn’t start
cmd> sc delete MySQL
cmd> "C:\Program Files\MySQL\bin\mysqld.exe" -installConfigure the ‘Path to Executable’ by setting the following registry entry
HKLM/SYSTEM/CurrentControlSet/Services/MySQL56/Image/Path
"C:\Program Files\MySQL\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\my.ini" MYSQL567) Start the slave
mysql> START SLAVE;8) After you have performed this procedure, the slave should connect to the master and catch up on any updates that have occurred since the snapshot was taken. Once a slave is replicating, you can find in its data directory one file named master.info and another named relay-log.info. The slave uses these two files to keep track of how much of the master’s binary log it has processed. Do not remove or edit these files unless you know exactly what you are doing and fully understand the implications. Even in that case, it is preferred that you use the CHANGE MASTER TO statement to change replication parameters. The slave will use the values specified in the statement to update the status files automatically.
You can check the slave status
mysql> SHOW SLAVE STATUSA good configuration should look like this.
Review the error log if there are errors (HOSTNAME.err)
9) If you locked tables and everything looks good on replication, unlock tables on the master
mysql> UNLOCK TABLES;
If the above command doesn’t work, you may need to restart the MySQL service.
Unfortunately this entire process is a bit convoluted, as I ran into some random issues along the way. Usually a simple Google search of your problem will help, however, you should be maintaining backups of all servers and databases just in case anything goes wrong.