List:Internals« Previous MessageNext Message »
From:John David Duncan Date:August 21 2003 5:45am
Subject:new mysqldump features (patch)
View as plain text  
Hi,

In the attached patch I have implemented two new options in mysqldump.
The -R (--rename-tables)  option loads all tables using temporary names
and then renames them while holding a lock.  This can be useful when it is
necessary to minimize lock time.  The output looks like this:

sh% mysqldump --rename-tables mydb zip city

  CREATE TABLE _new_zip ...
  CREATE TABLE _new_city ...
  INSERT ...

  LOCK TABLES zip city WRITE;
  DROP TABLE zip;
  ALTER TABLE _new_zip RENAME zip;
  DROP TABLE city;
  ALTER TABLE _new_city RENAME city;
  UNLOCK TABLES;

The -U (--with-use-db) option assures that "USE db_name;" is always in the
output; this is otherwise not true unless --databases or --all-databases
is used.

- JD
*** mysqldump.c	Fri Jul 18 07:57:46 2003
--- new-mysqldump.c	Wed Aug 20 22:00:15 2003
***************
*** 77,84 ****
                  opt_delayed=0,create_options=0,opt_quoted=0,opt_databases=0,
  	        opt_alldbs=0,opt_create_db=0,opt_first_slave=0,
                  opt_autocommit=0,opt_master_data,opt_disable_keys=0,opt_xml=0,
! 	        opt_delete_master_logs=0, tty_password=0,
! 		opt_single_transaction=0;
  static MYSQL  mysql_connection,*sock=0;
  static char  insert_pat[12 * 1024],*opt_password=0,*current_user=0,
               *current_host=0,*path=0,*fields_terminated=0,
--- 77,84 ----
                  opt_delayed=0,create_options=0,opt_quoted=0,opt_databases=0,
  	        opt_alldbs=0,opt_create_db=0,opt_first_slave=0,
                  opt_autocommit=0,opt_master_data,opt_disable_keys=0,opt_xml=0,
! 	        opt_delete_master_logs=0, tty_password=0,opt_rename_tables=0,
! 		opt_single_transaction=0,opt_with_use_db=0;
  static MYSQL  mysql_connection,*sock=0;
  static char  insert_pat[12 * 1024],*opt_password=0,*current_user=0,
               *current_host=0,*path=0,*fields_terminated=0,
