Exporting MySQL database from within a command prompt mysql tool. Without mysqldump

Question

I am logged into mysql on my server and able to type commands from there. I cannot find the command that will let me export from within the Command Line Tool. Sure I can log out and run mysqldump -u -p... but that is a pain. To import from inside the client, you just type source path/to/the/source

There must be something like this for export, but everything on the internet points to mysqldump, and so I cannot find the tool I am looking for. After all, mysqldump is just a wrapper for the command that I'm looking for. Does anyone know what to use? Thank you to anyone that can help.

Michael - sqlbot · Accepted Answer · 2013-11-19 23:55:32Z

4

After all, mysqldump is just a wrapper for the command that I'm looking for.

No, it actually isn't.

Some of the other utilities like mysqladmin and mysqlcheck are not much more than "convenient" (or, if you ask me, largely pointless) wrappers for SQL statements you can issue yourself, so I suppose your assumption could be reasonable in that light, but that's not case with mysqldump.

The source code for mysqldump is ~5,061 lines of C -- and that's not even including the code that actually manages the TCP or Unix socket communication with the MySQL server or a lot of other common code libraries that are shared among the MySQL Server and the utilities.

Yes, the mysqldump utility does -- where practical -- assemble some SQL statements, send them to the server for execution, and write the server's response in the dump file...

my_snprintf(query, sizeof(query), "SHOW CREATE TABLE %s", result_table);

... that one's kind of a no-brainer, pretty much a wheel that need not be reinvented.

However, there are a number of operations that it performs that are not at all handled by the MySQL server code. Examples...

if (opt_drop_trigger)
  fprintf(sql_file, "/*!50032 DROP TRIGGER IF EXISTS %s */;\n", (*show_trigger_row)[0]);

fprintf(sql_file,
      "DELIMITER ;;\n"
      "/*!50003 SET SESSION SQL_MODE=\"%s\" */;;\n"
      "/*!50003 CREATE */ ",
      (*show_trigger_row)[6]);

There is no functionality inside mysql to do what that does, or this...

if (write_data)
{
  if (opt_replace_into)
    dynstr_append_checked(&insert_pat, "REPLACE ");
  else
    dynstr_append_checked(&insert_pat, "INSERT ");
  dynstr_append_checked(&insert_pat, insert_option);
  dynstr_append_checked(&insert_pat, "INTO ");
  dynstr_append_checked(&insert_pat, opt_quoted_table);
  if (complete_insert)
  {
    dynstr_append_checked(&insert_pat, " (");
  }
  else
  {
    dynstr_append_checked(&insert_pat, " VALUES ");
    if (!extended_insert)
      dynstr_append_checked(&insert_pat, "(");
  }
}

while ((row= mysql_fetch_row(result)))
{
  if (complete_insert)
  {
    if (init)
    {
      dynstr_append_checked(&insert_pat, ", ");
    }
    init=1;
    dynstr_append_checked(&insert_pat,
                  quote_name(row[SHOW_FIELDNAME], name_buff, 0));
  }
}
num_fields= mysql_num_rows(result);
mysql_free_result(result);

That seems like a lot of unnecessary work for a wrapper. :) You may have worked out by now, that's an example of some of the simple things mysqldump does, reading the row data coming back from the server in a packed binary format after it issues the SELECT /*!40001 SQL_NO_CACHE */ * FROM ... to fetch all of the data from each table, and converting it into SQL statements. The code that actually crafts those INSERT statements is from mysqldump. Not in the server.

There's a reason why everything on the Internet points to mysqldump.

answered Nov 19, 2013 at 23:55

Michael - sqlbot

180k28 gold badges388 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

usumoio Over a year ago

Well that certainly does explain why everything was pointing to it. I have not yet fully digested the code you send me, so it might just be me, but it still seems strange that there is no inverse of source. While I understand that this might be complex to build it still sees surprising. It is truly the case that I have to log out to dump the DB?

Michael - sqlbot Over a year ago

In any practical sense, yes. Also, though, talk of "the inverse of source" is sort of like attempting division by zero, though... because source isn't exactly the opposite of mysqldump... you can craft a text file with any kind of arbitrary SQL statements in it and source will dutifully send those statements to the server for execution. Restoring dump files is just one of the things source can be used for, while mysqldump is specifically for constructing the equivalent SQL statements that would recreate an existing database in exactly its current state.

Kacer · Accepted Answer · 2013-11-19 22:51:55Z

0

What mysqldump can be achived by different SHOW commands and after enough effort you will end up with mysqldump ;)

Look at http://dev.mysql.com/doc/refman/5.5/en/show.html for all possible variants.

From my point of view it's more convenient to use mysqldump because it takes all the same parameters for connection as mysql Command Line Tool.

answered Nov 19, 2013 at 22:51

Kacer

6793 silver badges12 bronze badges

Collectives™ on Stack Overflow

Exporting MySQL database from within a command prompt mysql tool. Without mysqldump

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related