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...
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...
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.
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...
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...
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
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...
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...
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...
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...
