A 3-post collection

Mysql "-e" writing not to log bin

Since, mysql version 5.7 there is no easy way to turn off the log bin globally. In the other hand, you can stop the log bin locally (during session) using SET sql_log_bin = 0 but this way the result won't last over sessions.

Thus, rendering mysql -u {user} -p{user} -e "...." much harder to do without writing to the bin log. However, there is another way. Nothing prohibits you from sandwiching the sql command to keep it away from the bin log, this way :

mysql -u {user} -p{user} -e "SET sql_log_bin=0; {sql};SET sql_log_bin=1"  

By sandwiching the sql, your sql will be run in the context which log bin is not working and you are fine !

อ่านต่อ »

Homebrew's MySQL binds by default

I tried configuring bind-address option in my.cnf many attempts, but found no success.

After running ps -ax | grep mysql it shows:

83783 ??         0:00.03 /bin/sh /usr/local/opt/mysql/bin/mysqld_safe --bind-address= --datadir=/usr/local/var/mysql  

It's kinda like mysqld is bound to run as regardless of the my.cnf

A Post on DBA.stackexchange

"I have been banging my head against this error today on OSX Yosemite with MySQL 5.7 recently updated with Homebrew. Following suggestions on StackOverflow and elsewhere, I hunted around after my.cnf files all of which specified bind-address= I even removed and reinstalled MySQL following these instructions and then reinstalled using brew install mysql. Still no remote connections allowed.

It wasn't until I ran ps -ax | grep mysql and noticed that the bind address was being passed in the launch command (thus overriding any my.cnf files) that I dug some more and found out that Homebrew binds MySQL to by default.

Editing ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist to change --bind-address= to --bind-address= solved my problem (the latter should be changed to a specific IP address if this isn't just a development machine).

I feel this is a vital piece of information that was lacking from most of the resources I consulted so hopefully posting this here will help someone else!

EDIT: As LeandroCR indicated in the comments, running brew services restart mysql will overwrite the plist file in LaunchAgents with the default one, leading to MySQL mysteriously refusing connections again. So better advice than what I originally wrote is the following:

Edit /usr/local/Cellar/mysql/

อ่านต่อ »