MySQL permissions settings and common configuration

Source: Internet
Author: User
Tags memcached mysql import

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

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.