Operating MySQL under Linux

For MySQL database operations, you must first log in to MySQL.

After you turn on the MySQL service, you can log in using the MySQL command. General use of mysql-uroot-p can be. If the database is not native, you need to add parameters, common parameters are as follows:
1,-h, specifying an IP address, localhost by default
2,-u, specify the user name.
3,-p, specify the password, the password can be followed by-p input mysql-uroot-p123456. You can also mysql-uroot-p enter the password when prompted to enter, so that the input password does not echo.

The actions are 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 author tables and article tables in database news to author.article.sql files, 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;

Operating MySQL under Linux