Linux about the MySQL statement command encyclopedia

Source: Internet
Author: User
Tags db2 create database import database mysql command line

1.linux under the command to start MySQL:

The code is as follows Copy Code

Mysqladmin start

/ect/init.d/mysql Start (Previous installation path for MySQL)

2.linux Restart MySQL command:

The code is as follows Copy Code

Mysqladmin restart

/ect/init.d/mysql Restart (previous installation path for MySQL)

3.linux to close MySQL command:

The code is as follows Copy Code

Mysqladmin-u root-p Password shutdown

/ect/init.d/mysql Shutdown (Previous installation path for MySQL)

4. Connect MySQL on this computer:

Enter the directory Mysqlbin, then type the command mysql-uroot-p and prompt for the password when you enter.

Exit MySQL command: Exit (enter)

5. Modify MySQL Password:

  

The code is as follows Copy Code

Mysqladmin-u username-p Old password password new password

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

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

To modify your own password

The code is as follows Copy Code
SET PASSWORD = PASSWORD (' biscuit ');

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

The code is as follows Copy Code

Grant all privileges in *.* 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 connect the root user to MySQL, and then type the following command:

 

The code is as follows Copy Code
Grant Select,insert,update,delete on *.* to "identified by" 123;

7. Skip authorized access to MySQL

The code is as follows Copy Code

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

Second, the MySQL database aspects of the operation

You must first log in to MySQL, the operation is done under the MySQL prompt, and each command ends with a semicolon

1. Display the list of databases.

The code is as follows Copy Code

show databases;

2, display the data table in the library:

The code is as follows Copy Code

Use MySQL; Open Library

Show tables;

3, display the structure of the data table:

 

The code is as follows Copy Code
describe table name;

4, build the library:

Create database library name;

5, the establishment of the table:

The code is as follows Copy Code

Use library name;

CREATE TABLE table name (field set list);

6, delete the database and delete the table:

drop Database library name;

 

The code is as follows Copy Code
The drop table table name;

7. Empty the record of the table:

 

The code is as follows Copy Code
Delete from table name;

8, display the records in the table:

 

The code is as follows Copy Code
SELECT * from table name;

9, the Code modification

If you want to change the entire MySQL encoding format:

When you start MySQL, the Mysqld_safe command line joins

  

The code is as follows Copy Code
--default-character-set=gbk

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

 

The code is as follows Copy Code
ALTER DATABASE db_name default character set GBK;

Iii. Import and export of data

1, the text data to go to the database

The format that text data should conform to: field data is separated by a TAB key and Null values are used instead. Cases:

1 Name Duty 2006-11-23

Data incoming command load infile "filename" into table name;

2. Export Database and table

  

The code is as follows Copy Code
Mysqldump--opt News > News.sql

(Back up all tables in the database news to the News.sql file, News.sql is a text file, and the filename is optional.) )

  

The code is as follows Copy Code
Mysqldump--opt News Author Article >author.article.sql (

Back up the author table and article table in the database news to the Author.article.sql file, Author.article.sql is a text file, and the file name is optional. )

  

The code is as follows Copy Code
Mysqldump--databases db1 DB2 > News.sql

(Back up the database Dbl and DB2 to the News.sql file, News.sql is a text file, and the file name is optional.) )

 

The code is as follows Copy Code
Mysqldump-h host-u user-p pass--databases dbname > File.dump

is to import the database dbname in the host with the name user, password pass to the file File.dump

 

The code is as follows Copy Code
Mysqldump--all-databases > All-databases.sql (Backup all databases to All-databases.sql file, All-databases.sql is a text file, file name is optional.) )

3. Import data

The code is as follows Copy Code

MySQL < all-databases.sql (Import Database)

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

Mysql>source news.sql (execute under mysql command, import table)

Introduction to Mysqlimport's common options:

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

-F or--force Mysqlimport will force continued insertion of data regardless of whether an error has been encountered

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

-L or-lock-tables Lock the table before the data 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; This option will replace the record for the same unique key in the representative.

--fields-enclosed-by= char Specifies what is included in the record of the data in the text file, in many cases the data is enclosed in double quotes. By default, data is not Fu Guachi by character.

--FIELDS-TERMINATED-BY=CHAR Specifies the separator between the values of each data, and the separator is a period in a file separated by a period. You can specify the separator between data by using this option.

The default delimiter is Hop Geff (TAB)

--lines-terminated-by=str This option to specify a delimited string or character of data between rows and lines in a text file. By default, Mysqlimport is newline as a row separator.

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

A new line or a carriage return.

The Mysqlimport command commonly used options are the-V display (version),-p prompts for a password (password)

Example: Import a comma-delimited file

The record format for rows in a file is this:

The code is as follows Copy Code

"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, we use this command:

The code is as follows Copy Code

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

First, connect MySQL

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

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

First in the Open DOS window, and then into 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 direct return can enter into MySQL, The MySQL prompt is: mysql>.

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

The code is as follows Copy Code

Mysql-h110.110.110.110-uroot-pabcd123

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

3, exit MySQL command: Exit (enter).

MySQL Common maintenance command

The code is as follows Copy Code

1, show global status; list MySQL server running various status values

2, show variables; query MySQL server configuration information statement

3, view the slow query

The code is as follows Copy Code

Show variables like '%slow% ';

Show global status like '%slow% ';

4, the maximum number of connections

The code is as follows Copy Code

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

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

5. View table structure

  code is as follows copy code

Desc TableName;

Describe TableName;

Show columns from TableName;

Show CREATE TABLE tablename;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.