Blog
How do I create a simple MySQL database
I was asked this question recently “I am wanting to create a simple MySQL database consisting of 5 tables”? While it’s easy to tell people to RTFM, the question does warrant an answer for the MySQL beginner to provide a more specific guidance as to where to start, and what to do.
How do I find the storage engine of a MySQL table
This seems quite a trivial question, but developers don’t often know what a MySQL storage engine is and how to determine what storage engine is used for a table. The first choice is to describe the table with the DESC[RIBE] command.
Monitoring MySQL – The error log
It is important that you monitor the MySQL error log. There are a few different options available for defining the details of the log. If not specified the default is [datadir]/[hostname].
Explain this
The EXPLAIN command is an important tool to review how a SQL query is executed and in this example includes what indexes are used. By adding a covering index I ended up with the following EXPLAIN plan I was unable to explain.
MySQL Replication 102
One of the most asked questions is how to setup MySQL replication. The MySQL Reference Manual provides a good Replication How To as a starting guide on MySQL Replication 101.
Where can you find MySQL Events?
As a frequent traveler for my MySQL consulting (last 4 weeks were Sydney, San Francisco, New York and Vancouver), I like to keep abreast of any local tech event that includes MySQL that I may be able to attend.
InnoDB I_S.tables.table_rows out by a factor of 100x
I’ve always believed that the MySQL Information_schema.tables.table_rows figure for Innodb tables to be while approximate, approximately accurate. Today I found that the figures varied on one table from 10x to 100x wrong.
MySQL DML stats per table
MySQL provides a level of statistics for your INSERT, UPDATE, DELETE, REPLACE Data Manipulation Language (DML) commands using the STATUS output of various Com_ variables, however it is per server stats.
Has your blog been hacked?
While not a MySQL topic, as most of my readers view my MySQL Blog, my WordPress blog has been hacked? Has yours? Like many, I’m sure you may have read about it like at WordPress blogs under attack from hack attack but I was surprised when my custom permlinks did not work.
What do you monitor in MySQL?
If you are unfamiliar with what to monitor in MySQL, starting with looking at what popular Monitoring products monitor. For example, the following is the list of MySQL Cacti Plugin measurements.
SQL Analysis with MySQL Proxy – Part 2
As I outlined in Part 1 MySQL Proxy can be one tool for performing SQL analysis. The impact with any monitoring is the art of monitoring will affect the results, in this case the performance.
SQL query analysis with MySQL Proxy
Long before there was the official Query Analyzer (QUAN), a component of MySQL Enterprise, SQL analysis was possible using MySQL Proxy . The following is an introduction to logging and query analysis with MySQL Proxy.
Seeking public data for benchmarks
I have several side projects when time permits and one is that of benchmarking various MySQL technologies (e.g. MySQL 5.0,5.1,5.4), variants (e.g. MariaDB, Drizzle) and storage engines (e.g. Tokutek, Innodb plugin) and even other products like Tokyo Cabinet which is gaining large implementations.
Setting up a Virtual IP address (VIP)
These instructions are for CentOS/Redhat Linux distributions. Identify your current NIC’s and IP addresses in use. $ /sbin/ifconfig eth0 Link encap:Ethernet HWaddr 00:30:48:98:9C:A6 inet addr:192.168.53.201 Bcast:192.168.53.255 Mask:255.255.255.0 inet6 addr: 0080::230:48ff:fe98:9ca6/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:6159779 errors:0 dropped:0 overruns:0 frame:0 TX packets:6137085 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:100 RX bytes:1158210510 (1.