MySQL Common management Commands

1. Connect Server Login

>mysql-h 192.168.0.11-u Root-p

2. Modify User Password

>mysqladmin-u root-p 654321 Password 123456

3. User Rights Management

1) The grant command is used to add new users and control their permissions.

Grant Select,insert,update,delete on * * to [[email protected] '%][email protected] '%[/email] ' identified by ' ABC ';

: DDL, DML permissions

ALTER: Modifies an existing data table (for example, add/Remove Columns) and index.

Create: Create a new database or data table.

Delete

: Deletes the record for the table.

drop: Deletes a data table or database.

Index

: Establish or delete the indexes.

INSERT: Adds a table record.

SELECT: Displays/searches the table's records.

UPDATE: Modifies a record that already exists in the table.  

: Special Permissions

all: Allow to do anything (as root).

usage: Allow login only – nothing else is allowed."

: Global Administrative permissions

File: Read and write files on the MySQL server.

PROCESS: Displays or kills service threads belonging to other users.

RELOAD: Overloads the Access Control table, refreshes the log, and so on.

SHUTDOWN: Turn off the MySQL service.

4. Modify table name Rename command format: >rename table name to the new table name;

5. Display Object list show: >show databases; >show tables;

6, get the data table structure >DESC table name or >show columns from table name;

7. Backup Database mysqldump command is used to back up the database.

The mysqldump command executes under the DOS [Url=file://\\mysql\\bin]\\mysql\\bin[/url] directory.
1) Export the entire database (the export file is present in the Mysql\bin directory by default)
>mysqldump-u user name-p database name > exported file name
>mysqldump-u user_name-p123456 database_name > Outfile_name.sql
2) Export a table
>mysqldump-u user name-P database name Table name > exported file name
>MYSQLDUMP-U USER_NAME-P database_name table_name > OUTFILE_NAME.SQL
3) Export a database structure
>mysqldump-u user_name-p-d–add-drop-table database_name > Outfile_name.sql
-D No data –add-drop-table add a drop table before each CREATE statement
4) Export with language parameters
>mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name.sql