Linux mysql Operations Command

Reprint: http://www.cnblogs.com/xiaochaohuashengmi/archive/2011/10/18/2216279.html

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 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 Root=password ("root");

6. Add new users. (Note: Commands in the MySQL environment are followed by a semicolon as a command terminator)
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;

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;

Gbk:create database test2 DEFAULT CHARACTER SET GBK COLLATE gbk_chinese_ci;
Utf8:create DATABASE ' test2 ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;
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;

TRUNCATE 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;

10. Renaming a table

ALTER TABLE t1 rename T2;

11. View the efficiency of SQL statements

Explain < table_name >

For example: Explain select * from T3 where id=3952602;

12. Loading data into a database table (for example, d:/mysql.txt) in text mode

mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" into TABLE MYTABLE;

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>source News.sql; (executes under MySQL command, can import tables)

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).

Second, change the password

Format: Mysqladmin-u username-P Old password password new password

1, Example 1: Add a password to root ab12. First enter directory Mysqlbin under DOS, and then type the following command:

Mysqladmin-uroot-password AB12

Note: Because Root does not have a password at the beginning, the-p old password can be omitted.

2, Example 2: Then change the root password to djg345.

MYSQLADMIN-UROOT-PAB12 Password djg345

Third, add new users. (Note: Unlike the above, the following is because it is a command in a MySQL environment, so it is followed by a semicolon as a command terminator)

Format: Grant Select on database. * To User name @ login host identified by \ "Password \"

Example 1, add a user test1 password for ABC, 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 [email protected]\ "%\" identified by \ "Abc\";

But example 1 increases the user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log on your MySQL database and to your data can do whatever, workaround see Example 2.

Example 2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.

Grant Select,insert,update,
Delete on mydb.* to [e-mail protected] identified by \ "Abc\";

If you do not want to test2 have a password, you can call another command to erase the password.

Grant Select,insert,update,delete on MyDB
. * to [e-mail protected] identified by \ "\";

Start: net start mySql;
Enter: Mysql-u root-p/mysql-h localhost-u root-p databaseName;
List databases: show databases;
Select database: Use DatabaseName;
List table: Show tables;
Show table Column properties: Show columns from TableName;
Build database: Source FileName.txt;
Match characters: You can use wildcard characters _ to represent any one character, and% to represent any string;
Add a field: ALTER TABLE tabelname add column FieldName datetype;
Add multiple fields: ALTER TABLE tabelname add column fieldName1 datetype,add columns fieldName2 datetype;
Multi-line command input: Note that you cannot break the word, and when inserting or changing data, you cannot expand the string of the field into multiple lines, or the hard return will be stored in the data;
Add an Administrator account: Grant all on * * to [e-mail protected] identified by "password";
Fill in the end of each statement with a semicolon '; ', or add ' \g ' to it;
Query time: Select Now ();
Query Current User: Select User ();
Querying database version: Select version ();
Query the database currently in use: select databases ();

1. Delete the students data sheet from the Student_course database:
Rm-f student_course/students.*

2. Back up the database: (Backup DATABASE Test)
Mysqldump-u root-p Test>c:\test.txt
Backup table: (Back up the MyTable table under test database)
Mysqldump-u root-p Test Mytable>c:\test.txt
Import the backup data into the database: (Back to test database)
Mysql-u root-p Test

3. Create temporary table: (Create temp table Zengchao)
Create temporary table Zengchao (name varchar (10));

4. Create a table to determine if the table exists first
CREATE table if not exists students (...);

5. Copying the structure of tables from existing tables
CREATE TABLE table2 SELECT * FROM table1 where 1<>1;

6. Copy the table
CREATE TABLE table2 SELECT * FROM table1;

7. Renaming the table
ALTER TABLE table1 Rename as table2;

8. Modify the type of the column
ALTER TABLE table1 Modify ID int unsigned;//The type of the modified column ID is int unsigned
ALTER TABLE table1 change ID sid int unsigned;//the name of the modified column ID is SID, and the property is modified to int unsigned

9. Create an index
ALTER TABLE table1 ADD index ind_id (ID);
CREATE INDEX ind_id on table1 (ID);
Create unique index ind_id on table1 (ID);//establishing a unique indexing

10. Delete Index
Drop index idx_id on table1;
ALTER TABLE table1 DROP INDEX ind_id;

11, union character or multiple columns (Connect column ID to ":" and column name and "=")
Select Concat (ID, ': ', name, ' = ') from students;

12, limit (choose 10 to 20) < The first Recordset is numbered 0>
SELECT * FROM students order by ID limit 9, 10;

13, MySQL does not support the function
Transactions, views, foreign key and referential integrity, stored procedures, and triggers

14, MySQL will use the index operation symbol
<,<=,>=,>,=,between,in, like with no% or _

