mysqldump

I backed up a MySQL database using a UNIX shell script as shown below. First I created a database to backup:

mysql> create database andrew;

Query OK, 1 row affected (0.00 sec)

 

mysql> use andrew;

Database changed

mysql> create table andrews_table (col1 int)

    -> select 1,4,9,16,25;

Query OK, 1 row affected (0.03 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> select * from andrews_table;

+------+---+---+---+----+----+

| col1 | 1 | 4 | 9 | 16 | 25 |

+------+---+---+---+----+----+

| NULL | 1 | 4 | 9 | 16 | 25 |

+------+---+---+---+----+----+

1 row in set (0.00 sec)

 

mysql>

 

Then I decided to create an option file to store the MySQL root user’s password. I typed mysqldump --help, to see where to put this option file (see the final lines in bold):

 

zge-mktred-drp1@root:mysql # mysqldump --help

mysqldump  Ver 9.11 Distrib 4.0.31, for pc-solaris2.10 (i386)

By Igor Romanenko, Monty, Jani & Sinisa

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL license

 

Dumping definition and data mysql database or table

Usage: mysqldump [OPTIONS] database [tables]

OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

 

Default options are read from the following files in the given order:

/etc/my.cnf /var/mysql/my.cnf ~/.my.cnf

Etc

Etc

 

This is what I put in the option file. It tells MySQL that when I run mysqldump, I want to supply the password shown (that's not the real password BTW):

 

UNIX > cat my.cnf

[mysqldump]

password="5bJd69wM"

UNIX >

 

I stored the file in the following directory as it was the first in the list of default options above:

 

UNIX > pwd

/etc

UNIX >

 

... and gave it these permissions:

 

UNIX > ls -l my.cnf

-rw-------   1 mysql    mysql         32 Jul 11 15:57 my.cnf

UNIX >

 

That’s because I want to run mysqldump as the UNIX mysql user and I don’t want anybody else to see the password. I created and ran the following shell script:

 

UNIX > cat backup.ksh

#!/bin/ksh

#

# Backup a MySQL database called andrew

#

/usr/local/mysql/bin/mysqldump -u root \

--compact \

--databases andrew \

--lock-tables \

--result-file=/usr/local/mysql/andrew/test_backup \

 

if [ $? -eq 0 ]

then

echo "mysqldump finished successfully"

exit 0

else

echo "*** mysqldump failed ***"

exit 1

fi

 

UNIX >

 

The --compact option creates a smaller dump file.

The --databases option specifies which database(s) to backup. If you specify a database name which does not exist, mysqldump terminates with a non-zero return code (I checked this).

The --lock-tables option tells mysqldump to lock the database’s tables before starting.

The --result-file option tells mysqldump where to put the backup.

 

It produced the backup file below:

 

UNIX > cat test_backup

 

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `andrew` /*!40100 DEFAULT CHARACTER SET latin1 */;

 

USE `andrew`;

SET @saved_cs_client     = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `andrews_table` (

  `col1` int(11) default NULL,

  `1` int(1) NOT NULL default '0',

  `4` int(1) NOT NULL default '0',

  `9` int(1) NOT NULL default '0',

  `16` int(2) NOT NULL default '0',

  `25` int(2) NOT NULL default '0'

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET character_set_client = @saved_cs_client;

INSERT INTO `andrews_table` VALUES (NULL,1,4,9,16,25);

UNIX >

 

I tested the backup as follows. First I dropped the database:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 266797

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> drop database andrew;

Query OK, 1 row affected (0.01 sec)

 

mysql>

 

Then I restored it:

 

UNIX > mysql -u root -p < test_backup

Enter password:

UNIX >

 

Finally I checked that the restored database had the same information as before:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 266806

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> use andrew

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> select * from andrews_table;

+------+---+---+---+----+----+

| col1 | 1 | 4 | 9 | 16 | 25 |

+------+---+---+---+----+----+

| NULL | 1 | 4 | 9 | 16 | 25 |

+------+---+---+---+----+----+

1 row in set (0.00 sec)

 

mysql>

MySQL explain Command

In this example, a table is created with a TIME column. It has 12 rows.

 

A select statement shows that the table has 3 rows with TIME > 1000. The explain command is then used to see how this statement works. The rows column in the output from the explain command is MySQL’s estimate of the number of rows which the query will need to examine. In the absence of an index, My SQL needs to do a full table scan so the answer is 12.

 

An index is then added to the table’s TIME column and the explain command is rerun. It uses the index and estimates that 4 rows will need to be examined to run the query:

 

--------------

create table t1

like information_schema.PROCESSLIST

--------------

 

Query OK, 0 rows affected (0.00 sec)

 

--------------

insert into t1 select * from

information_schema.PROCESSLIST

--------------

 

Query OK, 12 rows affected (0.00 sec)

Records: 12  Duplicates: 0  Warnings: 0

 

--------------

select count(*) from t1 where TIME > 1000

--------------

 

+----------+

| count(*) |

+----------+

|        3 |

+----------+

1 row in set (0.00 sec)

 

--------------

explain

select count(*) from t1 where TIME > 1000

--------------

 

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

 

--------------

create index i on t1(TIME)

--------------

 

Query OK, 12 rows affected (0.01 sec)

Records: 12  Duplicates: 0  Warnings: 0

 

--------------

explain

select count(*) from t1 where TIME > 1000

--------------

 

+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |

+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

|  1 | SIMPLE      | t1    | range | i             | i    | 4       | NULL |    4 | Using where; Using index |

+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

1 row in set (0.00 sec)

How to Copy Data from Oracle to MySQL

This example shows how to copy a table from Oracle to MySQL. First create a table in Oracle and add some data, including some null values and a date column:

 

SQL> create table andrews_table

  2  (first_name varchar2(10),

  3   dob        date,

  4   salary     number)

  5  /

 

Table created.

 

SQL> insert into andrews_table values

  2  ('Fred', '25-DEC-1980', null)

  3  /

 

1 row created.

 

SQL> insert into andrews_table values

  2  ('Jim', null, 10000)

  3  /

 

1 row created.

 

SQL> insert into andrews_table values

  2  (null, '01-SEP-1983', 20000)

  3  /

 

1 row created.

 

SQL> select * from andrews_table

  2  /

 

FIRST_NAME DOB           SALARY

---------- --------- ----------

Fred       25-DEC-80

Jim                       10000

           01-SEP-83      20000

 

SQL>

 

Write a query to extract the data to a file. Note the date format and the way that null values are represented as \N:

 

UNIX > cat extract.sql

set feedback off

set lines 100

set pages 0

set termout off

set trimspool on

spool extract

select nvl(first_name,'\N')||'|'||

       nvl(to_char(dob,'YYYY-MM-DD'),'\N')||'|'||

       nvl(to_char(salary),'\N')

from andrews_table

/

spool off

UNIX >

 

... and run the SQL:

 

SQL> @extract

SQL>

 

The output file will look like this:

 

UNIX > cat extract.lst

Fred|1980-12-25|\N

Jim|\N|10000

\N|1983-09-01|20000

UNIX >

 

Copy it to a machine running MySQL then you can load it into a table as follows:

 

--------------

create table andrews_table

(first_name varchar(10),

dob        date,

salary     int)

--------------

 

Query OK, 0 rows affected (0.01 sec)

 

--------------

load data local infile 'extract.lst'

into table andrews_table

fields terminated by '|'

lines terminated by '\n'

(first_name, dob, salary)

--------------

 

Query OK, 3 rows affected (0.00 sec)

Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

 

--------------

select * from andrews_table

--------------

 

+------------+------------+--------+

| first_name | dob        | salary |

+------------+------------+--------+

| Fred       | 1980-12-25 |   NULL |

| Jim        | NULL       |  10000 |

| NULL       | 1983-09-01 |  20000 |

+------------+------------+--------+

3 rows in set (0.00 sec)

 

mysql>

MySQL db.opt File

When you create a database, you have to specify options such as the character set it will use:

 

mysql> create database andrew default charset utf8;

Query OK, 1 row affected (0.01 sec)

 

mysql>

 

These options are recorded in the db.opt file in the database directory:

 

UNIX > pwd

/usr/local/mysql/data/andrew

UNIX > cat db.opt

default-character-set=utf8

default-collation=utf8_general_ci

UNIX >

 

You can change them with the alter database command:

 

mysql> alter database andrew charset = dec8;

Query OK, 1 row affected (0.05 sec)

 

mysql>

 

... and the db.opt file will be updated:

 

UNIX > pwd

/usr/local/mysql/data/andrew

UNIX > cat db.opt

default-character-set=dec8

default-collation=dec8_swedish_ci

UNIX >

How to Add a Primary Key to a MySQL Table

In another post, I showed how to create a new table with a single-column primary key. This example shows how to add a multi-column primary key to an existing table: 

mysql> create table andrews_names

    -> (first_name varchar(10),

    ->  surname    varchar(10));

Query OK, 0 rows affected (0.06 sec)

 

mysql> alter table andrews_names

    -> add primary key

    -> (first_name, surname);

Query OK, 0 rows affected (0.30 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert into andrews_names

    -> values ('Andrew', 'Reid');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_names

    -> values ('Andrew', 'Bloggs');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_names

    -> values ('Joe', 'Reid');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_names

    -> values ('Andrew', 'Reid');

ERROR 1062 (23000): Duplicate entry 'Andrew-Reid' for key 1

mysql> select *from andrews_names;

+------------+---------+

| first_name | surname |

+------------+---------+

| Andrew     | Bloggs  |

| Andrew     | Reid    |

| Joe        | Reid    |

+------------+---------+

3 rows in set (0.01 sec)

 

mysql>

Creating Indexes in MySQL

This example shows how to create single and multi-column indexes in MySQL. There appears to be nothing to stop you creating two indexes with the same name, as long as they are on separate tables. Then it shows how to list the indexes on a table. As always, click on the image to display it at its actual size and bring it into focus:

You can drop indexes as follows. Note that you have to include the table name in case there is more than one index with a given name:

 

--------------

drop index i on t1

--------------

 

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

--------------

drop index i on t2

--------------

 

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

MySQL Analyze Command

You can analyze a table to update its statistics as follows:

 

--------------

create table andrews_table like

information_schema.PROCESSLIST

--------------

 

Query OK, 0 rows affected (0.01 sec)

 

--------------

insert into andrews_table select * from

information_schema.PROCESSLIST

--------------

 

Query OK, 12 rows affected (0.00 sec)

Records: 12  Duplicates: 0  Warnings: 0

 

--------------

analyze table andrews_table

--------------

 

+--------------------+---------+----------+----------+

| Table              | Op      | Msg_type | Msg_text |

+--------------------+---------+----------+----------+

| test.andrews_table | analyze | status   | OK       |

+--------------------+---------+----------+----------+

1 row in set (0.00 sec)

Killing a MySQL User Session

When you login to MySQL, you are given a connection id. In the example below, this is 40:

 

UNIX > mysql -u andrew -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 40

Server version: 5.5.19 MySQL Community Server (GPL)

 

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql>

 

root can see this as the id column in the output from show processlist:

 

mysql> select user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

 

mysql> show processlist;

+----+--------+-----------+------+---------+------+-------+------------------+

| Id | User   | Host      | db   | Command | Time | State | Info             |

+----+--------+-----------+------+---------+------+-------+------------------+

| 40 | andrew | localhost | NULL | Sleep   | 2327 |       | NULL             |

| 41 | root   | localhost | NULL | Query   |    0 | NULL  | show processlist |

+----+--------+-----------+------+---------+------+-------+------------------+

2 rows in set (0.00 sec)

 

mysql>

 

... and root can use it to kill the session:

 

mysql> select user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

 

mysql> kill 40;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+

| Id | User | Host      | db   | Command | Time | State | Info             |

+----+------+-----------+------+---------+------+-------+------------------+

| 41 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |

+----+------+-----------+------+---------+------+-------+------------------+

1 row in set (0.00 sec)

 

mysql>

 

When the user accesses his session, he is to told that it has lost its connection. He is then allowed to reconnect without supplying a password and is given a new connection id:

 

mysql> use test;

No connection. Trying to reconnect...

Connection id:    42

Current database: *** NONE ***

 

Database changed

mysql>

MySQL verbose Option

The verbose option tells MySQL to display SQL before running it. This is useful when using the source command to run SQL from a file. You can see what I mean in the example which follows. Here is a file containing a single SQL statement:

 

UNIX > cat count_star.sql

select count(*) from mysql.user;

UNIX >

 

When you run it from MySQL with the verbose (-v) option, the SQL appears before the output it produces:

 

UNIX > mysql -u root -p -v

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 53444

Server version: 5.0.67 Source distribution

 

Reading history-file /usr/local/mysql/.mysql_history

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> source count_star.sql

--------------

select count(*) from mysql.user

--------------

 

+----------+

| count(*) |

+----------+

|       18 |

+----------+

1 row in set (0.00 sec)

 

mysql> exit

Writing history-file /usr/local/mysql/.mysql_history

Bye

UNIX >