Solved

MySQL database connecting breaks after changing my.cnf

Posted on 2015-02-24
23
227 Views
Last Modified: 2015-04-15
Hi,

Here is an instruction list to change the default db setting:
http://help.directadmin.com/item.php?id=44

When i edit this file a bit, because i have 4gb ram instead of 1-2gb the sites on the vps lose connection to the db.
See attached file with my settings.
What could cause this? Server = vps, directadmin, mysql, all updated to latest version.

# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password	= your_password
port		= 3306
socket	= /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 1M

table_cache = 1K
table_definition_cache = 4K
open_files_limit = 3K

sort_buffer_size = 2M
read_buffer_size = 2M
write_buffer = 2M

read_rnd_buffer_size = 8M

#thread_cache_size = 8
thread_cache_size = 16
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 3M
long_query_time = 5
join_buffer_size = 3M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
low_priority_updates = 1
concurrent_insert = 2

tmp_table_size = 128M
max_heap_table_size = 128M

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
#
# binary logging format - mixed recommended 
#binlog_format=mixed

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Open in new window

0
Comment
Question by:peps03
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 7
  • 2
23 Comments
 
LVL 12

Expert Comment

by:Kent W
ID: 40629233
What is the VPS technology / hypervisor in use here?
0
 

Author Comment

by:peps03
ID: 40629264
cent os 6.6?
0
 

Author Comment

by:peps03
ID: 40629312
i don't know the vps technology, but does that have anything to do with the mysql config / settings file?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 12

Expert Comment

by:Kent W
ID: 40629318
You stated it's a VPS.  Is the virtualization technology used VMWare/ESX, OpenVZ, Xen, Hyper-v.....etc....?
0
 
LVL 12

Expert Comment

by:Kent W
ID: 40629324
Answering the second question - but if it's, say, OpenVZ or a "budget" solution, it may be actively killing threads. MySQL is quite intensive in it's process usage.
0
 

Author Comment

by:peps03
ID: 40629376
Ok, where can i find it? I hire it from a good company.
And it says still 1 gb ram is free (not used).

btw i'm using Apache 2.4.12 and MySQL 5.5.41.

Maybe an error in the my.cnf file?
0
 
LVL 12

Expert Comment

by:Kent W
ID: 40629410
It very well could be.  Hopefully you have the original my.cnf backed up.
I would take this new one and do a "diff" on it, and see if there are any key lines that got left out, or vice-versa.
Such as the innodb_file_per_table=1 it was talking about in the link you sent.

If you have linux shell access, you can simply to a
diff /path/to/original/my.cnf /path/to/new/my.cnf
It will spit out all the difference between the documents.  
If you are windows based, maybe download a trial of "beyond compare" and do the same.

Your VPS provider should state on the web page what virtualization technologies they provide.  Possibly on your original order receipt also?
0
 

Author Comment

by:peps03
ID: 40629439
Blade vps? The vps is ssd based.

The original default my.cnf file is:
[mysqld]
bind-address = 127.0.0.1
bind-address = 127.0.0.1
bind-address = 127.0.0.1
local-infile=0
innodb_file_per_table

Open in new window


My file lacks the innodb_file_per_table, what does that mean/do?
0
 

Author Comment

by:peps03
ID: 40629462
Does it matter where i add  innodb_file_per_table=1 in the file?
0
 
LVL 12

Expert Comment

by:Kent W
ID: 40629482
You only want that if your original had it.  Basically what you want to look for is something in your original that didn't make it over to the new conf file.  If your original didn't have the innodb_file_per_table directive, there's no reason to worry about it.  It just tells mysql to store innodb talbes and indexes in a separate file, rather than the default system tablespace.  

Does your new my.cnf have the bind-address = 127.0.0.1?  This is "localhost".  Your db connection file, wherever that's named, should have a user / pass / host.  This would be the "host" address - "localhost" or 127.0.0.1.
0
 

Author Comment

