14

What would be the best way to change the mysql bind-address in the my.cnf from a shell script? Is there a way using a tool like mysqladmin or should I use sed or should I simply append it to the my.cnf and hope that the second entry overwrites the first (this one sounds sick). I try to write a simple shell provisioner for vagrant that runs after installing mysql-server to allows connections from outside the vm.

4 Answers 4

20

Adding to what's been said by @nonsenz, If you use puphpet.com's provisioning scripts then you can add a bash file to the /puphpet/files/startup-always folder and place all your commands in there. Anytime vagrant starts or reloads it will call the script:

mysql.sh

#!/bin/bash

echo "Updating mysql configs in /etc/mysql/my.cnf."

if [ 'sudo sed -i "s/.*bind-address.*/bind-address = 0.0.0.0/" /etc/mysql/my.cnf' ]; then

    echo "Updated mysql bind address in /etc/mysql/my.cnf to 0.0.0.0 to allow external connections."

    sudo /etc/init.d/mysql stop
    sudo /etc/init.d/mysql start

fi

Even Better (to enable cleaner output)

mysql.sh

#!/bin/bash

echo "Updating mysql configs in /etc/mysql/my.cnf."
sudo sed -i "s/.*bind-address.*/bind-address = 0.0.0.0/" /etc/mysql/my.cnf
echo "Updated mysql bind address in /etc/mysql/my.cnf to 0.0.0.0 to allow external connections."
sudo service mysql stop
sudo service mysql start

This enabled me to connect my IDE (PhpStorm) directly to the database on my guest machine via vagrant. Of course I gave the guest mysql user access to '%' and forwarded port 3306 on the guest to a port (3309) on the host. The access grant could also be done in this file as well.

Even Better'er (mysql execute add in)

mysql.sh

#!/bin/bash

echo "Updating mysql configs in /etc/mysql/my.cnf."
sudo sed -i "s/.*bind-address.*/bind-address = 0.0.0.0/" /etc/mysql/my.cnf
echo "Updated mysql bind address in /etc/mysql/my.cnf to 0.0.0.0 to allow external connections."

echo "Assigning mysql user user1 access on %."
sudo mysql -u user1 -pyourpassword --execute "GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY 'yourpassword' with GRANT OPTION; FLUSH PRIVILEGES;" yourdatabase
echo "Assigned mysql user user1 access on all hosts."

sudo service mysql stop
sudo service mysql start
1
  • Bottom script was perfect for me bar a few small bits (CentOS 6.5 box) - Using sudo resulted in a "sorry, you must have a tty" error (fixed by just removing 'sudo' from the statements) and my MySQL service was restarted using /etc/init.d/mysqld stop and /etc/init.d/mysqld start. Both of those may just be CentOS quirks rather than errors with the script. Sep 24, 2014 at 16:23
13

It looks like sed is the easiest way in this context (after a clean install):

sudo sed -i "s/.*bind-address.*/bind-address = 0.0.0.0/" /etc/mysql/my.cnf 
1
  • I think the regex should have /g because there are multiple at least 2 lines that have bind-address Jul 22, 2020 at 13:39
4
augtool -s set '/files/etc/mysql/my.cnf/target[ . = "mysqld"]/bind-address 0.0.0.0'

The Ubuntu package is called 'augeas-tools'

4
  • 1
    This answer need some more explanation.
    – kasperd
    Sep 30, 2015 at 10:33
  • Using Augeas (as noted in one of the other answers) allows manipulation of configuration files. This example shows how this is done. Oct 19, 2015 at 5:45
  • From attempting to use this answer: -s is "auto save any updates". set is action Augeas will take with this with this line. /files/etc/mysql/my.cnf which part/file Augest will work on (it loads all of /etc). /target is an (?array?) of decoded values. [ . = "mysqld"] selects whichever target number has the value "mysqld". bind-address is the bind-address field in the "mysqld" section, and the value after the space 0.0.0.0 is the value it will be set to.
    – Mike Lutz
    Jun 27, 2016 at 16:33
  • /files is mandatory before the real path, do not delete it, that is not an "example" Jan 12, 2023 at 15:43
1

Sounds like you're looking for Augeas.

The project page is here and some documentation and examples are here. MySQL is included on the stock lenses page but unfortunately the documentation links to a 404 page now.

There are also some examples of using it within Puppet here.

3
  • looks interesting. but i think before using something like this just to change the bind-address i stick with sed :-)
    – nonsenz
    Mar 26, 2014 at 11:48
  • puppetlabs/inifile could work too, once you get rid of vagrant and move to professional configuration management... Mar 31, 2014 at 11:30
  • 1
    The sed approach will only work if the entry already exists, since sections matters in the inifile. The Augeas approach will work regardless, because it will place the entry in the right position.
    – raphink
    Aug 4, 2014 at 7:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .