Limited Time Offer!

For Less Than the Cost of a Starbucks Coffee, Access All DevOpsSchool Videos on YouTube Unlimitedly.
Master DevOps, SRE, DevSecOps Skills!

Enroll Now
General

MySQL Basic Troubleshooting Guide | MySQL common Issues

May 15, 2013 Leave a Comment
mysql-troubleshooting

mysql-troubleshooting

PLEASE NOTE: I am currently reviewing this Article.

How to check the mysql file location:
> which mysql
> locate mysql

Check mysqld process is started or not?
> service mysqld status
> “mysqld is stopped” – Means mysqld is not running
> “mysqld: unrecognized service” – Means mysqld is not set in service. This can be register using chkconfig under /etc/init.d.
> ps -eaf | grep mysqld

To check if port 3306 is bind with mysqld or another program.
> lsof -i TCP:3306
> netstat -lp | grep 3306
> netstat -tap | grep mysql
> ps -aux | grep mysql
> netstat -a -t – to show only tcp ports

Note: – if you could not found 3306 is listening with mysqld, then it must not be running or running with another ports. To find this, refer my.cnf and pid-file

How to Stop mysqld?
> /etc/init.d/mysqld stop
> kill <pid>
> /sbin/service mysqld start/stop/restart

If you have problems starting the server, here are some things to try:

Check the error log to see why the server does not start.
The Location of error log file can be found in my.cnf or my.ini(windows). please refer below to know more about my.cnf file. The log file can be specified also in mysqld service resided
under /etc/init.d/

Make sure that the server knows where to find the data directory.
Make sure my.cnf file is set with “datadir” and its required ownership and permission. Make sure that the server can access the data directory. The ownership and permissions of the data directory and its contents must be set such that the server can read and modify them.

Verify that the network interfaces the server wants to use are available. If the server starts but you cannot connect to it, you should make sure that you have an entry in /etc/hosts that
looks like this:
127.0.0.1 localhost

If mysqld is running, To find all the variable set using
> mysqladmin -h hostname -p variables

Issues 1:
Can’t start server: Bind on TCP/IP port: Address already in use
Can’t start server: Bind on unix socket…
Solution:
Use ps to determine whether you have another mysqld server running. If so, shut down the server before starting mysqld again.

Issues 2:
mysqld will not start
Can’t start server: Bind on TCP/IP port: Address already in use
Do you already have another mysqld server running on port: 3306 ?
Solution:
This may be due to 3306 port is being used or Disk Space issues. You can look up on the log file.

Recovering a crashed MySQL server if the system itself or just the MySQL daemon corrupted table files

You’ll see this when checking the /var/log/syslog, as the MySQL daemon checks tables during its startup.

Apr 17 13:54:44 live1 mysqld[2613]: 090417 13:54:44 [ERROR]
/usr/sbin/mysqld: Table ‘./database1/table1’ is marked as
crashed and should be repaired

In this situation, Database and tables need to be repaired.

> mysql -u root -p
mysql> REPAIR TABLE database1.table1;

This works, but there is a better way: First, using OPTIMIZE in combination with REPAIR is suggested and there is a command line tool only for REPAIR jobs. Consider this call:
> mysqlcheck -u username -p -o –auto-repair -v –optimize database_name

Using “mysqlcheck” is, that it can also be run against all databases in one run
> mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

Recreating databases and tables the right way
mysql> show create database database1;

How to find location of my.cnf (or my.ini on Windows)?

Default options are read from the following files in the given order:
/etc/my.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
~/.my.cnf

Or, on Windows:
Default options are read from the following files in the given order:
C:\Windows\my.ini
C:\Windows\my.cnf
C:\my.ini
C:\my.cnf
C:\Program Files\MySQL\MySQL Server 5.5\my.ini
C:\Program Files\MySQL\MySQL Server 5.5\my.cnf

This command also help you in linux to find my.conf file location…
> strace mysql “;” 2>&1 | grep cnf

Another Option to use following commands…
> whereis my.cnf
> locate my.cnf
> find – -name my.cnf

my.cnf will contain following…
datadir – The path to the MySQL data directory.
tmpdir
default-character-set
default-storage-engine
innodb_data_home_dir
log-error- The location of log file.
pid-file – The path name of the file in which the server should write its process ID.

MySQL Performance Troubleshooting
There are three main utilities I’ll run to in a situation like this:

top
First I’m going to use top to see if anything is hogging CPU on the machine. If there are non-mysql processes using a substantial percentage of the CPU cores, I’m going to want to havea look at what that is and see about limiting its use or moving it a dedicated server. If I see mysqld using up a lot of CPU, I know it’s working hard and will have to drill into what’shappening inside of MySQL (maybe some poorly written queries). If nothing is apparently chewing up the CPU time, I know that the problem is likely elsewhere.

vmstat 5
I generally run this for at least two or three minutes to get a sense of what the CPU and memory use are like. I’m also watching to see how much time the CPU is stalled waiting for I/Orequests. Doing this for several minutes will make the occasional spikes really stand out and also allow for more time to catch those cron jobs that fire up every few minutes.

iostat -x 5 | grep sdb
I’m going to run it with a short interval (5 or 10 seconds) and do so for several minutes. I’ll likely filter the output so that I only see the output for the most active disk or array (the onewhere all of MySQL’s data lives).

slow queries
To find out about slow queries I’m going hope that the slow query log is enabled and the server has a sane long_query_time. But even the default of 10 seconds is helpful in truly badsituations.

MySQL’s error log
I’ll also want to glance through MySQL’s error log to make sure nothing bad-looking has started to appear. To Find a error log file location, refer my.cnf file “log-error”.

Network issues
telnet your_host_name tcp_ip_port_number.

mysqladmin :
mysqladmin is a client for performing administrative operations. You can use it to check the server’s configuration and current status, to create and drop databases, and more.

mysqladmin -h hostname -p <command_as_follows>

–help, -? – Display a help message and exit.
refresh – Flush all tables and close and open log files.
variables – Display the server system variables and their values.
flush-logs – Flush all logs.
flush-privileges – Reload the grant tables (same as reload).
flush-status – Clear status variables.
password new-password – Set a new password. This changes the password to new-password for the account that you use with mysqladmin for connecting to the server.
ping – Check whether the server is available
processlist – Show a list of active server threads.
shutdown – Stop the server.
status – Display a short server status message.
Uptime – The number of seconds the MySQL server has been running.
Slow queries – The number of queries that have taken more than long_query_time seconds
Open tables – The number of tables that currently are open.

Reference
http://dev.mysql.com/doc/refman/5.5/en/mysqladmin.html
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
http://www.linux-mag.com/id/7473/
http://dev.mysql.com/doc/refman/5.1/en/starting-server.html

Rajesh Kumar
Follow me
Latest posts by Rajesh Kumar (see all)
Subscribe
Notify of
guest
0 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x