使用 C++ 导入和导出MySQL数据库的另一种方法

分享于 

19分钟阅读

数据库

  繁體
  • Download ImportExportMySQLDBWithoutUsingBatchFile.rar - 268KB
  • Download ImportExportMySQLDBWithoutUsingBatchFile.zip - 289.3 KB
  • 背景

    这是MySQL服务器导入和导出数据库的C编程教程,它涵盖了MySQL的导入和导出,不使用MySQL提供的capi。

    MySQL是领先的开源数据库管理系统,它是一个多用户,多线程的数据库管理系统,MySQL在1994年由瑞典公司MySQL AB开发,Sun Microsystems于2008年收购了MySQL AB。Sun于2010年被Oracle收购。今天,Oracle公司是MySQL数据库的所有者,MySQL是用C/C 开发的。

    注意:对于从ImportDatabase函数调用的函数,请参考应用程序代码中的类以及本文

    
    
    bool CDatabaseManipulation::ImportDatabase(/*[IN]*/const TCHAR *ptchHost,
    
    
     /*[IN]*/const TCHAR *ptchUserID, 
    
    
     /*[IN]*/const TCHAR *ptchPassword, 
    
    
     /*[IN]*/const TCHAR *ptchDatabaseNameToImport, 
    
    
     /*[IN]*/const TCHAR *ptchImportDatabaseFileWithPath)
    
    
    /* ===================================================================================================
    
    
    NAME OF FUNCTION: CDatabaseManipulation::ImportDatabase
    
    
    CREDIT: Satish Jagtap
    
    
    PURPOSE: This function is used to import MySQL database.
    
    
    Special Note: Here we can achieve our task of importing database using following 2 ways:
    
    
     1) Using CreateProcess API
    
    
     2) Using ShellExecute API
    
    
     User of this class can modify this function according to his/her need.
    
    
     Means user can remove other section 
    
    
    PARAMETERS: [IN] 1) const TCHAR *ptchHost: Server host name
    
    
     [IN] 2) const TCHAR *ptchUserID: User of MySQL server.
    
    
     [IN] 3) const TCHAR *ptchPassword: Password of MySQL server.
    
    
     [IN] 4) const TCHAR *ptchDatabaseNameToImport: Database to import.
    
    
     [IN] 5) constTCHAR *ptchImportDatabaseFileWithPath: Database file to import.
    
    
    RETURN VALUE: Returns true on success.
    
    
    CALLS TO: 1) SearchForFilePath
    
    
    CALLED FROM: None
    
    
    Added date: 31 March, 2015
    
    
    Updated date:
    
    
    =====================================================================================================*/
    
    
    {
    
    
     bool bImportDBSuccess = false;
    
    
     TCHAR strProgramFilePath[MAX_PATH] = { 0 };
    
    
    
     //Retreive program file path
    
    
     if(!SHGetSpecialFolderPath(0, strProgramFilePath, CSIDL_PROGRAM_FILES, FALSE)) 
    
    
     {
    
    
     LPTSTR lpstrError = new TCHAR[1024];
    
    
    
     _stprintf_s(lpstrError, 1024, _T("Unable to retrieve program file path (%d)n"), 
    
    
     GetLastError());
    
    
     m_objLogger.log(lpstrError);
    
    
    
     delete[] lpstrError;
    
    
    
     bImportDBSuccess = false;
    
    
     }
    
    
     else
    
    
     {
    
    
     TCHAR *strReturnSQLFilePath = new TCHAR[MAX_PATH];
    
    
     TCHAR *strImportCommand = new TCHAR[MAX_PATH];
    
    
    
     _tcscpy_s(strReturnSQLFilePath, MAX_PATH, _T(""));
    
    
     SearchForFilePath(strProgramFilePath, _T("mysql.exe"), strReturnSQLFilePath);
    
    
    
     _tcscat_s(strReturnSQLFilePath, MAX_PATH, _T("mysql.exe"));
    
    
    
     //Populate command to import database
    
    
     _tcscpy_s(strImportCommand, MAX_PATH, _T("-u"));
    
    
     _tcscat_s(strImportCommand, MAX_PATH, ptchUserID);
    
    
     _tcscat_s(strImportCommand, MAX_PATH, _T(" -p"));
    
    
     _tcscat_s(strImportCommand, MAX_PATH, ptchPassword);
    
    
     _tcscat_s(strImportCommand, MAX_PATH, _T(" -h"));
    
    
     _tcscat_s(strImportCommand, MAX_PATH, ptchHost);
    
    
     _tcscat_s(strImportCommand, MAX_PATH, _T(" -e"));
    
    
     _tcscat_s(strImportCommand, MAX_PATH, _T(""drop database if exists"));
    
    
     _tcscat_s(strImportCommand, MAX_PATH, ptchDatabaseNameToImport);
    
    
     _tcscat_s(strImportCommand, MAX_PATH, _T(";"));
    
    
     _tcscat_s(strImportCommand, MAX_PATH, _T(" create database"));
    
    
     _tcscat_s(strImportCommand, MAX_PATH, ptchDatabaseNameToImport);
    
    
     _tcscat_s(strImportCommand, MAX_PATH, _T(";"));
    
    
     _tcscat_s(strImportCommand, MAX_PATH, _T("use"));
    
    
     _tcscat_s(strImportCommand, MAX_PATH, ptchDatabaseNameToImport);
    
    
     _tcscat_s(strImportCommand, MAX_PATH, _T(";."));
    
    
     _tcscat_s(strImportCommand, MAX_PATH, ptchImportDatabaseFileWithPath);
    
    
     _tcscat_s(strImportCommand, MAX_PATH, _T("""));
    
    
    
     /***************************************************************************************
    
    
     Here we can achieve our task of importing database using following 2 ways:
    
    
     1) Using CreateProcess API
    
    
     2) Using ShellExecute API
    
    
     Note: I have just commented"Achieve import database process using CreateProcess API"
    
    
     section
    
    
     ***************************************************************************************/
    
    
     #pragma region"Achieve import database process using CreateProcess API"
    
    
     //LPTSTR strImportDatabaseCommandLine = new TCHAR[1024];
    
    
    
     //_stprintf_s(strImportDatabaseCommandLine, 1024, _T("%s %s"), strReturnSQLFilePath,
    
    
     // strImportCommand);
    
    
    
     //STARTUPINFO si = { 0 };//alternative way to zero array
    
    
     //si.cb = sizeof(si);
    
    
     //PROCESS_INFORMATION pi = { 0 };
    
    
    
     //if (!CreateProcess(NULL,
    
    
     // strImportDatabaseCommandLine,
    
    
     // NULL,
    
    
     // NULL,
    
    
     // FALSE,
    
    
     // 0,
    
    
     // NULL,
    
    
     // NULL,
    
    
     // &si,
    
    
     // &pi)
    
    
     // )
    
    
     //{
    
    
     // LPTSTR lpstrError = new TCHAR[1024];
    
    
    
     // _stprintf_s(lpstrError, 1024, _T("CreateProcess failed (%d)n"), GetLastError());
    
    
     // m_objLogger.log(lpstrError);
    
    
    
     // delete[] lpstrError;
    
    
    
     // bImportDBSuccess = false;
    
    
     //}
    
    
     //else
    
    
     //{
    
    
     // bImportDBSuccess = true;
    
    
     //}
    
    
    
     //WaitForSingleObject(pi.hProcess, INFINITE);
    
    
     //CloseHandle(pi.hProcess);
    
    
     //CloseHandle(pi.hThread);
    
    
    
     //delete [] strImportDatabaseCommandLine;
    
    
     //strImportDatabaseCommandLine = NULL;
    
    
     #pragma endregion
    
    
    
    
    
     #pragma region"Achieve import database process using ShellExecute API"
    
    
     UINT nReturnErrorCode = (UINT)ShellExecute(NULL, 
    
    
     _T("open"),
    
    
     strReturnSQLFilePath, //mysql.exe file path
    
    
     strImportCommand, //command to import database
    
    
     //into MySQL server
    
    
     NULL, 
    
    
     SW_HIDE); //hides command window
    
    
    
     if(nReturnErrorCode <= 32)
    
    
     {
    
    
     LPTSTR lpstrError = new TCHAR[1024];
    
    
    
     _stprintf_s(lpstrError, 1024, _T("ShellExecute failed (%d)n"), GetLastError());
    
    
     m_objLogger.log(lpstrError);
    
    
    
     delete[] lpstrError;
    
    
    
     bImportDBSuccess = false;
    
    
     }
    
    
     else
    
    
     {
    
    
     bImportDBSuccess = true;
    
    
     }
    
    
     #pragma endregion
    
    
    
    
    
     delete [] strReturnSQLFilePath;
    
    
     strReturnSQLFilePath = NULL;
    
    
     delete [] strImportCommand;
    
    
     strImportCommand = NULL;
    
    
     }
    
    
    
     return bImportDBSuccess;
    
    
    }
    
    
    
    
    

    从当前运行的MySqlServer中检索数据库列表:要从MySQL服务器导出数据库,我们需要知道当前正在运行MySQL server的数据库,本节从MySQL服务器检索数据库列表,在这里,我创建了此函数的两个版本,一个用于Unicode字符集,另一个用于MBCS。

    
    
    #pragma region Retrieve databases list from currently running MySqlServer
    
    
    
    
    
    #ifdef UNICODE
    
    
    
    
    
    void CDatabaseManipulation::RetrieveDatabasesListInMySqlServer(vector<wchar_t*> &vecMySqlDatabasesList)
    
    
    /* ===================================================================================================
    
    
    NAME OF FUNCTION: CDatabaseManipulation::RetrieveDatabasesListInMySqlServer
    
    
    CREDIT: Satish Jagtap
    
    
    PURPOSE: This function is used to receives databases list in currently running MySql 
    
    
     server instance
    
    
    PARAMETERS: 1) vector<wchar_t*> &vecMySqlDatabasesList - Receives databases list in currently 
    
    
     running MySql server instance
    
    
    RETURN VALUE: None
    
    
    CALLS TO: None
    
    
    CALLED FROM: None
    
    
    Added date: 27 March, 2015
    
    
    Updated date:
    
    
    ====================================================================================================*/
    
    
    {
    
    
     char strTemp[MAX_PATH] = { 0 };
    
    
     size_t nTempLen = 0;
    
    
    
     wcstombs_s(&nTempLen, strTemp, _T("%"), wcslen(_T("%")) + 1);
    
    
     MYSQL_RES *myqlResult = mysql_list_dbs(mysqlConnection, strTemp /* fetch all */);
    
    
    
     if (!myqlResult) 
    
    
     {
    
    
     LPTSTR lptstrError = new TCHAR[1024];
    
    
    
     _stprintf_s(lptstrError, 1024, _T("Couldn't get db list: %s"), GetError());
    
    
     m_objLogger.log(lptstrError);
    
    
    
     delete [] lptstrError;
    
    
     }
    
    
     else 
    
    
     {
    
    
     MYSQL_ROW mysqlRow;
    
    
    
     while(mysqlRow = mysql_fetch_row(myqlResult)) 
    
    
     {
    
    
     size_t nLen = 0;
    
    
     int nLenInfoSchema = strlen(mysqlRow[0]) + 1;
    
    
     wchar_t wcstrRow[MAX_PATH] = { 0 };
    
    
    
     mbstowcs_s(&nLen, wcstrRow, mysqlRow[0], nLenInfoSchema);
    
    
    
     if((_tcscmp(wcstrRow, _T("information_schema"))) && 
    
    
     (_tcscmp(wcstrRow, _T("performance_schema"))) && 
    
    
     (_tcscmp(wcstrRow, _T("mysql"))))
    
    
     {
    
    
     size_t nTempLen = 0;
    
    
     wchar_t strRow[MAX_PATH] = { 0 };
    
    
    
     strMySQLResultRow = new wchar_t[MAX_PATH];
    
    
    
     mbstowcs_s(&nTempLen, strRow, mysqlRow[0], strlen(mysqlRow[0]) + 1);
    
    
     _tcscpy_s(strMySQLResultRow, _tcslen(strRow) + 1, strRow);
    
    
    
     vecMySqlDatabasesList.push_back(strMySQLResultRow);
    
    
     }
    
    
     }
    
    
     }
    
    
    }
    
    
    #else
    
    
    void CDatabaseManipulation::RetrieveDatabasesListInMySqlServer(vector<CHAR*> &vecMySqlDatabasesList)
    
    
    /* ===================================================================================================
    
    
    NAME OF FUNCTION: CDatabaseManipulation::RetrieveDatabasesListInMySqlServer
    
    
    CREDIT: Satish Jagtap
    
    
    PURPOSE: This function is used to receives databases list in currently running MySql 
    
    
     server instance
    
    
    PARAMETERS: 1) vector<CHAR*> &vecMySqlDatabasesList - Receives databases list in currently 
    
    
     running MySql server instance
    
    
    RETURN VALUE: None
    
    
    CALLS TO: None
    
    
    CALLED FROM: None
    
    
    Added date: 21 March, 2015
    
    
    Updated date:
    
    
    =====================================================================================================*/
    
    
    {
    
    
     MYSQL_RES *myqlResult = mysql_list_dbs(mysqlConnection, _T("%") /* fetch all */);
    
    
    
     if (!myqlResult) 
    
    
     {
    
    
     LPTSTR lptstrError = new TCHAR[1024];
    
    
    
     _stprintf_s(lptstrError, 1024, _T("Couldn't get db list: %s"), GetError());
    
    
     m_objLogger.log(lptstrError);
    
    
    
     delete [] lptstrError;
    
    
     }
    
    
     else 
    
    
     {
    
    
     MYSQL_ROW mysqlRow;
    
    
    
     while(mysqlRow = mysql_fetch_row(myqlResult)) 
    
    
     {
    
    
     if((_tcscmp(mysqlRow[0], _T("information_schema"))) && (_tcscmp(mysqlRow[0], _T("performance_schema"))) && (_tcscmp(mysqlRow[0], _T("mysql"))))
    
    
     {
    
    
     vecMySqlDatabasesList.push_back(mysqlRow[0]);
    
    
     }
    
    
     }
    
    
     }
    
    
    }
    
    
    #endif
    
    
    #pragma endregion
    
    
    
    
    

    将数据库导出到MySQL服务器:导出数据库用户需要提供以下信息:

  • 用户名
  • 密码
  • 要导出的数据库名称
  • 数据库导出文件(.sql扩展名)及它位置
  • 这里,通过以下2种方式实现导出数据库的任务:1)使用CreateProcess API2)使用ShellExecute API

    用户可以根据需求使用功能。

    注意:对于从ExportDatabase函数调用的函数,请参考应用程序代码中的类以及本文。

    
    
    bool CDatabaseManipulation::ExportDatabase(/*[IN]*/const TCHAR *ptchUserID, 
    
    
     /*[IN]*/const TCHAR *ptchPassword, 
    
    
     /*[IN]*/const TCHAR *ptchDatabaseNameToExport, 
    
    
     /*[IN]*/const TCHAR *ptchExportDatabaseFileWithPath)
    
    
    /* ===================================================================================================
    
    
    NAME OF FUNCTION: CDatabaseManipulation::ExportDatabase
    
    
    CREDIT: Satish Jagtap
    
    
    PURPOSE: This function is used to export MySQL database.
    
    
    Special Note: Here we can achieve our task of exporting database using following 2 ways:
    
    
     1) Using CreateProcess API
    
    
     2) Using ShellExecute API
    
    
     User of this class can modify this function according to his/her need.
    
    
     Means user can remove other section 
    
    
    PARAMETERS: [IN] 1) const TCHAR *ptchUserID: User of MySQL server.
    
    
     [IN] 2) const TCHAR *ptchPassword: Password of MySQL server.
    
    
     [IN] 3) const TCHAR *ptchDatabaseNameToExport: Database to export.
    
    
     [IN] 4) constTCHAR *ptchExportDatabaseFileWithPath: Database file to export.
    
    
    RETURN VALUE: Returns true on success.
    
    
    CALLS TO: 1) SearchForFilePath
    
    
    CALLED FROM: None
    
    
    Added date: 31 March, 2015
    
    
    Updated date:
    
    
    ====================================================================================================*/
    
    
    {
    
    
     bool bExportDBSuccess = false;
    
    
     TCHAR strProgramFilePath[MAX_PATH] = { 0 };
    
    
    
     //Retreive program file path
    
    
     if(!SHGetSpecialFolderPath(0, strProgramFilePath, CSIDL_PROGRAM_FILES, FALSE)) 
    
    
     {
    
    
     LPTSTR lpstrError = new TCHAR[1024];
    
    
    
     _stprintf_s(lpstrError, 1024, _T("CreateProcess failed (%d)n"), GetLastError());
    
    
     m_objLogger.log(lpstrError);
    
    
    
     delete[] lpstrError;
    
    
    
     bExportDBSuccess = false;
    
    
     }
    
    
     else
    
    
     {
    
    
     TCHAR *strReturnSQLFilePath = new TCHAR[MAX_PATH];
    
    
     TCHAR *strExportCommand = new TCHAR[MAX_PATH];
    
    
    
     _tcscpy_s(strReturnSQLFilePath, MAX_PATH, _T(""));
    
    
     SearchForFilePath(strProgramFilePath, _T("mysqldump.exe"), strReturnSQLFilePath);
    
    
     _tcscat_s(strReturnSQLFilePath, MAX_PATH, _T("mysqldump.exe"));
    
    
    
     //Populate command to export database
    
    
     _tcscpy_s(strExportCommand, MAX_PATH, _T(" --user="));
    
    
     _tcscat_s(strExportCommand, MAX_PATH, ptchUserID);
    
    
     _tcscat_s(strExportCommand, MAX_PATH, _T(" --password="));
    
    
     _tcscat_s(strExportCommand, MAX_PATH, ptchPassword);
    
    
     _tcscat_s(strExportCommand, MAX_PATH, _T(""));
    
    
     _tcscat_s(strExportCommand, MAX_PATH, ptchDatabaseNameToExport);
    
    
     _tcscat_s(strExportCommand, MAX_PATH, _T(" -r"));
    
    
     _tcscat_s(strExportCommand, MAX_PATH, ptchExportDatabaseFileWithPath);
    
    
    
     /***************************************************************************************
    
    
     Here we can achieve our task of exporting database using following 2 ways:
    
    
     1) Using CreateProcess API
    
    
     2) Using ShellExecute API
    
    
     Note: I have just commented"Achieve export database process using CreateProcess API"
    
    
     section
    
    
     ***************************************************************************************/
    
    
     #pragma region"Achieve export database process using CreateProcess API"
    
    
     //LPTSTR strExportDatabaseCommandLine = new TCHAR[1024];
    
    
    
     //_stprintf_s(strExportDatabaseCommandLine, 1024, _T("%s %s"), strReturnSQLFilePath,
    
    
     // strExportCommand);
    
    
    
     //STARTUPINFO si = { 0 };//alternative way to zero array
    
    
     //si.cb = sizeof(si);
    
    
     //PROCESS_INFORMATION pi = { 0 };
    
    
    
     //if (!CreateProcess(NULL,
    
    
     // strExportDatabaseCommandLine,
    
    
     // NULL,
    
    
     // NULL,
    
    
     // FALSE,
    
    
     // 0,
    
    
     // NULL,
    
    
     // NULL,
    
    
     // &si,
    
    
     // &pi)
    
    
     // )
    
    
     //{
    
    
     // LPTSTR lpstrError = new TCHAR[1024];
    
    
    
     // _stprintf_s(lpstrError, 1024, _T("CreateProcess failed (%d)n"), GetLastError());
    
    
     // m_objLogger.log(lpstrError);
    
    
    
     // delete[] lpstrError;
    
    
    
     // bExportDBSuccess = false;
    
    
     //}
    
    
     //else
    
    
     //{
    
    
     // bExportDBSuccess = true;
    
    
     //}
    
    
    
     //WaitForSingleObject(pi.hProcess, INFINITE);
    
    
     //CloseHandle(pi.hProcess);
    
    
     //CloseHandle(pi.hThread);
    
    
    
     //delete [] strExportDatabaseCommandLine;
    
    
     //strExportDatabaseCommandLine = NULL;
    
    
     #pragma endregion
    
    
    
    
    
     #pragma region"Achieve export database process using ShellExecute API"
    
    
     UINT nReturnErrorCode = (UINT)ShellExecute(NULL, 
    
    
     _T("open"), 
    
    
     strReturnSQLFilePath, //mysqldump.exe file path
    
    
     strExportCommand, //command to import
    
    
     //database into MySQL server
    
    
     _T(""), 
    
    
     SW_HIDE); //hides command window
    
    
    
     if(nReturnErrorCode <= 32)
    
    
     {
    
    
     LPTSTR lpstrError = new TCHAR[1024];
    
    
    
     _stprintf_s(lpstrError, 1024, _T("ShellExecute failed (%d)n"), GetLastError());
    
    
     m_objLogger.log(lpstrError);
    
    
    
     delete[] lpstrError;
    
    
    
     bExportDBSuccess = false;
    
    
     }
    
    
     else
    
    
     {
    
    
     bExportDBSuccess = true;
    
    
     }
    
    
     #pragma endregion
    
    
    
    
    
     delete [] strReturnSQLFilePath;
    
    
     strReturnSQLFilePath = NULL;
    
    
     delete [] strExportCommand;
    
    
     strExportCommand = NULL;
    
    
     }
    
    
    
     return bExportDBSuccess;
    
    
    }
    
    
    
    
    

    数据  IMP  EXP  导出  导入  
    相关文章