Carding of common commands in mysql
Indexes
1. Creating Index
Indexes can be created in the CREATE TABLE statement, or indexed by CREATE INDEX or ALTER TABLE alone. The following command statements show how to create PRIMARY KEY, UNIQUE and INDEX, respectively.
Mysql > ALTER TABLE table name ADD INDEX index column name; Mysql > ALTER TABLE table name ADD UNIQUE index column name; Mysql > ALTER TABLE table name ADD PRIMARY KEY index column name; MySQL > CREATE INDEX index name ON table name column name; Mysql > CREATE UNIQUE INDEX Index Name ON Table Name Column Name;
For example:
mysql>ALTER TABLE `article` ADD INDEX `id`; //Add id index to the article table
Or:
mysql>ALTER TABLE
articleADD INDEX (id,order_id); //Add ID index, order_id index to the article table
2. Reconstructing Index
Reconstructed index is often used in routine database maintenance operations. After the database has been running for a long time, the index may be damaged, and then need to be rebuilt. Data reconstruction index can improve retrieval efficiency.
Mysql > REPAIR TABLE table name QUICK;
3. Query Data Table Index
MySQL query table index command has two command forms:
Mysql > SHOW INDEX FROM table name;
Or:
Mysql > SHOW keys FROM table name;
For example:
mysql> SHOW INDEX FROM uc_member;
+-----------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| uc_member | 0 | PRIMARY | 1 | member_id | A | 1099 | NULL | NULL | | BTREE | |
| uc_member | 1 | idx_nickname_passwd | 1 | member_nickname | A | 549 | NULL | NULL | | BTREE | |
| uc_member | 1 | idx_nickname_passwd | 2 | member_password | A | 1099 | NULL | NULL | | BTREE | |
| uc_member | 1 | member_mobile | 1 | member_mobile | A | 1099 | NULL | NULL | | BTREE | |
+-----------+------------+---------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)Explanation:
Non_unique is 0 if the index cannot include duplicates. If so, it is 1.
The name of the Key_name index.
The column sequence number in the Seq_in_index index index starts at 1.
Column_name column name.
How do Collation columns be stored in the index? In MySQL, there is a value of'A'(ascending order) or NULL (no classification).
An estimate of the number of unique values in the Cardinality index. Updates can be made by running AnalyYZE TABLE or myisamchk-a. The cardinality is counted based on statistics stored as integers, so even for small tables, the value is not necessarily accurate. The larger the cardinality, the greater the chance MySQL will have to use the index when joining.
Sub_part is the number of characters indexed if the column is only partially indexed. If the entire column is indexed, it is NULL.
Packed indicates how keywords are compressed. If it is not compressed, it is NULL.
Null contains YES if the column contains NULL. If not, the column contains NO.
Index_type used index methods (BTREE, FULLTEXT, HASH, RTREE).
4. Delete Index
Deleting indexes can be done using ALTER TABLE or DROP INDEX statements.
DROP INDEX can be processed as a statement within ALTER TABLE in the following format:
Mysql > DROP index index index name ON table name column name; Mysql > ALTER TABLE table name DROP INDEX index column name; Mysql > ALTER TABLE table name DROP UNIQUE index column name; Mysql > ALTER TABLE table name DROP PRIMARY KEY index column name;
In the preceding three statements, the index_name in table_name is deleted.
In the last statement, it is only used to delete the PRIMARY KEY index, because a table may have only one PRIMARY KEY index, so the index name may not be specified.
If no PRIMARY KEY index is created, but the table has one or more UNIQUE indexes, MySQL deletes the first UNIQUE index.
If a column is deleted from a table, the index is affected. For a multi-column composite index, if a column is deleted, the column is also deleted from the index.
If all columns that make up the index are deleted, the entire index will be deleted.
—————————— Index, Primary Key, Unique Index and Joint Index
Talk about Mysql Index Concept:
Indexing is like a book's catalogue, it will let you find content faster. Obviously, the more catalogues (indexes), the better. If the book has 1000 pages and 500 pages, it is also a catalogue. Of course, it is inefficient. Catalogues occupy paper, and indexes occupy disk space.
Mysql index has two main structures: hash and B + tree.
hash:hsah index is less used in mysql. It organizes the index of data in hash form, so when looking up a record, it is very fast. Because it is hash structure, each key corresponds to only one value, and it is distributed in hash way. So he does not support range search and sorting.
B+tree: b+tree is the most frequently used index data structure in mysql. The data structure is organized in the form of a balanced tree. Because it is a tree structure, it is more suitable for dealing with sorting, range lookup and other functions. Compared with hash index, B+tree is not as fast as hash index in searching single record, but it is more popular with users because it is more suitable for sorting and other operations. A single record operation is performed on the database.
Mysql common indexes are: primary key index, unique index, general index, full-text index, composite index
PRIMARY KEY (Primary Key Index): ALTER TABLE Table Name ADD PRIMARY KEY Column Name UNIQUE (Unique Index): ALTER TABLE Table Name ADD UNIQUE Column Name INDEX (General Index): ALTER TABLE Table Name ADD INDEX Index Column Name FULLTEXT (Full Text Index): ALTER TABLE Table Name ADD FULLTEXT Column Name Composite index: ALTER TABLE table name ADD INDEX index name (column name 1, column name 2, column name 3)
Mysql Various Index Differences:
Ordinary index: the most basic index, without any restrictions
Unique Index: Similar to "normal index", the difference is that the value of the index column must be unique, but the null value must be allowed.
Primary key index: It is a special and unique index, ==== No null value is allowed ==.
Full-text index: It can only be used in MyISAM tables. For larger data, it takes a lot of time and space to generate full-text index.
Composite index: In order to improve mysql efficiency, we can establish a composite index, following the principle of "== the leftmost prefix ==".
Index is a special kind of file (the index on InnoDB data table is a part of table space), which contains reference pointers to all records in the data table.
The only task of a normal index (=== index defined by keyword KEY or INDEX==) is to speed up access to data.
Ordinary indexes allow indexed data columns to contain duplicate values. If it can be determined that a data column will contain only different values from each other, the keyword UNIQUE should be used to define it as a unique index when creating an index for the data column. That is to say, a unique index can guarantee the uniqueness of data records.
Primary key is a special unique index. In a table, only one primary key index can be defined. Primary key is used to uniquely identify a record. Primary key is created by using the keyword PRIMARY KEY.
Indexes can cover multiple data columns, such as INDEX(columnA, columnB) indexes, which are joint indexes.
Primary bond is divided into composite primary bond and joint primary bond.
Composite primary key means that the primary key of your table contains more than one field.
For example:
create table test
(
name varchar(19),
id number,
value varchar(10),
primary key (id,name)
)The combination of the above fields is the composite primary key of your test table == (if it is a single index field, the id on the left will have an index)
It appears because your name field may be renamed, so add the ID field so that you can ensure the uniqueness of your records.
Generally speaking, the less field length and number of primary keys, the better.
Joint primary key, as its name implies, is the combination of multiple primary keys to form a major key combination, which is reflected in the joint.
(Primary keys are unique in principle. Don't be bothered by unique values. )
Indexing can greatly improve the query speed of data, but it will reduce the speed of inserting, deleting and updating tables, because when these write operations are performed, the index files are also operated.
Take a simple example.
The primary key A and the primary key B form a joint primary key.
The data of primary key A and primary key B can be exactly the same (trouble, it doesn't matter). The union is that the union primary key A and primary key B form is the only union primary key.
In the following example, the primary key A data is 1, and the primary key B data is 1. The joint primary key is actually 11. This 11 is the only value, and it is absolutely not allowed to appear 11 again. (This is the many-to-many relationship)
Primary key A data Primary key B data
1 1
2 2
3 3
The joint primary key value of primary key A and B is the maximum.
11
12
13
21
22
23
31
32
33
mysql operation command carding (2) - alter (update, insert)
Alter is often used in mysql operations to modify tables. alter tables allow you to modify the structure of an existing table, such as adding or deleting columns, creating or eliminating indexes, changing the type of existing columns, or renaming columns or tables themselves. alter tables can also change the type of comments and tables of tables.
Following is a review of the use of alter modification commands:
Alter is often used in mysql operations to modify tables. alter tables allow you to modify the structure of an existing table, such as adding or deleting columns, creating or eliminating indexes, changing the type of existing columns, or renaming columns or tables themselves. alter tables can also change the type of comments and tables of tables.
Following is a review of the use of alter modification commands:
2) Delete columns
== Delete column alter table table table table table name drop column name ==; //or alter table table table table table name drop column column name;
drop database library name; //delete Library drop table table table name; //delete table delete from table name; / / empty all data in the table, but this table is not deleted, leaving empty tables. delete from table name where;//delete field in table
2) Additional columns
Attributes of ADD column columns with alter table table names (such as INT NOT NULL COMMENT'Annotation Notes')
3) Modify column type information. alter table table table name CHANGE column name new column name new column attribute;
alter table table name CHANGE column name new column name (here can be used with the same name as the original column) BIGINT NOT NULL COMMENT'Note Notes'
4) Rename columns
alter table table table name CHANGE column name new column name BIGINT NOT NULL COMMENT'comment note'
5) Rename table
alter table table name RENAME table new name
6) Delete the primary key in the table
alter table table name drop primary key
------------------------------------------------------------------------------------- If you delete a primary key with a self-increasing attribute, you first delete the self-increasing key, and then delete the primary key. Alter table haha change id int (10); //delete self-growth Alter table haha drop primary key; //Delete main build -------------------------------------------------------------------------------------
7) Adding primary keys
alter table Table name ADD CONSTRAINT PK_Table name PRIMARY KEY(List 1,List 2)
-------------------------------------------------------------------------------------
Alter table haha add primary key(id); //Add primary key to id column of haha table
Alter table haha change id id int(10) not null auto_increment; //Self-growth Attribute
-------------------------------------------------------------------------------------
sql in constraint The intention of prefix( PK,UK,DF,CK,FK)
--Primary key
constraint PK_field primary key(field),
--Unique constraint
constraint UK_field unique key(field),
--Default constraints
constrint DF_field default('Default values') for field,
--Check constraints
constraint CK_field check(Constraints. Such as: len(field)>1),
--Primary and Foreign Bond Relations
constraint FK_Master table_From table foreign(Foreign key field) references Master table(Primary key field of main table)
-------------------------------------------------------------------------------------8) Add/Create Index
alter table table table name add index index index column name; //common index ALTER TABLE Table Name ADD UNIQUE index Index Index Name (Column Name 1, Column Name 2);//Joint Index ALTER TABLE Table Name ADD PRIMARY index KEY Index Column Name; //Primary Key Index ------------------------------------------------------------------------------------- SHOW INDEX FROM Table Name; //Query Index Deleting indexes can be done using ALTER TABLE or DROP INDEX statements. DROP INDEX can be processed as a statement inside ALTER TABLE DROP index Index Name ON Table Name Column Name; //Delete Ordinary Index ALTER TABLE Table Name DROP INDEX Index Column Name; //Delete Ordinary Index ALTER TABLE Table Name DROP UNIQUE Index Name (Column Name 1, Column Name 2); //Delete Joint Index ALTER TABLE Table Name DROP PRIMARY KEY Index Column Name; //Delete Primary Key Index -------------------------------------------------------------------------------------
9) Adding Unique Restriction Index
alter table table name add unique emp_name2 (cardnumber); //empname type is String string type
10) Creating a Joint Unique Index
alter table table table name ADD UNIQUE INDEX index name (column name 1, column name 2);
alter ignore table table table name add unique index(user_id,user_name); / / it deletes duplicate records (don't be afraid, it keeps one), and then creates a unique index, which is efficient and humane. (caution)
11) Modify field properties
alter table table name modify column field name type alter table table name modify column field 1 type, field 2 type For example, change the name column attribute of the class table to varchar(100) alter table class modify column name varchar(100); Or: alter table table name change column name varchar(100); alter table class change name name varchar(100);
As mentioned above, alter command is used to modify table name and column name; then update command is needed to modify field value, as follows:
mysql> select * from huanqiu.haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | wangshibo |
| 11 | ouou |
| 19 | hahahahha |
+----+-----------+
3 rows in set (0.00 sec)
mysql> update huanqiu.haha set name="wangshikui" where id=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from huanqiu.haha;
+----+------------+
| id | name |
+----+------------+
| 1 | wangshibo |
| 11 | wangshikui |
| 19 | hahahahha |
+----+------------+
3 rows in set (0.01 sec)Modify the root login password:
mysql> update mysql.user set password=password("654321") where host='192.168.1.101' and user="root";
update table name set column name = "new field value" [where restriction 1 and restriction 1];
update table name set column name 1="new field value", column name 2="new field value"[where restriction 1 and restriction 1] [ORDER BY... ] [LIMIT row_count];
insert into table name values (insert values for all fields);
insert into table name (specified field) values (specified field);
insert into table name values (insert values for all fields), (insert values for all fields), (insert values for all fields); //bulk insert
mysql> select * from huanqiu.haha;
+----+------------+
| id | name |
+----+------------+
| 11 | wangshikui |
+----+------------+
1 row in set (0.00 sec)
mysql> insert into huanqiu.haha values(1,"wangshibo"),(2,"wangshikui"),(3,"wangjuan"),(4,"wangman");
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from huanqiu.haha;
+----+------------+
| id | name |
+----+------------+
| 1 | wangshibo |
| 2 | wangshikui |
| 3 | wangjuan |
| 4 | wangman |
| 11 | wangshikui |
+----+------------+When creating a table, specify the initial value of AUTO_INCREMENT self-increment:
mysql> CREATE TABLE Table name (ID INT(5) PRIMARY KEY AUTO_INCREMENT)AUTO_INCREMENT=100;
Modify the initial value through ALTER TABLE (but greater than the AUTO_INCREMENT self-increment in the table, otherwise the setting is invalid):
mysql>ATLER TABLE Table name AUTO_INCREMENT=100;
If the maximum value of the self-increasing sequence is deleted, the value is reused when a new record is inserted.
That is to say, if the original AUTO_INCREMENT attribute values in the table are consecutive values of 78 and 100, but the data of 100 is deleted, then the self-increment of 101,100 is reused when adding data.
Even after you empty all data delete s in the entire table, the previous maximum self-incremental sequence will be reused.
The solution is to use the following commands:
ATLER TABLE Table Name AUTO_INCREMENT=0; //Reset Self-Increasing Initial Value
Set AUTO_INCREMENT_INCREMENT and AUTO_INCREMENT_offset user variable values (after restarting MySQL, these changes will revert to the initial value of 1):
mysql>SET auto_increment_increment=10; //The value of each increment of self-increment is changed to 10.
mysql>SET auto_increment_offset=2; //Bit values of offset values at first loading
mysql>SHOW VARIABLES LIKE 'AUTO_INC%'; //View the value of the modified variable
mysql>SHOW TABLE STATUS FROM NAME_DB; //Display database NAME_DB Information on all tables in
mysql>SHOW CREATE TABLE NAME_TBL; //Display table NAME_TBL Information at creation time
mysql>SHOW VARIABLES LIKE 'AUTO_INC%'; //User session variables (SHOW GLOBAL VARIABLES) that display the beginning of AUTO_INC for MySQL
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)Carding of mysql operation commands (3) - pager
In the daily operation of mysql, using pager to set display mode can greatly improve work efficiency. For example, if the result set of select is more than a few screens, then the previous results can not be seen in a flash. At this time, using pager can set more or less to call OS to display the query results, just like using more or less to view large files in os.
pager usage:
In fact, the output of all mysql operation commands after setting it is executed by page setting command, similar to the function of pipeline character.
== The nopager command ==: Cancel the pager settings and restore the previous output state. (If no pager is set, the recovery can only be achieved by restarting the mysql service)
Let's give some examples.
1) When dealing with large amounts of data, we do not want to display the results of the query, but only need to know the time spent on the query.
mysql> select * from huanqiu.haha;
+----+------------+
| id | name |
+----+------------+
| 1 | wangshibo |
| 2 | wangshikui |
| 3 | wangjuan |
| 4 | wangman |
| 11 | wangshikui |
+----+------------+
5 rows in set (0.00 sec)
mysql> pager cat /dev/null; //Actually, it's equal to the command | cat /dev/null executed later, so that the result is just execution time.
PAGER set to 'cat /dev/null'
mysql> select * from huanqiu.haha;
5 rows in set (0.00 sec)<br>
mysql> nopager; //Restore the previous output state
PAGER set to stdout<br>
mysql> select * from huanqiu.haha;
+----+------------+
| id | name |
+----+------------+
| 1 | wangshibo |
| 2 | wangshikui |
| 3 | wangjuan |
| 4 | wangman |
| 11 | wangshikui |
+----+------------+
5 rows in set (0.00 sec)2) If there are a lot of connections, it is inconvenient to use show process list to see how many Sleep States there are, you can use pager.
mysql> show processlist;
+------+-------+---------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-------+---------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
| 5 | root | localhost | huanpc | Query | 0 | init | show processlist |
| 1801 | slave | 192.168.1.102:37125 | NULL | Binlog Dump | 9904 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
+------+-------+---------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
mysql> pager grep Sleep |wc -l;
PAGER set to 'grep Sleep |wc -l'
mysql> show processlist; //Similar to show process list results are displayed through grep Sleep |wc -l; the following shows a total of two connections, of which 0 are Sleep state connections.
0
2 rows in set (0.00 sec)
mysql> nopager; //Restore the previous output state3) Set pager to view only a few status values of slave status.
mysql> show slave status \G; //Where G indicates that the display is to be displayed in a new line
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 5370489
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 2476520
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: huanqiu,huanpc
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 5370489
Relay_Log_Space: 2476693
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_UUID: b667a58f-d6e0-11e6-8c0a-fa163e2d66ac
Master_Info_File: /data/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> pager cat | egrep -i 'system user|Exec_Master_Log_Pos|Seconds_Behind_Master|Read_Master_Log_Pos';
PAGER set to 'cat | egrep -i 'system user|Exec_Master_Log_Pos|Seconds_Behind_Master|Read_Master_Log_Pos''
mysql> show slave status \G;
Read_Master_Log_Pos: 5370489
Exec_Master_Log_Pos: 5370489
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> nopager; //Pre-restore display status
PAGER set to stdoutCarding of mysql operation commands (4) - grant authorization and revoke recovery privileges
In the maintenance of mysql, it is very important to manage the privilege well.
Following is the carding of mysql privilege operation:
The permission command of mysql is grant, and revoke when the permission revokes.
Grant authorization format: grant privilege list on library. table to user name @'ip'identified by "password";
Revoke recovery permission format: revoke permission list on library. table from user name @'ip';
Here are some examples to illustrate:
1.grant authorization
1) grant ordinary data users, the right to query, insert, update and delete all table data in the database.
mysql> grant all on *.* to wang@'192.168.1.150' identified by "password"; //All is equivalent to all privilege s, in which privileges can be omitted
mysql> grant all privileges on *.* to wang@'192.168.1.%' identified by "123456"; //192.168.1%. Represents a segment
mysql> grant insert,select,update,delete,drop,create,alter on huanqiu.* to wang@'%' identified by "123456";
mysql> flush privileges //After authorization, don't forget to update the permission table2. View permissions
1) View all permissions under the current user
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)2) View all permissions under the specified user
USAGE is the default initial state, indicating no permission!!
The following shows that after the wang user logged in to the local mysql in the 192.168.1.0/24 network segment, all tables under the huanqiu library have insert,update,alter,delete,create,select operation rights!
mysql> show grants for wang@'192.168.1.%'; //You can find the corresponding permission user information in the result of select user,host,password from mysql.user execution
+---------------------------------------------------------------------------------------------------------------+
| Grants for wang@192.168.1.% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang'@'192.168.1.%' IDENTIFIED BY PASSWORD '*678E2A46B8C71291A3915F92736C080819AD76DF' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `huanqiu`.* TO 'wang'@'192.168.1.%' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)3.revoke revokes authority
Revoke is similar to grant grammar, just replace the keyword "to" with "from", and the revoke statement does not need to be set with the password.
Note: == revoke can reclaim all permissions or part of them.
mysql> revoke all on *.* from wang@'192.168.1.150';
mysql> revoke all privileges on *.* from wang@'192.168.1.%';
mysql> revoke insert,select,update,delete,drop，create，alter on huanqiu.* from wang@'%';
mysql> flush privilegesMatters needing attention:
1) grant, revoke user privileges, the user only reconnect MySQL database, privileges can take effect.
2) If you want authorized users to grant these rights to other users, you need to add the option "grant option" when authorizing.
With the following settings, the wang user can also grant these privileges to other users after connecting to mysql.
mysql> grant insert,select,update,alter on huanqiu.* to wang@'%' identified by "123456" with grant option;
------------------------------------------------------------------------------------------------------------------- The mysql authorization table involves five tables: user, db, host, tables_priv and columns_priv. The contents and uses of these five tables are as follows: 1) user table The user table lists the users who can connect to the server and their passwords, and it specifies which global (super-user) permissions they have. Any permissions enabled in the user table are global permissions and apply to all databases. For example, if you enable DELETE privileges, the users listed here can delete records from any table, so think carefully before you do so. 2) db table db tables list databases, and users have access to them. The permissions specified here apply to all tables in a database. 3) host table The combination of host table and db table can control the access rights of a particular host to the database at a better level, which may be better than using db alone. This table is not affected by GRANT and REVOKE statements, so you may find that you are not using it at all. 4) tables_priv table The tables_priv table specifies table-level permissions, and one of the permissions specified here applies to all columns of a table. 5) columns_priv table Column_priv table specifies column-level permissions. The permissions specified here apply to specific columns of a table. ------------------------------------------------------------------------------------------------------------------
== Look at the following example:==
The authorization rights for wang users are too large. Now we need to withdraw some of them, leaving only the rights for wang users select and alter.
mysql> show grants for wang@'192.168.1.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for wang@192.168.1.% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang'@'192.168.1.%' IDENTIFIED BY PASSWORD '*678E2A46B8C71291A3915F92736C080819AD76DF' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `huanqiu`.* TO 'wang'@'192.168.1.%' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke insert,update,delete,create on huanqiu.* from wang@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for wang@'192.168.1.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for wang@192.168.1.% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang'@'192.168.1.%' IDENTIFIED BY PASSWORD '*678E2A46B8C71291A3915F92736C080819AD76DF' |
| GRANT SELECT, ALTER ON `huanqiu`.* TO 'wang'@'192.168.1.%' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)After the revoke recovery operation is discovered, only select and alter privileges are left.
revoke reclaims some permissions and the password for the remaining permissions remains unchanged.
Here special attention is paid to:
== If the permission set to a user is too large, in addition to using revoke to reclaim part of the permission above, you can also use grant to modify the permission!
That is to say, grant can not only add permissions, but also modify permissions (in fact, set permissions on the same'username'@'ip' to override previous permissions);
grant's modified permissions will override previous permissions! = =
Then the question arises: the password after authorization is stored in ciphertext form. If you can't remember the password when authorization was carried out before, how can you ensure that the permissions after authorization are consistent with those before?
Don't panic!
In grant authorization operation, not only can the plaintext password be set, but also the ciphertext password be set, as follows:
1) grant permission list on library. table. * to username @'ip'identified by "plaintext password"
2) grant privilege list on library. table. * to username @'ip'identified by password "ciphertext password".
In other words:
When grant resets permissions, you can use the password you see as a new password, and then override the previous permissions, which ensures that the passwords before and after modification are consistent!
For the example above, the operation of grant is as follows:
mysql> show grants for wang@'192.168.1.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for wang@192.168.1.% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang'@'192.168.1.%' IDENTIFIED BY PASSWORD '*678E2A46B8C71291A3915F92736C080819AD76DF' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `huanqiu`.* TO 'wang'@'192.168.1.%' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> grant alter,select on huanqiu.* to wang@'192.168.1.%' identified by password '*678E2A46B8C71291A3915F92736C080819AD76DF';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for wang@'192.168.1.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for wang@192.168.1.% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wang'@'192.168.1.%' IDENTIFIED BY PASSWORD '*678E2A46B8C71291A3915F92736C080819AD76DF' |
| GRANT SELECT, ALTER ON `huanqiu`.* TO 'wang'@'192.168.1.%' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)Usually, when the development colleagues let the operation and maintenance colleagues open MySQL privileges, they will generate a ciphertext password in their local mysql, and then give the ciphertext password to the operation and maintenance colleagues, who use the ciphertext password for authorization.
Then the authorized password is only known by the development colleagues themselves, and no one else knows it! A safer approach~
Carding of mysql operation commands (5) - executing sql statement queries, i.e. mysql status description
In the daily operation and maintenance of mysql, it is often necessary to query the sql statements being executed under the current MySQL and other mysql-related threads running, which uses the command==mysql processlist==
MySQL > show processlist; // query the sql statement being executed
MySQL > show full processlist; // query the complete sql statement being executed
MySQL > kill connection id // stop a thread queried by processlist, id is the corresponding id number
mysql> show processlist;
+------+-------+---------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-------+---------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
| 1574 | root | localhost | huanpc | Sleep | 702 | | NULL |
| 1955 | root | localhost | NULL | Query | 0 | init | show processlist |
| 1958 | slave | 192.168.1.102:37399 | NULL | Binlog Dump | 10 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
+------+-------+---------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> kill connection 1574;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+------+-------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 1955 | root | localhost | NULL | Query | 0 | init | show processlist |
| 1958 | slave | 192.168.1.102:37399 | NULL | Binlog Dump | 18 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
+------+-------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)In addition, show process list can also view the current number of mysql connections.
If it is a root account, you can see the current connections of all users.
If it is another ordinary account, you can only see the connection you occupy.
Be careful:
Show process list; list only the first 100 items
If you want to list all, use show full process list.
Use show status; you can see mysql status more fully
mysql> show status; Parametric interpretation: Aborted_clients The number of connections that have been abandoned because the customer failed to close the connection properly has died. Number of times Aborted_connections attempted a failed connection to MySQL server. The number of times Connections attempted to connect to MySQL servers. Created_tmp_tables When executing statements, the number of implicit temporary tables that have been created. The number of delayed insert processor threads being used by Delayed_insert_threads. Number of rows written by Delayed_writes with INSERT DELAYED. The number of rows where Delayed_errors write with INSERT DELAYED that have some errors (possibly duplicate key values). The number of times Flush_commands execute FLUSH commands. The number of times a Handler_delete request deletes rows from a table. The number of times Handler_read_first requests are read into the first row of the table. Handler_read_key requests numbers based on key-read rows. The number of times Handler_read_next requests to read a row based on a key. The number of times Handler_read_rnd requests to read a row based on a fixed location. Number of times Handler_update requests update a row in a table. The number of times Handler_write requests insert a row into a table. Number of blocks used for keyword caching by Key_blocks_user. Number of times a Key_read_requests request reads a key value from the cache. Number of times Key_reads physically read a key value from disk. Number of times a Key_write_requests request writes a key block to the cache. The number of times Key_writes physically write a key block to disk. Maximum number of connections used simultaneously by Max_used_connections. Not_flushed_key_blocks have been changed in the key cache but have not been emptied into the key blocks on disk. Not_flushed_delayed_rows The number of rows to be written in the INSERT DELAY queue. Number of tables opened by Open_tables. Number of open files for Open_files. Number of Open_streams open streams (mainly for logging) Number of tables that have been opened by Opened_tables. Number of queries sent by Questions to the server. Slow_queries take more queries than long_query_time. Threads_connected Number of currently open connections. Threads_running is not the number of threads sleeping. How many seconds did the Uptime server work?
Carding of mysql operation commands (6) - Chinese scrambling problem
In the normal operation and maintenance of mysql, we often encounter scrambling after inserting Chinese fields. The causes of Chinese scrambling are as follows:
1) The encoding format of mysql is incorrect. It is latin1 encoding. It is strongly recommended that the encoding format under mysql be changed to utf8, because it is compatible with all characters in the world!
2) Family setting of mysql tables (including character s and collations)
3) Link Family Setting of Client Programs (e.g. php)
Here is a record of the operation of Mysql to deal with the disorder of Chinese fields in the data table:
In order to prevent chaos in subsequent operations, it is better to set the correct encoding when creating a database or data table.
When creating a database, set the encoding format
mysql> CREATE DATABASE hqsb
-> CHARACTER SET utf8
-> COLLATE utf8_general_ci;
Query OK, 1 row affected (0.01 sec)When creating a table, set the encoding format
mysql> use hqsb;
Database changed
mysql> CREATE TABLE haha (
-> id int(10) PRIMARY KEY AUTO_INCREMENT,
-> name varchar(64) NOT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)With these three settings in place, there will be no problem, that is, the same encoding format will be used when building the database and tables.
If the encoding format is not specified when the database is built and the table is built, the Chinese random code can be queried in the following way.
1) View the default encoding format of mysql system (ensure that all encoding formats in the following query results are utf8, if not, manually modify it!) :
mysql> show variables like "%char%";
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)Manual modification:
mysql> SET character_set_filesystem='utf8';
Query OK, 0 rows affected (0.00 sec)Check again whether the changes have been made:
mysql> show variables like "%char%";
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | utf8 |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)2) View the encoding format of the database (e.g. hqsb):
mysql> show create database hqsb;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| hqsb | CREATE DATABASE `hqsb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)If the encoding format of the database is incorrect, you can modify it manually:
mysql> ALTER DATABASE hqsb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.01 sec)3) View the encoding format of data tables (such as haha):
mysql> show create table haha;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| haha | CREATE TABLE `haha` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)If the encoding format of the data table is incorrect, you can modify it manually:
mysql> ALTER TABLE haha DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.01 sec)Be careful:
Connect mysql to insert data in the client (such as xshell), ==== It's best to ensure that the encoding format is Utf-8===.
Solving the Problem of Scrambling in Web Pages
1) Set the website code to utf-8.
2) If the website has been in operation for a long time and has a lot of old data, and can not change the simplified Chinese settings, then it is recommended that the page code be set to GBK.
The difference between GBK and GB2312 is that GBK can display more characters than GB2312. To display simplified traditional characters, only GBK can be used.
3) Edit / etc/my.cnf and add default_character_set=utf8 in the paragraph [mysql].
4) When writing the Connection URL, add the? UseUnicode = true & characterEncoding = UTF-8 parameter;
5) Add a "set names utf8" or "set names gbk" instruction to the page code to tell MySQL to use utf8 or GBK for connection content.