Solution

To set the system variables dynamically and easily without access to the server's options file, you can do so by creating stored procedures. To create them, do something like the following:

DROP PROCEDURE IF EXISTS set_global_dynamic;
DELIMITER //
CREATE PROCEDURE set_global_dynamic(IN pvar CHAR(64), IN pval CHAR(255))
BEGIN
   CREATE TABLE IF NOT EXISTS mysql.dynamics 
      (variable CHAR(64), value CHAR(255), PRIMARY KEY (variable));
   SET @cmd = CONCAT("SET GLOBAL ", pvar, " = '", pval, "'");
   PREPARE stmt FROM @cmd;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
   SET @cmd = '';
   REPLACE INTO mysql.dynamics VALUES (pvar, pval);
 END;
 //
 DELIMITER ;

 DROP PROCEDURE IF EXISTS load_global_dynamic;
 DELIMITER //
 CREATE PROCEDURE load_global_dynamic ()
 BEGIN
   DECLARE done INT DEFAULT 0;
   DECLARE pvar CHAR(64);
   DECLARE pval CHAR(255);
   DECLARE cur CURSOR FOR SELECT variable, value FROM mysql.dynamics;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   DECLARE EXIT HANDLER FOR SQLSTATE '42S02' BEGIN END;

   OPEN cur;
   REPEAT
     FETCH cur INTO pvar, pval;
     SET @cmd = CONCAT("SET GLOBAL ", pvar, " = '", pval, "'");
     PREPARE stmt FROM @cmd;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
   UNTIL done END REPEAT;
   CLOSE cur;
 END;
//
DELIMITER ;

For the following two points you will need proper access to the server you are executing this. Ask your system administrator for access or assistance if you don't have direct access.

Create a file init.sql containing the following:

CALL load_global_dynamic();

Next, update your MySQL option file and add the following:

[mysqld] init_file = /path/to/init.sql

After all is setup, you can set global dynamic variables with an SQL statement like this:

CALL set_global_dynamic('storage_engine', 'InnoDB');

When you execute the set_global_dynamic stored procedure with a global variable and the wanted value, two things are done: SET GLOBAL variable = value. The second task performed is REPLACE INTO mysql.dynamics VALUES (variable,value).

The file passed as argument to the --init-file option will be executed on each start of MySQL and executes the stored procedure load_global_dynamic(). The latter will get data from mysql.dynamics and re-apply what you have set using the set_global_dynamic() stored procedure.

There are lots of possibilities here and the stored procedures can surely be extended and made more robust. Use cases are for example where the MySQL server is administrated by a DBA not having direct access to the machine itself. She needs to contact the system administrator each time asking to make a variable permanent. With the solution in this article, it makes it possible for dynamic variables to become permanent even when the system administrator needs to reboot the server itself.

Notes:You will need to make sure that every time you set something you use the set_global_dynamic() procedure. You can of course insert manually in the table mysql.dynamics. To make this work you'll need the system administrator make sure that MySQL server is configured to use the --init-file.