2

Ok so I have been trying to upgrade MySQL 5.5 to 5.6. I have been given this set my.cnf file (see below). I was able to uninstall the MySQL 5.5 rpms and install the 5.6. When I reload the back up my.cnf file the mysql server does not want to start. Is there something wrong with this file? I have ran mysql_install_db. However, I cannot get the server to start up with my my.cnf file. I have looked in the error log, but it hasn't been copying anything recent to it. The my.cnf works when it is blank.

This is what my my.cnf file from MySQL 5.5 looks like (see below). I need this my.cnf file to work on the 5.6 version. Anyone have any incite?

# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port            = 3306
socket          = /u02/mysqldata1/mysqld_3306.sock
#
#
[mysqld_safe]
socket          = /u02/mysqldata1/mysqld_3306.sock
nice            = 0
#
#
[mysqld]
#performance_schema=1
user            = mysql
socket          = /u02/mysqldata1/mysqld_3306.sock
port            = 3306
basedir         = /usr
datadir         = /u02/mysqldata1/data/3306
tmpdir          = /u02/mysqldata/tmp
skip-external-locking
character_set_server    = utf8
lower_case_table_names  = 1
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address            = 0.0.0.0
max_allowed_packet              = 64M
max_connections                 = 500
max_connect_errors              = 100
max_heap_table_size             = 256M
tmp_table_size                  = 256M
net_buffer_length               = 8K
sort_buffer_size                = 4M
read_buffer_size                = 1M
read-rnd-buffer-size    = 2M
table_cache                     = 1024
table-definition-cache  = 512
thread_stack                    = 256K
thread_cache_size               = 256
sql-mode                        = NO_AUTO_CREATE_USER
query_cache_size                = 64M
query_cache_limit               = 4M
#
# MyISAM Configuration
#
key_buffer_size                 = 512M
myisam_sort_buffer_size         = 128M
ft_min_word_len                 = 3
myisam_recover_options          = BACKUP,FORCE
myisam_repair_threads           = 6
myisam_use_mmap                 = 1
#
# General Logging
#
log_error                       = /u02/mysqldata/mysqllog/3306/err/error.log
long_query_time                 = 1
slow_query_log                  = 1
slow_query_log_file             = /u02/mysqldata/mysqllog/3306/slow/mysql_slow.log
general_log                     = 0
general_log_file                = /u02/mysqldata/mysqllog/3306/general/mysql_general.log
#
# Binary Log and Replication Configuration
#
log_bin                        = /u02/mysqldata/mysqllog/3306/bin/mysql-bin.log
log-bin-index         = /u02/mysqldata/mysqllog/3306/bin/mysql_bin.index
binlog-cache-size     = 256K
binlog_format         = ROW
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
relay-log               = /u02/mysqldata/mysqllog/3306/relay/mysql_bin.log
relay-log-index         = /u02/mysqldata/mysqllog/3306/relay/mysql_bin.index
expire-logs-days        = 7
max_binlog_size         = 100M
sync_binlog             = 0
#
# Replication
#
server-id=129206
#log-slave-updates
#replicate-ignore-db = mysql
#replicate-ignore-table=
slave_transaction_retries = 20
#slave-skip-errors = 1062
#auto_increment_increment = 3
#auto_increment_offset = 3


#
# InnoDB Configuration
#

innodb_file_format                      = Barracuda
innodb_file_format_max                  = Barracuda

innodb_data_home_dir                    = /u02/mysqldata1/innodb/3306/data
innodb_log_group_home_dir               = /u02/mysqldata1/innodb/3306/log
innodb_file_per_table
innodb_data_file_path                   = ibdata1:10M:autoextend

transaction-isolation                   = READ-COMMITTED

innodb_flush_log_at_trx_commit          = 0
innodb_log_buffer_size                  = 8M
innodb_log_file_size                    = 512M

innodb_buffer_pool_size                 = 500M
innodb_additional_mem_pool_size         = 128M

innodb_flush_method                     = O_DSYNC
innodb_thread_concurrency               = 0
innodb_lock_wait_timeout                = 60
innodb-open-files                       = 500
innodb-support-xa                       = 0


#
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet      = 128M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 1G
  • 1
    See here for help on formatting your questions correctly. – terdon Jul 25 '14 at 19:02
  • i reformated it. – lkalti Jul 28 '14 at 16:55
1

How much RAM does your server have? On a mostly InnoDB database the 500M is too high for a key buffer size. I generally start with 32M. You can not run MySQL with it set to 0.

If the innodb_buffer_size + the key_buffer_size = > RAM then you will have problems.

Also I generally need this setting in a secure network: bind-address = 0.0.0.0

A good starting place for my.cnf is here: https://tools.percona.com/wizard

1

I think you need to comment out these two lines in the my.ini

table_cache and max_connection

Hope that helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.