Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

How to Do Replication on UNIX & Linux

This document describes the configuration required to use the database replication feature in CUBRID.

Configuring the Replication Feature

Prerequisites

Two machines are required; both machines must run the same operating system and CUBRID2008.
The database for which replication is configured: Triggers are not supported, so they must not be created.
Create the following tables in advance.
To perform replication, a primary key must be defined in advance.

create class info (
id int,
name char(10),
address varchar(100),
primary key(id) < br> )

Terminology

  • Master machine: A machine on which the master database resides
  • Slave machine: A machine on which the slave database resides
  • Master database: A database to be replicated
  • Slave database: A database to which the contents of the master database are to be written. That is, the contents of masterdb are replicated into slavedb.
  • Distribution database: A database that has information about the master database to be replicated; it resides in the slave machine.
  • Replication server: A process that passes changes in the master database to the replication agent
  • Replication agent: A process that performs replication for the slave database

Configuring Replication

Terminating the master database

To configure a replication environment, first terminate the master database on the master machine. The name of the master database will be called masterdb.

				cubrid server stop masterdb 

You do not need to stop the master database to configure replication. When you configure replication for the first time, however, it is recommended to configure it after terminating the database.

Most importantly, you need to configure a replication-related parameter. If this parameter is not configured yet, restart the database server after configuring it. Configure the parameter "replication" as "yes" in $CUBRID/conf/cubrid.conf and then restart the database server as shown below.

cubrid server restart masterdb 

Backing up the master database

Back up the entire master database because a backup copy is used to replicate the database and create a slave database.

				cubrid backupdb -S masterdb 

Moving the backup file

Move the backup file to the slave machine. At this time, you must move both the backup file (masterdb_bv0v*) and backup information file (masterdb_bkvinf). In addition, the directory in which each backup file is stored on the slave machine must be the same as the directory of the master machine. For example, if the backup information file of the master database is located in /database/masterdb of the master machine, and the backup file is located in /backup/masterdb, both /database/master and /backup/masterdb directories must also exist in the slave machine, for which you must have appropriate authorization.

ftp slave_machine
ftp cd /database/masterdb
ftp put masterdb_bkvinf
ftp cd /backup/masterdb
ftp mput masterdb_bk0v*

Creating the distribution database

You need to create a distribution database that stores the master database information in the slave machine. The location of the distribution database can be determined by a user. How to create a distribution database is described below.

Execute a command to create a distribution database. The following shows how to make the command.

repl_make_distdb distribution database name -p DBA user password of the distribution database

The DBA user password is not required. However, it is recommended to provide it for the security reason. Distribution database name : Specify the name of a distribution database to be created; here, it will be called distdb.

When creating a distribution database, you are asked to provide the master server information such as the master database name, IP address of the master machine, connection port number for communication between a replication server and a replication agent.

Then, you are asked to provide the directory for log copies, trail logs, and error logs. Simply specify the directory with space, for which you have authorization. Create a log copy directory for the following example

mkdir /home/replication_logs
repl_make_distdb distdb -p distdb_password
#########################################################################
# #
# Configuration of the Replication Agent #
# #
# To configure the replication environment, #
# 1. Configure the replication agent (using make_distdb script) #
# 2. Back up the master database (using cubrid backup) #
# 3. Copy the backup files to the slave site #
# 4. Construct the slave database (using make_slavedb script) #
# 5. Start the repl_server at the master site #
# 6. Start the repl_agent at the slave site #
# #
# NOTE1: You have to back up the master database before constructing #
# the slave databases. If you back up the master database just #
# before constructing the slave database, you can save the #
# time to synchronize the master database and slave database. #
# Following files should be copied to the slave site. #
# - master_db_name.bk_vinf #
# - master_db_name.bk0v??? #
# #
# NOTE2: To configure the distributor database, DBA account is #
# is necessary. Please provide the DBA account. #
# #
# NOTE3: A distributor database should be created per slave database. #
# Please create the distributor database and start up the #
# repl_agent at the slave site for the best performance. #
# #
# When you stop this script using Ctrl-C, please retry again after #
# deleting the distributor database using 'cubrid server stop'after #
# then 'cubrid delete'. #
#########################################################################
STEP 1 : Creating the distributor database.
Creating database with 5000 pages.
CUBRID 2008 R2.0
STEP 2 : Starting up the server for distributor.
STEP 3 : Setting the DBA account of distributor database.
STEP 4 : Creating the objects for replication.
STEP 5 : Please provide information of the master databases.
1. Please enter the name of the master database.  masterdb
2. Please enter the IP address of the master database.
- Replication would not work without correct IP address.
IP address of the master  10.31.14.206
3. Please enter the TCP/IP port number of the repl_server.  5621
4. Please enter the directory path of copy log.
If the target directory is /home/Rel_test, just strike enter.  /home/replication_logs
STEP 6 : Configuring the replication parameters.
1. Please enter the directory path of trail log.
If the target directory is /home/Rel_test, just strike enter.  /home/replication_logs
2. Please enter the directory path of error log.
If the target directory is /home/Rel_test, just strike enter.  /home/replication_logs
3. Please enter the TCP/IP port number for information of repl_agent status.  5622
4. Please enter the file size(line number) of replication delay time log.

When the distribution database is created, it is ready to run. You can check its status by using cub_commdb -P.

				cub_commdb -P
				Server distdb (rel 8.2, pid 22086) 

Creating the slave database

Once the distribution database is created, create a slave database. Because a slave database is created based on the backup copy of the master database, you must configure the same environment as that of the master database in the slave machine. For example, if the master database resides under /database/masterdb, the same directory must exist in the slave machine, for which you must have authorization. How to create a slave database is described below.

