Mysql: Analyzing mysql slow query log

I had yet to have a need for a tool to analyze a MySQL slow query log, after some searching I came across a tool I already had!

Most MySQL installs come with the utility mysqldumpslow which is great for parsing your slow query log. I needed to sort by lock time so I just ran the following:

[code]mysqldumpslow -s l mysql-slow.log | less[/code]

How to log in to mysql and query the database from linux terminal

I am using debian linux. I have a linux machine on which mysql is install. I can log in to my linux machine using root user as well as other user. I can connect to mysql database on linux machine from windows machine using sqlyog. Now I want to execute queries on linux machine only using linux terminal

I tried some following things on terminal

I went to root directory then I went to /var/lib directory

I run following commands on terminal

mysqladmin -u root -p
mysqladmin -u root -ppassword

everytime I have get following error message

ERROR 1045 (28000) Access denied for user 'root'@'localhost' (Using password NO)

please guide me for following

1. How do I get mysql prompt in linux terminal?
2. How I stop the mysql server from linux terminal?
3. How I start the mysql server from linux terminal?
4. How do I get mysql prompt in linux terminal?
5. How do I login to mysql server from linux terminal?
6. How do I solve following error?

ERROR 1045 (28000) Access denied for user 'root'@'localhost' (Using password NO)

Please give me solutions for above question. Thank You

 Answers

1.- How do I get mysql prompt in linux terminal?

mysql -u root -p

At the Enter password: prompt, well, enter root's password :)

You can find further reference by typing mysql --help or at the online manual.

2. How I stop the mysql server from linux terminal?

It depends. Red Hat based distros have the service command:

service mysqld stop

Other distros require to call the init script directly:

/etc/init.d/mysqld stop

3. How I start the mysql server from linux terminal?

Same as #2, but with start.

4. How do I get mysql prompt in linux terminal?

Same as #1.

5. How do I login to mysql server from linux terminal?

Same as #1.

6. How do I solve following error?

Same as #1.

---

I assume you are looking to use mysql client, which is a good thing and much more efficient to use than any phpMyAdmin alternatives.

The proper way to log in with the commandline client is by typing:

mysql -u username -p

Notice I did not type the password. Doing so would of made the password visible on screen, that is not good in multi-user environnment!

After typing this hit enter key, mysql will ask you for your password.

Once logged in, of course you will need:

use databaseName;

to do anything.

Good-luck.

---

At the command prompt try:

mysql -u root -p

give the password when prompted.

---

1. you should use mysql command. It's a command line client for mysql RDBMS, and comes with most mysql installations: http://dev.mysql.com/doc/refman/5.1/en/mysql.html
2. To stop or start mysql database (you rarely should need doing that 'by hand'), use proper init script with stop or start parameter, usually /etc/init.d/mysql stop. This, however depends on your linux distribution. Some new distributions encourage service mysql start style.
3. You're logging in by using mysql sql shell.
4. The error comes probably because double '-p' parameter. You can provide -ppassword or just -p and you'll be asked for password interactively. Also note, that some instalations might use mysql (not root) user as an administrative user. Check your sqlyog configuration to obtain working connection parameters.

---

if you're already logged in as root just

mysql -u root

prompting the password will otherwise return as error

How to enable MySQL Query Log?

How do I enable the MySQL function that logs each SQL query statement received from clients and the time that query statement has submitted? Can I do that in phpmyadmin or NaviCat? How do I analyse the log?

 Answers

First, Remember that this logfile can grow very large on a busy server.

For mysql < 5.1.29:

To enable the query log, put this in /etc/my.cnf in the [mysqld] section

log   = /path/to/query.log  #works for mysql < 5.1.29

Also, to enable it from MySQL console

SET general_log = 1;

See http://dev.mysql.com/doc/refman/5.1/en/query-log.html

For mysql 5.1.29+

With mysql 5.1.29+ , the log option is deprecated. To specify the logfile and enable logging, use this in my.cnf in the [mysqld] section:

