Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is sql-dump for?

Tags:

I know that a SQL dump is a series of insert SQL statements which reflect all the records inside the database. But what is it used for? Why should we dump the database records? Does every database support a dumping function?

like image 839
Yang Avatar asked Mar 25 '10 01:03

Yang


People also ask

What causes SQL dump files?

When there is something “unexpected”, SQL Server kills the SPID and generates a dump. There are situations where SQL threads are not yielding (“Non-yielding Scheduler”, “Non-yielding IOCP Listener” or “Non-yielding Resource Monitor”) and there would be data needed to diagnose the problem later, so a dump is generated.

What is difference between dump and backup?

An RMAN backup is a physical backup and a Data Pump backup is a logical backup. A database dump using expdp is a 1-time export of one or more database schemas. It backs up DDL (table structures, views, synonyms, stored procedures, packages, etc), plus data.

What is dumping data for table?

Introduction to MySQL Table Dump. MySQL Table Dump is a technique responsible to dump different databases organised by the MySQL server. A database dump is a kind of text file that comprises a record of database table structure or/and also the data which is normally in the arrangement of SQL statements list.

Can I delete SQL dump files?

Your available disk space - If the disk where your dumps are accumulating is also the same disk where your database files or transaction log files also reside, and you are going to run out of disk space if you don't do something, and you have no where to move the dump files to, then by all means, delete them.


2 Answers

mysqldump produces an SQL representation of the data for one or more tables or databases. As the format is SQL, it will run on any other MySQL server, regardless of architecture or major/minor version (obviously, views won't work on 4.x etc. but it is mostly forwards compatible).

There is another tool, mysqlhotcopy, but as this tool produces binary files, they are tied to the machine they have been generated on, and cannot be used elsewhere. SQL has the advantage of running on any MySQL server, and being independent of the underlying file storage mechanism of the database(s).

The two main use cases for dumping SQL are:

  • Backing up the database data. The SQL can be read in ("played back") to an empty database server and it will re-create the tables and populate them with rows.
  • Migrating the data to another server. Say you are upgrading from MySQL 5.0 to 5.1. You have two machines. You use mysqldump to produce an SQL dump on the 5.0 machine, and feed it into the 5.1.

There are some less common uses. For example, SQL snapshot of your application's database could be taken for unit testing against a known state. It is also possible to transform SQL code into another dialect, e.g. PostgeSQL or SQLite, to port your data to another database.

You asked if other databases provide SQL dump functionality. The answer is yes in almost all cases. PostgreSQL provides pg_dump, SQLite has a .dump command, etc.

like image 20
rjh Avatar answered Nov 07 '22 13:11

rjh


Somewhat strangely, this is actually the usual way to back up a database. Copying the files themselves that actually hold the data is not the usual backup method, for various complicated reasons.

All relational databases work this way, or at least I've never heard of one that doesn't: they all have a facility to export a bunch of SQL code that, when executed, will recreate the database in the same state it was in when the dump was started.

However these various formats are generally incompatible, due to subtle differences between the various dialects of SQL used by the different database systems. There are utilities that can convert between some of them, but I'm not aware of any 'Rosetta Stone' that handles every possible case.

As well as being the primary method of backing up a database, this technique is also useful when staging the data of db apps between different servers, ie from development to testing to production.

like image 73
intuited Avatar answered Nov 07 '22 15:11

intuited