Cache option for MySQL
<< CSQL Datatype Mappings - TOC - Cache Option for POSTGRES >>
This chapter shows a step-by-step method for caching MySQL data into CSQL and demonstrates automatic update propagation to MySQL.
Setting up CSQL and MySQL
This section demonstrates how CSQL can be configured to work as data cache for MySQL database.
Prerequisites
For CSQL Cache to work, MySQL Server, MySQL ODBC Connector, and UnixODBC packages need to be installed. Follow below links to download the packages
- MySQL Database (http://dev.mysql.com/downloads/mysql/5.0.html#downloads)
- MySQL ODBC Connector (http://dev.mysql.com/downloads/connector/odbc/3.51.html)
- UnixODBC (http://rpm.pbone.net/index.php3/stat/3/srodzaj/1/search/unixODBC)
MySQL Database
After installing MySQL, it automatically starts the MySQL server. If it is not started for any reason, just start it using the following command after logging in with user ‘mysql ‘
$/etc/init.d/mysqld start
MySQL ODBC Connector
The driver library of MySQL ODBC connector is usually named as libmyodbc3.so and will be available under the installation directory.
UnixODBC
The driver library of unixODBC is named as odbc.so and will be available at /usr/lib or /usr/local/lib directory.
Configure – odbc.ini file
The odbc.ini file is data-source configuration information file. This file contains list of data sources and properties for each database those are present in system. There are two types of odbc.ini files.
- System Configuration File
- User Configuration File
The System odbc.ini file is present at /etc/odbc.ini while the user file is usually present at ~/.odbc.ini.
Below odbc.ini file contains DSN (Data Source Name), named myodbc3 along with driver path and some other information.
The ~/.odbc.ini file which is present in home directory should contain the below lines.
[myodbc3] Driver = /usr/lib/libmyodbc3.so Description = Connector/ODBC 3.51 Driver DSN SERVER = localhost PORT = 3306 USER = lakshya Password = lakshya123 Database = test OPTION = 16 SOCKET = /var/lib/mysql/mysql.sock
The complete path for the Driver would be /usr/lib/libmyodbc3.so. If the file libmyodbc3.so, which has been downloaded, is present in some other path then the complete path for the library should be specified accordingly.
This guide assumes that the driver library is present in /usr/lib/libmyodbc3.so.
The isql tool could be used to check whether the above configuration for MySQL ODBC Connector is set properly.
$isql myodbc3 It should display the following +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
If you get the SQL prompt, then configuration is proper and we are ready to cache tables from MySQL.
Unidirectional Cache
The default caching in CSQL is one-way (or unidirectional cache), which means all updates (INSERT, UPDATE, DELETE statement execution) on cached tables will be automatically propagated to target database.
Configure the Cache
Some cache variables have to be modified in configuration file. Refer Cache Section variables.
Cache section variables should contain below specified value for unidirectional cache.
CACHE_TABLE = true SITE_ID = 1 DSN = myodbc3 CACHE_MODE = SYNC ENABLE_BIDIRECTIONAL_CACHE = false CACHE_RECEIVER_WAIT_SECS = 10 TABLE_CONFIG_FILE = /tmp/csql/csqltable.conf DS_CONFIG_FILE = /tmp/csql/csqlds.conf
Steps for Unidirectional Cache
This section describes how to create a unidirectional cache that caches the contents of a single table from MySQL database. Although CSQL supports multiple cache tables, one MySQL table could be cached to keep the example simple.
Create a MySQL table
Using the command isql in myodbc3, the SQL prompt could be achieved. Through this prompt the DDL and DML statements can be executed in MySQL database.
Connect to the database and create a table:
$isql myodbc3; SQL> CREATE TABLE T1 (f1 int, f2 char (196), primary key (f1));
Now insert some records and commit the changes:
SQL> INSERT INTO T1 VALUES (1, 'Hi'); SQL> INSERT INTO T1 VALUES (2, 'All'); SQL> COMMIT;
Load MySQL table into Cache
Before using the cachetable tool to cache the table from mysql, the data sources needs to be added into csqlds.conf file by using “csqlds” tool. Mainly the data source name from which the table belongs will be entered into the csqlds.conf file followed by DS’s user name, password and Target DB name. The user name and password could be NULL. Refer the section csqlds tool and csqlds.conf file. Open another terminal and move to the CSQL root directory and set up the environment using the command (. ./setupenv.ksh) .
$csqlds -U lakshya -P lakshya123 -D myodbc3 -N mysql -a
After execution of this command, the entries for ‘myodbc3’ data source will be present in csqlds.conf file, which will be used by the cachetable tool to cache the table from this DS. Now, use the cachetable tool to load the MySQL table into Cache. Make sure that CSQL Server is running before the cachetable tool is used. Refer the Section 3.7. in CSQL UserManual for starting and stopping the CSQL Server.
Open another terminal and move to the CSQL root directory and set up the environment using the command $. ./setupenv.ksh.
$ cachetable -U root -P manager -t T1
Now check the contents of the cache table, which is present in main memory database using CSQL tool.
CSQL>SELECT * FROM T1;
----------------------------------------------------
f1 f2
----------------------------------------------------
1 Hi
2 All
Refer the section cachetable for more information on cachetable tool.
CSQL - to - MySQL Updates
The unidirectional feature makes sure that if any modification operations (INSERT, UPDATE, DELETE) performed on the table in Cache will propagate to the original table at MySQL automatically.
Now update one record of cache table, it should propagate to the MySQL. The ‘T1’ table would have been updated in both the database.
Run csql tool with -g option, it creates an isql session which acts as gateway for CSQL and MySQL and propagates changes to the original tables at target database.
Follow the below instructions:
Create gateway connection
$csql -g CSQL>
Update first row in CSQL
CSQL>update T1 set f2='Hi Smith' where f1=1; Statement Executed: Rows Affected = 1
Checks the update in CSQL
CSQL>select * from T1;
-----------------------------
f1 f2
-----------------------------
1 Hi Smith
2 All
Checks the update in MySQL
SQL> select * from T1; +------+----------+ | f1 | f2 | +------+----------+ | 1 | Hi Smith | | 2 | All | +------+----------+
If the INSERT or DELETE operation is performed, the effect of that operation will be done at both cache and target database. For Example, if new record is inserted into the cache, it will be inserted in target database as well.
Unload the cache table
Cached tables can be unloaded (removed from the cache) using –u option of cachetable tool
Unload the Table using “cachetable -u” option
$cachetable -U root -P manager -t T1 -u
Note: CSQL restricts dropping the cache table using DROP statement. Application is expected to unload the table first and then drop it.
Bi-directional Cache
The default caching in CSQL is unidirectional caching, which means all updates (INSERT, UPDATE, DELETE) on cached tables will be automatically propagated to target database. CSQL also supports bi-directional caching in which, direct updates on target database are also propagated to CSQL cache automatically.
Bi-directional caching is implemented using triggers of the target database. This requires additional tables and triggers, which needs to be installed on the tables in target database.
Sample trigger code is available in the file trigger.sql under the CSQL root directory.
Configuring csql.conf file
For bi-directional cache, some modification needs to be made in cache section in csql.conf file. Refer 4.1.1 Cache Section variables.
Cache section variables should contain below specified value
CACHE_TABLE=true SITE_ID=1 DSN = myodbc3 CACHE_MODE=SYNC ENABLE_BIDIRECTIONAL_CACHE=true CACHE_RECEIVER_WAIT_SECS=10 TABLE_CONFIG_FILE=/tmp/csql/csqltable.conf
The only difference between unidirectional and bi-directional configuration is the value of ENABLE_BIDIRECTIONAL_CACHE parameter, which is set to true for bi-directional caching.
Setting up MySQL
After configuring csql.conf file, a log table needs to be created in target database to hold the operation log records for all the cached tables and the triggers need to be installed on cached table for INSERT, UPDATE and DELETE operation.
Create a MySQL table for caching
$isql myodbc3; SQL> CREATE TABLE p1 (f1 int, f2 char (196), primary key (f1)); Now insert some records and commit the changes: SQL> INSERT INTO p1 VALUES (1, 'Hi'); SQL> INSERT INTO p1 VALUES (2, 'All'); SQL> COMMIT;
Create log table in MySQL
As mentioned before, for propagating direct updates on MySQL, a log table would be created to hold operation logs for all cached tables. Create the csql_log_int table with five fields, Follow below statements.
SQL>CREATE TABLE csql_log_int ( tablename CHAR(64), pkid INT, operation INT, cacheid INT, id INT NOT NULL UNIQUE AUTO_INCREMENT ) engine='innodb';
Execute trigger
Following is the format for creating the triggers for cached table ‘p1’ having primary key ‘f1’in MySQL database. A sample file (trigger.sql) is available at the CSQL root directory; that could be modified with cached table name and its primary key field.
use test;
drop trigger if exists triggerinsertp1;
drop trigger if exists triggerupdatep1;
drop trigger if exists triggerdeletep1;
DELIMITER |
create trigger triggerinsertp1
AFTER INSERT on p1
FOR EACH ROW
BEGIN
Insert into csql_log_int (tablename, pkid, operation, cacheid) values ('p1', NEW.f1, 1, 1);
End;
create trigger triggerupdatep1
AFTER UPDATE on p1
FOR EACH ROW
BEGIN
Insert into csql_log_int (tablename, pkid, operation, cacheid) values ('p1', OLD.f1, 2, 1);
Insert into csql_log_int (tablename, pkid, operation, cacheid) values ('p1', NEW.f1, 1, 1);
End;
create trigger triggerdeletep1
AFTER DELETE on p1
FOR EACH ROW
BEGIN
Insert into csql_log_int (tablename, pkid, operation, cacheid) values ('p1', OLD.f1, 2, 1);
End;
|
Note: Trigger name ends with the table name. Replace ‘p1’ in the above script to the cached table name and ‘f1’ to the primary key fieldname of the cached table as per table and field name.
After a successful editing of trigger.sql file, now it’s ready for execution. Follow the below command to execute the trigger.
$ mysql -u root -p <trigger.sql
Load MySQL table to Cache
Now run the csqlserver and use the cachetable tool to cache MySQL table into cache.
Create another terminal and move towards csql root directory and set up the environment (. ./setupenv.ksh). Make the entries in csqlds.conf file for your appropriate Data Source. Refer the Section csqlds tool
$ cachetable -U root -P manager -t p1
Now check the contents of the cache table, which is present in Cache using CSQL tool.
CSQL>SELECT * FROM p1;
-------------------------
f1 f2
-------------------------
1 Hi
2 All
Refer the section cachetable tool.
MySQL – to – CSQL Update propagation
Insert some records in p1 table in MySQL, and the record should be propagated to the cached table at CSQL automatically based on the time specified in CACHE_RECEIVER_WAIT_SECS variable. The update will appear in cache before CACHE_RECEIVER_WAIT_SECS.
After this query the cached table in CSQL and the original table in MySQL database will contain the same number of records.
Insert Records in MySQL
$isql myodbc3 SQL> insert into p1 values (3,'NoOne'); SQLRowCount returns 1 SQL> insert into p1 values (4,'EveryOne'); SQLRowCount returns 1
two more records are inserted.
Query the p1 table in MySQL
SQL> select * from p1; +----+----------+ | f1 | f2 | +----+----------+ | 4 | EveryOne | | 3 | NoOne | | 2 | All | | 1 | Hi | +----+----------+
Query the p1 table in CSQL with -g option
CSQL>select * from p1;
----------------------------------
f1 f2
----------------------------------
1 Hi
2 All
3 NoOne
4 EveryOne
The records will be found in cache, those are inserted into the original table at MySQL.
Bi-directional cache on non-integer primary key
Let us consider a table having non-integer (say char) primary key field to be cached for the purpose of bi-directional. For example:
$isql myodbc3 SQL>create table t1 (f1 int, f2 char (10), f3 float, primary key(f2));
For bi-directional cache on table 't1' having non-integer primary key field f2, we need to add a extra key field “f4” which is not-null and unique Auto_Increment field.
$alter table t1 add column f4 int not null unique auto_increment;
After adding an extra key field, make necessary changes on trigger file for the field 'f4' as key field. Run the trigger and do the operation as per the requirement.
<< CSQL Datatype Mappings - TOC - Cache Option for POSTGRES >>