general_log_file = /path/to/query.log
general_log      = 1

Alternately, to turn on logging from MySQL console (must also specify log file location somehow, or find the default location):

SET global general_log = 1;

Also note that there are additional options to log only slow queries, or those which do not use indexes.

---

I use this method for logging when I want to quickly optimize different page loads. It's a little tip...

Logging to a TABLE

SET global general_log = 1;
SET global log_output = 'table';

You can then select from my mysql.general_log table to retrieve recent queries.

I can then do something similar to tail -f on the mysql.log, but with more refinements...

select * from mysql.general_log 
where  event_time  > (now() - INTERVAL 8 SECOND) and thread_id not in(9 , 628)
and argument <> "SELECT 1" and argument <> "" 
and argument <> "SET NAMES 'UTF8'"  and argument <> "SHOW STATUS"  
and command_type = "Query"  and argument <> "SET PROFILING=1"

This makes it easy to see my queries that I can try and cut back. I use 8 seconds interval to only fetch queries executed within the last 8 seconds.

---

You can disable or enable the general query log (which logs all queries) with

SET GLOBAL general_log = 1 # (or 0 to disable)

---

I also wanted to enable the mysql log file to see the queries and i have resolved this with the below instructions

1 - Go to /etc/mysql/mysql.conf.d 
2 - open the mysqld.cnf 
and enable the below lines

general_log_file = /var/log/mysql/mysql.log 
general_log = 1

3 - restart the mysql with this command /etc/init.d/mysql restart 
4 - go to /var/log/mysql/ and check the logs

---

for mysql>=5.5 only for slow queries (1 second and more) my.cfg

[mysqld]
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

---

Not exactly an answer to the question because the question already has great answers. This is a side info. Enabling general_log really put a dent on MySQL performance. I left general_log =1 accidentally on a production server and spent hours finding out why performance was not comparable to a similar setup on other servers. Then I found this which explains the impact of enabling general log. 

Gist of the story, don't put general_log=1 in the .cnf file. Instead use set global general_log =1 for a brief duration just to log enough to find out what you are trying to find out and then turn it off.

---

I had to drop and recreate the general log at one point. During the recreation, character sets got messed up and I ended up having this error in the logs:

[ERROR] Incorrect definition of table mysql.general_log: expected the type of column 'user_host' at position 1 to have character set 'utf8' but found character set 'latin1'

So if the standard answer of "check to make sure logging is on" doesn't work for you, check to make sure your fields have the right character set.

Log all queries in mysql

Is it possible for me to turn on audit logging on my mysql database?
I basically want to monitor all queries for an hour, and dump the log to a file.

 Answers

Start mysql with the --log option:

mysqld --log=log_file_name

or place the following in your my.cnf file:

log = log_file_name

Either one will log all queries to log_file_name.

You can also log only slow queries using the --log-slow-queries option instead of --log. By default, queries that take 10 seconds or longer are considered slow, you can change this by setting long_query_time to the number of seconds a query must take to execute before being logged.

---

Besides what i came across here, running the following was the simplest way to dump queries to a log file without restarting

SET global log_output = 'FILE';
SET global general_log_file='/Applications/MAMP/logs/mysql_general.log';
SET global general_log = 1;

can be turned off with

SET global general_log = 0;

---

Enable the log for table

mysql> SET GLOBAL general_log = 'ON';
mysql> SET global log_output = 'table';

View log by select query

select * from mysql.general_log

---

For the record, general_log and slow_log were introduced in 5.1.6:

http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html

5.2.1. Selecting General Query and Slow Query Log Output Destinations

As of MySQL 5.1.6, MySQL Server provides flexible control over the destination of output to the general query log and the slow query log, if those logs are enabled. Possible destinations for log entries are log files or the the general_log and slow_log tables in the mysql database

---

In case using AWS RDS MYSQL, step by step guide here.

When set as 'file' output you can view the log directly from AWS RDS "Log" Console.

AWS RDS MYSQL Logging