Data replication allows data to be copied between running instances of the BearDB server. Data replication can be used for a myriad of different purposes, such as:
- Backup or copy table data from multiple devices to a centralized server instance.
- Provide dynamic content updates for remote servers, mobile devices, and multiuser applications.
Replication can be a powerful feature for systems and applications which utilize BearDB. Just like BearDB, data replication works with any database version on any supported platform with any amount of data you require.
Replication of data is executed between server instances.
For the following examples, we will assume there are two database servers named
Bob will be our user who is connected to DBServerA via a SQL client.
Bob would like to replicate the Widget table from DBServerB to DBServerA.
Before proceeding, the following prerequisites are assumed:
- Bob's user account in DBServerA has been granted CREATE_TABLE and REPLICATE.
- DBServerA and DBServerB have replication enabled in server.properties.
- The table Widget exists in DBServerB, but does not exist in DBServerA.
- DBServerB has an I.P. address of
- DBServerB uses the default BearDB port of
Once Bob is connected to DBServerA with a client, he can issue the following replication command:
replicate Widget from 188.8.131.52:7169 using userBob b3llz
The replication command Bob issued is defined as follows:
Replicate the database table DBServerB.Widget to DBServerA. DBServerB is at I.P. address 184.108.40.206, port 7169, and a valid BearDB login for DBServerB is userBob/b3lls.
Once the command is issued, the replication process starts immediately. If there is something in the replication sql request which generates an error, the replication process is not started and the user is provided with a detailed error.
When using replication it is important to know the state of the local and remote databases before copying data all over the place.
When Bob replicated the Widget table, he knew the table existed in DBServerB and did not exist in DBServerA.
To get the table to DBServerA, Bob connected his BearDB client to DBServerA and issued the query to replicate the table FROM DBServerB.
Keep in mind that when running a replication query you need to connect to the server you want the data replicated TO.
Replication can be configured to pull whatever data you want.
The format of the SQL to execute table replication is the following:
replicate tableName [as (newTableName|query)] from serverOrIp[:portnum] using username password
tableName: The table name on the remote server to replicate.
as newTableName: Optional. The table name to create on the local server. If using newTableName, then a query cannot be used. If newTableName is omitted, then it is assumed that the table tableName should be created on the local database if it does not exist.
as query: Optional. A sql query to identify the records on the remote server that should be replicated to the local server. If using a query, then a newTableName cannot be used. Replication will attempt to merge data based on the column name. If the query 'SELECT * FROM SomeTable' returns the same column names as the local table (tableName), then the data from the remote server will be inserted into the table tableName on the local server. If the table tableName on the local server does not exist, it will be created and populated with the result of the query.
from serverOrIp: The server name or I.P. address of the remote BearDB server to replicate data FROM.
from serverOrIp:portNum: Optional. The server name or I.P. address and port number of the remote BearDB server to replicate data FROM. The port number is optional. If the port number is omitted, replication will use the default BearDB port of 7169.
using username password: The username and password needed to access the remote BearDB server. This user account should already be created on the remote server and the user should have SELECT permissions.
Some replication examples are provided below:
replicate tableName as (newTableName) from myServer using tinaw g00bits
Will replicate the table tableName from the remote server
myServer to the local server with the table name
newTableName. If the table
newTableName does not exist, it will be created. If newTableName does exist and has the same columns as the table tableName, then the data from the remote server will be inserted into newTableName.
If the table newTableName does exist and does not have the same columns as the table tableName, then no data will be replicated and the client will be provided with an error message.
replicate tableName as (SELECT * FROM otherTable WHERE value = 'something') from happyServer using m0nkey t1bble
Replicates the records returned from a SELECT query on the remote server
happyServer to the local database. Assumes the columns in the SELECT query are the same as the columns in the local table
tableName. If the table
tableName does not exist, it will be created. If tableName does exists then the data returned from the SELECT query will be inserted into that table.
Limitations and Known Issues
- Know your server name or I.P. address: BearDB does not scan networks for database instances. The name or i.p. address provided to BearDB for replication must be valid. In Windows, for example, the network name is not always the same as the 'computer name'. If a connection to the remote server cannot be made, no data will be replicated. Most operating systems provide the tool ipconfig to gather network identification values. When you are unsure which network name or value to use, an I.P. address will usually work in most circumstances.
- BearDB uses port 7169 by default: The port needs to be 'open' in any firewalls, virus checkers, or other security-related applications.
- When using a query, only select records from one table: Using a select query with a JOIN or with multiple tables will result in failure for the replication process.
- Keep track of which columns in the local table are NOT NULL: If you replicate data using a select query that does not contain every column in the local table, then BearDB will attempt to insert a NULL value into the columns that were not in the SELECT query. If a NULL is not allowed in any missing columns, then no data will be replicated.
- Replication does not copy foreign key constraints: When table data is replicated, any foreign keys to other tables are not copied to the local database. The data and values are kept intact, but the foreign key identifier is not included in the local table schema.
- Be aware of other users: You can replicate data with a SELECT query into an existing table. Note that other users may be reading or changing data in that table, and your replication job will change the data as well. Replication can be performed safely when there are no users on the system.