Where should I store a custom ".my.cnf" file?

Question

I am working in a Vagrant box (for development) and I have a provisioner script written in Bash. The script does install MySQL and setup a few things: basically it does what mysql_secure_installation does but without use it. See below:

sudo yum install -y mysql-server
sudo chkconfig --add mysqld && chkconfig mysqld on
mysql --user=root -e "DROP DATABASE IF EXISTS test;USE mysql;DELETE FROM mysql.user WHERE User='';DELETE FROM mysql.db WHERE Db='test' OR Db='test_%';CREATE USER 'root'@'%';GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;FLUSH PRIVILEGES;"

That worked as expected however I need to import a "large" SQL file and MySQL complaints and fails with the famous max_allowed_packet error.

I know the solution is to increase such value by editing the my.cnf file and add it to the section [mysqld].

Because the provisioner script should do this by default I am looking for the easiest way to do it which for me is: copy a file from the host to the guest while provisioning. I could use commands like sed to insert that line in the default my.cnf file but is overcomplicate something easy.

Having said that I did some research and from docs found here I can see a few places where I could store that custom file:

+---------------------+------------------------------------------------------------+
|      File Name      |                          Purpose                           |
+---------------------+------------------------------------------------------------+
| /etc/my.cnf         | Global options                                             |
| /etc/mysql/my.cnf   | Global options (as of MySQL 5.1.15)                        |
| SYSCONFDIR/my.cnf   | Global options                                             |
| $MYSQL_HOME/my.cnf  | Server-specific options                                    |
| defaults-extra-file | The file specified with --defaults-extra-file=path, if any |
| ~/.my.cnf           | User-specific options                                      |
+---------------------+------------------------------------------------------------+

I do know also that I could execute a query as follow (not sure if it's correct since I took it from here during my research):

SET GLOBAL max_allowed_packet = 1073741824;

Now that I have all that information above I wanted to ask: if I choose the latest option "User-specific options" and knowing that by default a vagrant user is created, knowing also that MySQL runs as root (I think so I might be wrong):

Where I should store the custom file? The vagrant user home directory? The root user home directory?

Is not clear to where ~/.my.cnf points to in the docs.

I am open to suggestions as well if the best way to do it is by running the query or by putting the needed config in some other path just let me know.

The MySQL version is as shown below:

[root@appserver home]# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Answer 1

To keep the things simple place all custom server settings into the /etc/my.cnf file. Some user-specific options can be stored in the ~/.my.cnf where ~/ means "the homedir for the given system user". Also note the leading dot in the user-specific file name. That file is used for command-line client utilities like mysql or mysqldump only, not for server. Therefore the [mysqld] section can be completely omitted in the user-specific config file.