How to use mysqldump to create a database backup

    I had talked ablout we can't use mysqldump to backup database in MySQL console , because it is not a command in MySQL console , it is a application like mysql.exe . But how can we use it ?

    We can't use it in mysql> mode , bu we can use it in CMD mode . First , you must switch to the bin folder of installation directory , like the handle in following picture , because mysqldump.exe is not a windows command !

P1

    Then enter "mysqldump -u root -p test > d:\test.sql" to run mysqldump.exe and create a database backup of test , like the following picture . You can replace root and test by another username and database name.

P2

    As the same way , you can run mysql.exe into MySQL console (mysql>) in CMD , just enter "mysql -h 127.0.0.1 -u root -p password" then can get into mysql> mode , like the following picture .

P3

Why can't i use mysqldump to backup database in MySQL console

   If you want to take a backup of MySQL database , and your site is running at WAMP5 , how to do that ? Maybe somebody first to do this work are usually to make a mistakes , they must try to do it use mysqldump command by MySQL console , and then , they not only failed to take a database bckup , but also confuse themselves .

   Howerver , the reason is that mysqldump is a tool , it's whole name is mysqldump.exe , and it with the mysql.exe and mysqld-nt exe are stored in the bin floder under mysql installation directory . MySQL console is a workmode of mysql.exe , you can't use mysqldump.exe in mysql.exe . In MySQL console , some command you can use in the following command .

+--------------------+
mysql> ?

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear command.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new 
               delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
notee     (\t) Don't write into outfile.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with 
               multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

mysql>
mysql>
+--------------------+

But how to use mysqldump.exe ? See the next article ...please !

how to import SQL file to create tables of mysql database

you must create database at first , and use the database , use source command import SQL file at last . This SQL file is used to create tables of magento database .

mysql>
mysql> create database test;
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> use test;
Database changed
mysql>
mysql> source E:\magento\magento_sample_data_for_1.2.0.sql;


Query OK, 0 rows affected (0.00 sec)

