MySQL large Database Import and Export script

Source: Internet
Author: User
When using the MySQL database, if you import data frequently. To export the entire database using MySQL Internal commands, it is often because a table fails, resulting in the entire Import and Export job failure. So I want to divide and conquer it. Use PHP to write a script. Each import/export operation has a higher chance of success.
Use:

Usage: PhP dbio. php IM/import/EX/export/TR/truncate DB: // User: password @ host/dat
Abase path [-N]
Im/import is import DB from SQL files which is exists;
EX/export is export dB to SQL files;
TR/truncate is empties all tables in this database; the parm 'path' will be
Ignored. Path: the directory of the SQL files;
[-N] is optional parameter just for create new database when parm IM/import is
Exists.

Personal use is good.

The Code is as follows (dbio. php)

  1. <? PHP
  2. /**
  3. * @ File dbio. php
  4. * Import/export/truncate tables from a database, one file per table
  5. * Import/im tb: // User: passwd @ localhost/database path [-N]
  6. * Export/ex TB: // User: passwd @ localhost/database path
  7. * Truncate/tr tb: // User: passwd @ localhost/database path
  8. *
  9. * Mit license
  10. *
  11. * $ ID $
  12. */
  13. /* VIM: Set tabstop = 4 shiftwidth = 4 expandtab :*/
  14. Define ('schema _ database', 'schema. SQL ');
  15. Function usage ($ order ){
  16. Echo "Usage: PhP $ order IM/import/EX/export/TR/truncate DB: // User: password @ host/database path [-N]/n ";
  17. Echo "im/import/T is import DB from SQL files which is exists;/N ";
  18. Echo "EX/export/T is export dB to SQL files;/N ";
  19. Echo "TR/truncate/T is empties all tables in this database; the parm 'path' will be ignored .";
  20. Echo "Path:/t the directory of the SQL files;/N ";
  21. Echo "[-N] is optional parameter just for create new database when parm IM/import is exists./N ";
  22. Die ();
  23. }
  24. Function checkparams ($ argv, $ argc ){
  25. If ($ argc <3 | $ argc> 5 ){
  26. Foreach ($ argv as $ ELEM ){
  27. If (! Assert ($ ELEM) return false;
  28. }
  29. Return false;
  30. }
  31. Return true;
  32. }
  33. Function actionimport ($ TB, $ user, $ passwd, $ host, $ database, $ path, $ new ){
  34. $ Dbconn = mysql_select_db ($ database );
  35. If (! $ Dbconn ){
  36. If ($ new = '-n '){
  37. Echo "now create database $ database./N ";
  38. Mysql_query ("create database $ Database ");
  39. $ Exec = "mysql-h $ host-U $ user-p $ passwd $ database <$ path/". schema_database;
  40. Passthru ($ Exec );
  41. } Elseif ($ new = ''){
  42. Echo "database do not exist !, You need Param '-n' at last./N ";
  43. Die ();
  44. }
  45. }
  46. $ Files = scandir ($ PATH );
  47. If (! $ Files ){
  48. Echo "Check Params $ path ";
  49. } Else {
  50. $ SQL _file = preg_grep ("/[a-zA-Z0-9] */. SQL $/I", $ files );
  51. Echo "now start importing </N ";
  52. Foreach ($ SQL _file as $ ELEM ){
  53. If ($ ELEM = schema_database) continue;
  54. $ Exec = "mysql-h $ host-U $ user-p $ passwd $ database <$ path/$ ELEM ";
  55. Dopassthru ($ Exec );
  56. }
  57. }
  58. }
  59. Function actiontruncate ($ database ){
  60. Echo "now emptying all tables exporting >>>>/N ";
  61. $ Result = mysql_list_tables ($ database );
  62. $ TBS = array ();
  63. For ($ I = 0; $ I <mysql_num_rows ($ result); $ I ++)
  64. $ TBS [] = mysql_tablename ($ result, $ I );
  65. Mysql_free_result ($ result );
  66. Foreach ($ TBS as $ ELEM ){
  67. $ Exec = "truncate $ ELEM ";
  68. $ Result = mysql_query ($ Exec) or die ($ exec. 'failed ');
  69. }
  70. Echo "truncate success .";
  71. }
  72. Function actionexport ($ TB, $ user, $ passwd, $ host, $ database, $ path, $ new ){
  73. Echo "now start exporting ---/N ";
  74. $ Result = mysql_list_tables ($ database );
  75. $ TBS = array ();
  76. For ($ I = 0; $ I <mysql_num_rows ($ result); $ I ++)
  77. $ TBS [] = mysql_tablename ($ result, $ I );
  78. Mysql_free_result ($ result );
  79. Foreach ($ TBS as $ ELEM ){
  80. $ Exec = "mysqldump-h $ host-U $ user-p $ passwd-Q-T $ database $ ELEM> $ path/$ ELEM. SQL ";
  81. Dopassthru ($ Exec );
  82. }
  83. $ Exec = "mysqldump-h $ host-U $ user-p $ passwd-Q-d $ database> $ path/". schema_database;
  84. Dopassthru ($ Exec );
  85. }
  86. Function dopassthru ($ Exec ){
  87. Echo $ exec;
  88. Passthru ($ exec, $ RET );
  89. If ($ RET! = 0 ){
  90. Echo ": Failed/N ";
  91. } Else {
  92. Echo ": OK/N ";
  93. }
  94. Return true;
  95. }
  96. // Main ()
  97. If (checkparams ($ argv, $ argc )){
  98. $ Action = $ argv [1];
  99. List ($ TB, $ tmp1, $ tmp2, $ user, $ passwd, $ host, $ database) = Split ('[: // @]', $ argv [2]);
  100. $ Path = $ argv [3];
  101. $ New = $ argv [4];
  102. If (! Mysql_connect ($ host, $ user, $ passwd) Die ("cocould not connect to MySQL ");
  103. Switch ($ action ){
  104. Case 'im ':
  105. Case 'import ':
  106. Actionimport ($ TB, $ user, $ passwd, $ host, $ database, $ path, $ new );
  107. Break;
  108. Case 'X ':
  109. Case 'Port ':
  110. Actionexport ($ TB, $ user, $ passwd, $ host, $ database, $ path, $ new );
  111. Break;
  112. Case 'tr ':
  113. Case 'truncate ':
  114. Actiontruncate ($ database );
  115. Break;
  116. Default:
  117. Die ("Action Param '$ action' is error !!! ");
  118. }
  119. } Else {
  120. Usage ($ argv [0]);
  121. }
  122. ?>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.