Create the same directory structure as that of the master database on the slave machine and give a user appropriate authorization for the directory. You may be required to create a directory at the root level.

				mkdir /database/masterdb
				chown <user_id>:<user_group> /database/masterdb 

Execute a command to create a slave database The command must be executed in the directory in which the backup information file (masterdb_bkvinf) of the master database is located.

				cd /database/masterdb 

The following shows how to make a command to create a slave database.

				repl_make_slavedb master database name slave database name -u slave database user id -p slave database user password

For the volume of the slave database, you must enter the same directory as the one in which the volume of the master database is stored. Similarly, for the log of the slave database, enter the same directory as the one in which the log of the master database is stored.
As a distribution database name, enter the name of the distribution database that you have just created. As a DBA password of the distribution database, enter the password specified when creating the database. If there is a DBA password of the master database, enter the password; otherwise, simply press the Enter key.

				repl_make_slavedb masterdb slavedb -u repl2 -p repl2
				#########################################################################
				# #
				# Constructing the slave database #
				# #
				# To configure the replication environment, #
				# 1. Configure the replication agent (using make_distdb script) #
				# 2. Back up the master database (using cubrid backup) #
				# 3. Copy the backup files to the slave site #
				# 4. Construct the slave database (using make_slavedb script) #
				# 5. Start the repl_server at the master site #
				# 6. Start the repl_agent at the slave site #
				# #
				# NOTE1: Please create and configure the distributor database #
				# using make_distdb script before executing this script. #
				# NOTE2: Please back up the master database before constructing #
				# the slave databases. If you back up the master database just #
				# before constructing the slave database, you can save the #
				# time to synchronize the master database and slave database. #
				# Following files should be copied to the slave site. #
				# - master_db_name.bk_vinf #
				# - master_db_name.bk0v??? #
				# NOTE3: It's necessary to execute this script at the directory #
				# in which the backup files locate. #
				# #
				# When you stop this script using Ctrl-C, please retry again after #
				# deleting the distributor database using cubrid server stop and #
				# then cubrid delete. #
				#########################################################################
				STEP 1 : Preprocess to restore the master database.
				a. Please provide the directory path of data volume.
				Just enter if the target directory is /home/master.  /home/slavedb
				b. Please provide the directory path of log volume.
				Just enter if the target directory is /home/master.  /home/slavedb
				STEP 2 : Restore the master database backup to make the slave database.
				- Backup files and volume information file should be
				in the current directory.
				- Now, restoring the master database backup, please wait a moment.
				- Slave database is created.
				STEP 3 : Insert the necessary information into the distributor database.
				a. Provide the name of the distributor database.  distdb
				b. Provide the password of the DBA of distributor database.  distdb_password
				STEP 4 : Start the slave database.STEP 5 : Post process after starting up the slave database.
				- the DBA password of the master database is needed.
				- Provide the DBA password of the master database.  dba_password
				STEP 6 : Set the replication parameters.
				1. perf_poll_interval - performance polling interval(sec)
				2. size_of_log_buffer - log buffer size of repl_agent
				3. size_of_cache_buffer - cache buffer size of the repl_agent
				4. size_of_copylog - page count of the copy log
				5. index_replication - 'y' for index replication
				6. for_recovery - 'y' for recovery mode
				7. log_apply_interval - interval between log page fetches(sec)
				8. restart_interval - interval between connections to the slave(sec)
				-- Parameter Number to be changed (q - quit )  q
				STEP 7 : Set the replication group.
				MASTER_HOST : 10.31.14.206
				-------------------------------------------------------------------------
				All the classes of masterdb are included in the replication group.
				Would you like to reset the replication group? (y or n)  n 

Starting the replication server

Start the master database and then start the replication server. At this time, enter the name of master database and enter the connection port. The connection port number must be one you specified when creating the distribution database.

				cubrid server start masterdb
				cubrid repl_server master-database-name server-network-port 

Starting the replication agent

Now, start the replication agent. At this time, enter the name of the distribution database and enter its DBA password.

Replication

Access the master database by using cubrid.conf and check whether or not the data from the master database is replicated to a slave database while executing SQL. As mentioned earlier, corresponding tables must be created in advance.

Inserting

Insert data by executing the following SQL with esql in the master database.

				csql insert into info values(1, 'Jason', 'North 34th Street Seattle WA')
				csql insert into info values(2, 'Alex', 'North 34th Street Seattle WA') 

Check whether or not the data is inserted by retrieving in the slave database.

				csql select * from info
				csql ;x
				=== Result of SELECT Command in Line 1 ===
				id name address
				=========================================================
				1 'Jason ' ' North 34th Street Seattle WA'
				2 'Alex ' ' North 34th Street Seattle WA'
				2 rows selected. 

Updating

Update data by executing the following SQL in the master database.

				csql update info set address = 'North 34th Street Seattle WA' where name = 'Jason' 

Check whether or not the data is updated by retrieving in the slave database.

				csql select * from info
				csql ;x
				=== Result of SELECT Command in Line 1 ===
				id name address
				=========================================================
				1 'Jason ' ' North 34th Street Seattle WA'
				2 'Alex ' ' North 34th Street Seattle WA'
				2 rows selected. 

Deleting

Delete data by executing the following SQL in the master database.

				csql delete from info where name = 'Alex'

Check whether or not the data is updated by retrieving in the slave database.

				csql select * from info
				csql ;x
				=== Result of SELECT Command in Line 1 ===
				id name address
				=========================================================
				1 'Jason ' ' North 34th Street Seattle WA'
				1 rows selected. 




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: