What are common MySQL skills?

Source: Internet
Author: User

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;

 
 
  1. MySQL> grant select on db.* to user@host identified by ‘passwd’;  
  2. MySQL> revoke all on db.* from user@host;  
  3. 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 skills 4. Set the maximum number of concurrent response connections and the maximum number of waiting connections in the response queue to be 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. Optimize table fragmentation)

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:

 
 
  1. MySQLbinlog [options] log-files 

Appendix permission Name and description list

 
 
  1. Privilege  
  2. Meaning  
  3. ALL [PRIVILEGES]  
  4. Sets all simple privileges except GRANT OPTION  
  5. ALTER  
  6. Allows use of ALTER TABLE  
  7. CREATE  
  8. Allows use of CREATE TABLE  
  9. CREATE TEMPORARY TABLES  
  10. Allows use of CREATE TEMPORARY TABLE  
  11. DELETE  
  12. Allows use of DELETE  
  13. DROP  
  14. Allows use of DROP TABLE  
  15. EXECUTE  
  16. Not implemented  
  17. FILE  
  18. Allows use of SELECT … INTO OUTFILE and LOAD DATA INFILE  
  19. INDEX  
  20. Allows use of CREATE INDEX and DROP INDEX  
  21. INSERT  
  22. Allows use of INSERT  
  23. LOCK TABLES  
  24. Allows use of LOCK TABLES on tables for which you have the SELECT privilege  
  25. PROCESS  
  26. Allows use of SHOW FULL PROCESSLIST  
  27. REFERENCES  
  28. Not implemented  
  29. RELOAD  
  30. Allows use of FLUSH  
  31. REPLICATION CLIENT  
  32. Allows the user to ask where slave or master servers are  
  33. REPLICATION SLAVE  
  34. Needed for replication slaves (to read binary log events from the master)  
  35. SELECT  
  36. Allows use of SELECT  
  37. SHOW DATABASES  
  38. SHOW DATABASES shows all databases  
  39. SHUTDOWN  
  40. Allows use of MySQLadmin shutdown  
  41. SUPER  
  42. Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, 
  43. the MySQLadmin debug command; allows you to connect (once) even if max_connections is reached  
  44. UPDATE  
  45. Allows use of UPDATE  
  46. USAGE  
  47. Synonym for “no privileges”  
  48. GRANT OPTION  
  49. Allows privileges to be granted  

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.