I have had to install a few instances of MySQL replication, and its never as simple as you expect it to be. However, I have written a simple HOWTO to help you along if you need it.
Chapter 1 – Setting up the servers.
The master server is the main server you want replicated. There’s not a great deal to configure on this server, but it will have to have the MySQL service stopped whilst you acquire an up-to-date copy of the schemas.
First you need to tell the server that it is part of a “chain” of servers by assigning it a server-id. You can do this by editing the configuration file, usually MY.INI or MY.CNF In the [mysqld] section of the configuration file, enter the following two lines:
log-bin will be the basis for MySQL binary logging file-name. If you wanted to change it to suit your company then you can. MySQL will append incremental numbers to the end of the file-name If you don’t enter a value for this, then the server’s name will be used.
Please be aware, that if the skip-networking option is activated in the configuration file, then the master server will not be able to communicate with any other servers, for MySQL operations anyway.
For the slave server, all that needs setting is a server-id as in the MASTER server. The only rule here is that the server-id must be different from the MASTER server-id:
The skip-slave-start command will ensure that the MySQL slave does not start replicating before we’ve finished setting up all the parameters. We’ll remove this later. We do not need to specify the log-bin here, as it is handled by the MASTER server.
Restart the MySQL instance.
Chapter 2 – Copying the databases.
When the server restarts it will apply MASTER settings to the instance. This will start the replication logging. In order to get an accurate snap shot of the database we need to stop users from writing to it. We do this by running the following statement on the database:
FLUSH TABLES WITH READ LOCK;
This will ensure all outstanding queries are completed, and then no other changes can be made until we have finished configuring the servers.
We can now get the MASTER STATUS. This will tell us how many bytes of the data has been replicated to the bin-log as specified in the first chapter.
Run SHOW MASTER STATUS; on the master server and we’ll receive the following values:
File – This will be the bin-log that the server is currently outputting to.
Position – This is an integer value of what position the server has output to.
The last two values are settings that the master server uses to explicitly replicate or ignore specified schemas.
Write down the file and the position, as these will need entering on the slave server to set where it should start replicating from.
I prefer to use mysqldump to get the data dump. Its an extremely quick and effortless way of doing so. On the master server, using the command line, navigate to the mysqlbin folder and run the following statement:
mysqldump -u <user-name> -p<password> --all-databases > <file-name>
Replace <user-name> and <password> with the relevant user-name and password of a valid user. Note that there is no space between the -p and the password, unlike the user-name parameter. The <file-name> parameter specifies where to name the data dump.
Copy this dumped file to the slave server. And unlock the tables on the MASTER using:
On the slave server, enter the following at the command prompt:
mysql < <file-name> -u <user-name> -p<password>
This will import the file-name into the slave server, using the same syntax for user-names and passwords as the example above.
Chapter 3 – Setting up a replication user
Its always best to have a separate user for the replication account – it can be revoked at any time without causing disruption to any other users.
On the MASTER server, enter the following command:
GRANT REPLICATION SLAVE ON *.* TO '<user-name>'@'%' IDENTIFIED BY '<password>';
Replacing the user-name and password with the required details.
Chapter 4 – setting up the replication
There is an SQL command to configure the constants and variables that the slave will use to connect to the MASTER server.
CHANGE MASTER TO
MASTER_HOST = “192.168.1.1”,
MASTER_USER = “<user-name>”,
MASTER_PASSWORD = “<password>”,
MASTER_LOG_FILE = “<log-file>”,
MASTER_LOG_POS = x;
<user-name> and <password> need to be the user that we set up in Chapter 3.
The Master Host setting can be either the IP address or the fully qualified domain name, i.e. www.mydomain.com
The master log file and log pos are the values we recovered in Chapter 2 – SHOW MASTER STATUS. After this command has been run, we need to start the slave and let MySQL catch up with anything that has changed on the MASTER since the dump. This is performed with the following statement:
The slave can also be stopped with SLAVE STOP;.
Now, remember to remove the skip-slave-start parameter from the configuration file.