MySQL does not read user specific options in ~/.the.cnf

character-setconfigurationMySQL

I want the server to run an initial SQL query every time it starts:

[mysqld]
init-connect='SET NAMES utf8mb4'

and I want it to be only user specific not globally. so I created a .my.cnf in my user's home directory(~/.my.cnf) but when I try to see if it has affected the server it does not show my added setting and it seems that my settings are being ignored by the server:

    mysqld --print-defaults
------------------------------------
--user=mysql 
--pid-file=/var/run/mysqld/mysqld.pid 
--socket=/var/run/mysqld/mysqld.sock 
--port=3306 
--basedir=/usr 
--datadir=/var/lib/mysql 
--tmpdir=/tmp 
--lc-messages-dir=/usr/share/mysql 
--skip-external-locking 
--bind-address=0.0.0.0 --

key_buffer_size=16M 
--max_allowed_packet=16M 
--thread_stack=192K 
--thread_cache_size=8 
--myisam-recover-options=BACKUP 
--query_cache_limit=1M 
--query_cache_size=16M 
--log_error=/var/log/mysql/error.log 
--expire_logs_days=10 
--max_binlog_s

ize=100M 
--default_password_lifetime=0

also this is the result of SHOW VARIABLES LIKE 'character_set_%'; :

I want theses params to be set to 'utf8mb4' wach time the server boots.

character_set_client
character_set_results
character_set_connection

also I should say that I use Laravel Vagrant as my development environment

Best Answer

init-connect is ignored when you connect as the MySQL root user.

Check the file config/database.php for Laravel. It needs to say something like: 'mysql' => [..., 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', ...]

Related Solutions

Thesqld_safe version different than thesqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.

MySQL high CPU usage (MyISAM table indexes)

SUGGESTION

Your key_buffer_size is only 16MB, but your MyISAM indexes total 59MB. In a highly-trafficked website, I can see index pages getting kicked out of and pushed into the MyISAM keycache frequently. This could have an affect on indexes being updated and overall write performance on the MyISAM tables.

You could just bump up key_buffer_size to 64 MB.

You must add this to /etc/my.cnf

[mysqld]
key_buffer_size=64M

You don't even need to restart mysql. Just run this:

SET @MB = 1024 * 1024;
SET GLOBAL key_buffer_size = @MB * 64;

You can also convert the row format of all your MyISAM tables. This will just about double the disk space used by will increase read performance 20-25%. I wrote about this before as well:

Here is a script to convert all your MyISAM tables to Fixed Row Format

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ROW_FORMAT=Fixed;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN ('information_schema','mysql','performance_schema')"
mysql -uroot -p -A --skip-column-names -e"${SQLSTMT}" > /root/MakeFixedRows.sql
mysql -uroot -p < /root/MakeFixedRows.sql

ALTERNATE SUGGESTION

Given you have a total of 488 MB of of MyISAM data and indexes, I would like to suggest the following: Switch Everything to InnoDB. I have two reasons:

REASON # 1

MyISAM only caches indexes. InnoDB caches data and indexes. See my post : What are the main differences between InnoDB and MyISAM?

This will reduce disk I/O for reading data and indexes. In your particular case, you can easily handle an InnoDB Buffer Pool of 512M and basically fit everything into RAM.

REASON # 2

The MyISAM storage engine cannot access multiple CPUs. InnoDB can. In fact, I have many posts on tweeking InnoDB for this:

Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

EPILOGUE

I would like you really consider the conversion from MyISAM to InnoDB