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 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 >

MySQL Character Set (part 2)

Go to part 1 

You can specify a database’s character set when you create it:

 

mysql> create database andrew

    -> default character set utf8;

Query OK, 1 row affected (0.00 sec)

 

mysql> show create database andrew;

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

| Database | Create Database                                                 |

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

| andrew   | CREATE DATABASE `andrew` /*!40100 DEFAULT CHARACTER SET utf8 */ |

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

1 row in set (0.00 sec)

 

mysql>

 

... and you can alter it subsequently as follows:

 

mysql> alter database andrew charset = latin1;

Query OK, 1 row affected (0.00 sec)

 

mysql> show create database andrew;

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

| Database | Create Database                                                   |

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

| andrew   | CREATE DATABASE `andrew` /*!40100 DEFAULT CHARACTER SET latin1 */ |

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

1 row 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>

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>

Creating a MySQL Database on UNIX

In the previous example, I created a MySQL database on Windows and found that database names are not case sensitive.

I ran the commands below on a UNIX machine and, as you might expect, MySQL database names ARE case sensitive there:

mysql> create database Andrew;

Query OK, 1 row affected (0.01 sec)

 

mysql> drop database andrew;

ERROR 1008 (HY000): Can't drop database 'andrew'; database doesn't exist

mysql> drop database Andrew;

Query OK, 0 rows affected (0.14 sec)

 

mysql>

Creating a MySQL Database on Windows XP

In the example below, the show databases command is used to display the names of databases which already exist. The create database command is then used to create a database called andrew. The show databases command is run again and database andrew appears in the list. Next, the drop database command is used to drop a database called ANDREW, showing that in Windows, MySQL database names are not case sensitive. Finally, the show databases command is run again to show that the database has gone:
  

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
6 rows in set (0.05 sec)

mysql> create database andrew;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| andrew             |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
7 rows in set (0.01 sec)

mysql> drop database ANDREW;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
6 rows in set (0.00 sec)

mysql>