MySQL common tips

Today we will share with you some of the frequently-used MySQL skills. We hope that you will learn the frequently-used MySQL skills to help you. If you are curious about its practical application, the following articles will unveil its mysteries.

1) Common MySQL Skills 1. It is best to subscribe to user permission management at the DB or Table level. do not enable global permissions easily;

2) use grant and revoke for user authorization and authorization;

 
 

  
  
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) 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 are 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) 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) 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: Error Log and binlog

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 grants t 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.