I have tried to re-configure the old web site log analyst tools - LiveStats XSP 6.2 for a long, long time. Finally I decided to wipe all of them and re-install again. To be safe, I have to back-up all MySql data (installed by LiveStats) first. Since the server is a Windows 2000, and I could not use web interface like Phpmyadmin. Well, I can copy all *.MYD files. But I found a better way by using the the following commands came with MySQL installation.
First, found the MySQL bin folder. To run any MySQL executable command, you need to locate the installation path. In my case, I could locate it at “E:\program files\Livestats\db\bin”.
Export data from MySQL database:
Then click “Start” in the Windows Server, and “Run” then type “CMD“. In the DOS prompt window, type the export/dump command. If you receive “not recognized as an internal or external command,”, then use “CD MYSQL Installed Path\bin” command to be in the executable command directory.
mysqldump -u username -ppassword database_name > FILE.sql
The words in red color above should be replaced with your own MySQL username, password, and the database name which you need to back-up and export. And the File.sql in green above can be any name as long as you remember and can find it later. If you do not want to dump the file in the bin folder, you can use absolute path before the dump file name.
Remember, the ‘-p’ and ‘password’ do not have any spaces.
Other options in the “mysqldump” command you can find by typing “mysqldump –help”. Like I used “–no-create-info” in my case, since my re-installed application already created all tables for me. So I do not need the dump file include all “CREATE TABEL” SQL Queries.
And I also put my individual table names after the database name since I got errors when I tried to export the whole database. So my final dump command looks like the below.
mysqldump -u username -ppassword database_name table_name1 table_name2 “table long name 1″ > FILE.sql
Import MySQL data
For import, you can use the following command:
mysql -u username -ppassword database_name < FILE.sql
Same rule as export, you should replace with your own username, password and database name.
These export/import commands are very handy if you want to move around your MySQL database. For example, re-install applications (in my case) or change your web site host provider, etc.
Reference WebSite:
0 Responses to “How to export and import MySql database”