Mac OS X Yosemite and MySql configuration my.cnf file

This is just going to be a super quick post.

I’m running Mac OS X Yosemite and MySql 5.6.25.  I was having problem with importing a SQL file in to it.

I was running the following mysql -uroot mydatabasename < prod-database-backup.sql  and it would get to the same point each time at 80k line and then just bomb out.

ERROR 2006 (HY000) at line 23388: MySQL server has gone away

I had no idea why. I compared the MySQL configuration file, my.cnf, to my coworkers and saw they had some differences.

Adding the following to /private/etc/my.cnf (and restarting MySql) made all the difference. The import was now successful.

Here’s what I had to add:

updating my.cnf fixed the issue with the local import; 

This is what I added to mine:

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

max_allowed_packet=128M

max_connections=15

query_cache_size=16M

max_heap_table_size = 64M

tmp_table_size = 128M

# skip-innodb

thread_cache_size=4

 

 

Here’s my entire file… just incase you were curious 🙂

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 768M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = …..
# datadir = …..
# port = …..
# server_id = …..
# socket = …..

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet=128M
max_connections=15
query_cache_size=16M
max_heap_table_size = 64M
tmp_table_size = 128M

# skip-innodb
thread_cache_size=4

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 384M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M
innodb_log_file_size = 128M

table_open_cache = 700

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

Comments

  1. Pingback: El Capitan Mysql “ERROR! The server quit without updating PID file” | Johnish.com

Leave a Reply