When I first ran across Events in MySQL, I wondered what I could use them for, particularly things I normally did with PHP or Python.  In most instances where I am loading data from one database to another, I’m also performing tasks to archive the data that I’m exporting.  Most often, the data I need can be adequately parsed from a CSV or tab delimited file, so it can be faster to let the database do the heavy lifting, and then have the Python or PHP manipulate any text.

Creating the Procedure & Event

For this post, I’m going to assume that we want to archive user data into a report archive table, and write an outfile that will be used by another script.  First, we’ll create a stored procedure that we can call either manually, or by the EVENT scheduler.

Stored Procedure

delimiter //
CREATE PROCEDURE archive_and_write_user_outfile()
BEGIN
 -- Copy data to an archive table, where curdate() specifies an export_date field
 INSERT INTO user_archive 
 SELECT user_id, sourceId, username, given_name, family_name, email, acct_status, acct_created, curdate() FROM users;
 
 -- Write out a tab delimited file
 -- using the @path as specified by: show variables like "secure_file_priv";
 -- if that flag is turned off, you can specify another directory path
 SET @path := '/var/lib/mysql-files/';
 -- add 0 to remove the hyphens
 SET @filename := concat(@path,curdate()+0,"_user_outfile.txt");
 SET @query_filename := concat("SELECT user_id, sourceId, username, given_name, family_name, email, acct_status, acct_created
 INTO OUTFILE '", @filename,
 "' FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"'
 LINES TERMINATED BY '\n'
 FROM users;");

 prepare statement_one from @query_filename;
 execute statement_one;
 deallocate prepare statement_one;

END //
delimiter ;

The archiving is simple in this example, but you can get as complicated as you wish in the archive process, or even do something entirely different.

Creating the Event

Once we have a stored procedure, we can create the event that will call it.

CREATE EVENT e_process_user_outfile
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Writes outfile of all users after archiving.'
    DO CALL archive_and_write_user_outfile();

The Rest of the Transfer

Once we have a file, we can do several things, such as using PHP or Python to process the file and upload into a separate database, adjusting the data as needed or gathering additional information.  Or, we could simply use a shell script to move the file to another server, and then use a cron job to push the data into the database there; although I would expect that the data would be transformed more remarkably than just pushing data from one database to another.  If that were the case, then a simple dump and transfer would be better.

 

Leave a Reply

Your email address will not be published. Required fields are marked *