***************
*** 212,217 ****
--- 212,219 ----
    {"result-file", 'r',
     "Direct output to a given file. This option should be used in MSDOS, because it prevents new line '\\n' from being converted to '\\r\\n' (carriage return + line feed).",
     0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+   {"rename-tables", 'R',"Create tables using temporary names and then rename them while holding a lock",
+    (gptr*) &opt_rename_tables, (gptr*) &opt_rename_tables, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
    {"socket", 'S', "Socket file to use for connection.",
     (gptr*) &opt_mysql_unix_port, (gptr*) &opt_mysql_unix_port, 0, GET_STR,
     REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
***************
*** 226,231 ****
--- 228,235 ----
     (gptr*) &current_user, (gptr*) &current_user, 0, GET_STR, REQUIRED_ARG,
     0, 0, 0, 0, 0, 0},
  #endif
+   {"with-use-db", 'U',"Always include 'USE db_name;' in the output",
+    (gptr*) &opt_with_use_db, (gptr*) &opt_with_use_db, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
    {"verbose", 'v', "Print info about the various stages.",
     (gptr*) &verbose, (gptr*) &verbose, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
    {"version",'V', "Output version information and exit.", 0, 0, 0,
***************
*** 254,265 ****
--- 258,276 ----
  			int string_value);
  static int dump_selected_tables(char *db, char **table_names, int tables);
  static int dump_all_tables_in_db(char *db);
+ 
  static int init_dumping(char *);
  static int dump_databases(char **);
  static int dump_all_databases();
  static char *quote_name(char *name, char *buff);
  static void print_quoted_xml(FILE *output, char *fname, char *str, uint len);
  
+ static char *rename_and_quote(char *name, char *buff);
+ static int drop_and_rename(char *table);
+ static int rename_tables(char *db, char **table_names, int tables);
+ static int rename_all_in_db(char *database);
+ 
+ 
  static void print_version(void)
  {
    printf("%s  Ver %s Distrib %s, for %s (%s)\n",my_progname,DUMP_VERSION,
***************
*** 555,560 ****
--- 566,587 ----
  } /* quote_name */
  
  
+ static char *rename_and_quote(char *name, char *buff)
+ {
+   char *end;
+   int y;
+ 
+   end = buff;
+   y = (opt_quoted || test_if_special_chars(name));
+   if(y) *end++ = QUOTE_CHAR;
+   end=strmov(end,"_new_");
+   end=strnmov(end,name,NAME_LEN-4);
+   if(y) *end++ = QUOTE_CHAR;
+   *end =  0;
+   return buff;
+ } /* rename_and_quote */
+ 
+ 
  /*
  ** getStructure -- retrievs database structure, prints out corresponding
  **       CREATE statement and fills out insert_pat.
***************
*** 566,574 ****
    MYSQL_ROW  row;
    my_bool    init=0;
    uint       numFields;
!   char	     *strpos, *table_name;
    const char *delayed;
!   char	     name_buff[NAME_LEN+3],table_buff[NAME_LEN+3];
    FILE       *sql_file = md_result_file;
    DBUG_ENTER("getTableStructure");
  
--- 593,601 ----
    MYSQL_ROW  row;
    my_bool    init=0;
    uint       numFields;
!   char	     *strpos, *table_name, *target_table;
    const char *delayed;
!   char	     name_buff[NAME_LEN+3],table_buff[NAME_LEN+3],target_buff[NAME_LEN+3];
    FILE       *sql_file = md_result_file;
    DBUG_ENTER("getTableStructure");
  
***************
*** 579,584 ****
--- 606,614 ----
  
    sprintf(insert_pat,"SET OPTION SQL_QUOTE_SHOW_CREATE=%d", (opt_quoted || opt_keywords));
    table_name=quote_name(table,table_buff);
+   if(opt_rename_tables) 
+     target_table=rename_and_quote(table,target_buff);
+   else target_table=table_name;
    if (!mysql_query(sock,insert_pat))
    {
      /* using SHOW CREATE statement */
***************
*** 610,624 ****
          write_header(sql_file, db);
        }
        if (!opt_xml)
! 	fprintf(sql_file, "\n--\n-- Table structure for table '%s'\n--\n\n",
! 		table);
        if (opt_drop)
!         fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n",table_name);
  
        tableRes=mysql_store_result(sock);
        row=mysql_fetch_row(tableRes);
!       if (!opt_xml)
! 	fprintf(sql_file, "%s;\n", row[1]);
        mysql_free_result(tableRes);
      }
      sprintf(insert_pat,"show fields from %s",table_name);
--- 640,666 ----
          write_header(sql_file, db);
        }
        if (!opt_xml)
! 	    fprintf(sql_file, "\n--\n-- Table structure for table '%s'\n--\n\n",table);
        if (opt_drop)
!         fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n",target_table);
  
        tableRes=mysql_store_result(sock);
        row=mysql_fetch_row(tableRes);
!       if(opt_rename_tables) {
!         char *s = row[1];
!         if(!(bcmp(row[1],"CREATE TABLE ",13))) {
!           fprintf(sql_file,"CREATE TABLE %s ",target_table);
!           for(s+=13 ; *s++ != ' ' ; );
!           fprintf(sql_file, "%s;\n", s);
!         }
!         else { 
!           fprintf(sql_file,"-- Failed to use renamed table\n\n");
!           target_table=table_name;
!           opt_rename_tables=0;
!         }
!       }  
!       else if (!opt_xml)
! 	    fprintf(sql_file, "%s;\n", row[1]);
        mysql_free_result(tableRes);
      }
      sprintf(insert_pat,"show fields from %s",table_name);
***************
*** 633,642 ****
      }
  
      if (cFlag)
