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*) ¤t_user, (gptr*) ¤t_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 */
