Wednesday, 14 December 2011

Dump table and database to a file in MySQL

For DB Backup : -

mysqldump -d -h HOST -u USER -p PASSWORD DBNAME > dumpfile.sql

For Table Backup : -

mysqldump -d -h HOST -u USER -p PASSWORD DBNAME TABLENAME > table.sql

For Only Table Structure :-

mysqldump -d -h HOST -u USER -p PASSWORD DBNAME TABLENAME  –no-data > table.sql

You Can Restore Dump as Below : -

mysql > source /path/SQLfile
at

3 comments:

  1. Vijay9 February 2012 at 14:28

    nice thanks for this.how can we take backup only update data from table. becaus it is too big like 80mb etc.?

    ReplyDelete
  2. Dhiraj Rokade15 February 2012 at 11:03

    MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup.

    #mysqlbinlog binlog_files > temp

    #mysql -u root -p < temp

    ReplyDelete
  3. Dhiraj Rokade15 February 2012 at 11:05

    You must start the server with the --log-bin option to enable binary logging.

    #mysqlbinlog binfile > temp

    #mysql -u root -p < temp

    ReplyDelete