What are common MySQL skills?

The following articles mainly introduce common MySQL skills. We will mainly introduce seven practical application skills. If you are interested in these seven common MySQL skills, you can browse the following articles and hope to bring you some help in learning. 1) One of the common MySQL skills, it is best to subscribe to the database or Table level for user permission management.

The following articles mainly introduce common MySQL skills. We will mainly introduce seven practical application skills. If you are interested in these seven common MySQL skills, you can browse the following articles and hope to bring you some help in learning. 1) One of the common MySQL skills, it is best to subscribe to the database or Table level for user permission management.

The following articles mainly introduce common MySQL skills. We will mainly introduce seven practical application skills. If you are interested in these seven common MySQL skills, you can browse the following articles and hope to bring you some help in learning.

1) One of the common MySQL skills, it is best to subscribe to user permission management at the DB or Table level. do not enable global permissions easily;

2) Common MySQL Skills 2: grant and revoke for user authorization and permission acquisition;

 
 

  
  
MySQL> grant select on db.* to user@host identified by ‘passwd’; 
  
  
MySQL> revoke all on db.* from user@host; 
  
  
MySQL> flush privileges; 
 
 

Note: After changing user permissions, run flush to make the change take effect;

3) Common MySQL skills 3: several important commands for viewing the server running status;

Show status; displays the running status of the system.

Show variables; displays system running parameter settings and Environment variables

Show processlist; displays the existing access connections;

For the master slave system, there are also: show master/slave status;

4) MySQL common tips 4: set the maximum number of concurrent response connections, the maximum number of waiting connections in the response queue (the upper limit is related to the specific operating system), and the idle connection timeout time.

Max connections

View: MySQL> show variables like 'max _ connections ';

Settings: MySQL> set global max_connections = 200;

The default value is 100. If it is set to 0, no restriction is imposed;

Transient concurrent waiting connections

View: MySQL> show variables like 'back _ log ';

Set: MySQL> set global back_log = 200;

The default value is 50;

Idle connection timeout

MySQL> set wait_timeout = 3600;

The default value is 28800, which is 8 hours, in seconds;

5) MySQL common skills 5. Table optimization (fragment)

If a table with a large data volume is modified in large quantities, you can run the following command:

MySQL> optimize table table_name;

To achieve the purpose of fragment;

6) Tips for MySQL 6. Use MySQLhotcopy for Hot Backup of database files

/Home/MySQL/bin/MySQLhotcopy db_name [./table_regex/] [new_db_name | direc release]

Example:

/Home/MySQL/bin/MySQLhotcopy-u root-p 'xxxx' test./^ tt $ /./

Note: MySQLhotcopy is a perl program and must be supported by the DBI and DBD perl modules.

7) Common MySQL skills 7. error logs and binlogs

The error log is generally in the data directory, for example:

Var/. err

Binlog can record all operation records in the database. It is enabled using the log-bin option in my. cnf. If it is commented out, the binlog content can be viewed using the following command:

 
 

  
  
MySQLbinlog [options] log-files 
 
 

Appendix permission Name and description list

 
 

  
  
Privilege 
  
  
Meaning 
  
  
ALL [PRIVILEGES] 
  
  
Sets all simple privileges except GRANT OPTION 
  
  
ALTER 
  
  
Allows use of ALTER TABLE 
  
  
CREATE 
  
  
Allows use of CREATE TABLE 
  
  
CREATE TEMPORARY TABLES 
  
  
Allows use of CREATE TEMPORARY TABLE 
  
  
DELETE 
  
  
Allows use of DELETE 
  
  
DROP 
  
  
Allows use of DROP TABLE 
  
  
EXECUTE 
  
  
Not implemented 
  
  
FILE 
  
  
Allows use of SELECT … INTO OUTFILE and LOAD DATA INFILE 
  
  
INDEX 
  
  
Allows use of CREATE INDEX and DROP INDEX 
  
  
INSERT 
  
  
Allows use of INSERT 
  
  
LOCK TABLES 
  
  
Allows use of LOCK TABLES on tables for which you have the SELECT privilege 
  
  
PROCESS 
  
  
Allows use of SHOW FULL PROCESSLIST 
  
  
REFERENCES 
  
  
Not implemented 
  
  
RELOAD 
  
  
Allows use of FLUSH 
  
  
REPLICATION CLIENT 
  
  
Allows the user to ask where slave or master servers are 
  
  
REPLICATION SLAVE 
  
  
Needed for replication slaves (to read binary log events from the master) 
  
  
SELECT 
  
  
Allows use of SELECT 
  
  
SHOW DATABASES 
  
  
SHOW DATABASES shows all databases 
  
  
SHUTDOWN 
  
  
Allows use of MySQLadmin shutdown 
  
  
SUPER 
  
  
Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, 
  
  
the MySQLadmin debug command; allows you to connect (once) even if max_connections is reached 
  
  
UPDATE 
  
  
Allows use of UPDATE 
  
  
USAGE 
  
  
Synonym for “no privileges” 
  
  
GRANT OPTION 
  
  
Allows privileges to be granted 
 
 

The above content is an introduction to common MySQL skills. I hope you will get something better.