MySQL database Execute SQL Export data method

Access to the MySQL database in addition to the use of phpMyAdmin, Third-party database clients, can also use the command line of the MySQL client, because the command is not convenient, so many people to abandon AH.

But sometimes there is no way, phpmyadmin can not use, can only use the command line mode, but more, feel the command line is also good, in addition to write SQL is slow.

# # Basic Command

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

-H MySQL IP

-P Port

-U user Name

-p password

dbname database name directly into the database, do not use the Dbanem

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

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

# # Login MySQL

# access the local test database

Mysql-h127.0.0.1-p3306-uroot-proot test-a

# # Execute SQL files on database, import data

# Execute Sql.sql file, generally do import data use

Mysql-h hostip-p port-u usernmae-p passwd dbname < Sql.sql

# # Execute SQL Export data to file

# executes the SQL statements in the Sql.sql file and then exports the data to the/tmp/data file

# Data Tab delimited, no column name

Cat Sql.sql | Mysql-h hostip-p port-u usernmae-p passwd dbname >/tmp/data

Import Excel to use tab to separate the data.

# # Save MySQL Interactive text to file

# Save the text of the MySQL client output to a file

Mysql> Tee/tmp/textdata # Set the file to save

Logging to file '/tmp/textdata '

Mysql> select * from user;

Cat/tmp/textdata

This allows you to see just the output of the text in '/tmp/textdata ', and you can also export data in this way.

xcopy command back up MySQL database

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

net stop MySQL

xcopy E:appservmysqldatalastnonsp3*.* d:db_backup%date:~0,10%/y

net start MySQL

Save As Db_back.bat. The bat file means to stop the MySQL service first, and then use the Xcopy command to copy the entire folder of the source database to D:db_backup, and name the file on today's date.

Let's test the bat to see if it works under CMD. "Run"->cmd, enter CD C: Navigate to the directory where the bat file is located