Objective
This page is intended to share common issues related to MySQL and also as a start-up guide for new users.
Databases are located in: /var/lib/mysql/ System database is mysql: locattion /var/lib/mysql/mysql/ Default config file: /etc/my.cnf
Installing from binary
Installing MySQL 5.1 binary
Note: If you are installing multiple copies then please read notes below.
Changes to user table
Any changes to the user table will require a "FLUSH PRIVILEGES" before those changes are applied. For example after GRANT / REVOKE.
What are .frm, .MYD, and .MYI files?
Behind the scenes of DBMS is a storage engine. There are two types: InnoDB and MyISAM. MyISAM uses a .frm file to store the definition of the table, but this file is not a part of the MyISAM engine, but instead is a part of the server. The data file has a .MYD (MYData) extension. The index file has a .MYI (MYIndex) extension. I will post a new page about storage engines soon.
Import and Export database
Running multiple MySQL servers
I am running two MySql servers on Centos 4.4 and Red Hat 7.3 system. Below are my notes regarding multiple !MySQL installation.
- Use binary file for installation Its really tiring process to install MySql from source especially in case of multiple installation.
- You need to run mysql_installdb script in order install database and system tables. It will try to use my.cnf file from /etc if you already have one. In this case it may give error as
Installing MySQL system tables... ERROR: 1136 Column count doesn't match value count at row 1 080716 13:21:32 [ERROR] Aborting 080716 13:21:32 [Note] ./bin/mysqld: Shutdown complete 080716 13:35:34 [ERROR] ./bin/mysqld: Can't sync file '/dev/null' to disk (Errcode: 22) 080716 13:35:34 [ERROR] Error writing file '/dev/null' (errno: 22) 080716 13:35:34 [ERROR] ./bin/mysqld: Can't sync file '/dev/null' to disk (Errcode: 22) 080716 13:35:34 [ERROR] Error writing file '/dev/null' (errno: 22) 080716 13:35:34 [Note] ./bin/mysqld: Shutdown complete
Errors related to /dev/null are because my log file was set to be /dev/null for some reason. However, the Error #1136 is pretty common even while upgrading. You can bypass this problem by renaming or moving your my.cnf to a different location. Related bug report can be found over here.
- Be sure which mysqld demon you want to use. Common mistake I did was starting it using service command. This will start the MySQL, but it may not be the one you want. Make sure to run them on different ports and keep separate log files for each.
MySQL Protocol
The protocol has been implemented by various mysql sniffer tools as well as query loggers. It describes protocol basics from making a connection, error codes, and how data is returned. This document describes the MySQL network protocol version 10, as of February 2005 (MySQL 4.1.9).
Getting MySQL query output in a CSV format
Original script at: http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/
Example query:
SELECT order_id,product_name,qty FROM orders
To get output in a tab separated text file:
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.txt'
Following will give us comma separated values enclosed in double quotes with one row/result on a single line:
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
Make sure that MySQL has write permissions to output this file. Example output:
"1","Tech-Recipes sock puppet","14.95" "2","Tech-Recipes chef's hat","18.95"
