How To Set Up Database Replication In MySQL 5.0

[admin]

This tutorial describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.

1- Setting the Replication Master Configuration
On a replication master, you must enable binary logging and establish a unique server ID. If this has not already been done, this part of master setup requires a server 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, and must be a positive integer between 1 and (232)–1. How you organize and select the numbers is entirely up to you.
To configure the binary log and server ID options, you will need to shut down your MySQL server and edit the my.cnf or my.ini file. Add the following options to the configuration file within the [mysqld] section. If these options already exist, but are commented out, uncomment the options and alter them according to your needs. For example, to enable binary logging using a log file name prefix of mysql-bin, and configure a server ID of 1, use these lines:

Code: [Select]

[mysqld]
log-bin=mysql-bin
server-id=1After making the changes, restart the server.

Code: [Select]

/etc/rc.d/init.d/mysql restart

2- Then we log into the MySQL database as root and create a user with replication privileges:

Code: [Select]

mysql -u root -p
Enter password:Now we are on the MySQL shell.

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>'; (Replace <some_password> with a real password!)
FLUSH PRIVILEGES;

Next (still on the MySQL shell) do this:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The last command will show something like this:

Code: [Select]

+---------------+----------+--------------+------------------+
| File                | Position   | Binlog_do_db  | Binlog_ignore_db   |
+---------------+----------+--------------+------------------+
| mysql-bin.006  | 183         |                    |                          |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Write down this information, we will need it later on the slave!

If the master has been running previously without binary logging enabled, the log file 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.

You now have 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 then proceed to Creating a Data Snapshot Using mysqldump”. The idea here is to prevent any further changes so that the data copied to the slaves is in synchrony with the master.
If you are setting up a brand new master and slave replication group, you can exit the first session to release the read lock.

So open a new session while keeping the lock and Creating a Data Snapshot Using mysqldump.
4- Creating a Data Snapshot Using mysqldump
To obtain a snapshot of the data using mysqldump:
1.If you have not already locked the tables on the server to prevent statements that update data from executing:
Start a session on the server by connecting to it with the command-line client, and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

Code: [Select]

mysql> FLUSH TABLES WITH READ LOCK;Remember to use SHOW MASTER STATUS and record the binary log details for use when starting up the slave. The point in time of your snapshot and the binary log position must match.
2.In another session, use mysqldump to create a dump either of all the databases you want to replicate, or of selected individual databases. For example:

Code: [Select]

shell> mysqldump --all-databases --lock-all-tables >dbdump.dbAn alternative to using a bare dump, is to use the --master-data option, which automatically appends the CHANGE MASTER TO statement required on the slave to start the replication process.

Code: [Select]

shell> mysqldump --all-databases --master-data >dbdump.db3.In the client where you acquired the read lock, release the lock:

Code: [Select]

mysql> UNLOCK TABLES;Alternatively, exit the first session to release the read lock.
When choosing databases to include in the dump, remember that you will need to filter out databases on each slave that you do not want to include in the replication process.
You will need either to copy the dump file to the slave, or to use the file from the master when connecting remotely to the slave to import the data.


5- Setting the Replication Slave Configuration
On a replication slave, you must establish a unique server ID. If this has not already been done, this part of slave setup requires a server restart.
If the slave server ID is not already set, or the current value conflicts with the value that you have chosen for the master server, you should shut down your slave server and edit the configuration to specify a unique server ID. For example:
[mysqld]
server-id=2
After making the changes, restart the server.

Code: [Select]

/etc/rc.d/init.d/mysql restart
6- restore master data on the slave
If you are setting up a new replication environment using the data from a different existing database server, you will now need to run the dump file generated from that server on the new master. The database updates will automatically be propagated to the slaves:

Code: [Select]

shell> mysql -h master < dbdump.db
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

Code: [Select]

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;


MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
MASTER_USER is the user we granted replication privileges on the master.
MASTER_PASSWORD is the password of MASTER_USER on the master.
MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

8- Start the slave
Now all that is left to do is start the slave. Still on the MySQL shell we run

Code: [Select]

START SLAVE;
quit;

[admin]

To see the replication state in the slave run under Mysql:

Code: [Select]

SHOW SLAVE STATUS\G