MySQL database commands

The following articles mainly describe the practical application of MySQL database commands, including how to correctly escalate the permissions of a user and how to obtain the actual recovery steps from the relevant backup files, the following describes the specific content of the article. I hope you will find some gains. 1: Enhance the permissions of a user. dbname: Specify the user grantallon *. * tosfsuse

The following articles mainly describe the practical application of MySQL database commands, including how to correctly escalate the permissions of a user and how to obtain the actual recovery steps from the relevant backup files, the following describes the specific content of the article. I hope you will find some gains. 1: Enhance the permissions of a user. dbname: Specify the user grantallon *. * tosfsuse

The following articles mainly describe the practical application of MySQL database commands, including how to correctly escalate the permissions of a user and how to obtain the actual recovery steps from the relevant backup files, the following describes the specific content of the article. I hope you will find some gains.

1. Enhance the permissions of a user

Dbname specifies the user

 
 

  
  
grant all on *.* to sfsuser@'%'; 
 
 

2: Restore from the backup file

 
 

  
  
MySQL-usfsuser-p111111 dbname <file path/file name;
 
 

3: log on to the remote MySQL database

 
 

  
  
MySQL -h10.0.0.1 -usfsuser -p111111; 
 
 

4: Set the default data Character Set

 
 

  
  
vi /etc/my.cof 
 
 

Find the client configuration [client] and add it below

Default-character-set = utf8 default character set: utf8

Add in [MySQLd]

Default-character-set = utf8 default character set: utf8

 
 

  
  
init_connect='SET NAMES utf8' 
 
 

Restart the MySQL database

 
 

  
  
/etc/init.d/MySQL stop 
  
  
/etc/init.d/MySQL start 
 
 

Enter MySQL to run

 
 

  
  
show variables like 'character%'; 
  
  
+--------------------------+----------------------------+ 
  
  
| Variable_name | Value | 
  
  
+--------------------------+----------------------------+ 
  
  
| character_set_client | utf8 | 
  
  
| character_set_connection | utf8 | 
  
  
| character_set_database | utf8 | 
  
  
| character_set_filesystem | binary | 
  
  
| character_set_results | utf8 | 
  
  
| character_set_server | utf8 | 
  
  
| character_set_system | utf8 | 
  
  
| character_sets_dir | /usr/share/MySQL/charsets/ | 
  
  
+--------------------------+----------------------------+ 
 
 

5. Slow query logs

In the MySQL database configuration file my. cnf, add

Log-slow-queries =/var/lib/MySQL/slowquery. log (specify the location where the log file is stored, which can be empty and the system will give a default file host_name-slow.log)

Long_query_time = 2 (including all 5. * series, before 5.1.21) Slow query logs are in seconds by default, and the time later than the millisecond record is 10 s by default)

Log-queries-not-using-indexes, if you want to enable the control, it is best to install the microtime slow query patch to avoid misunderstanding that some records are slow queries. for example, you can perform the count (*) operation on the entire table and other operations that do not use indexes but are fast)

Log-long-format (if it is set, all queries without indexes will be recorded)

After the database is restarted, use show variables to check whether the corresponding fields take effect;