by:peps03
ID: 40629487
Should i also add:
bind-address = 127.0.0.1
bind-address = 127.0.0.1
bind-address = 127.0.0.1
local-infile=0

I tested with just adding: innodb_file_per_table=1 and i still get the connection error. Maybe this is also required?
0
 
LVL 12

Expert Comment

by:Kent W
ID: 40629503
I'd try the bind-address first, it may be binding to your public IP.  If you don't have an innodb_file_per_table in your original file, it's not relevant.  If you DO, on the off-chance (it's not default, but it's possible directadmin may use it) just place it under the same section as in your original.

If you have shell access, you can do a
netstat -n -a |grep 3306
and actually see which IP you are binding to. It will be the IP to the LEFT of ":3306".  (colon 3306)
0
 

Author Comment

by:peps03
ID: 40629510
If you DO, on the off-chance (it's not default, but it's possible directadmin may use it) just place it under the same section as in your original.

Open in new window


I do, see my previous post id ID: 40629439. I added it.

netstat -n -a |grep 3306
tcp        0      0 127.0.0.1:3306              0.0.0.0:*                   LISTEN

Ok, i'll also add:
bind-address = 127.0.0.1
local-infile=0
0
 

Author Comment

by:peps03
ID: 40629513
well actually my original says "innodb_file_per_table" with no value..
0
 

Author Comment

by:peps03
ID: 40629542
Should i add it? It doesn't say innodb_file_per_table=1, but it is there in the original..
0
 

Author Comment

by:peps03
ID: 40629636
Ok now my files starts with:
[mysqld]
bind-address = 127.0.0.1
local-infile=0
port = 3306
innodb_file_per_table
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 1M

Open in new window


I uploaded it via sftp, restarted mysql via directadmin like before, and when i refresh the sites, they all can't connect to the db. So i re-uploaded the backup, restarted mysql via directadmin, and all sites can connect again.

Weird.
0
 
LVL 12

Assisted Solution

by:Kent W
Kent W earned 350 total points
ID: 40631223
I left work in the middle of our convo yesterday, but I'm glad you got it going.
Working OK now?
0
 

Author Comment

by:peps03
ID: 40632676
Nope..
No matter what i try, when i upload a new my.cnf the connection to the db drops on all sites. Also the Memory usage on mysqld stays at 0.

My last try was, so with very few additional lines to the original:
[mysqld]
bind-address = 127.0.0.1
bind-address = 127.0.0.1
bind-address = 127.0.0.1
local-infile=0
innodb_file_per_table

skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 1M

table_open_cache = 16384
table_cache = 1K
table_definition_cache = 4K
open_files_limit = 3K

sort_buffer_size = 2M
read_buffer_size = 2M
write_buffer = 2M

read_rnd_buffer_size = 8M

#thread_cache_size = 8
thread_cache_size = 16
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
long_query_time = 5
join_buffer_size = 3M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
low_priority_updates = 1
concurrent_insert = 2

tmp_table_size = 128M
max_heap_table_size = 128M

Open in new window

0
 

Author Comment

by:peps03
ID: 40637029
Does someone have an idea?

And what does this mean without a value:
innodb_file_per_table ?

I didn't change the default MySQL settings.
0
 
LVL 62

Accepted Solution

by:
gheist earned 150 total points
ID: 40637893
Can you revert to previous cnf file apply the change line by line?
Why you now say you did not change mysql settings when in question you state you did?
What do you see in mysql.log (usually in /var/log, sometimes in first data directory) - thats the only place startup errors can get.
0
 

Author Comment

by:peps03
ID: 40696405
Had this issue on hold for some weeks. Sorry for that.
I'm picking it up now. I'll try the changes line by line! Good one!
0
 
LVL 62

Expert Comment

by:gheist
ID: 40696529
Shortcut is to rview startup messages in log. Try that first.
0
 

Author Closing Comment

by:peps03
ID: 40724860
Thanks both!
Got it working.

These two lines gave issues:
#write_buffer = 2M
#table_cache = 1K
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question