!       sprintf(insert_pat, "INSERT %sINTO %s (", delayed, table_name);
      else
      {
!       sprintf(insert_pat, "INSERT %sINTO %s VALUES ", delayed, table_name);
        if (!extended_insert)
          strcat(insert_pat,"(");
      }
--- 675,684 ----
      }
  
      if (cFlag)
!       sprintf(insert_pat, "INSERT %sINTO %s (", delayed, target_table);
      else
      {
!       sprintf(insert_pat, "INSERT %sINTO %s VALUES ", delayed, target_table);
        if (!extended_insert)
          strcat(insert_pat,"(");
      }
***************
*** 690,703 ****
  	fprintf(sql_file, "\n--\n-- Table structure for table '%s'\n--\n\n",
  		table);
        if (opt_drop)
!         fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n",table_name);
!       fprintf(sql_file, "CREATE TABLE %s (\n", table_name);
      }
      if (cFlag)
!       sprintf(insert_pat, "INSERT %sINTO %s (", delayed, table_name);
      else
      {
!       sprintf(insert_pat, "INSERT %sINTO %s VALUES ", delayed, table_name);
        if (!extended_insert)
          strcat(insert_pat,"(");
      }
--- 732,745 ----
  	fprintf(sql_file, "\n--\n-- Table structure for table '%s'\n--\n\n",
  		table);
        if (opt_drop)
!         fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n",target_table);
!       fprintf(sql_file, "CREATE TABLE %s (\n", target_table);
      }
      if (cFlag)
!       sprintf(insert_pat, "INSERT %sINTO %s (", delayed, target_table);
      else
      {
!       sprintf(insert_pat, "INSERT %sINTO %s VALUES ", delayed, target_table);
        if (!extended_insert)
          strcat(insert_pat,"(");
      }
