Forwarding data to Oracle database

We have an application that collects the logs from various network devices and saves it in a MySQL database. This happens every 10 minutes. The MySQL database keeps a week worth of logs.

Is it possible to make MySQL forward such logs to an O开发者_Go百科racle database if any of the log messages match certain criteria? For example, MySQL should forward all logs if the line starts with "ABC".

Can this be done?

---

Here is one way of doing it (I've mainly worked with Oracle, so the programming is on the Oracle side):

1. Create a DB link from Oracle to mySQL. Using the DB link Oracle can read tables in the mySQL database. This requires Heterogeneous Services.

2. Set up an Oracle job that reads the MySQL table and inserts the records in an Oracle table. The job executes SQL that looks similar to this:

   INSERT INTO oracle_log_table (field1, field2, field3)
SELECT field1, field2, field3
FROM mySQL_link.mysql_log_table
WHERE mySQL_link.mysql_log_table.line LIKE "ABC%"
AND _expression to check that the line is new_;

---

MySQL won't do it for you. However you could use a script to automate this process.

You can use the mysqldump utility to get records matching your criteria for export to Oracle. Something like this (substitute your db user, db password, 'ABC' column name, database name and table name):

mysqldump --user=DBUSER --password=DBPASS --compatible=oracle 
    --no-create-db --no-create-info  
    --where="log_line LIKE 'ABC%'" 
    --result-file=sql_for_oracle.sql
    DATABASE_NAME TABLE_NANE

Then you can use sqlplus on Oracle to import sql_for_oracle.sql into Oracle.

---

You can do this with GoldenGate for MySQL (see chapter 17 in the Administrator's Guide).