Solved

MySQL database connecting breaks after changing my.cnf

Posted on 2015-02-24
23
213 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
  • 14
  • 7
  • 2
23 Comments
 
LVL 12

Expert Comment

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

Author Comment

by:peps03
Comment Utility
cent os 6.6?
0
 

Author Comment

by:peps03
Comment Utility
i don't know the vps technology, but does that have anything to do with the mysql config / settings file?
0
 
LVL 12

Expert Comment

by:Kent W
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Does it matter where i add  innodb_file_per_table=1 in the file?
0
 
LVL 12

Expert Comment

by:Kent W
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Expert Comment

by:Kent W
Comment Utility
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
Comment Utility
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
Comment Utility
well actually my original says "innodb_file_per_table" with no value..
0
 

Author Comment

by:peps03
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 61

Accepted Solution

by:
gheist earned 150 total points
Comment Utility
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
Comment Utility
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 61

Expert Comment

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

Author Closing Comment

by:peps03
Comment Utility
Thanks both!
Got it working.

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hi, in this article I'm going to teach you how to run your own site, and how to let people in (without IP). I'll talk about and explain each step... :) By the way, everything in this Tutorial is completely free and legal. This article is for …
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now