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