MySql

MySql Query Optimization

You can turn on the slow query logs in configrations and then see logs by mysqldumpslow command mysqldumpslow -s c -t 30 /var/log/mysql/mysql-slow.log   You can optimize your queries with explain command explain extended <Execute your query here>   MySQL Explain has different row count than slow query log. Try the below command to get more stats. FLUSH STATUS; <Execute your...

Read more...

How to Disable Strict SQL Mode in MySQL 5.7

What is this error? "Database query failed: Data truncated for column 'column_name' at row 1 Your mysql might be running in strict mode. you need to disable it. To disable strict SQL mode, SSH in to your server as root and create this file: /etc/mysql/conf.d/disable_strict_mode.cnf Open the file and enter these two lines: [mysqld] sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION   Restart...

Read more...

MySql slow query file parsers

These are the list of mysql parser which will beauify you log files Online http://nk.gl/slow_queries/analyze Restrictions: 8mb file only and replaces some characters with its own custom variables.

Read more...

How to run time consuming commands on server and also do benchmarking

Make a shell script file. nano script_benchmakring.sh #!/bin/bash echo `date` > /home/log.txt sleep 5 echo `date` >> /home/log.txt You can place any command in the script in place of "sleep 5". We are using sleep command to verify everything is working fine and time difference is being recorded. Now we will make this script executable chmod +x...

Read more...

MySql configurations & tweeking

You can build your own configurations but it takes lot of time and R&D to get best configurations. You can generate configurations through wizard percona tools https://tools.percona.com/wizard We will modify some parameters according to our needs. Sample configurations which i use on MySql Percona 5.6 are as follow. # Generated by Percona...

Read more...

Finding duplicate records in mysql

We have two tables epaper and epaper_pages. Every epaper_pages table recrod has a specific page number assigned. We have to check that each epaper_pages table record  has no epaper_page_no duplicate number. SELECT ( SELECT COUNT(*) FROM epaper_pages WHERE epaper_pages.epaper_id = epaper.id GROUP BY epaper_pages.epaper_page_no HAVING COUNT(*) > 1 ) AS dublicate_count, epaper.* FROM epaper ORDER BY dublicate_count DESC  

Read more...

MySql backup & restore commands

This article has some handy commands to backup and restore mysql databases. Some of the terms used below refer to USERNAME = your username MYSQL_PASSWORD = your password DATABASE_1 = name of the database LOCAL_DOMAIN.COM = local domain name DESTINATION_DOMAIN.COM = remote domain name Take MySql dump, gzip it and take it in a directory. mysqldump...

Read more...

MySql monitoring & optimzing commands

Some of the terms used in the article below refer to USERNAME = your username MYSQL_PASSWORD = your password DATABASE_1 = name of the database LOCAL_DOMAIN.COM = local domain name DESTINATION_DOMAIN.COM = remote domain name Restart MySql Server sudo service mysql restart Check database size mysql> SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS...

Read more...

Server Monitoring Commands

Disk Stats Disk Usage detials df -h LSOF means list of open files. This command watches total open files on server. A very good article for further reading http://www.catonmat.net/blog/unix-utilities-lsof/ watch --interval=1 --differences "lsof | wc -l" Disk IO Stats iotop sar disk io stats sar -s 15:00:00 -e 17:00:00 shows the disk I/O stats watch --interval=1 iostat http://www.tecmint.com/how-to-check-disk-space-in-linux/ General Monitoring Find Out...

Read more...

Convert MySql database tables types from MyISAM to InnoDB

Conversion on dump file If you have a dump file of mysql, The easiest way is to run the command on the dump file. It will replace the tables from MyISAM to INNODB sed -i 's/MyISAM/INNODB/g' dbname.sql You can also change the command and convert INNODB to MyISAM by this command sed -i 's/INNODB/MyISAM/g' dbname.sql     Conversion on current...

Read more...