MySQL permissions settings and common configuration

After the MySQL installation is complete, the general password is empty, the password needs to be initialized, the specific command is as follows:

mysql> /usr/local/mysql/bin/mysqladmin-u root password ' xxx '

Mysql> SELECT User,host,password from Mysql.user;

mysql> use MySQL;

mysql> Update user set Password=password ("xxxx") where user= "root";

mysql> Delete from user where user= "" and host= "localhost";

mysql> Delete from user where user= "" and host= "Localhost.localdomain";

Mysql> Grant all privileges on * * to ' test ' @ '% ' identified by ' XXXX ';

mysql> flush Privileges;

Mysql> SELECT User,host,password from Mysql.user;

View MySQL Users

mysql> Select User,host,password from Mysql.user;

View User Permissions

Mysql> Show grants for [email protected];

mysql password hack:

join in the MY.CNF Skip-grant-tables , and then restart MySQL,

Go to modify user password:

Mysql>use MySQL

mysql>Update user Setpassword=password ("New_pass") where user= "root";

Modify the skip configuration in delete my.cnf, and then restart MySQL

Ps:update User Set host = '% ' where user = ' root '; can be accessed by any user, not just local users

MySQL import and export and backup:

1. Export the entire database

Mysqldump-u user name-p database name > exported file name

Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql

2. Export a table

Mysqldump-u user name-P database name Table name > exported file name

Mysqldump-u wcnc-p smgp_apps_wcncusers> Wcnc_users.sql

Writing the password into the script will prompt you:

warning:using a password on the command line Interfacecan is insecure. interface can be unsafe

　　3. Export a database structure ( desc See the structure of the table )

　mysqldump-u Wcnc-p-D --add-drop-table SMGP_APPS_WCNC >d:wcnc_db.sql

-D No data--add-drop-table add a drop table before each CREATE statement

4. If you need to export a function or stored procedure inside MySQL

Mysqldump-hhostname-uusername-ppassword-ntd-r databasename > Backupflie.sql

Mysqldump-hlocalhost-uroot-ntd-r hqgr > Hqgr.sql or

Mysqldump -hlocalhost-uroot- R hqgr > Hqgr.sql

Where the-NTD is the export stored procedure;-R is a representation of the export function

Common SOURCE Commands:

　　Go to MySQL Database console,

such as Mysql-u root-p

Mysql>use Database

Then use the source command, followed by the script file (for example, the. SQL used here)

Mysql>source D:wcnc_db.sql

MySQL Import:

mysql-hhostname-uusername-ppassword databasename < Backupfile.sql

eg : [email protected] ~]#/usr/local/mysql/bin/mysqldump-h localhost-utest-p xx > Xx.sql

To delete a database:

Mysql> drop databasetest;

Query OK, $ affected (1.84 sec)

Rebuilding libraries and tables
Use mysqldump--no-data to export the table SQL, and then drop the database to CREATE DATABASE, execute the exported SQL file, build the table;

Delete a table
Mysql>drop TABLE MYTABLE;
Clear table
Mysql>delete from MYTABLE;

DELETE FROMtable1

Turn on MySQL log

[Email protected] Log]#/usr/local/mysql/bin/mysql-v

/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.1.39, for Pc-linux-gnu (i686) using ReadLine 5.1, configuration method:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/4E/24/wKioL1RgPbLx9hKCAADe59uvCa4693.jpg "title=" QQ picture 20141110122123.jpg "alt=" Wkiol1rgpblx9hkcaade59uvca4693.jpg "/>

Long_query_time = 2, Unit is S

To view slow log related information:

Mysql> Show variables like ' long_query_time ';

Mysql> Show status like ' Slow_queries ';

5.6 Log Configuration method: MySQL 5.6 Turn on slow log

#log

General_log = On

General_log_file =/usr/local/mysql/log/mysql.log

Slow_query_log = On

Slow_query_log_file =/usr/local/mysql/log/mysql-slow.log

Long_query_time = 2 units is s

#log =/usr/local/mysql/log/mysql.log

#long_query_time =2

#log_slow_queries =/usr/local/mysql/log/slowquery.log

Ps:memcache Start command:

Memcached-d-P 11211-u root-m 1024-c 1024-p/var/run/memcached.pid

This article from the "Operation and maintenance of the road" blog, declined to reprint!

MySQL permissions settings and common configuration