So you have your mysql database running on lets call it SERVER01 and now you want to replicate data to SERVER02, maybe for failover, or just to take load off the master machine so you can do backups and reporting of the slave machine.
Setting everything up
On SERVER01 (this will be the replication Master) you will need to add the following to the my.cnf file:
bind-address=SERVER01's IP ADDRESS
log_bin=/var/log/mysql/mysql-bin.log
server-id=100
innodb_flush_log_at_trx_commit=1
sync_binlog=1
Make sure that skip-networking is disabled!
If you use functions and are sure they don’t modify data – are save for replication – you may want to add the following line the my.cnf as well:
log-bin-trust-function-creators=1
In mysql create a replication user and grant them access to all databases:
mysql> CREATE USER 'replusr'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL ON *.* to replusr@'10.0.0.2' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
Restart the MySql server to load the updated my.cnf file
On SERVER02 (this will be the replication slave) install mysql and add the following to the my.cnf file:
bind-address=SERVER02's IP ADDRESS
log_bin=/var/log/mysql/mysql-bin.log
server-id=101
Note that the server-id MUST be different from the master and any other slaves you set up for replication.
Starting replication
On SERVER01 (the replication Master)
Open two terminals with mysql. In the first terminal run:
mysql> FLUSH TABLES WITH READ LOCK;
In the second terminal run:
mysql> SHOW MASTER STATUS;
Write down the values for the column FILE and POSITION. You will need them later when setting up the Slave! (the values will look like mysql-bin.000001 and 106).
In the second terminal exist mysql and issue the following command to backup all databases, so you can import them on the slave machine:
shell> mysqldump --all-databases --master-data > dbdump.db
The backup may take a while if you have a big database. Once completed you can go to terminal one and close it, this will automatically release the lock and allow you to continue using the master normally.
Copy the backup file (dbdump.db) to the slave machine and continue with the next steps.
On SERVER02 (the replication Slave)
Start the MySql server with the –skip-slave-start option so that replication doesn’t start and import the database backup you copied from the master.
shell> mysqld_safe –-skip-slave-start
shell> mysql –u root –p < dbdump.db
Execute the following command on mysql to let the slave know where the master is and how to access it. This is also where you’ll be using the FILE and POSITION you wrote down earlier when starting replication on the master.
CHANGE MASTER TO MASTER_HOST='SERVER01', MASTER_USER='replusr', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
Stop the MySql server to exit safe mode.
Start the MySql server normally so the slave will start up and you’re done.
You can run the following command to check if the slave is up and running:
show status like ‘Slave%’;
You should see output like the following
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | ON |
+----------------------------+-------+
3 rows in set (0.00 sec)
Check that Slave_running is set to ON.
Recent Comments