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> SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected
mysql> 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.