Dynamic change to innodb_flush_log_at_trx_commit This is related to this question. It does help to get better performance for InnoDB tables.

According to MySQL manual, innodb_flush_log_at_trx_commit is a global dynamic variable. Thus, I can change it using SET GLOBAL command and it seems to be working.

mysql&gt; SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected

mysql&gt; SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set


But, it did not make the actual MySQL setting changed.
When I updated my.cnf and restarted the MySQL server,  it did work. So, I cannot change the global variable at run time?

I prefer the default value innodb_flush_log_at_trx_commit=1, but I need to change it to 2 before I run a restore process for a large database to get faster.
But when the process done, I want to change the value back to 1.
Is it possible to do this at run time?

I don't have access to my.cnf on my shared hosting server.
While I agree with Rolando's recommendation to change innodb_flush_method, I wasn't 100% clear what you meant by:


  it did not make the actual MySQL setting changed


I want to point out the caveat that making a change to the GLOBAL variable affects any new connections, but does not modify the current session (emphasis mine):


  The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement).


So to check that:

mysql&gt; SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)


mysql&gt; SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql&gt; SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql&gt; SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql&gt; connect;
Connection id:    6
Current database: *** NONE ***

mysql&gt; SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.00 sec)