How to execute SQL statements to export data from a mysql database

Source: Internet
Author: User
Tags rehash

How to execute SQL statements to export data from a mysql database

In addition to phpmyadmin and a third-party database client, you can also use the MySQL client of the command line to access the MySQL database. Many people discard the command because it is not convenient to knock the command.

But sometimes there is no way, phpmyadmin can not be used, but only the command line mode is used, but more, it feels that the command line is good, in addition to writing SQL is slow.

# Basic commands

Mysql-h hostip-P port-u usernmae-p passwd dbname-

-H ip address of mysql

-P Port

-U User Name

-P Password

Dbname: Enter the database directly without using dbanem.

-A, -- no-auto-rehash does not automatically perform the hash operation again.

This option enables mysql to start faster, but if you want to complete the table and column name, you must issue the rehash command.

# Log on to MySQL

# Accessing the local test Database

Mysql-h127.0.0.1-P3306-uroot-proot test-

# Execute SQL files on the database and import data

# Run the SQL. SQL file, which is generally used for data import.

Mysql-h hostip-P port-u usernmae-p passwd dbname <SQL. SQL

# Execute SQL to export data to a file

# Execute the SQL statement in the SQL. SQL file and export the data to the/tmp/data file.

# Data tab separation without column names

Cat SQL. SQL | mysql-h hostip-P port-u usernmae-p passwd dbname>/tmp/data

You can use tab to separate data in the import excel file.

# Save the MySQL interaction text to a file

# Save the text output from the mysql client to a file

Mysql> tee/tmp/textdata # Set the file to be saved

Logging to file '/tmp/textdata'

Mysql> select * from user;

Cat/tmp/textdata

In this way, you can see the output text in '/tmp/textdata' and export data in this way.

Xcopy command back up mysql database

First, create a batch file of db_back.bat. Enter the following command in Notepad

Net stop mysql

Xcopy E: \ AppServ \ MySQL \ data \ lastnonsp3 \ *. * D: \ db_backup \ % date :~ 0, 10% \/y

Net start mysql

Save it as db_back.bat. The bat file indicates that the mysql service is stopped first, and then the xcopy command is used to copy the entire folder where the source database is located to D: \ db_backup, and the file is named after the current day.

Run the bat command in cmd. "Run"-> cmd, enter cd c: \ to locate the directory where the bat file is located

Related Article

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.