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 select user() and show processlist Commands

The select user() command shows who you are logged in as:

 

UNIX > mysql -u andrew -p

Enter password:

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

Your MySQL connection id is 37

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> use test;

Database changed

mysql> select user();

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

| user()           |

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

| andrew@localhost |

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

1 row in set (0.00 sec)

 

mysql>

 

... and the show processlist command shows who is logged in. I had to shrink it so that it would fit in the screen. The time column shows how long the user has been logged in for in seconds:

 

UNIX > mysql -u root -p

Enter password:

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

Your MySQL connection id is 38

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> show processlist;

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

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

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

| 37 | andrew | localhost | test | Sleep   |  237 |       | NULL             |

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

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

2 rows in set (0.00 sec)

 

mysql> show processlist;

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

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

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

| 37 | andrew | localhost | test | Sleep   |  248 |       | NULL             |

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

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

2 rows in set (0.00 sec)

 

mysql>

MySQL source Command

The source command runs SQL commands from an operating system file. I created the file below:

 

UNIX > cat my_first_script

create database fred;

select database();

use fred;

select database();

create table freds_table

(col1 varchar(10));

desc freds_table;

drop table freds_table;

show tables;

drop database fred;

UNIX >

 

... and it produced the following output when I ran it from within MySQL:

 

mysql> source my_first_script

Query OK, 1 row affected (0.00 sec)

 

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

| database() |

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

| NULL       |

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

1 row in set (0.00 sec)

 

Database changed

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

| database() |

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

| fred       |

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

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.01 sec)

 

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

| Field | Type        | Null | Key | Default | Extra |

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

| col1  | varchar(10) | YES  |     | NULL    |       |

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

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Empty set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql>

MySQL show tables from command

If you are using one database, you can see the tables in a different one with the show tables from command:

 

mysql> use test;

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> show tables from andrew;

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

| Tables_in_andrew |

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

| andrews_table    |

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

1 row in set (0.00 sec)

 

mysql>

And you can look at the contents of a table from another database by concatenating the database name, a dot and the table name:

mysql> select * from andrew.andrews_table;

Empty set (0.01 sec)

 

mysql>

MyISAM versus InnoDB Tables in MySQL

This is a worked example comparing 2 MySQL table types:

1. MyISAM – you cannot rollback DML on these tables.
2. InnoDB – you can (sometimes) rollback DML on these tables.

First, create a table of each type:

 

UNIX > mysql -u root -p

Enter password:

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

Your MySQL connection id is 10370

Server version: 5.0.67 Source distribution

 

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

 

mysql> use test;

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> create table andrews_myisam

    -> (col1 varchar(10)) engine = myisam;

Query OK, 0 rows affected (0.00 sec)

 

mysql> create table andrews_innodb

    -> (col1 varchar(10)) engine = innodb;

Query OK, 0 rows affected (0.38 sec)

 

mysql>

 

Then insert a row in each table and rollback the insert. Note the warning that you cannot rollback non-transactional tables:

 

mysql> insert into andrews_myisam values ('MYISAM');

Query OK, 1 row affected (0.06 sec)

 

mysql> insert into andrews_innodb values ('INNODB');

Query OK, 1 row affected (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> show warnings;

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

| Level   | Code | Message                                                       |

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

| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |

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

1 row in set (0.00 sec)

 

mysql>

 

Query the tables and note that the rollback has failed in BOTH tables and the inserted rows are still there:

 

mysql> select * from andrews_myisam;

+--------+

| col1   |

+--------+

| MYISAM |

+--------+

1 row in set (0.00 sec)

 

mysql> select * from andrews_innodb;

+--------+

| col1   |

+--------+

| INNODB |

+--------+

1 row in set (0.00 sec)

 

mysql>

 

This is because MySQL’s default behaviour is to commit DML immediately. To alter this, you need to set autocommit = 0. Do this then repeat the test. This time the DML deletes the rows inserted above:

 

mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> delete from andrews_myisam;

Query OK, 1 row affected (0.00 sec)

 

mysql> delete from andrews_innodb;

Query OK, 1 row affected (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> show warnings;

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

| Level   | Code | Message                                                       |

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

| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |

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

1 row in set (0.00 sec)

 

mysql> select * from andrews_myisam;

Empty set (0.00 sec)

 

mysql> select * from andrews_innodb;

+--------+

| col1   |

+--------+

| INNODB |

+--------+

1 row in set (0.00 sec)

 

mysql>

 

The delete statement on the MyISAM table could not be rolled back, as you might expect. Setting autocommit to zero prevented the delete statement on the InnoDB being committed immediately. The rollback therefore worked and the inserted row reappeared in the table.

Granting Access to a MySQL Database

In this example, I had a go at granting access to a database. First, I created a database and a user:

 

UNIX > mysql -u root -p

Enter password:

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

Your MySQL connection id is 8718

Server version: 5.0.67 Source distribution

 

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

 

mysql> create database fred;

Query OK, 1 row affected (0.00 sec)

 

mysql> create user 'john'@'localhost'

    -> identified by 'smith';

Query OK, 0 rows affected (0.00 sec)

 

mysql> exit

Bye

UNIX >

 

Then I logged in as the new user and tried to use the new database:

 

UNIX > mysql -u john -p

Enter password:

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

Your MySQL connection id is 8721

Server version: 5.0.67 Source distribution

 

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

 

mysql> use fred

ERROR 1044 (42000): Access denied for user 'john'@'localhost' to database 'fred'

mysql> quit

Bye

UNIX >

 

That failed and the reason for failure was fairly obvious so I decided to see what I could do about it:

 

UNIX > mysql -u root -p

Enter password:

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

Your MySQL connection id is 8734

Server version: 5.0.67 Source distribution

 

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

 

mysql> grant all on fred.* to john;

Query OK, 0 rows affected (0.28 sec)

 

mysql> exit

Bye

UNIX >

 

Next, I logged back in as the new user and tried to access the database again. It seemed to work OK. N.B. The grant all command even allows the user to drop the database:

 

UNIX > mysql -u john -p

Enter password:

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

Your MySQL connection id is 8735

Server version: 5.0.67 Source distribution

 

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

 

mysql> use fred;

Database changed

mysql> drop database fred;

Query OK, 0 rows affected (0.00 sec)

 

mysql>

 

Finally I dropped the new user:

 

UNIX > mysql -u root -p

Enter password:

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

Your MySQL connection id is 8761

Server version: 5.0.67 Source distribution

 

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

 

mysql> drop user john;

Query OK, 0 rows affected (0.00 sec)

 

mysql>

select database()

The select database() command lets you see which database you are using. When you first login to mysql, you are not using a database so the query returns a null:

 

mysql> select database ();
+-------------+
| database () |
+-------------+
| NULL        |
+-------------+
1 row in set (0.01 sec)

mysql>

You can choose a database with the use command. Unlike other commands, it seems to work equally well with or without a semi colon at the end:
  

mysql> use test
Database changed
mysql> use test;
Database changed
mysql>

After that, if you run the query again, it shows which database you have chosen:

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.02 sec)

mysql>