Thursday, June 21, 2012

Backup and Restore MySQL Database

The mysqldump is a backup program used to dump a database or collection of database for a backup
Syntax: Table level
$ mysqldump [options] db_name [tbble_name ...]
Database level
$ mysqldump [options] --databases database_name ...
All Databases
$ mysqldump [options] --all-databases

The mysqlimport is used to import the data into database.
Syntax:
$mysqlimport [options] db_name dumpfile1 [dumpfile2 ...]

You can load the dumpfile into server using mysql command.

Backup Mysql Database using mysqldump1. Take a backup of single database on the same server
$mysqldump -u user_name -d database_name –p > database.sql

2. Backup single database from remote server
$ mysqldump -u user_name -h host_name -d database_name –p > database.sql

3. If you are running more than one MYSQL instance on different ports them you need to mention the port to backup database
$ mysqldump -u user_name -h host_name -P port -d database_name –p > database.sql

4. Backup a table from a database
$ mysqldump -u username database_name table_name > table.sql

5. Backup all databases on the server as ROOT
$ mysqldump -u root --all-databases > all_databases.sql

Restore MySQL Database using “mysql” command
If you are moving the database from one server to another server or remotely then use below commands

1. Restore a database on the same server
$mysql -u user_name database_name –p < database.sql
Or
$ mysql -e "source /tmp/database.sql " database_name

2. Restore database from remote server
$ mysql -u user_name -h host_name database_name –p < database.sql

3. If you are running more than one MYSQL instance on different ports them you need to mention the port of Instance to restore
$ mysql -u user_name -h host_name -P port database_name –p < database.sql

4. Restore a table from a database
$ mysql -u username database_name < table.sql

5. Restore all databases from the dump as ROOT
$ mysql -u root < all_databases.sql

Restore database using mysqlimportThe database should exist in order to import dump using mysqlimport.
$ mysqlimport -u user_name -p password database_name /tmp/dumpfile.sql

Write MySQL table data to a file
Use below syntax to write table data/contents to a file
mysql> SELECT * FROM . ORDER BY DESC LIMIT 0,99000 INTO OUTFILE '/tmp/table_name.txt'

If you don’t have access to write from MySQL to a file then you will receive below error.
ERROR 1045 (28000): Access denied for user 'xxxxxxx'@'%' (using password: YES)

If you dont have permission the use below alternate option
$ echo "SELECT * FROM database_name.table_name" mysql -u -h --port= --password= > /tmp/table_name.txt

Rename Mysql Database
Either you can backup and restore the database into new database or follow below steps
mysql>create database new_database;
$ mysqldump -u user_name -d old_database -p | mysql -u user_name -d new_database -p
mysql> drop database old_database

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

1 comment: