mysql Command encyclopedia under Linux

1.linux commands to start MySQL:

Mysqladmin start

/ect/init.d/mysql start (front of MySQL installation path)

2.linux Restart MySQL command:

Mysqladmin restart

/ect/init.d/mysql Restart (front of MySQL installation path)

3.linux the command to turn off MySQL:

Mysqladmin-u root-p Password shutdown

/ect/init.d/mysql shutdown (front of MySQL installation path)

4. Connect to MySQL on this computer:

Enter the directory Mysql\bin, then type the command mysql-uroot-p, enter the password after entering.

Exit MySQL command: Exit (enter)

5. Modify the MySQL password:

Mysqladmin-u username-p Old password password new password

or go to MySQL command line set PASSWORD for ' username ' @ ' host ' = PASSWORD (' password ');

GRANT USAGE on * * to ' username ' @ ' host ' identified by ' biscuit ';

Modify your own password set PASSWORD = PASSWORD (' biscuit ');

6. Add new users. (Note: Commands in the MySQL environment are followed by a semicolon as a command terminator)

Grant all privileges on * * to username @ '% ' identified by ' password ' with GRANT option;

flush privileges; (Refresh permission settings)

Grant SELECT on database. * To User name @ login host identified by "password"

If you add a user test password of 123, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:

Grant Select,insert,update,delete on * * to ' identified by ' 123;

7. Skip authorization to access MySQL

Mysqld_safe--user=mysql--skip-grant-tables--skip-networking &

II. operations related to MySQL database

You must log in to MySQL first, and the operation is performed at the prompt of MySQL, and each command ends with a semicolon

1. Display the list of databases.

show databases;

2. Display the data table in the library:

Use MySQL; Open Library

Show tables;

3, display the structure of the data table:

describe table name;

4, build the library:

Create database name;

5, build the table:

Use library name;

CREATE TABLE table name (field settings list);

6. Deleting the library and deleting the table:

drop database name;

drop table name;

7. Empty the records in the table:

Delete from table name;

8. Display the records in the table:

SELECT * from table name;

9, the revision of the Code

If you want to change the entire MySQL encoding format:

When you start MySQL, the Mysqld_safe command line joins

--default-character-set=gbk

If you want to change the encoding format for a library: Enter a command after the MySQL prompt

ALTER DATABASE db_name default character set GBK;

Iii. Import and export of data

1. The text data goes to the database

The format that text data should conform to: The field data is separated by the TAB key, and the null value is used instead. Cases:

1 Name Duty 2006-11-23

Data incoming command load data local infile "file name" into table name;

2. exporting databases and tables

Mysqldump--opt News > News.sql (back up all the tables in database news to the News.sql file, News.sql is a text file, and the file name is taken.) )

Mysqldump--opt News Author Article >author.article.sql ( Back up the author and article tables in the database news to the Author.article.sql file, Author.article.sql is a text file, and the file name is either taken. )

Mysqldump--databases db1 DB2 > News.sql (Database dbl and DB2 back to News.sql file, News.sql is a text file, filename is taken.) )

Mysqldump-h host-u user-p pass--databases dbname > File.dump

is to import the name of the host on the user, password pass database dbname into the file file.dump

Mysqldump--all-databases > All-databases.sql (Back up all databases to the All-databases.sql file, All-databases.sql is a text file, and the file name is taken.) )

3. Import data

MySQL < all-databases.sql (Import Database)

Mysql-u root-p Fukai–force < Dmc010003_db.myisam.sql (forced import)

Mysql>source News.sql; (executes under MySQL command, can import tables)

Common options for Mysqlimport are:

-D or--delete all information in the data table before the new data is imported into the datasheet

-F or--force Mysqlimport will force the data to be inserted regardless of whether or not an error is encountered

-I or--ignore mysqlimport skips or ignores rows that have the same unique keyword, and the data in the import file is ignored.

-L or-lock-tables locks the table before it is inserted, which prevents the user's queries and updates from being affected when you update the database.

-R or-replace This option is the opposite of the-I option, and this option will replace the record with the same unique keyword in the representative.

--fields-enclosed-by= char Specifies, in many cases, the data in a text file is enclosed in double quotation marks. By default, the data is not surround by the word.

--FIELDS-TERMINATED-BY=CHAR Specifies the delimiter between the values of each data, in a period-delimited file, where the delimiter is a period. You can use this option to specify a delimiter between data.

The default delimiter is Jump Geff (Tab)

--LINES-TERMINATED-BY=STR This option specifies the delimited string or character of data between rows and lines in a text file. By default, Mysqlimport with newline as the line delimiter.

You can choose to replace a single character with a string:

A new line or a carriage return.

Mysqlimport command common options are also-v Display version (version),-P Prompt for password (password)

Example: Importing a comma-delimited file

The record format for the rows in the file is this:

"1", "ORD89876", "1 dozen Roses", "19991226"

Our task is to import the data from this file into the table orders in the database Meet_a_geek, and we use this command:

Bin/mysqlimport–prl–fields-enclosed-by= "–fields-terminated-by=, Meet_a_geek Orders.txt

One, connect MySQL

Format: mysql-h host address-u user name-P user Password

1. Example 1: Connect to MySQL on this machine.

First open the DOS window, and then enter the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so directly enter into MySQL, The prompt for MySQL is: mysql>.

2. Example 2: Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: You and root can be used without spaces, others are the same)

3. Exit MySQL command: Exit (enter).

MySQL Common maintenance commands

1. Show global status; list MySQL server running various status values

2, show variables; querying MySQL server configuration information statements

3. View Slow Query

Show variables like '%slow% ';

Show global status like '%slow% ';

4, the maximum number of connections

Show variables like ' max_connections '; Maximum number of MySQL server connections

Show global status like ' Max_used_connections '; Maximum number of connections for server response

5. View table structure

Desc Tablename;

Describe Tablename;

Show columns from Tablename;

Show CREATE TABLE Tablename;

This article is from the "lake and Laughter" blog, please make sure to keep this source http://hashlinux.blog.51cto.com/9647696/1791000

mysql Command encyclopedia under Linux