![\"mysql-troubleshooting\"](\"http:\/\/www.scmgalaxy.com\/tutorials\/wp-content\/uploads\/2013\/05\/mysql-troubleshooting.png\")
<\/p>\n<\/p>\n
PLEASE NOTE: I am currently reviewing this Article.<\/p>\n
How to check the mysql file location:<\/strong> Check mysqld process is started or not?<\/strong> To check if port 3306 is bind with mysqld or another program.<\/strong> 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<\/p>\n How to Stop mysqld?<\/strong> If you have problems starting the server, here are some things to try:<\/p>\n Check the error log to see why the server does not start.<\/strong> Make sure that the server knows where to find the data directory.<\/strong> 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 If mysqld is running, To find all the variable set using <\/strong> Issues 1:<\/strong> Issues 2:<\/strong> Recovering a crashed MySQL server if the system itself or just the MySQL daemon corrupted table files<\/strong><\/p>\n You\u2019ll see this when checking the \/var\/log\/syslog, as the MySQL daemon checks tables during its startup.<\/p>\n Apr 17 13:54:44 live1 mysqld[2613]: 090417 13:54:44 [ERROR] In this situation, Database and tables need to be repaired.<\/p>\n > mysql -u root -p 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: Using \u201cmysqlcheck\u201d is, that it can also be run against all databases in one run Recreating databases and tables the right way How to find location of my.cnf (or my.ini on Windows)?<\/strong><\/p>\n Default options are read from the following files in the given order: Or, on Windows: This command also help you in linux to find my.conf file location…<\/strong> Another Option to use following commands…<\/strong> my.cnf will contain following…<\/strong> MySQL Performance Troubleshooting<\/strong> top vmstat 5 iostat -x 5 | grep sdb slow queries MySQL\u2019s error log<\/strong> Network issues<\/strong> mysqladmin : <\/strong> mysqladmin -h hostname -p <command_as_follows><\/p>\n –help, -? – Display a help message and exit. Reference<\/strong> 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 > \u201cmysqld is stopped\u201d \u2013 Means mysqld is not running > \u201cmysqld: unrecognized service\u201d \u2013 Means mysqld is not set in service. This…<\/p>\n","protected":false},"author":1,"featured_media":3783,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","_joinchat":[],"footnotes":""},"categories":[49],"tags":[2380,2396,545,255,493,2394,2395,2393,2387,2398,2390,2391,2388,2389,2397,2392,293],"class_list":["post-334","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-general","tag-buildforge-server","tag-common-problems-in-mysql","tag-how","tag-issues","tag-mysql","tag-mysql-basic-issues","tag-mysql-basic-problems","tag-mysql-basic-troubleshooting","tag-mysql-common-issues","tag-mysql-guide","tag-mysql-issues","tag-mysql-problems","tag-mysql-troubleshooting","tag-mysql-troubleshooting-guide","tag-mysql-tutorial","tag-troubleshoot-mysql","tag-troubleshooting"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/334","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=334"}],"version-history":[{"count":2,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/334\/revisions"}],"predecessor-version":[{"id":3782,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/334\/revisions\/3782"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media\/3783"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=334"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=334"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=334"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n> which mysql
\n> locate mysql<\/p>\n
\n> service mysqld status
\n> \u201cmysqld is stopped\u201d \u2013 Means mysqld is not running
\n> \u201cmysqld: unrecognized service\u201d \u2013 Means mysqld is not set in service. This can be register using chkconfig under \/etc\/init.d.
\n> ps -eaf | grep mysqld<\/p>\n
\n> lsof -i TCP:3306
\n> netstat -lp | grep 3306
\n> netstat -tap | grep mysql
\n> ps -aux | grep mysql
\n> netstat -a -t – to show only tcp ports<\/p>\n
\n> \/etc\/init.d\/mysqld stop
\n> kill <pid>
\n> \/sbin\/service mysqld start\/stop\/restart<\/p>\n
\nThe 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
\nunder \/etc\/init.d\/<\/p>\n
\nMake 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.<\/p>\n
\nlooks like this:<\/strong>
\n127.0.0.1 localhost<\/p>\n
\n> mysqladmin -h hostname -p variables<\/p>\n
\nCan’t start server: Bind on TCP\/IP port: Address already in use
\nCan’t start server: Bind on unix socket…
\nSolution:<\/strong>
\nUse ps to determine whether you have another mysqld server running. If so, shut down the server before starting mysqld again.<\/p>\n
\nmysqld will not start
\nCan’t start server: Bind on TCP\/IP port: Address already in use
\nDo you already have another mysqld server running on port: 3306 ?
\nSolution:<\/strong>
\nThis may be due to 3306 port is being used or Disk Space issues. You can look up on the log file.<\/p>\n
\n\/usr\/sbin\/mysqld: Table ‘.\/database1\/table1’ is marked as
\ncrashed and should be repaired<\/p>\n
\nmysql> REPAIR TABLE database1.table1;<\/p>\n
\n> mysqlcheck -u username -p -o –auto-repair -v –optimize database_name<\/p>\n
\n> mysqlcheck -u root -p –auto-repair –check –optimize –all-databases<\/p>\n
\nmysql> show create database database1;<\/p>\n
\n\/etc\/my.cnf
\n\/etc\/mysql\/my.cnf
\n\/usr\/etc\/my.cnf
\n~\/.my.cnf<\/p>\n
\nDefault options are read from the following files in the given order:
\nC:\\Windows\\my.ini
\nC:\\Windows\\my.cnf
\nC:\\my.ini
\nC:\\my.cnf
\nC:\\Program Files\\MySQL\\MySQL Server 5.5\\my.ini
\nC:\\Program Files\\MySQL\\MySQL Server 5.5\\my.cnf<\/p>\n
\n> strace mysql “;” 2>&1 | grep cnf<\/p>\n
\n> whereis my.cnf
\n> locate my.cnf
\n> find – -name my.cnf<\/p>\n
\ndatadir – The path to the MySQL data directory.
\ntmpdir
\ndefault-character-set
\ndefault-storage-engine
\ninnodb_data_home_dir
\nlog-error- The location of log file.
\npid-file – The path name of the file in which the server should write its process ID.<\/p>\n
\nThere are three main utilities I\u2019ll run to in a situation like this:<\/p>\n
\nFirst I\u2019m 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\u2019m 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\u2019s working hard and will have to drill into what\u2019shappening 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.<\/p>\n
\nI generally run this for at least two or three minutes to get a sense of what the CPU and memory use are like. I\u2019m 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.<\/p>\n
\nI\u2019m going to run it with a short interval (5 or 10 seconds) and do so for several minutes. I\u2019ll likely filter the output so that I only see the output for the most active disk or array (the onewhere all of MySQL\u2019s data lives).<\/p>\n
\nTo find out about slow queries I\u2019m 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.<\/p>\n
\nI\u2019ll also want to glance through MySQL\u2019s 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”.<\/p>\n
\ntelnet your_host_name tcp_ip_port_number.<\/p>\n
\nmysqladmin 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.<\/p>\n
\nrefresh – Flush all tables and close and open log files.
\nvariables – Display the server system variables and their values.
\nflush-logs – Flush all logs.
\nflush-privileges – Reload the grant tables (same as reload).
\nflush-status – Clear status variables.
\npassword 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.
\nping – Check whether the server is available
\nprocesslist – Show a list of active server threads.
\nshutdown – Stop the server.
\nstatus – Display a short server status message.
\nUptime – The number of seconds the MySQL server has been running.
\nSlow queries – The number of queries that have taken more than long_query_time seconds
\nOpen tables – The number of tables that currently are open.<\/p>\n
\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/mysqladmin.html<\/a>
\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/slow-query-log.html<\/a>
\nhttp:\/\/www.linux-mag.com\/id\/7473\/<\/a>
\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/starting-server.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"