Export MySQL Database using Command line Interface


First check if your command line recognizes mysql command. If not go to command and type in

set path=c:\wamp\bin\mysql\mysql5.1.17\bin

Then use this command to export your database

mysqldump -u YourUserName -p YourDatabaseName > backup_database_file_name.sql

This exports the database to the path you are currently in, while executing this command

More commands fro export/Backup database or databases.

# Backup a single database
# NOTE: you can also use the shortcuts -u and -p to specify username and password
mysqldump –user=… –password=… your_database_name > your_database_name.sql

# Backup multiple databases:
mysqldump –user=… –password=… –databases db1 db2 > backup.sql

# or all the databases in one shot:
mysqldump –user=… –password=… –all-databases > backup.sql

# Optionally, it’s easy to compress the backup, for example with gzip:
mysqldump –user=… –password=… your_database_name \
| gzip > your_database_name.sql

# And, if your database also has other objects (apart from tables) such as functions,
# views, and stored procedures, you can back them up too with –routines:

mysqldump –user=… –password=… –routines \
your_database_name > your_database_name.sql

# It may also be useful to include a timestamp in the target file name, so to know
# right away when a backup was taken:

mysqldump –user=… –password=… \
your_database_name > “your_database_name-$(date +%Y-%m-%d-%H.%M.%S).sql”

# If you are backing up data with the purpose of restoring it to an instance you want
# to use as replication slave, then the option –master-data is handy as it adds to
# the dump the information needed to configure the connection with the
# replication master:

mysqldump –user=… –password=… –all-databases –master-data > backup.sql

Advertisements

MySQL Commands


This is a list of handy MySQL commands that I use time and time again. At the bottom are statements, clauses, and functions you can use in MySQL. Below that are PHP and Perl API functions you can use to interface with MySQL.

Continue reading