Running a SQL Script on Startup



Running sql on startup is something Oracle does via STARTUP trigger.  This can also be done via my MySQL but requires some extra steps
Make sure the source exists - I tested this with a table that I insert into on instance startup.
DB1> desc test.startup_log;
+----------+-----------+------+-----+-------------------+-----------------------------+
| Field    | Type      | Null | Key | Default           | Extra                       |
+----------+-----------+------+-----+-------------------+-----------------------------+
| id       | int(11)   | NO   | PRI | NULL              | auto_increment              |
| login_ts | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

I then created a stored procedure which inserts into the source table:
DROP PROCEDURE test.startup_trigger;
DELIMITER //
CREATE PROCEDURE test.startup_trigger()
       SQL SECURITY DEFINER
BEGIN
  select 'this is a test';
  INSERT INTO test.startup_log (login_ts)
  VALUES (CURRENT_TIMESTAMP());
  commit;
END;
//
DELIMITER ;

On the o/s, I create a file that is called from startup:
[mysql@mysqldb1 DB1 /home/mysql ]$ cat startup.sql
CALL test.startup_trigger();

In the my.cnf file in the mysqld section use init-file=<path/filename> e.g. init-file=/home/mysql/startup.sql
You can check this by running a
DB1> SHOW GLOBAL VARIABLES LIKE 'init%';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| init_connect  |                         |
| init_file     | /home/mysql/startup.sql |
| init_slave    |                         |
+---------------+-------------------------+
3 rows in set (0.01 sec)

FYI
init_connect - A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters. For example, each client session begins by default with autocommit mode enabled. For older servers (before MySQL 5.5.8), there is no global autocommit system variable to specify that autocommit should be disabled by default, but as a workaround init_connect can be used to achieve the same effect:
SET GLOBAL init_connect='SET autocommit=0';

init_slave -  This variable is similar to init_connect, but is a string to be executed by a slave server each time the SQL thread starts. The format of the string is the same as for the init_connect variable.

Note
The SQL thread sends an acknowledgment to the client before it executes init_slave. Therefore, it is not guaranteed that init_slave has been executed when START SLAVE returns. See Section 13.4.2.5, “START SLAVE Syntax”, for more information.

init_file - Read SQL statements from this file at startup. Each statement must be on a single line and should not include comments.

Please see the following helpful link:
http://www.fromdual.com/sites/default/files/logon_trigger_wp.pdf

No comments:

Post a comment