Shulou(Shulou.com)05/31 Report--

This article mainly explains "mysql statement command summary under linux". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian slowly and deeply to study and learn "mysql statement command summary under linux" together!

1.Linux command to start mysql:

mysqladmin start

/ect/init.d/mysql start (preceded by mysql installation path)

2.Linux command to restart mysql:

mysqladmin restart

The requested URL/ect/init.d/mysql/was not found on this server.

3.Linux command to close mysql:

mysqladmin -u root -p Password shutdown

/ect/init.d/mysql shutdown (preceded by mysql installation path)

4. Connect mysql on this machine:

Enter the directory mysql\bin, and then type the command mysql -uroot -p, enter and prompt for the password.

Exit mysql command: exit(Enter)

5. Change mysql password:

mysqladmin -u username-p old password password new password

or enter mysql command line SET PASSWORD FOR 'username'@'host'= PASSWORD ('password');

GRANT USAGE ON *.* TO 'username'@'host' IDENTIFIED BY 'biscuit';

SET PASSWORD = PASSWORD ('biscuit ');

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

grant all privileges on *.* to username @'%' identified by 'password' with grant option;

flush privileges;

grant select on .* to username @ login host identified by "password"

For example, add a user test password to 123, so that he can log in on any host and have query, insert, modify and delete permissions on all databases. First connect as root, then type the following command:

grant select,insert,update,delete on *.* to " Identified by "123";

7. Skip authorized access to mysql

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

II. Operations related to my aspects

You must first log in to mysql, which is done at the mysql prompt and ends with a semicolon.

1. Display the database list.

show databases;

2. Display the data tables in the library:

use mysql; //open library

show tables;

3. Display the structure of the data table:

describe table name;

4. Library establishment:

create database name;

5. Table:

use library name;

create table name (field setting list);

6. Delete database and table:

drop database name;

drop table name;

7. Clear the records in the table:

delete from table name;

8. Records in the display table:

select * from table name;

9. Modification of coding

If you want to change the entire mysql encoding format:

When starting mysql, mysqld_safe command line is added

--default-character-set=gbk

To change the encoding format of a library: Enter the command at the mysql prompt

alter database db_name default character set gbk;

III. Import and export of data

1. Text data transferred to database

Text data should conform to the format: field data is separated by tabs and null values are used instead. Example:

1 name duty 2006-11-23

load data local infile "file name" into table name;

2. Export databases and tables

mysqldump --opt news > news.sql (Backup all tables in database news to news.sql file, news.sql is a text file, file name is arbitrary.)

mysqldump --opt news author article > author.article.sql (Backup author tables and article tables in database news to author.article.sql file, author.article.sql is a text file with any filename.)

mysqldump --databases db1 db2 > news.sql (Backup databases dbl and db2 to news.sql file, news.sql is a text file, file name is arbitrary.)

mysqldump -h host -u user -p pass --databases dbname > file.dump

That is, import the database dbname with the name user and password pass on host into the file file.dump

mysqldump --all-databases > all-databases.sql (Backup all databases to all-databases.sql file, all-databases.sql is a text file, file name is arbitrary.)

3. Import data

mysql

source news.sql;(executed under mysql command, import table)

MySQLimport's common options:

-d or --delete Delete all information in a datasheet before importing new data into the datasheet

-f or --force MySQLimport forces continued insertion of data regardless of whether errors are encountered

-i or --ignore MySQLimport Skipping or ignoring lines with the same unique keyword, data in imported files will be ignored.

-l or -lock-tables Locks tables before data is inserted, which prevents user queries and updates from being affected when you update the database.

-r or -replace This option has the opposite effect of the-i option; it replaces records in the table that have the same unique key.

--fields-enclosed- by= char Specifies what to enclose data records in a text file, in many cases data is enclosed in double quotes. By default, data is not enclosed in characters.

--fields-terminated- by=char Specifies the delimiter between values of individual data, which is a period in a period delimited file. You can specify separators between data with this option.

The default delimiter is Tab

--lines-terminated- by=str This option specifies a string or character that separates data between lines in a text file. By default MySQLimport takes 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 commonly used options are-v display version (version), -p prompt password (password)

Example: Import a comma delimited file

The format of the rows in the file is as follows:

"1", "ORD89876", "1 Dozen Roses", "19991226"

Our task is to import the data in this file into the table Orders in the database Meet_A_Geek, we use this command:

bin/MySQLimport –prl –fields-enclosed-by=" –fields-terminated-by=, Meet_A_Geek Orders.txt

1. Connect MySQL

Format: mysql -h host address-u user name-p user password

Example 1: Connect to MYSQL on the local machine.

First open the DOS window, and then enter the directory mysql bin, and then type the command mysql -uroot -p, carriage return prompts you to enter the password, if just installed MYSQL, super user root is no password, so enter directly into MYSQL, MYSQL prompt is: mysql>.

Example 2: Connect to MYSQL on a remote host. Suppose the IP of the remote host is 110.110.110.110, the username is root, and the password is abcd123. Type the following command:

mysql -h210.110.110.110 -uroot -pabcd123

(Note:u and root can be used without spaces, and the same is true for others)

Exit MYSQL command: exit (carriage return).

mysql common maintenance commands

1. show global status; lists the various status values of MySQL server running

2. show variables; query MySQL server configuration information statement

3. View slow queries

show variables like '%slow%';

show global status like '%slow%';

Maximum number of connections

show variables like 'max_connections';MySQL Server Max connections

show global status like 'Max_used_connections'; Maximum number of connections the server responds to

5. View table structure

desc Tablename;

describe Tablename;

show columns from Tablename;

show create table Tablename;

Thank you for reading, the above is the "mysql statement command summary under linux" content, after the study of this article, I believe we have a deeper understanding of the mysql statement command summary under linux, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.