Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ Linux Command-line MySQL summary     - Unity Greeter Badges: the lost session icon back to the login screen Ubuntu (Linux)

- The difference between IPython and Python (Linux)

- Django Signals from practice to source code analysis (Programming)

- RHEL5 / 6 Installation Notes (Linux)

- OpenSUSE installation on CentOS6 (GUI) (Linux)

- How to Create a file can not be changed under Linux (Linux)

- Monitoring network traffic with Iptraf in Linux environment (Linux)

- Oracle 11g statistics collection - collection of multi-column statistics (Database)

- Guide: Trickle restrict application bandwidth usage (Linux)

- Tomcat itself through simple movement separation (Server)

- Supervisor Installation and Configuration (Server)

- CentOS use wget (Linux)

- Source code is installed MySQL 5.6.28 (Database)

- CentOS 7 hard disk expansion toss record (Linux)

- Use XtraBackup be physical standby database MySQL (Database)

- Ubuntu configuration SVN and http mode access (Server)

- Three methods easy data encryption on Linux (Linux)

- Normal start Lazarus 1.0.8 under Ubuntu (Linux)

- C # DateTime structure common method (Programming)

- DRBD rapid installation and deployment (Server)

  Linux Command-line MySQL summary
  Add Date : 2018-11-21      
  (Note: later MySQL environment command with a semicolon as a command terminator)

1.Linux next start MySQL command:

mysqladmin start
 /ect/init.d/mysql start (in front of the mysql installation path)

2.Linux next restart MySQL command:

mysqladmin restart
 /ect/init.d/mysql restart (in front of the mysql installation path)

3.Linux closed MySQL command:

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

4. Connect MySQL on the machine:

Go to the directory mysql \ bin, then type the command mysql -uroot -p, prompted for a password and then press Enter (under Linux, if mysql server system is directly mysql -u root -p Enter on it, do not enter the mysql \ bin table of Contents);
 Exit mysql command: exit (Enter)

5. Modify the MySQL password:

mysqladmin -u username -p password old password new password
 Or enter the mysql command line SET PASSWORD FOR root = PASSWORD ( "root");

6. Add a new user.

grant select on database. * to username @ log on the host identified by "password"
Such as adding a user test password to 123, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First to root user connected to mysql, and then type the following command:
. Grant select, insert, update, delete on * * to "Identified by" 123 ";

MySQL database-related aspects of the operation

We must first log on to MySQL, and related operations are carried out in the MySQL prompt, and end each command with a semicolon

1, display a list of databases.

show databases;

2, the display library data sheets:

use mysql; // Open Library
show tables;

3, shows the structure of the data table:

describe table name;

4, building a database:

create database database name;

GBK: create database test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

5, built table:
use the library name;
create table table name (field set list);

6, delete the database and delete tables:

drop database database name;
drop table table name;

7, will be recorded in the table empty:

delete from table name;

truncate table table name;

8, show records in the table:
select * from table name;

9, the modified encoding

If you want to change the mysql encoding formats:
 Mysql start time, mysqld_safe command line to join
-default-character-set = gbk

If you want to change the encoding format of a library: After mysql prompt, enter the command
alter database db_name default character set gbk;

10. Rename table

alter table t1 rename t2;

11. Review of the efficiency sql statement

explain < table_name>

For example: explain select * from t3 where id = 3952602;

12. The text mode loading data into a database table (for example, D: /mysql.txt)


Importing and exporting data

1, the text data to the database

Text data should be consistent with the format: use the tab key between fields separated data, null values are used instead. example:
1 name duty 2006-11-23
Data incoming command load data local infile "filename" into table table name;

2, export the database and tables

mysqldump -opt news> news.sql (news database all the tables to news.sql backup file news.sql is a text file, the file name either take.)
mysqldump -opt news author article> author.article.sql (author of the database tables and news article to author.article.sql table backup file author.article.sql is a text file, the file name either take.)
mysqldump -databases db1 db2> news.sql (dbl and db2 backup database file to news.sql, news.sql is a text file, the file name either take.)
mysqldump -h host -u user -p pass -databases dbname> file.dump
In the name of the user host is on, the password database dbname pass into the file file.dump
mysqldump -all-databases> all-databases.sql (all database backup to all-databases.sql file, all-databases.sql is a text file, the file name either take.)

3, import data

mysql < all-databases.sql (into the database)
mysql> source news.sql; (in the mysql command is executed, you can import the table)

First, connect MySQL
Format: mysql -h host address -u user name -p User password

