MySQL question

  • How do I change innodb_flush_log_at_trx_commit=1 to innodb_flush_log_at_trx_commit=2 ? This is on a CentoS 6.7 x86_64 server and I have SSH access but don't know which command lines to use.

    • Official Post

    You need to edit the my.cnf file, usually found in /etc/mysql/my.cnf on Debian/Ubuntu. On CentOS it seems to be sometimes located at /etc/my.cnf instead, maybe ask Google where exactly it is located in your system.

  • On CentOS it seems to be sometimes located at /etc/my.cnf instead, maybe ask Google where exactly it is located in your system.

    I think I got to the correct /etc/my.cnf file but I did not see anything that said innodb_flush_log_at_trx_commit there, I saw a couple of lines that said innodb_xxxx_ but not what I need to change.

    • Official Post

    If an option is not present, the default value will be used instead. As such you should add innodb_flush_log_at_trx_commit=2 inside the section marked with [mysqld]. It doesn't matter if it is before or after some other lines in the same section as long as it is between [mysqld] and any other section marked with [someFancyStuffInBrackets] (or the end of file whichever occurs first).

  • If an option is not present, the default value will be used instead. As such you should add innodb_flush_log_at_trx_commit=2 inside the section marked with [mysqld]. It doesn't matter if it is before or after some other lines in the same section as long as it is between [mysqld] and any other section marked with [someFancyStuffInBrackets] (or the end of file whichever occurs first).

    I got an error saying permission denied when trying to save my changes, it looks like I will have to create a support ticket with my host. Thanks for trying to help @Alexander Ebert

  • You may need to stop the server running (MYSQL) before you can save any changes in that file. And then the server restarted.


    Not sure? But don't think you can save changes in that file while MYSQL is running, not if anything like WAMP on localhost it won't let you do it.

  • sounds a bit like a shared hosting w/ ssh access to me.

    It's managed VPS but all of the accounts (my websites) share the same IP address. I'm not sure if I have the same permissions as root when trying to change something (mysite@my-shared-IP-address in Putty) on them through SSH access and it's something that I'm trying to get figured out with my host through a support ticket right now.

  • You may need to stop the server running (MYSQL) before you can save any changes in that file. And then the server restarted.


    Not sure? But don't think you can save changes in that file while MYSQL is running, not if anything like WAMP on localhost it won't let you do it.

    In a Linux environment that file can be edited any time. The only thing required to make it take effect is issuing a restart to the mySQL process.

  • I was told that I could have done it globally while logged in as root. It has been taken care of now.

  • @GTB No, MySQL isn't blocking it because the configuration is loaded on startup rather than permanently being monitored.

    In a Linux environment that file can be edited any time. The only thing required to make it take effect is issuing a restart to the mySQL process.

    OK, I was just going off running WAMP on Windows localhost wondering if he may need to stop MYSQL first but guess that is different because Windows won't let you edit and save a file while it's being used by another program.