Mysqldump use of the detailed

MySQL Common commands

MySQL installation directory
Database directory
/var/lib/mysql/
Configuration file
/usr/share/mysql (mysql.server command and configuration file)
Related commands
/usr/bin (Mysqladmin mysqldump and other commands)
Startup scripts
/etc/init.d/mysql (startup script file for MySQL directory)

System Management
Connect to MySQL
Format: mysql-h host address-u user name-P user Password
Example 1: Connect to MySQL on this computer.
[Email protected]:~$ mysql-uroot-pmysql;

Example 2: Connect to MYSQL on a remote host.
[Email protected]:~$ mysql-h 127.0.0.1-uroot-pmysql;

Modify New Password
In Terminal input: Mysql-u user name-p password, enter MySQL.
> Use MySQL;
> Update user Set Password=password (' New password ') where user= ' username ';
> Flush Privileges; #更新权限
> quit; #退出

Add new users
Format: Grant Select on database. * To User name @ Login host identified by ' password '
Example:
Example 1: Add a user test1 password for ABC so that he can log on on any host and have all databases
Permissions to query, insert, modify, delete. First, use the root user to connect to MySQL, and then type the following command:
Mysql>grant select,insert,update,delete On * * to [e-mail protected] identified by ' MySQL ';
Or
Grant all privileges on * * to [email protected] identified by ' MySQL ';
Then refresh the permission settings.
Flush privileges;

Example 2: If you do not want root to have the password operation database "MyDB" in the data table, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by ';

Delete User
[Email protected]:~$ mysql-u user name-p password
Mysql>delete from user where user= ' user name ' and host= ' localhost ';
Mysql>flush privileges;
Delete a user's database
Mysql>drop database dbname;

Database Operations
Show all databases
mysql> show databases; (note: There is a last s)

Create a database
mysql> CREATE DATABASE test;

Connecting to a database
mysql> use test;

View the database currently in use
Mysql> Select Database ();

Table information contained in the current database
Mysql> Show tables; (Note: There is a last s)

Deleting a database
mysql> drop database test;

Table Operations
Note: You should connect to a database by using use < database name > before the operation.
Build table
Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]);
Example:
Mysql> CREATE TABLE MyClass (
> ID int (4) NOT null primary key auto_increment,
> Name char () NOT NULL,
> Sex int (4) NOT null default ' 0 ',
> Degree double (16,2));

Get table structure
Command: DESC table name, or Show columns from table name
Example:
Mysql> describe MyClass
mysql> desc MyClass;
Mysql> show columns from MyClass;

Delete a table
Command: DROP table < table name >
Example: Deleting a table with a table named MyClass
mysql> drop table MyClass;

Inserting data
Command: INSERT into < table name > [(< Field name 1>[,.. < field name n >])] VALUES (value 1) [, (value N)]
Example:
mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);

Querying data in a table
Querying all Rows
Mysql> select * from MyClass;

Querying the first few rows of data
Example: Viewing the first 2 rows of data in a table MyClass
Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;
Or
Mysql> select * from MyClass limit 0, 2;

Delete data from a table
Command: Delete from table name where expression
Example: Deleting a record with number 1 in table MyClass
Mysql> Delete from MyClass where id=1;

modifying data in a table
Command: Update table name SET field = new value,... WHERE condition
mysql> Update MyClass set name= ' Mary ' where id=1;

Add fields to a table
Command: ALTER TABLE name add field type other;
For example: Added a field passtest in table MyClass, type int (4), default value of 0
Mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 '

Change table name
Command: Rename table name to new table name;
For example, change the name of the table MyClass to Youclass
Mysql> Rename table MyClass to Youclass;

Update Field Contents
Command: Update table name set field name = new Content
Update table name set field name = Replace (field name, ' old content ', ' new content ');
For example: Add 4 spaces in front of the article
Update article Set Content=concat (", content);

database Import and Export
To export a database file from a database
Using the "mysqldump" command
First, go to the DOS interface and do the following.
1) Export all databases
Format: mysqldump-u [database user name]-p-a>[save path to backup file]

2) Export data and structure
Format: mysqldump-u [Database user name]-p [database name to be backed up]>[backup file save path]
Example:
Example 1: Export the database mydb to a e:\MySQL\mydb.sql file.
Open start, run, input "cmd" and enter command line mode.
c:\> mysqldump-h localhost-u root-p mydb >e:\mysql\mydb.sql
Then enter the password, wait for an export to succeed, you can check the target file for success.

Example 2: Export mytable from Database mydb to the E:\MySQL\mytable.sql file.
c:\> mysqldump-h localhost-u root-p mydb mytable>e:\mysql\mytable.sql

Example 3: Export the structure of the database mydb to a e:\MySQL\mydb_stru.sql file.
c:\> mysqldump-h localhost-u root-p mydb--add-drop-table >e:\mysql\mydb_stru.sql
Note:-h localhost can be omitted, it is generally used on the virtual host.

3) Export data structure only
Format:
mysqldump-u [Database user name]-p-t [the name of the database to be backed up]>[the save path to the backup file]

4) Export the events in the database
Format: mysqldump-u [database user name]-p-e [Database user name]>[save path to backup file]

5) exporting stored procedures and functions in the database
Format: mysqldump-u [database user name]-p-r [Database user name]>[save path to backup file]

Import the database from an external file
1) Use the "source" command
First go to the MySQL command console, then create the database, and then use the database. Finally, perform the following actions.
Mysql>source [Save path of backup file]

2) Use the "<" symbol
First go to the "MySQL" command console, then create the database, then exit MySQL and enter the DOS interface. Finally, perform the following actions.
Mysql-u Root–p < [save path of backup file]

Reprint: http://www.cnblogs.com/linjiqin/archive/2013/03/01/2939384.html

Mysqldump use of the detailed