Query OK, 458 rows affected (0.05 sec)
Records: 458  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.14 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.13 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> show tables;
+--------------------------------------------+
| Tables_in_test                            |
+--------------------------------------------+
| admin_assert                               |
| admin_role                                 |
| admin_rule                                 |
| admin_user                                 |
| adminnotification_inbox                    |
| api_assert                                 |
| api_role                                   |
| api_rule                                   |
| api_user                                   |
| catalog_category_entity                    |
| catalog_category_entity_datetime           |
| catalog_category_entity_decimal            |
| catalog_category_entity_int                |
| catalog_category_entity_text               |
| catalog_category_entity_varchar            |
| catalog_category_product                   |
| catalog_category_product_index             |
| catalog_compare_item                       |
| catalog_product_bundle_option              |
| catalog_product_bundle_option_value        |
| catalog_product_bundle_selection           |
| catalog_product_enabled_index              |
| catalog_product_entity                     |
| catalog_product_entity_datetime            |
| catalog_product_entity_decimal             |
| catalog_product_entity_gallery             |
| catalog_product_entity_int                 |
| catalog_product_entity_media_gallery       |
| catalog_product_entity_media_gallery_value |
| catalog_product_entity_text                |
| catalog_product_entity_tier_price          |
| catalog_product_entity_varchar             |
| catalog_product_link                       |
| catalog_product_link_attribute             |
| catalog_product_link_attribute_decimal     |
| catalog_product_link_attribute_int         |
| catalog_product_link_attribute_varchar     |
| catalog_product_link_type                  |
| catalog_product_option                     |
| catalog_product_option_price               |
| catalog_product_option_title               |
| catalog_product_option_type_price          |
| catalog_product_option_type_title          |
| catalog_product_option_type_value          |
| catalog_product_super_attribute            |
| catalog_product_super_attribute_label      |
| catalog_product_super_attribute_pricing    |
| catalog_product_super_link                 |
| catalog_product_website                    |
| catalogindex_aggregation                   |
| catalogindex_aggregation_tag               |
| catalogindex_aggregation_to_tag            |
| catalogindex_eav                           |
| catalogindex_minimal_price                 |
| catalogindex_price                         |
| cataloginventory_stock                     |
| cataloginventory_stock_item                |
| catalogrule                                |
| catalogrule_affected_product               |
| catalogrule_product                        |
| catalogrule_product_price                  |
| catalogsearch_fulltext                     |
| catalogsearch_query                        |
| catalogsearch_result                       |
| checkout_agreement                         |
| checkout_agreement_store                   |
| chronopay_api_debug                        |
| cms_block                                  |
| cms_block_store                            |
| cms_page                                   |
| cms_page_store                             |
| core_config_data                           |
| core_email_template                        |
| core_flag                                  |
| core_layout_link                           |
| core_layout_update                         |
| core_resource                              |
| core_session                               |
| core_store                                 |
| core_store_group                           |
| core_translate                             |
| core_url_rewrite                           |
| core_website                               |
| cron_schedule                              |
| customer_address_entity                    |
| customer_address_entity_datetime           |
| customer_address_entity_decimal            |
| customer_address_entity_int                |
| customer_address_entity_text               |
| customer_address_entity_varchar            |
| customer_entity                            |
| customer_entity_datetime                   |
| customer_entity_decimal                    |
| customer_entity_int                        |
| customer_entity_text                       |
| customer_entity_varchar                    |
| customer_group                             |
| cybermut_api_debug                         |
| cybersource_api_debug                      |
| dataflow_batch                             |
| dataflow_batch_export                      |
| dataflow_batch_import                      |
| dataflow_import_data                       |
| dataflow_profile                           |
| dataflow_profile_history                   |
| dataflow_session                           |
| design_change                              |
| directory_country                          |
| directory_country_format                   |
| directory_country_region                   |
| directory_country_region_name              |
| directory_currency_rate                    |
| downloadable_link                          |
| downloadable_link_price                    |
| downloadable_link_purchased                |
| downloadable_link_purchased_item           |
| downloadable_link_title                    |
| downloadable_sample                        |
| downloadable_sample_title                  |
| eav_attribute                              |
| eav_attribute_group                        |
| eav_attribute_option                       |
| eav_attribute_option_value                 |
| eav_attribute_set                          |
| eav_entity                                 |
| eav_entity_attribute                       |
| eav_entity_datetime                        |
| eav_entity_decimal                         |
| eav_entity_int                             |
| eav_entity_store                           |
| eav_entity_text                            |
| eav_entity_type                            |
| eav_entity_varchar                         |
| eway_api_debug                             |
| flo2cash_api_debug                         |
| gift_message                               |
| googlebase_attributes                      |
| googlebase_items                           |
| googlebase_types                           |
| googlecheckout_api_debug                   |
| googleoptimizer_code                       |
| ideal_api_debug                            |
| log_customer                               |
| log_quote                                  |
| log_summary                                |
| log_summary_type                           |
| log_url                                    |
| log_url_info                               |
| log_visitor                                |
| log_visitor_info                           |
| newsletter_problem                         |
| newsletter_queue                           |
| newsletter_queue_link                      |
| newsletter_queue_store_link                |
| newsletter_subscriber                      |
| newsletter_template                        |
| oscommerce_import                          |
| oscommerce_import_type                     |
| oscommerce_orders                          |
| oscommerce_orders_products                 |
| oscommerce_orders_status_history           |
| oscommerce_orders_total                    |
| oscommerce_ref                             |
| paybox_api_debug                           |
| paybox_question_number                     |
| paygate_authorizenet_debug                 |
| paypal_api_debug                           |
| paypaluk_api_debug                         |
| poll                                       |
| poll_answer                                |
| poll_store                                 |
| poll_vote                                  |
| product_alert_price                        |
| product_alert_stock                        |
| protx_api_debug                            |
| rating                                     |
| rating_entity                              |
| rating_option                              |
| rating_option_vote                         |
| rating_option_vote_aggregated              |
| rating_store                               |
| rating_title                               |
| report_event                               |
| report_event_types                         |
| review                                     |
| review_detail                              |
| review_entity                              |
| review_entity_summary                      |
| review_status                              |
| review_store                               |
| sales_flat_order_item                      |
| sales_flat_quote                           |
| sales_flat_quote_address                   |
| sales_flat_quote_address_item              |
| sales_flat_quote_item                      |
| sales_flat_quote_item_option               |
| sales_flat_quote_payment                   |
| sales_flat_quote_shipping_rate             |
| sales_order                                |
| sales_order_datetime                       |
| sales_order_decimal                        |
| sales_order_entity                         |
| sales_order_entity_datetime                |
| sales_order_entity_decimal                 |
| sales_order_entity_int                     |
| sales_order_entity_text                    |
| sales_order_entity_varchar                 |
| sales_order_int                            |
| sales_order_tax                            |
| sales_order_text                           |
| sales_order_varchar                        |
| salesrule                                  |
| salesrule_customer                         |
| sendfriend_log                             |
| shipping_tablerate                         |
| sitemap                                    |
| strikeiron_tax_rate                        |
| tag                                        |
| tag_relation                               |
| tag_summary                                |
| tax_calculation                            |
| tax_calculation_rate                       |
| tax_calculation_rate_title                 |
| tax_calculation_rule                       |
| tax_class                                  |
| weee_discount                              |
| weee_tax                                   |
| wishlist                                   |
| wishlist_item                              |
+--------------------------------------------+
229 rows in set (0.00 sec)

mysql>

how to delete mysql database through shell command

If you want to delete a database from mysql through shell command , to follow by the following steps.

mysql>
mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| phpmyadmin         |
| test               |
| zhiwu              |
+--------------------+
5 rows in set (0.06 sec)

mysql> drop database test;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| phpmyadmin         |
| zhiwu              |
+--------------------+
4 rows in set (0.00 sec)

mysql>