How to set-up MySql replication

PDF

There are a number of different methods for setting up replication, and the exact method that you use will depend on how you are setting up replication, and whether you already have data within your master database.

Create a user for replication

The slaves need to connect to the master using an account granted ‘REPLICATION SLAVE’. As the username and password is stored in a plain-text file it is advisable to create a user that only has privileges for the replication.

 

GRANT REPLICATION SLAVE ON *.* TO 'user'@'host' IDENTIFIED BY 'pass';

Setup the master

For replication the master has to be enabled with binary-logging and the server has to be configured with a unique server-id in my.cnf.
Edit my.cnf and add the lines in the [mysqld] section:

log-bin=mysql-bin
server-id=1

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, also add:

innodb_flush_log_at_trx_commit=1
sync_binlog=1

Ensure that skip-networking is not enabled otherwise the replication will not work

Don’t restart the mysql daemon at this point!

Setup the slave

Edit my.cnf the lines in the [mysqld] section:

server-id=2

Each slave should have an unique server-id and should not be the same as the server-id of the master and should not be 0. You don’t have to enable binary logging on the slave. However, if you enable binary logging on the slave then you can use the binary log for data backups and crash recovery on the slave, and also use the slave as part of a more complex replication topology (for example, where the slave acts as a master to other slaves).

Obtaining the Master Replication Information

To configure replication on the slave you must determine the master's current point within the master binary log. You will need this information so that when the slave starts the replication process, it is able to start processing events from the binary log at the correct point.
If you have existing data on your master that you want to synchronize on your slaves before starting the replication process, then you must stop processing statements on the master, obtain the current position, and then dump the data, before allowing the master to continue executing statements. 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.
To get the master status information, follow these steps:

FLUSH TABLES WITH READ LOCK;

You have to leave the client from which you issued

FLUSH TABLES

running, otherwise the read lock will be released!

To determine the current binary log file name and offset on the master use:

SHOW MASTER STATUS;

If the master has been running previously without binary logging enabled, the log name and position values displayed by

SHOW MASTER STATUS

or

mysqldump --master-data

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.

This is the information you need to enable the slave to start reading from the binary log in the correct place to start replication. If you have existing data that needs be to synchronized with the slave before you start replication, leave the client running so that the lock remains in place and create a snapshot of the data on the server.

source: mysql.com

Tags:
 

Please login first before adding a comment.

Search






You are here: Home Howtos and FAQs MySql How to set-up MySql replication