Database replication is the frequent copying data from a database in one server to a database in another server to make the data in all servers consistent. Usually one database server(master) maintains the master copy of the database and other servers(slaves) maintain slave copies of the database. Database writes are written to the master database server and are then replicated by the slave database servers. MySQL replication is asynchronous - slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, we can replicate all databases, selected databases, or even selected tables within a database.
There are mainly three types of replication:
Snapshot replication: Data on one server is simply copied to another server, or to another database on the same server.
Merging replication: Data from two or more databases is combined into a single database.
Transactional replication: Users receive full initial copies of the database and then receive periodic updates as data changes.
Benefits of replication:
Scale-out solutions - spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.
Data security - because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.
Analytics - live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.
Long-distance data distribution - if a branch office would like to work with a copy of your main data, you can use replication to create a local copy of the data for their use without requiring permanent access to the master.
In this tutorial we are using the following version of mysql
mysql-server-5.0.45-7.el5
We have two systems with Centos 5.2 os
192.168.137.100 server.lap.work server (Master)
192.168.137.55 apache.lap.work apache (Slave)
On both systems install mysql server.
yum install mysql*
In master system in the mysql ocnfiguration file and the log-bin variable and server-id entries
Master side:
[root@server mysql]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log-error=/var/log/mysqld.log
log-bin
server-id=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@server mysql]#
And restart the mysql service
/etc/init.d/mysqld restart
You can see the status of the master process as
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000003 | 342 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
And can check the server id and log-bin entries as
mysql> show variables like 'server%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'log%';
+---------------------------------+---------------------+
| Variable_name | Value |
+---------------------------------+---------------------+
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | /var/log/mysqld.log |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
+---------------------------------+---------------------+
8 rows in set (0.00 sec)
mysql>
We have to make a user and give him replication permissions. Here we are using root user
mysql> grant replication slave on *.* to 'root'@'%' identified by 'redhat';
mysql> grant select,super,reload on *.* to 'root'@'%' identified by 'redhat';
Now checking the grants for root user:
mysql> show grants for root;
+------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '27c30f0241a5b69f' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
mysql>
Now we can take the backup of databases in master server and copy to slaves. before copying lock the tables with read lock. So that writes wont happen when we take backup and transfer
mysql> flush tables with read lock;
You can unlock it aftet the transfer as
mysql> unlock tables;
Checking the log status
mysql> show binary logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| mysqld-bin.000001 | 117 |
| mysqld-bin.000002 | 117 |
| mysqld-bin.000003 | 342 |
+-------------------+-----------+
3 rows in set (0.04 sec)
mysql>
Checking the log events
mysql> show binlog events;
+-------------------+-----+-------------+-----------+-------------+--------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+-------------+-----------+-------------+--------------------------------+
| mysqld-bin.000001 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.45-log, Binlog ver: 4 |
| mysqld-bin.000001 | 98 | Stop | 1 | 117 | |
+-------------------+-----+-------------+-----------+-------------+--------------------------------+
2 rows in set (0.00 sec)
mysql>
Client side:
In client server also we have to set the server id, but different that the id of the master server.
[root@apache mysql]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=100
log-error=/var/log/mysqld.log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@apache mysql]#
Restart the mysql server and check the id
mysql> show variables like 'server%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 100 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
First you have tostop the slae service
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
and have to set the master details. The data to be given here will be obtained by running" show master status" on master. File name and position will be there in the output.
mysql> change master to master_host='server', master_user='root', master_password='redhat', master_log_file='mysqld-bin.000003', master_log_pos=342;
Query OK, 0 rows affected (0.01 sec)
now starting the slave service
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
Checking the slave status
mysql> show slave status;
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------+------------------+
| 27 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 30 | system user | | NULL | Connect | 60 | Waiting for master to send event | NULL |
| 31 | system user | | NULL | Connect | 60 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
+----+-------------+-----------+------+---------+------+-----------------------------+------------------+
3 rows in set (0.00 sec)
mysql>
In this example we have a database named test and a table people in it. in the table we have three entries..
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| people |
+----------------+
1 row in set (0.00 sec)
mysql> select * from people;
+---------------------+-----------+------------+
| first_name | last_name | mob_number |
+---------------------+-----------+------------+
| Randeep Raman 1234 | NULL | NULL |
| Nibul Roshan 5678 | NULL | NULL |
| Afilaj Hussain 1357 | NULL | NULL |
+---------------------+-----------+------------+
3 rows in set (0.00 sec)
mysql>
Now on the master server we update the table by insertting a new raw
mysql> INSERT INTO people (first_name,last_name,mob_number) VALUES ('Renjith','menon','1234');
Query OK, 1 row affected (0.00 sec)
mysql> select * from people;
+---------------------+-----------+------------+
| first_name | last_name | mob_number |
+---------------------+-----------+------------+
| Randeep Raman 1234 | NULL | NULL |
| Nibul Roshan 5678 | NULL | NULL |
| Afilaj Hussain 1357 | NULL | NULL |
| Renjith | menon | 1234 |
+---------------------+-----------+------------+
4 rows in set (0.00 sec)
mysql>
It should be reflected in the slave machine
Before
mysql> select * from people;
+---------------------+-----------+------------+
| first_name | last_name | mob_number |
+---------------------+-----------+------------+
| Randeep Raman 1234 | NULL | NULL |
| Nibul Roshan 5678 | NULL | NULL |
| Afilaj Hussain 1357 | NULL | NULL |
+---------------------+-----------+------------+
3 rows in set (0.00 sec)
mysql>
After
mysql> select * from people;
+---------------------+-----------+------------+
| first_name | last_name | mob_number |
+---------------------+-----------+------------+
| Randeep Raman 1234 | NULL | NULL |
| Nibul Roshan 5678 | NULL | NULL |
| Afilaj Hussain 1357 | NULL | NULL |
| Renjith | menon | 1234 |
+---------------------+-----------+------------+
4 rows in set (0.00 sec)
You can check the logs in slave machine if there is any errors
mysql>
[root@apache ~]# tail /var/log/mysqld.log
120321 21:05:42 [Note] Slave I/O thread killed while reading event
120321 21:05:42 [Note] Slave I/O thread exiting, read up to log 'mysqld-bin.000003', position 342
120321 21:05:42 [Note] Error reading relay log event: slave SQL thread was killed
120321 21:05:54 [Note] Slave SQL thread initialized, starting replication in log 'mysqld-bin.000003' at position 342, relay log '/var/run/mysqld/mysqld-relay-bin.000002' position: 480
120321 21:05:54 [Note] Slave I/O thread: connected to master 'root@server:3306', replication started in log 'mysqld-bin.000003' at position 342
120321 21:05:57 [Note] Slave I/O thread killed while reading event
120321 21:05:57 [Note] Slave I/O thread exiting, read up to log 'mysqld-bin.000003', position 342
120321 21:05:57 [Note] Error reading relay log event: slave SQL thread was killed
120321 21:06:05 [Note] Slave SQL thread initialized, starting replication in log 'mysqld-bin.000003' at position 342, relay log '/var/run/mysqld/mysqld-relay-bin.000001' position: 4
120321 21:06:05 [Note] Slave I/O thread: connected to master 'root@server:3306', replication started in log 'mysqld-bin.000003' at position 342
[root@apache ~]#
In the salve system there are some files which has information related to replication details
[root@apache ~]# ll /var/lib/mysql/
total 20536
-rw-rw---- 1 mysql mysql 10485760 Mar 21 20:23 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Mar 21 20:23 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Mar 20 18:16 ib_logfile1
-rw-rw---- 1 mysql mysql 67 Mar 21 21:21 master.info
drwx------ 2 mysql mysql 4096 Mar 20 18:16 mysql
srwxrwxrwx 1 mysql mysql 0 Mar 21 20:23 mysql.sock
-rw-rw---- 1 mysql mysql 66 Mar 21 21:21 relay-log.info
drwx------ 2 mysql mysql 4096 Mar 21 12:33 test
[root@apache ~]#
[root@apache ~]# cat /var/lib/mysql/master.info
14
mysqld-bin.000003
491
server
root
redhat
3306
60
0
0
[root@apache ~]#
[root@apache ~]# cat /var/lib/mysql/relay-log.info
/var/run/mysqld/mysqld-relay-bin.000002
385
mysqld-bin.000003
491
[root@apache ~]#