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>
