Basic knowledge of:
OS:
Python modules:
Installation:
yum -y install MySQL-python python-pip python-devel
pip
upgrade:
pip install --upgrade pip
Oracle module:
pip install cx_Oracle
cx_Oracle
need intantclient
to run:
-rw-r--r-- 1 root root 51M Jan 16 15:40 oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm -rw-r--r-- 1 root root 593K Jan 16 15:40 oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm -rw-r--r-- 1 root root 253K Jan 16 15:40 oracle-instantclient12.2-odbc-12.2.0.1.0-2.x86_64.rpm -rw-r--r-- 1 root root 692K Jan 16 15:40 oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm -rw-r--r-- 1 root root 922K Jan 16 15:40 oracle-instantclient12.2-tools-12.2.0.1.0-1.x86_64.rpm
Installation example:
AVDLP-MYSQL-101 ~ # rpm -Uvh oracle-instantclient12.2-* Preparing... ################################# [100%] Updating / installing... 1:oracle-instantclient12.2-basic-12################################# [ 20%] 2:oracle-instantclient12.2-devel-12################################# [ 40%] 3:oracle-instantclient12.2-odbc-12.################################# [ 60%] 4:oracle-instantclient12.2-sqlplus-################################# [ 80%] 5:oracle-instantclient12.2-tools-12################################# [100%]
Modify the ld.conf path if you change the instantclient version!!!!
Load libs:
echo "/usr/lib/oracle/12.2/client64/lib" > /etc/ld.so.conf.d/oracle-instant-client.conf ldconfig
Check:
/usr/bin/sqlplus64
Variables needed:
cat > /etc/profile.d/oracle-instant-client.sh<<EOF # Set ORACLE_HOME to the directory where the bin and lib directories are located for the oracle client export ORACLE_HOME=/usr/lib/oracle/12.2/client64 # No need to add ORACLE_HOME to the linker search path. oracle-instant-client.conf in # /etc/ld.so.conf.d should already contain /usr/lib/oracle/11.2/client64. # Alternately, you can set it here by uncommenting the following line: # export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib # Define the default location where Oracle should look for the server export TWO_TASK=//adaptive-oracle:1561/listener # Define where to find the tnsnames.ora file export TNS_ADMIN=/etc/oracle EOF
You'll have to write down the tnsnames. The best option is to use our centralized tnsnames.ora:
svn://10.52.2.15/oracle/tnsnames/tnsnames.ora
There are 2 scripts:
ora2my_sync.sh | shell script which build the database structure |
---|---|
ora2my_sync.py | sync script itself |
Both scripts need permissions on mysql database to drop/create/insert/delete tables.
The script has 2 ways of run:
Run example:
ora2my_sync.sh sync "TABLE1 TABLE2"
This script is multi-threaded, the number of threads can be configured inside the script:
[ ${DEBUG} -eq 0 ] && let MAXTHREADS=1 || let MAXTHREADS=1
In the case of recreate
option, the script will:
The configuration is done with the config file ora2my_sync.config
You'll find a template in the github repo, so:
cp ora2my_sync.config.tmpl ora2my_sync.config
######################################################################## # # VARIABLES # ######################################################################## ORACLE_HOME=/usr/lib/oracle/18.5/client64 ORAUSER="USERNAME" ORAPASSWD="THEPASSWORD" ORASID="TNSNAME_OF_DATABASE" ORAHOST="hostname:1521" SQLPLUS="/usr/bin/sqlplus64 -s ${ORAUSER}/${ORAPASSWD}@${ORASID}" MYUSER="root" MYPASSWD="THEPASSWORD" MYDB="thedatabase" MYSQL="mysql -u${MYUSER} -p${MYPASSWD}" ORA2MY="python /home/scripts/The_fucking_bofh.Database.MySQL/ora2my_transfer/ora2my_sync.py" TABLELIST="VW_FINDER_RESTAURANTS USER_PREFERRED_UNITS VW_FINDER_PARKINGS VW_FINDER_GAS_STATIONS" declare -a TABLEXTHREAD # SET DEBUG=0 for debugging DEBUG=1 #DEBUG=0 [ ${DEBUG} -eq 0 ] && let MAXTHREADS=1 || let MAXTHREADS=1 ######################################################################## # # / VARIABLES # ########################################################################
All the options are self-descriptive by its name…
Database Types conversion
In the CONSTANTS
section inside ora2my_sync.config
you'll find the conversion table.
This script will sync data between Oracle and Mysql.
The only parameter is the table name.
It uses cx_Oracle
for Oracle connection and MySQLdb
for MySQL connection (plus some additional python modules).
The script will check if there's a sync table to get changes and sync the table.
If the sync table doesn't exists, It will drop and import all the table data in a single transaction.
The config is done in the ora2my_sync.pycfg
file:
From the github repo:
cp ora2my_sync.pycfg.tmpl ora2my_sync.pycfg
[MySQL] username = root password = *** host = localhost databasename = mydiva_t01 [Oracle] username = DEVOLUIVA password = *** host = 10.63.3.150:1521 databasename = DBTEST
In the 1st synchronization, the sync table MUST NOT EXISTS (so drop or rename it).
Sync table name should be the same as the “master” table and adding the sufix _MYSYNC
.
Sync table metadata is:
CREATE TABLE "<MASTERTABLE>_MYSYNC" ( "<PK_COLNAME>_ID" NUMBER NOT NULL ENABLE, "ACTION" VARCHAR(1) NOT NULL ENABLE, "WHENITWAS" TIMESTAMP DEFAULT SYSTIMESTAMP ) TABLESPACE "<USERSCHEMA>" ;
We'll need a sync trigger:
CREATE OR REPLACE EDITIONABLE TRIGGER "<MASTERTABLE>_MYSYNC" AFTER INSERT OR UPDATE OR DELETE ON ACTOR_IMAGES FOR EACH ROW BEGIN CASE WHEN INSERTING OR UPDATING THEN INSERT INTO <MASTERTABLE>_MYSYNC VALUES (:NEW.<PK_COLNAME>, 'i', SYSTIMESTAMP); WHEN DELETING THEN INSERT INTO <MASTERTABLE>_MYSYNC VALUES (:OLD.<PK_COLNAME>, 'd', SYSTIMESTAMP); END CASE; END; / ALTER TRIGGER "<MASTERTABLE>_MYSYNC" ENABLE;
Variables to be replaced on the SQL's:
<MASTERTABLE> | Name of the master table |
---|---|
<PK_COLNAME> | Name of the PK column |
<USERSCHEMA> | Schema in which the table is created |
This synchro mode DOES NOT support master table with a PK multiple (multiple columns on the PK).
Disable trigger, rename _MYSYNC
table:
ALTER SESSION SET CURRENT_SCHEMA= <OWNER> ; ALTER TRIGGER "<MASTERTABLE>_MYSYNC" DISABLE ; ALTER TABLE "<MASTERTABLE>_MYSYNC" RENAME TO "<MASTERTABLE>_NOSYNC" ;
Launch sync
Reenable it all (this must be done meanwhile the ora2my_syn.sh
script is running):
ALTER TRIGGER "<MASTERTABLE>_MYSYNC" ENABLE ; ALTER TABLE "<MASTERTABLE>_NOSYNC" RENAME TO "<MASTERTABLE>_MYSYNC" ;
The script will perform all the actions on a single transaction, so the data will be available 100% of the time.
A view over multiple tables must be only sync'ed without sync table (or You'll need to create trigger on any table that the view access, which is not recommended).
Everything is the rest is the same than sync'ing a table.
The view must have pk!!
ALTER VIEW VW_FINDER_RESTAURANTS ADD CONSTRAINT VW_FINDER_RESTAURANTS_PK PRIMARY KEY (RESOURCE_ID) DISABLE;
Following this instructions You'll have a http server listening for calls and launch refresh's if the call is correct. It will answer with a JSON with the ressults.
yum -y install httpd httpd-tools systemctl enable httpd
cd /etc/httpd/conf.modules.d for i in 00-dav.conf 00-lua.conf 00-proxy.conf ; do mv $i ${i}.disabled ; done
cp /etc/httpd/conf/httpd.conf /etc/httpd/conf/httpd.conf.orig cat >/etc/httpd/conf/httpd.conf<<EOF ServerRoot "/etc/httpd" Listen 80 Include conf.modules.d/*.conf User apache Group apache ServerAdmin sysadmin@ciberterminal.net # CGI-BIN LONG TIMEOUT TimeOut 600 <Directory /> AllowOverride none Require all denied </Directory> DocumentRoot "/var/www/html" <Directory "/var/www"> AllowOverride None Require all granted </Directory> <Directory "/var/www/html"> Options Indexes FollowSymLinks AllowOverride None Require all granted </Directory> <IfModule dir_module> DirectoryIndex index.html </IfModule> <Files ".ht*"> Require all denied </Files> ErrorLog "logs/error_log" LogLevel warn <IfModule log_config_module> LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined LogFormat "%h %l %u %t \"%r\" %>s %b" common <IfModule logio_module> LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\" %I %O" combinedio </IfModule> CustomLog "logs/access_log" combined </IfModule> <IfModule alias_module> ScriptAlias /cgi-bin/ "/var/www/cgi-bin/" </IfModule> <Directory "/var/www/cgi-bin"> AllowOverride None Options None Require all granted </Directory> <IfModule mime_module> TypesConfig /etc/mime.types AddType application/x-compress .Z AddType application/x-gzip .gz .tgz AddType text/html .shtml AddOutputFilter INCLUDES .shtml </IfModule> AddDefaultCharset UTF-8 <IfModule mime_magic_module> MIMEMagicFile conf/magic </IfModule> EnableSendfile on IncludeOptional conf.d/*.conf ScriptLog logs/cgi.log EOF rm -fv /etc/httpd/conf.d/* cat >/etc/httpd/conf.d/auth.conf<<EOF <Location "/"> AuthType basic AuthName "private area" AuthBasicProvider file #AuthBasicUseDigestAlgorithm MD5 AuthUserFile "/var/www/html/auth/.basic_pw" Require valid-user </Location> EOF
Create folders
mkdir /var/www/html/auth cd /var/www/html/auth
Add users:
htpasswd -bc .basic_pw dodger <thepassword>
Launch Script:
cp /home/scripts/The_fucking_bofh.Database.MySQL/ora2my_transfer/launch_sync.sh /var/www/cgi-bin chmod +x /var/www/cgi-bin/launch_sync.sh
Sample url:
http://10.52.2.xx/cgi-bin/test_POST.sh?mode=recreate&tables=VW_FINDER_RESTAURANTS,USER_PREFERRED_UNITS,VW_FINDER_PARKINGS,VW_FINDER_GAS_STATIONS
Sample run:
curl -s -u dodger:<thepassword> --basic "http://10.63.3.170/cgi-bin/launch_sync.sh?mode=sync&tables=USER_PREFERRED_UNITS" | jq .
POST variables are:
VARIABLE | Values | Description |
---|---|---|
mode | sync | sync mode |
recreate | recreate mode | |
tables | table1,table2,table3 | List of tables that will be sync'ed |