1, Example 1: Connect to the machine on MYSQL.

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

2, Example 2: connect to remote host on MYSQL. Suppose the remote host IP is:, user name is root, password is abcd123. Type the following command:

mysql -h110.110.110.110 -uroot -pabcd123

(Note: u and the root can not have spaces, the other is the same)

3, exit MYSQL command: exit (Enter).

Second, modify the password

Format: mysqladmin -u username -p password old password new password

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

mysqladmin -uroot -password ab12

Note: Since the beginning of root without a password, so the -p an old password can be omitted.

2, Example 2: then the root password to djg345.

mysqladmin -uroot -pab12 password djg345

Third, add new users.

(Note: different from above, the following environment because MySQL command, so the back with a semicolon as a command terminator)

Format: grant select on database * to username @ log on the host identified by \ "password \."

Example 1, add a password for the user test1 abc, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First to root user connected to MySQL, then type the following command:

grant select, insert, update,
 . Delete on * * to test1 @ \ "% \" Identified by \ "abc \";

Example 1 but increasing number of users is very dangerous, you want to know as someone test1 password, then he can log on the internet on any computer on your MySQL database and the data you can do whatever the solution see Example 2.

Example 2, add a user test2 password is abc, so that he can only log on localhost, and can query the database mydb, insert, modify, delete operations (localhost means the local host, that is, the MySQL database resides that host) so that users know that the use test2 password, he can not directly access the database from the internet, can be accessed through the web page on the MySQL host.

grant select, insert, update,
 . Delete on mydb * to test2 @ localhost identified by \ "abc \";

If you do not want test2 password, you can resort to a command to eliminate the password.

grant select, insert, update, delete on mydb
 . * To test2 @ localhost identified by \ "\";

Start: net start mySql;

Enter: mysql -u root -p / mysql -h localhost -u root -p databaseName;

List Database: show databases;

Select the database: use databaseName;

Table listing: show tables;

Display attributes of table columns: show columns from tableName;

Establish a database: source fileName.txt;

Matching characters: You can use wildcard characters _ represent any one character,% represents any string;

Add a field: alter table tabelName add column fieldName dateType;

Increase the number of fields: alter table tabelName add column fieldName1 dateType, add columns fieldName2 dateType;

Multi-line command input: be careful not to disconnect the word; when you insert or change the data can not be expanded to the multi-string field line, otherwise hard return will be stored into the data;

Added an administrator account: grant all on * * to user @ localhost identified by "password";

After each statement have been entered for a filling at the end of the semicolon ';', or filling '\ g' to be;

Query time: select now ();

The current user query: select user ();

Query the database version: select version ();

Query the database currently in use: select database ();

1. Remove student_course database of students Data Sheet:

rm -f student_course / students. *

2, the backup database test database backup :()

mysqldump -u root -p test> c: \ test.txt
Backup table :( mytable table under the database backup test)
mysqldump -u root -p test mytable> c: \ test.txt
Will import the backup data back into the database :( test database)
mysql -u root -p test

3, create a temporary table :( establishment of a temporary table zengchao)

create temporary table zengchao (name varchar (10));

4, create a table is to determine whether there is table

create table if not exists students (......);

5, there has been copied from the table structure of the table

create table table2 select * from table1 where 1 < > 1;

6, copy table

create table table2 select * from table1;

7, rename the table

alter table table1 rename as table2;

8, modify the column type

alter table table1 modify id int unsigned; // modify the column id of type int unsigned
 alter table table1 change id sid int unsigned; // modify the column name is id sid, and modify the properties 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); // establish a unique index

10, delete the index

drop index idx_id on table1;
 alter table table1 drop index ind_id;

11, the joint character or multiple columns (columns with id ":" and the column name and the "=" Connection)

select concat (id, ':', name, '=') from students;

12, limit (select 10-20) < a record number of the first set is 0>

select * from students order by id limit 9,10;

13, MySQL does not support features

Transactions, views, foreign keys and referential integrity, stored procedures and triggers

14, MySQL will use the index operation symbol

< , < =,> =,>, =, Between, in,%, or _ without beginning like

15, using an index of disadvantage
1) additions and deletions to slow down the speed of data;
2) take up disk space;
3) increase the burden on the query optimizer;
 When the query optimizer generates the execution plan, it will consider the index, the index will give a lot of query optimizer increase the workload, making it impossible to select the optimal query plan;

16. Analysis of the efficiency index

