diff options
Diffstat (limited to 'client/mysqldump.c')
| -rw-r--r-- | client/mysqldump.c | 471 |
1 files changed, 453 insertions, 18 deletions
diff --git a/client/mysqldump.c b/client/mysqldump.c index 333bfbff1e6..6fcb143d2c9 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -29,14 +29,14 @@ ** master/autocommit code by Brian Aker <brian@tangent.org> ** SSL by ** Andrei Errapart <andreie@no.spam.ee> -** Tõnu Samuel <tonu@please.do.not.remove.this.spam.ee> +** Tõnu Samuel <tonu@please.do.not.remove.this.spam.ee> ** XML by Gary Huntress <ghuntress@mediaone.net> 10/10/01, cleaned up ** and adapted to mysqldump 05/11/01 by Jani Tolonen ** Added --single-transaction option 06/06/2002 by Peter Zaitsev ** 10 Jun 2003: SET NAMES and --no-set-names by Alexander Barkov */ -#define DUMP_VERSION "10.11" +#define DUMP_VERSION "10.12" #include <my_global.h> #include <my_sys.h> @@ -50,6 +50,7 @@ #include "mysql.h" #include "mysql_version.h" #include "mysqld_error.h" +#include "../sql/ha_ndbcluster_tables.h" /* Exit codes */ @@ -95,7 +96,10 @@ static my_bool verbose= 0, opt_no_create_info= 0, opt_no_data= 0, opt_single_transaction=0, opt_comments= 0, opt_compact= 0, opt_hex_blob=0, opt_order_by_primary=0, opt_ignore=0, opt_complete_insert= 0, opt_drop_database= 0, - opt_dump_triggers= 0, opt_routines=0, opt_tz_utc=1; + opt_replace_into= 0, + opt_dump_triggers= 0, opt_routines=0, opt_tz_utc=1, + opt_events= 0, + opt_alltspcs=0, opt_notspcs= 0; static ulong opt_max_allowed_packet, opt_net_buffer_length; static MYSQL mysql_connection,*mysql=0; static my_bool insert_pat_inited= 0, info_flag; @@ -146,7 +150,6 @@ static CHARSET_INFO *charset_info= &my_charset_latin1; const char *default_dbug_option="d:t:o,/tmp/mysqldump.trace"; /* have we seen any VIEWs during table scanning? */ my_bool seen_views= 0; - const char *compatible_mode_names[]= { "MYSQL323", "MYSQL40", "POSTGRESQL", "ORACLE", "MSSQL", "DB2", @@ -177,6 +180,14 @@ static struct my_option my_long_options[] = "Dump all the databases. This will be same as --databases with all databases selected.", (gptr*) &opt_alldbs, (gptr*) &opt_alldbs, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, + {"all-tablespaces", 'Y', + "Dump all the tablespaces.", + (gptr*) &opt_alltspcs, (gptr*) &opt_alltspcs, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, + 0, 0}, + {"no-tablespaces", 'y', + "Do not dump any tablespace information.", + (gptr*) &opt_notspcs, (gptr*) &opt_notspcs, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, + 0, 0}, {"add-drop-database", OPT_DROP_DATABASE, "Add a 'DROP DATABASE' before each create.", (gptr*) &opt_drop_database, (gptr*) &opt_drop_database, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, @@ -243,6 +254,9 @@ static struct my_option my_long_options[] = {"disable-keys", 'K', "'/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and '/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put in the output.", (gptr*) &opt_disable_keys, (gptr*) &opt_disable_keys, 0, GET_BOOL, NO_ARG, 1, 0, 0, 0, 0, 0}, + {"events", 'E', "Dump events.", + (gptr*) &opt_events, (gptr*) &opt_events, 0, GET_BOOL, + NO_ARG, 0, 0, 0, 0, 0, 0}, {"extended-insert", 'e', "Allows utilization of the new, much faster INSERT syntax.", (gptr*) &extended_insert, (gptr*) &extended_insert, 0, GET_BOOL, NO_ARG, @@ -365,6 +379,9 @@ static struct my_option my_long_options[] = {"quote-names",'Q', "Quote table and column names with backticks (`).", (gptr*) &opt_quoted, (gptr*) &opt_quoted, 0, GET_BOOL, NO_ARG, 1, 0, 0, 0, 0, 0}, + {"replace", OPT_MYSQL_REPLACE_INTO, "Use REPLACE INTO instead of INSERT INTO.", + (gptr*) &opt_replace_into, (gptr*) &opt_replace_into, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, + 0, 0}, {"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}, @@ -451,6 +468,10 @@ char check_if_ignore_table(const char *table_name, char *table_type); static char *primary_key_fields(const char *table_name); static my_bool get_view_structure(char *table, char* db); static my_bool dump_all_views_in_db(char *database); +static int dump_all_tablespaces(); +static int dump_tablespaces_for_tables(char *db, char **table_names, int tables); +static int dump_tablespaces_for_databases(char** databases); +static int dump_tablespaces(char* ts_where); #include <help_start.h> @@ -537,8 +558,10 @@ static void write_header(FILE *sql_file, char *db_name) if (opt_xml) { fputs("<?xml version=\"1.0\"?>\n", sql_file); - /* Schema reference. Allows use of xsi:nil for NULL values and - xsi:type to define an element's data type. */ + /* + Schema reference. Allows use of xsi:nil for NULL values and + xsi:type to define an element's data type. + */ fputs("<mysqldump ", sql_file); fputs("xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"", sql_file); @@ -1389,6 +1412,139 @@ static void print_xml_row(FILE *xml_file, const char *row_name, check_io(xml_file); } + +/* + create_delimiter + Generate a new (null-terminated) string that does not exist in query + and is therefore suitable for use as a query delimiter. Store this + delimiter in delimiter_buff . + + This is quite simple in that it doesn't even try to parse statements as an + interpreter would. It merely returns a string that is not in the query, which + is much more than adequate for constructing a delimiter. + + RETURN + ptr to the delimiter on Success + NULL on Failure +*/ +static char *create_delimiter(char *query, char *delimiter_buff, + int delimiter_max_size) +{ + int proposed_length; + char *presence; + + delimiter_buff[0]= ';'; /* start with one semicolon, and */ + + for (proposed_length= 2; proposed_length < delimiter_max_size; + delimiter_max_size++) { + + delimiter_buff[proposed_length-1]= ';'; /* add semicolons, until */ + delimiter_buff[proposed_length]= '\0'; + + presence = strstr(query, delimiter_buff); + if (presence == NULL) { /* the proposed delimiter is not in the query. */ + return delimiter_buff; + } + + } + return NULL; /* but if we run out of space, return nothing at all. */ +} + + +/* + dump_events_for_db + -- retrieves list of events for a given db, and prints out + the CREATE EVENT statement into the output (the dump). + + RETURN + 0 Success + 1 Error +*/ +static uint dump_events_for_db(char *db) +{ + char query_buff[QUERY_LENGTH]; + char db_name_buff[NAME_LEN*2+3], name_buff[NAME_LEN*2+3]; + char *event_name; + char delimiter[QUERY_LENGTH], *delimit_test; + FILE *sql_file= md_result_file; + MYSQL_RES *event_res, *event_list_res; + MYSQL_ROW row, event_list_row; + DBUG_ENTER("dump_events_for_db"); + DBUG_PRINT("enter", ("db: '%s'", db)); + + mysql_real_escape_string(mysql, db_name_buff, db, strlen(db)); + + /* nice comments */ + if (opt_comments) + fprintf(sql_file, "\n--\n-- Dumping events for database '%s'\n--\n", db); + + /* + not using "mysql_query_with_error_report" because we may have not + enough privileges to lock mysql.events. + */ + if (lock_tables) + mysql_query(mysql, "LOCK TABLES mysql.event READ"); + + if (mysql_query_with_error_report(mysql, &event_list_res, "show events")) + { + safe_exit(EX_MYSQLERR); + DBUG_RETURN(0); + } + + strcpy(delimiter, ";"); + if (mysql_num_rows(event_list_res) > 0) + { + fprintf(sql_file, "/*!50106 SET @save_time_zone= @@TIME_ZONE */ ;\n"); + + while ((event_list_row= mysql_fetch_row(event_list_res)) != NULL) + { + event_name= quote_name(event_list_row[1], name_buff, 0); + DBUG_PRINT("info", ("retrieving CREATE EVENT for %s", name_buff)); + my_snprintf(query_buff, sizeof(query_buff), "SHOW CREATE EVENT %s", + event_name); + + if (mysql_query_with_error_report(mysql, &event_res, query_buff)) + DBUG_RETURN(1); + + while ((row= mysql_fetch_row(event_res)) != NULL) + { + /* + if the user has EXECUTE privilege he can see event names, but not the + event body! + */ + if (strlen(row[3]) != 0) + { + if (opt_drop) + fprintf(sql_file, "/*!50106 DROP EVENT IF EXISTS %s */%s\n", + event_name, delimiter); + + delimit_test= create_delimiter(row[3], delimiter, sizeof(delimiter)); + if (delimit_test == NULL) { + fprintf(stderr, "%s: Warning: Can't dump event '%s'\n", + event_name, my_progname); + DBUG_RETURN(1); + } + + fprintf(sql_file, "DELIMITER %s\n", delimiter); + fprintf(sql_file, "/*!50106 SET TIME_ZONE= '%s' */ %s\n", + row[2], delimiter); + fprintf(sql_file, "/*!50106 %s */ %s\n", row[3], delimiter); + } + } /* end of event printing */ + } /* end of list of events */ + fprintf(sql_file, "DELIMITER ;\n"); + fprintf(sql_file, "/*!50106 SET TIME_ZONE= @save_time_zone */ ;\n"); + + mysql_free_result(event_res); + } + mysql_free_result(event_list_res); + + if (lock_tables) + VOID(mysql_query_with_error_report(mysql, 0, "UNLOCK TABLES")); + DBUG_RETURN(0); +} + + /* Print hex value for blob data. @@ -1426,7 +1582,7 @@ static void print_blob_as_hex(FILE *output_file, const char *str, ulong len) static uint dump_routines_for_db(char *db) { - char query_buff[512]; + char query_buff[QUERY_LENGTH]; const char *routine_type[]= {"FUNCTION", "PROCEDURE"}; char db_name_buff[NAME_LEN*2+3], name_buff[NAME_LEN*2+3]; char *routine_name; @@ -1467,9 +1623,9 @@ static uint dump_routines_for_db(char *db) while ((routine_list_row= mysql_fetch_row(routine_list_res))) { + routine_name= quote_name(routine_list_row[1], name_buff, 0); DBUG_PRINT("info", ("retrieving CREATE %s for %s", routine_type[i], name_buff)); - routine_name= quote_name(routine_list_row[1], name_buff, 0); my_snprintf(query_buff, sizeof(query_buff), "SHOW CREATE %s %s", routine_type[i], routine_name); @@ -1482,8 +1638,8 @@ static uint dump_routines_for_db(char *db) if the user has EXECUTE privilege he see routine names, but NOT the routine body of other routines that are not the creator of! */ - DBUG_PRINT("info",("length of body for %s row[2] '%s' is %d", - routine_name, row[2], (int) strlen(row[2]))); + DBUG_PRINT("info",("length of body for %s row[2] '%s' is %ld", + routine_name, row[2], (long) strlen(row[2]))); if (strlen(row[2])) { char *query_str= NULL; @@ -1581,8 +1737,8 @@ static uint get_table_structure(char *table, char *db, char *table_type, my_ulonglong num_fields; char *result_table, *opt_quoted_table; const char *insert_option; - char name_buff[NAME_LEN+3],table_buff[NAME_LEN*2+3]; - char table_buff2[NAME_LEN*2+3], query_buff[512]; + char name_buff[NAME_LEN+3],table_buff[NAME_LEN*2+3]; + char table_buff2[NAME_LEN*2+3], query_buff[QUERY_LENGTH]; FILE *sql_file= md_result_file; int len; MYSQL_RES *result; @@ -1786,7 +1942,10 @@ static uint get_table_structure(char *table, char *db, char *table_type, */ if (write_data) { - dynstr_append_checked(&insert_pat, "INSERT "); + 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); @@ -1852,7 +2011,10 @@ static uint get_table_structure(char *table, char *db, char *table_type, if (write_data) { - dynstr_append_checked(&insert_pat, "INSERT "); + 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, result_table); @@ -2069,7 +2231,7 @@ static void dump_triggers_for_table(char *table, { char *result_table; char name_buff[NAME_LEN*4+3], table_buff[NAME_LEN*2+3]; - char query_buff[512]; + char query_buff[QUERY_LENGTH]; uint old_opt_compatible_mode=opt_compatible_mode; FILE *sql_file= md_result_file; MYSQL_RES *result; @@ -2545,7 +2707,7 @@ static void dump_table(char *table, char *db) dynstr_append_checked(&extended_row, "NULL"); else { - if (field->type == FIELD_TYPE_DECIMAL) + if (field->type == MYSQL_TYPE_DECIMAL) { /* add " signs around */ dynstr_append_checked(&extended_row, "'"); @@ -2614,7 +2776,7 @@ static void dump_table(char *table, char *db) else if (my_isalpha(charset_info, *ptr) || (*ptr == '-' && my_isalpha(charset_info, ptr[1]))) fputs("NULL", md_result_file); - else if (field->type == FIELD_TYPE_DECIMAL) + else if (field->type == MYSQL_TYPE_DECIMAL) { /* add " signs around */ fputc('\'', md_result_file); @@ -2748,6 +2910,256 @@ static char *getTableName(int reset) } /* getTableName */ +/* + dump all logfile groups and tablespaces +*/ + +static int dump_all_tablespaces() +{ + return dump_tablespaces(NULL); +} + +static int dump_tablespaces_for_tables(char *db, char **table_names, int tables) +{ + DYNAMIC_STRING where; + int r; + int i; + char name_buff[NAME_LEN*2+3]; + + mysql_real_escape_string(mysql, name_buff, db, strlen(db)); + + init_dynamic_string(&where, " AND TABLESPACE_NAME IN (" + "SELECT DISTINCT TABLESPACE_NAME FROM" + " INFORMATION_SCHEMA.PARTITIONS" + " WHERE" + " TABLE_SCHEMA='", 256, 1024); + dynstr_append(&where, name_buff); + dynstr_append(&where, "' AND TABLE_NAME IN ("); + + for (i=0 ; i<tables ; i++) + { + mysql_real_escape_string(mysql, name_buff, + table_names[i], strlen(table_names[i])); + + dynstr_append(&where, "'"); + dynstr_append(&where, name_buff); + dynstr_append(&where, "',"); + } + dynstr_trunc(&where, 1); + dynstr_append(&where,"))"); + + DBUG_PRINT("info",("Dump TS for Tables where: %s",where.str)); + r= dump_tablespaces(where.str); + dynstr_free(&where); + return r; +} + +static int dump_tablespaces_for_databases(char** databases) +{ + DYNAMIC_STRING where; + int r; + int i; + + init_dynamic_string(&where, " AND TABLESPACE_NAME IN (" + "SELECT DISTINCT TABLESPACE_NAME FROM" + " INFORMATION_SCHEMA.PARTITIONS" + " WHERE" + " TABLE_SCHEMA IN (", 256, 1024); + + for (i=0 ; databases[i]!=NULL ; i++) + { + char db_name_buff[NAME_LEN*2+3]; + mysql_real_escape_string(mysql, db_name_buff, + databases[i], strlen(databases[i])); + dynstr_append(&where, "'"); + dynstr_append(&where, db_name_buff); + dynstr_append(&where, "',"); + } + dynstr_trunc(&where, 1); + dynstr_append(&where,"))"); + + DBUG_PRINT("info",("Dump TS for DBs where: %s",where.str)); + r= dump_tablespaces(where.str); + dynstr_free(&where); + return r; +} + +static int dump_tablespaces(char* ts_where) +{ + MYSQL_ROW row; + MYSQL_RES *tableres; + char buf[FN_REFLEN]; + DYNAMIC_STRING sqlbuf; + int first= 0; + /* + The following are used for parsing the EXTRA field + */ + char extra_format[]= "UNDO_BUFFER_SIZE="; + char *ubs; + char *endsemi; + + init_dynamic_string(&sqlbuf, + "SELECT LOGFILE_GROUP_NAME," + " FILE_NAME," + " TOTAL_EXTENTS," + " INITIAL_SIZE," + " ENGINE," + " EXTRA" + " FROM INFORMATION_SCHEMA.FILES" + " WHERE FILE_TYPE = 'UNDO LOG'" + " AND FILE_NAME IS NOT NULL", + 256, 1024); + if(ts_where) + { + dynstr_append(&sqlbuf, + " AND LOGFILE_GROUP_NAME IN (" + "SELECT DISTINCT LOGFILE_GROUP_NAME" + " FROM INFORMATION_SCHEMA.FILES" + " WHERE FILE_TYPE = 'DATAFILE'" + ); + dynstr_append(&sqlbuf, ts_where); + dynstr_append(&sqlbuf, ")"); + } + dynstr_append(&sqlbuf, + " GROUP BY LOGFILE_GROUP_NAME, FILE_NAME" + ", ENGINE" + " ORDER BY LOGFILE_GROUP_NAME"); + + if (mysql_query(mysql, sqlbuf.str) || + !(tableres = mysql_store_result(mysql))) + { + if (mysql_errno(mysql) == ER_BAD_TABLE_ERROR || + mysql_errno(mysql) == ER_BAD_DB_ERROR || + mysql_errno(mysql) == ER_UNKNOWN_TABLE) + { + fprintf(md_result_file, + "\n--\n-- Not dumping tablespaces as no INFORMATION_SCHEMA.FILES" + " table on this server\n--\n"); + check_io(md_result_file); + return 0; + } + + my_printf_error(0, "Error: Couldn't dump tablespaces %s", + MYF(0), mysql_error(mysql)); + return 1; + } + + buf[0]= 0; + while ((row= mysql_fetch_row(tableres))) + { + if (strcmp(buf, row[0]) != 0) + first= 1; + if (first) + { + if (!opt_xml && opt_comments) + { + fprintf(md_result_file,"\n--\n-- Logfile group: %s\n--\n", row[0]); + check_io(md_result_file); + } + fprintf(md_result_file, "\nCREATE"); + } + else + { + fprintf(md_result_file, "\nALTER"); + } + fprintf(md_result_file, + " LOGFILE GROUP %s\n" + " ADD UNDOFILE '%s'\n", + row[0], + row[1]); + if (first) + { + ubs= strstr(row[5],extra_format); + if(!ubs) + break; + ubs+= strlen(extra_format); + endsemi= strstr(ubs,";"); + if(endsemi) + endsemi[0]= '\0'; + fprintf(md_result_file, + " UNDO_BUFFER_SIZE %s\n", + ubs); + } + fprintf(md_result_file, + " INITIAL_SIZE %s\n" + " ENGINE=%s;\n", + row[3], + row[4]); + check_io(md_result_file); + if (first) + { + first= 0; + strxmov(buf, row[0], NullS); + } + } + dynstr_free(&sqlbuf); + init_dynamic_string(&sqlbuf, + "SELECT DISTINCT TABLESPACE_NAME," + " FILE_NAME," + " LOGFILE_GROUP_NAME," + " EXTENT_SIZE," + " INITIAL_SIZE," + " ENGINE" + " FROM INFORMATION_SCHEMA.FILES" + " WHERE FILE_TYPE = 'DATAFILE'", + 256, 1024); + + if(ts_where) + dynstr_append(&sqlbuf, ts_where); + + dynstr_append(&sqlbuf, " ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME"); + + if (mysql_query_with_error_report(mysql, &tableres, sqlbuf.str)) + return 1; + + buf[0]= 0; + while ((row= mysql_fetch_row(tableres))) + { + if (strcmp(buf, row[0]) != 0) + first= 1; + if (first) + { + if (!opt_xml && opt_comments) + { + fprintf(md_result_file,"\n--\n-- Tablespace: %s\n--\n", row[0]); + check_io(md_result_file); + } + fprintf(md_result_file, "\nCREATE"); + } + else + { + fprintf(md_result_file, "\nALTER"); + } + fprintf(md_result_file, + " TABLESPACE %s\n" + " ADD DATAFILE '%s'\n", + row[0], + row[1]); + if (first) + { + fprintf(md_result_file, + " USE LOGFILE GROUP %s\n" + " EXTENT_SIZE %s\n", + row[2], + row[3]); + } + fprintf(md_result_file, + " INITIAL_SIZE %s\n" + " ENGINE=%s;\n", + row[4], + row[5]); + check_io(md_result_file); + if (first) + { + first= 0; + strxmov(buf, row[0], NullS); + } + } + + dynstr_free(&sqlbuf); + return 0; +} + static int dump_all_databases() { MYSQL_ROW row; @@ -2970,6 +3382,12 @@ static int dump_all_tables_in_db(char *database) dump_triggers_for_table(table, database); } } + if (opt_events && !opt_xml && + mysql_get_server_version(mysql) >= 50106) + { + DBUG_PRINT("info", ("Dumping events for database %s", database)); + dump_events_for_db(database); + } if (opt_routines && !opt_xml && mysql_get_server_version(mysql) >= 50009) { @@ -3105,7 +3523,7 @@ static int dump_selected_tables(char *db, char **table_names, int tables) DBUG_ENTER("dump_selected_tables"); if (init_dumping(db, init_dumping_tables)) - return 1; + DBUG_RETURN(1); init_alloc_root(&root, 8192, 0); if (!(dump_tables= pos= (char**) alloc_root(&root, tables * sizeof(char *)))) @@ -3166,6 +3584,12 @@ static int dump_selected_tables(char *db, char **table_names, int tables) for (pos= dump_tables; pos < end; pos++) get_view_structure(*pos, db); } + if (opt_events && !opt_xml && + mysql_get_server_version(mysql) >= 50106) + { + DBUG_PRINT("info", ("Dumping events for database %s", db)); + dump_events_for_db(db); + } /* obtain dump of routines (procs/functions) */ if (opt_routines && !opt_xml && mysql_get_server_version(mysql) >= 50009) @@ -3814,16 +4238,27 @@ int main(int argc, char **argv) if (opt_single_transaction && do_unlock_tables(mysql)) /* unlock but no commit! */ goto err; + if (opt_alltspcs) + dump_all_tablespaces(); + if (opt_alldbs) + { + if (!opt_alltspcs && !opt_notspcs) + dump_all_tablespaces(); dump_all_databases(); + } else if (argc > 1 && !opt_databases) { /* Only one database and selected table(s) */ + if (!opt_alltspcs && !opt_notspcs) + dump_tablespaces_for_tables(*argv, (argv + 1), (argc -1)); dump_selected_tables(*argv, (argv + 1), (argc - 1)); } else { /* One or more databases, all tables */ + if (!opt_alltspcs && !opt_notspcs) + dump_tablespaces_for_databases(argv); dump_databases(argv); } #ifdef HAVE_SMEM |