电梯直达

1楼 [收藏(0)] [报告]

发表于 2011-12-19 13:55 |只看该作者 |倒序浏览

有时候我们不具有删除数据库的权限，只是被分配一个我们可以改动的数据库给我们。这个时候就不能使用以下方法：

下面我用C写的两个小程序可以简单地做同样的事情而不用删除数据库。而用脚本实现的方法（by VIVEK GITE）也将附于文后。

源码在Ubuntu Linux ubuntu 2.6.24-22-generic #1 SMP Mon Nov 24 19:35:06 UTC 2008 x86_64 GNU/Linux下测试通过。

使用方法如下：

/* C source file: mysql_list_tables.c
* Written by linuxqc@gmail.com */
#include </usr/include/mysql/mysql.h>
#include <stdio.h>
#include <stdlib.h>
int main(int argc, char *argv[])
{
MYSQL *mysql;
MYSQL_RES *result;
MYSQL_ROW row;
FILE *fp;
if (argc != 6) {
printf("mysql_list_tables, a small tool to list all your tables in a mysql database.\n");
printf("Usage:\n");
printf("mysql_list_tables <host> <user> <password> <database> <table_list.txt>\n");
printf("table_list.txt ia a text file in which all the names of the tables of the specified database will be stored one name per line.\n");
printf("This list can be used by the tool mysql_drop_tables.\n");
exit(EXIT_FAILURE);
}
fp = fopen(argv[5],"w");
if (fp == NULL) {
printf("can't open output stream.\n");
exit(EXIT_FAILURE);
}
if ((mysql=mysql_init(NULL)) == NULL) {
printf("mysql_init() error!\n");
}
if (mysql_real_connect(mysql, argv[1],argv[2], argv[3], argv[4],3306,NULL,0)== NULL) {
printf("mysql_connect() error!\n");
}
if ((result = mysql_list_tables(mysql, NULL)) == NULL) {
printf("mysql_list_tables() error!\n");
}
while ((row = mysql_fetch_row(result)) != NULL) {
printf("%s\n", row[0]);
fprintf(fp, "%s\n", row[0]);
}
fclose(fp);
mysql_free_result(result);
mysql_close(mysql);
exit(EXIT_SUCCESS);
}

/* C source file: mysql_drop_tables.c
* Written by linuxqc@gmail.com */
#define _GNU_SOURCE
#include <stdio.h>
#include </usr/include/mysql/mysql.h>
#include <stdlib.h>
int main(int argc, char *argv[])
{
MYSQL *mysql;
MYSQL_RES *result;
MYSQL_ROW row;
FILE * fp;
char * line = NULL;
size_t len = 0;
ssize_t read;
char command[] = "DROP TABLE ";
char query[100] = "\0";
if (argc != 6) {
printf("mysql_drop_tables, a small tool to drop all your tables.\n");
printf("Usage:\n");
printf("mysql_drop_tables <host> <user> <password> <database> <table_list.txt>\n");
printf("table_list.txt ia a text file in which all the names of the tables which you want to drop have been stored one name per line\n");
printf("You may use the tool mysql_list_tables to create the table_list.txt\n");
exit(EXIT_FAILURE);
}
if ((mysql=mysql_init(NULL)) == NULL) {
printf("mysql_init() error!\n");
}
if (mysql_real_connect(mysql, argv[1],argv[2], argv[3], argv[4],3306,NULL,0)== NULL) {
printf("mysql_connect() error!\n");
}
if ((result = mysql_list_tables(mysql, NULL)) == NULL) {
printf("mysql_list_tables() error!\n");
}
fp = fopen(argv[5],"r");
if (fp == NULL) {
printf("can't read input stream.\n");
exit(EXIT_FAILURE);
}
while ((read = getline(&line, &len, fp)) != -1) {
strcpy(query, command);
strcat(query, line);
if (mysql_query(mysql, query) != 0) {
printf("mysql_query() error!\n");
}
}
if (line) {
free(line);
}
fclose(fp);
mysql_free_result(result);
mysql_close(mysql);
exit(EXIT_SUCCESS);
}

附一：VIVEK GITE写的脚本：

#!/bin/bash
MUSER="$1"
MPASS="$2"
MDB="$3"
# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
if [ $# -ne 3 ]
then
echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"
echo "Drops all tables from a MySQL"
exit 1
fi
TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}'| $GREP -v '^Tables' )
for t in $TABLES
do
echo "Deleting $t table from $MDB database..."
$MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"
done

Drop All MySQL Tables without Deleting A Database [复制链接]