Jump to Navigation

020 - How to Set Up MySQL Replication

1. Creating a User for Replication
Each slave must connect to the master using a standard MySQL username and password,
so there must be a user account on the master that the slave can use to connect.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com IDENTIFIED by 'slavepass';

2.Setting the Replication Master Configuration
For replication to work you must enable binary logging on the master. If binary logging
is not enabled, replication will not be possible as it is binary log that is used to exchange
data between them master and slaves.
Each server within a replication group must be configured with unique server-id value.

[mysqld]

log-bin=mysql-bin

server-id=1

3. Setting the Replication Slave Configuration
The only option you must configure on the slave is to set the unique server ID. If this option
is not already set, or the current value conflicts with value that you have chosen for the master
server, then you should shut down your slave server, and edit the configuration to specify the
server ID.

[mysqld]

server-id=2

4. 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.

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

5. Creating a Data Snapshot Using
One way to create a snapshot of data in an existing master database is to use the mysqldump
tool. Once the data dump has been completed, you then import this data into the slave before
starting the replication process.

mysql> FLUSH TABLES WITH READ LOCK;

------------------------------------------------------------------------

shell> mysqldump --all-database --lock-all-tables >dbdump.db

or

shell> mysqldump --all-database --master-data > dbdump.db

6. Setting Up Replication with Existing Data
When setting up replication with existing data, you will need to decide how best to get the data
from the master to the slave before starting the replication service.

shell> mysql < fulldb.dump

or

shell tar xvf dbdump.tar

------------------------------------------------------------------------

mysql> START SLAVE;

7. Setting the Master Configuration on the Slave
To set up the slave to communicate with the master for replication, you must tell the slave
the necessary connection information. To do this, execute the following statement on the slave,
replacing the option values with the actual values relevant to your system.

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;



Main menu 2

Story | by Dr. Radut