***************
*** 719,725 ****
        if (!tFlag)
        {
          if (opt_keywords)
! 	  fprintf(sql_file, "  %s.%s %s", table_name,
  	  quote_name(row[SHOW_FIELDNAME],name_buff), row[SHOW_TYPE]);
          else
  	  fprintf(sql_file, "  %s %s", quote_name(row[SHOW_FIELDNAME],
--- 761,767 ----
        if (!tFlag)
        {
          if (opt_keywords)
! 	  fprintf(sql_file, "  %s.%s %s", target_table,
  	  quote_name(row[SHOW_FIELDNAME],name_buff), row[SHOW_TYPE]);
          else
  	  fprintf(sql_file, "  %s %s", quote_name(row[SHOW_FIELDNAME],
***************
*** 901,907 ****
  */
  static void dumpTable(uint numFields, char *table)
  {
!   char query[QUERY_LENGTH], *end, buff[256],table_buff[NAME_LEN+3];
    MYSQL_RES	*res;
    MYSQL_FIELD	*field;
    MYSQL_ROW	row;
--- 943,950 ----
  */
  static void dumpTable(uint numFields, char *table)
  {
!   char query[QUERY_LENGTH], *end, *target_table;
!   char buff[256],table_buff[NAME_LEN+3],target_buff[NAME_LEN+3];  
    MYSQL_RES	*res;
    MYSQL_FIELD	*field;
    MYSQL_ROW	row;
***************
*** 909,914 ****
--- 952,960 ----
  
    if (verbose)
      fprintf(stderr, "-- Sending SELECT query...\n");
+   if(opt_rename_tables) 
+     target_table=rename_and_quote(table,target_buff);
+   else target_table=quote_name(table,table_buff);
    if (path)
    {
      char filename[FN_REFLEN], tmp_path[FN_REFLEN];
***************
*** 985,995 ****
      }
  
      if (opt_disable_keys)
!       fprintf(md_result_file,"/*!40000 ALTER TABLE %s DISABLE KEYS */;\n",
! 	      quote_name(table, table_buff));
      if (opt_lock)
!       fprintf(md_result_file,"LOCK TABLES %s WRITE;\n",
! 	      quote_name(table,table_buff));
  
      total_length=net_buffer_length;		/* Force row break */
      row_break=0;
--- 1031,1039 ----
      }
  
      if (opt_disable_keys)
!       fprintf(md_result_file,"/*!40000 ALTER TABLE %s DISABLE KEYS */;\n",target_table);
      if (opt_lock)
!       fprintf(md_result_file,"LOCK TABLES %s WRITE;\n",target_table);
  
      total_length=net_buffer_length;		/* Force row break */
      row_break=0;
***************
*** 1152,1162 ****
        safe_exit(EX_CONSCHECK);
        return;
      }
      if (opt_lock)
        fputs("UNLOCK TABLES;\n", md_result_file);
      if (opt_disable_keys)
        fprintf(md_result_file,"/*!40000 ALTER TABLE %s ENABLE KEYS */;\n",
! 	      quote_name(table,table_buff));
      if (opt_autocommit)
        fprintf(md_result_file, "commit;\n");
      mysql_free_result(res);
--- 1196,1208 ----
        safe_exit(EX_CONSCHECK);
        return;
      }
+ 
+ 
      if (opt_lock)
        fputs("UNLOCK TABLES;\n", md_result_file);
      if (opt_disable_keys)
        fprintf(md_result_file,"/*!40000 ALTER TABLE %s ENABLE KEYS */;\n",
! 	      target_table);
      if (opt_autocommit)
        fprintf(md_result_file, "commit;\n");
      mysql_free_result(res);
***************
*** 1253,1262 ****
    }
    if (!path && !opt_xml)
    {
!     if (opt_databases || opt_alldbs)
      {
        fprintf(md_result_file,"\n--\n-- Current Database: %s\n--\n", database);
!       if (!opt_create_db)
  	fprintf(md_result_file,"\nCREATE DATABASE /*!32312 IF NOT EXISTS*/ %s;\n",
  		database);
        fprintf(md_result_file,"\nUSE %s;\n", database);
--- 1299,1308 ----
    }
    if (!path && !opt_xml)
    {
!     if (opt_databases || opt_alldbs || opt_with_use_db)
      {
        fprintf(md_result_file,"\n--\n-- Current Database: %s\n--\n", database);
!       if ((opt_databases || opt_alldbs) && !opt_create_db)
  	fprintf(md_result_file,"\nCREATE DATABASE /*!32312 IF NOT EXISTS*/ %s;\n",
  		database);
        fprintf(md_result_file,"\nUSE %s;\n", database);
***************
*** 1299,1305 ****
        DBerror(sock, "when doing refresh");
             /* We shall continue here, if --force was given */
    }
!   while ((table = getTableName(0)))
    {
      numrows = getTableStructure(table, database);
      if (!dFlag && numrows > 0)
--- 1345,1351 ----
        DBerror(sock, "when doing refresh");
             /* We shall continue here, if --force was given */
    }
!   while ((table = getTableName(1)))
    {
      numrows = getTableStructure(table, database);
      if (!dFlag && numrows > 0)
***************
*** 1309,1314 ****
--- 1355,1362 ----
      fprintf(md_result_file, "</database>\n");
    if (lock_tables)
      mysql_query(sock,"UNLOCK_TABLES");
+   if (opt_rename_tables) 
+     rename_all_in_db(database);
    return 0;
  } /* dump_all_tables_in_db */
  
***************
*** 1317,1330 ****
  static int dump_selected_tables(char *db, char **table_names, int tables)
  {
    uint numrows;
!   char table_buff[NAME_LEN+3];
  
    if (init_dumping(db))
      return 1;
    if (lock_tables)
    {
      DYNAMIC_STRING query;
-     int i;
  
      init_dynamic_string(&query, "LOCK TABLES ", 256, 1024);
      for (i=0 ; i < tables ; i++)
--- 1365,1379 ----
  static int dump_selected_tables(char *db, char **table_names, int tables)
  {
    uint numrows;
!   char *target_table;
!   char table_buff[NAME_LEN+3], target_buff[NAME_LEN+3];
!   int i;
  
    if (init_dumping(db))
      return 1;
    if (lock_tables)
    {
      DYNAMIC_STRING query;
  
      init_dynamic_string(&query, "LOCK TABLES ", 256, 1024);
      for (i=0 ; i < tables ; i++)
***************
*** 1345,1362 ****
    }
    if (opt_xml)
      fprintf(md_result_file, "<database name=\"%s\">\n", db);
!   for (; tables > 0 ; tables-- , table_names++)
    {
!     numrows = getTableStructure(*table_names, db);
      if (!dFlag && numrows > 0)
!       dumpTable(numrows, *table_names);
    }
    if (opt_xml)
      fprintf(md_result_file, "</database>\n");
    if (lock_tables)
      mysql_query(sock,"UNLOCK_TABLES");
    return 0;
  } /* dump_selected_tables */
  
  
  /* Print a value with a prefix on file */
--- 1394,1462 ----
    }
    if (opt_xml)
      fprintf(md_result_file, "<database name=\"%s\">\n", db);
!   for (i = 0; i < tables ; i++)
    {
!     numrows = getTableStructure(table_names[i], db);
      if (!dFlag && numrows > 0)
!       dumpTable(numrows, table_names[i]);
    }
    if (opt_xml)
      fprintf(md_result_file, "</database>\n");
    if (lock_tables)
      mysql_query(sock,"UNLOCK_TABLES");
+   if (opt_rename_tables) 
+     rename_tables(db,table_names,tables);
    return 0;
  } /* dump_selected_tables */
+ 
+ 
+ static int drop_and_rename(char *table) 
+ {
+   char *renamed_table, *real_table;
+   char table_buff[NAME_LEN+3],target_buff[NAME_LEN+3];
+ 
+   real_table=quote_name(table,table_buff);
+   renamed_table=rename_and_quote(table,target_buff);
+   fprintf(md_result_file,"DROP TABLE %s;\n",real_table);
+   fprintf(md_result_file,"ALTER TABLE %s RENAME %s;\n",
+     renamed_table,real_table);
+ } /* drop_and_rename */
+ 
+ 
+ static int rename_all_in_db(char *database) 
+ {
+   int i;
+   char *table;
+   char buff[NAME_LEN+3];
+   
+   fprintf(md_result_file,"\nLOCK TABLES ");
+   while ((table = getTableName(1)))
+     fprintf(md_result_file,"%s ",quote_name(table,buff));
+   fprintf(md_result_file,"WRITE;\n");
+   
+   while ((table = getTableName(1))) {
+     drop_and_rename(table);
+   }
+   fprintf(md_result_file,"UNLOCK TABLES;\n"); 
+ } /* rename_all_in_db */
+ 
+ 
+ static int rename_tables(char *db, char **table_names, int tables) 
+ {
+   int i;
+   char *table;
+   char buff[NAME_LEN+3];
+ 
+   fprintf(md_result_file,"\nLOCK TABLES ");
+   for (i=0 ; i < tables ; i++) 
+     fprintf(md_result_file,"%s ",
+             quote_name(table_names[i],buff));
+   fprintf(md_result_file,"WRITE;\n");
+ 
+   for (i=0 ; i < tables ; i++) 
+     drop_and_rename(table_names[i]);
+   fprintf(md_result_file,"UNLOCK TABLES;\n"); 
+ } /* rename_tables */
  
  
  /* Print a value with a prefix on file */
Thread
new mysqldump features (patch)John David Duncan21 Aug
  • Re: new mysqldump features (patch)Tim Bunce21 Aug
    • Re: new mysqldump features (patch)John David Duncan21 Aug
      • Re: new mysqldump features (patch)Tim Bunce21 Aug
  • Re: new mysqldump features (patch)Sergei Golubchik22 Aug