15. Disadvantages of using indexes
1) Slow down or change the speed of data;
2) occupy disk space;
3) Increase the burden of the query optimizer;
When the query optimizer generates an execution plan, it takes into account the index, too many indexes will increase the workload for the query optimizer, resulting in the inability to select the optimal query scheme;

16. Analyze Index efficiency
Method: Add explain before the general SQL statement;
Meaning of the analysis result:
1) Table: list name;
2) Type: Types of connections, (ALL/RANGE/REF). Where ref is the most desirable;
3) Possible_keys: The index name that the query can use;
4) Key: The actual use of the index;
5) Key_len: The length of the part used in the index (bytes);
6) Ref: Displays the column name or "Const" (does not understand what meaning);
7) Rows: Shows the number of rows that MySQL has to scan before finding the correct results;
8) Extra:mysql's recommendations;

17. Use shorter fixed-length columns
1) Use a shorter data type whenever possible;
2) Use fixed length data type whenever possible;
a) using char instead of varchar, fixed-length data processing is faster than the longer;
b) for frequently modified tables, the disk is prone to fragmentation, thus affecting the overall performance of the database;
c) In the event of a data table crash, a table with fixed-length data rows is easier to reconstruct. With fixed-length data rows, each record's starting position is a multiple of a fixed record length and can be easily detected, but using variable-length data rows is not necessarily the same;
d) for MyISAM types of data tables, although conversion to fixed-length data columns can improve performance, but occupy a large space;

18. Use NOT NULL and enum
Try to define the column as NOT NULL, which makes the data come out faster, requires less space, and when queried, MySQL does not need to check for a special case, or null value, to optimize the query;
If a column contains only a limited number of specific values, such as gender, whether it is valid or the year of entry, etc., it should be considered in this case to convert it to the value of the enum column, MySQL processing faster, because all enum values in the system are represented by the identification value;

19. Using Optimize table
For frequently modified tables, it is easy to fragment, making it necessary to read more disk blocks when querying the database and to reduce query performance. A disk fragmentation problem exists with variable-length tables, which are more prominent for BLOB data types because their size varies greatly. You can defragment the data by using optimize table to ensure that database performance is not degraded and that the tables that are affected by fragmentation are optimized. Optimize table can be used for data tables of type MyISAM and BDB. In fact, any defragmentation method is to use mysqldump to dump the data table, and then use the dump file and re-create the data table;

20. Using procedure analyse ()
You can use procedure analyse () to display the best type of recommendation, which is simple, with the addition of Procedure analyse () after the SELECT statement, for example:
SELECT * From students procedure analyse ();
SELECT * From students procedure analyse (16,256);
The second statement requires procedure analyse () not to suggest that there are more than 16 values, or an enum type with more than 256 bytes, and that if there is no limit, the output may be very long;

21. Using Query Caching
1) How the query cache works:
When a SELECT statement is executed for the first time, the server remembers the query's text content and query results, stores it in the cache, and returns the result directly from the cache the next time it encounters the statement, and when the data table is updated, any cached queries for that data table become invalid and discarded.
2) Configure the cache parameters:
Variables: Query_cache _type, querying the operating mode of the cache. There are 3 modes, 0: Not cached, 1: Cache queries, unless you start with select Sql_no_cache; 2: Only those queries that start with select Sql_cache are cached as needed; Query_cache_size: Sets the maximum result set size of the query cache, Larger than this value will not be cached.

22. Adjust the hardware
1) Install more memory on the machine;
2) Increase the speed of the hard drive to reduce I/O wait time;
Seek time is the main factor to determine the performance, literally moving the head is the slowest, once the head positioning, from the track to read quickly;
3) Re-allocate disk activity on different physical hard disk devices;
If possible, you should store the busiest databases on different physical devices, which are different from the different partitions that use the same physical device, because they will compete for the same physical resources (heads).

Delete MySQL Database

1. Create a database

mysql> CREATE DATABASE Drop_database;
Query OK, 1 row Affected (0.00 sec)

2. Delete a database that has been determined to exist

mysql> drop Database drop_database;
Query OK,0 rows Affected (0.00 sec)

3. Delete a database with an indeterminate existence

mysql> drop Database drop_database;
ERROR 1008 (HY000): Can ' t drop database ' drop_database '; Database doesn ' t exist

An error occurred and the ' drop_database ' database could not be deleted, and the database does not exist.

Mysql> drop database if exists drop_database;
Query OK, 0 rows affected, 1 Warning (0.00 sec)//generates a warning stating that this database does not exist

mysql> CREATE DATABASE Drop_database;
Query OK, 1 row Affected (0.00 sec)

Mysql> drop database if exists drop_database;//if exists to determine if the databases exist, are not present, and do not produce errors
Query OK, 0 rows Affected (0.00 sec)

Linux mysql Operations Command