Methods: Before the general SQL statement with explain;
 Analyze the meaning of the results:
1) table: table name;
2) type: the type of connection, (ALL / Range / Ref). Where ref is the best;
3) possible_keys: query can use the index name;
4) key: the index of actual use;
5) key_len: index being used length of the portion (in bytes);
6) ref: display the column name or "const" (do not understand what it means);
7) rows: MySQL show that the number of rows to be found right before the scan;
8) extra: MySQL recommendations;

17, using a shorter fixed-length column

1) Whenever possible, use a shorter data type;
2) Whenever possible, use fixed-length data types;
a) instead of using char varchar, fixed-length data processing faster than the variable-length;
b) for the frequent changes of meter, easy to form a disk of debris, thus affecting the overall performance of the database;
c) in case of crash data tables, use fixed-length data table rows easier to reconstruct. The use of fixed-length data row, the start position of each record is a fixed multiple of the length of the record, can easily be detected, but the use of variable-length data line might not be;
d) For MyISAM type tables, although the data is converted into a fixed-length columns can improve performance, but also the space occupied by the Great;

18, use not null and enum

Try to define a column as not null, it will give out data faster, required less space, but also in the query, MySQL does not need to check the exception to this is if there is a null value, thereby optimizing the query;
 If a specific value contains only a limited number of, such as gender, whether valid or school year and so on, in which case you should consider converting it to a column enum values, MySQL faster processing, because all enum values in the system to identify the values are represented;

19, using the optimize table

For frequently modified table, prone to fragmentation, so when querying the database must read more disk blocks reduce query performance. Table with variable length are present disk fragmentation problem, the problem is more prominent on the blob data type, because of its size vary greatly. By using the optimize table to defragment the database to ensure that the performance does not drop, optimize those data table by debris impacts. optimize table can be used for MyISAM and BDB type tables. Virtually any defragmentation method is to use mysqldump to dump the data table, then use the file dump after both the new data table;

20, using the procedure analyse ()

You can use the procedure analyse () to display the best types of recommendations very simple to use, after the select statement with procedure analyse () on it; for example:
select * from students procedure analyse ();
 select * from students procedure analyse (16,256);
The second statement requires procedure analyse () do not recommend having more than 16 values, or contain more than 256 byte enum type, without limitation, the output may be very long;

21, use the query cache

1) The query cache works:
 The first time you execute a select statement, text and query the server to remember the result of the query, stored in the cache, the next encounter this statement that returns results directly from the cache; when updating the data table, the data any cached lookup table have become invalid and will be discarded.
2) configure the cache parameters:
 Variables: query_cache _type, query cache mode of operation. There are 3 modes, 0: not cache; 1: cache query, except at the beginning select sql_no_cache; 2: According to cache only those beginning with select sql_cache to query; query_cache_size: Sets the size of the query cache maximum result set than this value large will not be cached.

22, adjust the hardware

1) the machine is equipped with more memory;
2) increase faster hard disk to reduce I / O wait time;
 Seek time is the main factor determining the performance of the head is literally moving the slowest, once the head positioning, then quickly read from the track;
3) Re-allocation of disk activity on a different physical hard disk drives;
 If possible, it should be the busiest database stored in different physical devices, which use the same physical device with different partitions are different, because they will compete for the same physical resource (head).
- rsync + inotify to achieve real-time synchronization (Server)
- QEMU code analysis: BIOS loading process (Linux)
- Java memory mechanism Description (Programming)
- 10 Nginx safety tips (Linux)
- Spring inject a type of object to enumerate (Programming)
- Python 2.7.9 Installation on Linux CentOS 6.6 (Linux)
- Linux iptables: combat scenes (Linux)
- How to use the Linux kill command to kill the process / program is not responding (Linux)
- A script to make your Ubuntu 14.04 Memory screen brightness (Linux)
- Strategy Games Ubuntu installation of Wesnoth 1.12.1 (Linux)
- Oracle SQL statement tracking (Database)
- CentOS / RedHat system partition essential requirements and partition scheme (Linux)
- Change all child files and subdirectories in the owner permissions Ubuntu (Linux)
- C ++ copy constructor (Programming)
- IDS Intrusion Detection System built (Linux) (Linux)
- Linux disk virtualization (Linux)
- Python Basics: Search Path (Programming)
- To share Internet access through NAT mode under Virtual Linux VMware Workstation (Linux)
- Analysis examples: Intrusion Response Linux platform Case (Linux)
- CentOS 6.5 installation configuration DRBD (Server)
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.