Question Editing my.cnf - Global MySQL Settings

[Dukemaster]

Hi Plesk-Friends,
I've a special question for a beginner about how to change the global innodb_flush_log_at_trx_commit value from 1 to 2 for better performance in my.cnf in the correct and best way.

 --innodb-flush-log-at-trx-commit[=2]

Ubuntu 14.0.4 with Plesk Onyx (upgraded from 12.5) on dedicated server (ISP is 1and1) running apache2 with nginx as proxy, all in default configuration but with installed DNS (bind) and Letsencrypt over graceful Plesk Onyx.

I really would be so thankful if someone could give a detailed step-by-step instruction with all bash commands. Over 12 hours I searched in internet but didn't get the correct and safe way.

Thanks for help and lots of greets

 

[UFHH01]

Hi Dukemaster,

you can set the option "2" INSIDE the "[mysqld]" - section, as for example:

...
[mysqld]
innodb_flush_log_at_trx_commit=2
...

Pls. don't forget: Make sure, that you read the official MySQL - documentation, in order to be informed about the usage and risks:

=> http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

( If you have another MySQL - version installed, you are as well able to change the URL from "/5.7/" to "/5.6/", or whatever version you have installed, so that you can be sure to read the depending documentation. )​

 

[Dukemaster]

Thanks a lot for the answer. But my problem is, that I'm not be able to reach the mysqld section. That is my inner problem.
Bash vi /etc/my.cnf or vi /etc/mysql/my.cnf doesn't work to open the daemon. I don't know the correct list of commands opening the mysqld section.

Today my wonderful administrator told me about my big mistake before yesterday and changed the value to 2, today, but has no time to tell me how it works:
mv /etc/mysql/my.conf /etc/mysql/my.cnf.bakup
cp instead of mv - not move but copy
I moved the whole my.cnf instead of copying, that's why I get the server-response
Found no such file or directory...*****

But if someone has the time, passion and will to write the list of commands to edit the value of innodb_flush_log_at_trx_commit in a detailed way, it would be amazing. I know many pleskians and linux users who have the same environment are looking hungry for this description.

 

[UFHH01]

Hi Dukemaster,

let's assume, that you use WINDOWS on your computer, then I could recommend to use "WinSCP" ( a SFTP client, FTP client, WebDAV client and SCP client for windows. => https://winscp.net/eng/download.php ) for example.

Bash vi /etc/my.cnf or vi /etc/mysql/my.cnf doesn't work to open the daemon

Well, "vi" is an EDITOR and a command like "vi /etc/mysql/my.cnf" doesn't open the daemon, it opens the FILE "/etc/mysql/my.cnf".

I hope, that you are aware, that you can't open this file, logged in as a domain-user, you really have to use the system-user "root" for that, because the file is owned by "root : root".

Second, pls. be aware, that ( depending on your operating system and your unique configuration! ) the file can be a SYMLINK to "/etc/alternatives/my.cnf", so consider to try as well to open the file "/etc/alternatives/my.cnf" instead and see how that works for you.

Third, if you would like to inform yourself about the "vi" - usage, pls. consider to visit for example:

=> http://manpages.ubuntu.com/manpages/trusty/man1/nvi.1.html

... or use the command "man vi" over your command line.​

 

[Dukemaster]

Many thanks for your help!
I was logged in as root. Only one well-trusted person has a close look about security and the whole running system, has also the same root access, no one else.
I want to do more and more by myself. Learning and practising. That's the background. And I'm not too stupid to destroy a running system by senseless actions.
However, but to get the real close information is hard. Nobody has exactly the how-to-do in this case. I spent so much time reading tons of good and bad sites. Nobody helps getting the right information.
Another thank you for the hint to WinSCP, just installed it. I always worked only with Putty. This is the problem I have to start the whole story again in administrating my server, now with better informations from the best sources, like You.

 

[Dukemaster]

Thank You once again. Today I was able to use WinSCP for the very first time over years..... Yeah Yeah Yeah... feel happy like an absolute newbie.
First I managed your advice to the backup problem, then just 3 minutes ago I opened in my.cnf, not opening I clicked on edit (german: bearbeiten). So I saw the change of
innodb_flush_log_at_trx_commit=2 what my great administration helper did for me few days ago. I closed editor and downloaded this modified my.cnf for future reinitialisations to my computer.
Then I also looked in /etc/alternatives where a lot of tar or zip files are located, most files are only shortcuts and no my.cnf in it.

I don't know much about WinSCP, but now I see the most important thing: The differende between root and /root/, I ever wondered why I couldn't find /ect/ when I made "ls" (list) in root folder over Putty and only found parallels. Lol...
You can't believe how thankful I am. Amazing help for lifetime. But enough outing me here because of security reasons Better to delete this post..smile