My MySQL favorite dump command

In MySQL on May 4th, 2011

My favorite MySQL command line

Dumps whole database table into mydb_mytable.sql file:

mysqldump
    -umyuser
    -pmypass
    --skip-lock-tables
    -h 127.0.0.1
    --databases mydb
    --tables mytable > mydb_mytable.sql

Dumps whole database into mydb.sql file:

mysqldump
    -umyuser
    -pmypass
    --skip-lock-tables
    -h 127.0.0.1
    --databases mydb > mydb.sql

Reset MySQL root password

In Linux, MySQL on April 15th, 2011

Reseting MySQL root Password is easy:

1. Stop MySQL 

    sudo /etc/init.d/mysql stop

2. Start the mysqld demon process using the –skip-grant-tables option with this command

    sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

3. Login to MySQL as root.

    mysql -u root mysql

4. You done, flush privileges

    FLUSH PRIVILEGES;

5. You might want to change root password.

    UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root';
    FLUSH PRIVILEGES;

CrossTab query (Pivot Table or Transformation of rows into columns)

In MySQL on February 7th, 2011

CrossTab query is amazing, which helps generating reports and play with aggregate values. Excel/Ms Access gives nice user interface for Pivot Table. It’s way to transfer rows into column. It is more often used to generate matrix form of report. Some real-life example:

1. Give me last 3 months sales report product wise. (It may look like below)

Month -->  | Jan    |  Feb     | March
---------- | ------ | -------- | -------
Product A  | 3387   | 87985    | 2338
Product B  | 6386   | 67983    | 6374
Product C  | 3880   | 76988    | 9378
..... and product lists goes down ....

2. Give me last 3 months sales report country wise. (It may look like below)

Country -> | USA    |  France  | Japan
---------- | ------ | -------- | -------
Jan        | 3387   | 87985    | 2338
Feb        | 6386   | 67983    | 6374
Mar        | 3880   | 76988    | 9378

#1 and #2 can be ask like ‘Give me top-ten selling products in last 3 months, or give me top-ten selling countries in last 3 months”

If you see field name (column title)  in #1, it’s name of month (date), which is row data and similarly country is row data in #2. They are not field name in database table. It is possible to get reports like above by transforming rows into columns. This is what called Pivot Table, and can be done using CrossTab query. Let’s take bit example (example belows are used with MySQL):

1. Create Database, tables and insert dummy data. Use script below:

CREATE TABLE  `products` (
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	`product` VARCHAR( 100 ) NOT NULL
);

CREATE TABLE  `sales` (
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	`product_id` INT NOT NULL ,
	`qty` INT NOT NULL,
	`sales_date` DATETIME NOT NULL
)

INSERT INTO `products` (`id`, `product`) VALUES
(1, 'Fanta'),
(2, 'Coke'),
(3, 'Mirinda'),
(4, 'Sprite');

INSERT INTO `sales`
(`id`, `sold_date`, `product_id`, `qty`)
VALUES
(1, '2011-02-07 11:49:53', 1, 4),
(2, '2011-02-07 11:49:53', 1, 4),
(3, '2011-02-08 11:50:05', 1, 8),
(4, '2011-02-07 11:49:53', 2, 5),
(5, '2011-02-07 11:49:53', 3, 2),
(6, '2011-02-08 11:50:05', 2, 7),
(7, '2011-02-07 11:49:53', 3, 5),
(8, '2011-02-07 11:49:53', 4, 2),
(9, '2011-02-08 11:50:05', 3, 7),
(10, '2011-02-07 11:49:53', 2, 5),
(11, '2011-02-07 11:49:53', 3, 2),
(12, '2011-02-08 11:50:05', 4, 7);

2. Determine output you want (in our case, below is format):

Date       | Fanta  | Coke   | Mirinda | Sprite
xxxx:xx:xx | x      | x      | x       | x
xxxx:xx:xx | x      | x      | x       | x

3. Write query, run it:

SELECT
	s.sales_date `Sales Date`,
	SUM(IF(p.product='Fanta', s.qty, 0)) Fanta,
	SUM(IF(p.product='Coke', s.qty, 0))  Coke,
	SUM(IF(p.product='Mirinda', s.qty, 0)) Mirinda,
	SUM(IF(p.product='Sprite', s.qty, 0))  Sprite
FROM
	products p,
	sales s
WHERE
	p.id = s.product_id
GROUP BY
	s.sales_date

4. Output is as given below:

Date                 | Fanta  | Coke   | Mirinda | Sprite
2011-02-07 11:49:53  | 8      | 10     | 9       | 2
2011-02-08 11:50:05  | 8      | 7      | 7       | 7