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

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