Linux Learning notes (MySQL operation)

Forgot MySQL Password:

Edit the MySQL Master profile my.cnf add parameters under the [mysqld] field skip-grant

Restart the database service so that you can access the database without authorization Mysql-uroot

Modify the corresponding user password use MySQL;

Update user Setpassword=password (' password ') where user= ' root ';

Flushprivileges; Refresh

Last modified/etc/my.cnf remove skip-grant, restart MySQL service

To view the root user:

Mysql-uroot-p Password -e "Use Mysql;select user,host from user where user= ' root ';"

MYSQ's command history:/root/.mysql_history

It's not safe to delete this file, then point to the black hole, and it won't produce a command history.

Mysql:

-H: Plus IP

-S: You can use the specified sock file

-P: Add port number

-u: Plus user name

-P: plus password

show databases;: Display database

Use database;: Select databases

Show tables;: View tables in the database

See which libraries show databases;
View the table of a library use DB; Show tables;
View the table's fields desc TB;
View the Build Table statement show create TABLE TB;
Which user is currently Select users ();
Current Library Select database ();
Creating a library Create database db1;
CREATE TABLE T1 (' id ' int (4), ' name ' char (40));
View database version select version ();
View MySQL status show status;
modifying MySQL parameters

Show variables like '%timeout% ';

Set global table_open_cache= 128;

View MySQL Queue

Showprocesslist; (equivalent to PS)

Create a regular user and authorize

Grant all on * * to user identified by ' password ';

Grant all on db1.* to ' user ' @ ' 10.0.2.100 ' identified by ' password ';

Grant all on db1.* to ' User3 ' @ ' percent ' identified by ' password ';
Change Password

UPDATE mysql.user SET Password=password ("Newpwd") WHERE user= ' username ';
Inquire

Select COUNT (*) from Mysql.user;

SELECT * from Mysql.db;

SELECT * from mysql.db where host like ' 10.0.% ';
Insert

Update db1.t1 Set name= ' * * * * * * ' where id=1;
Clear table

TRUNCATE TABLE db1.t1;
Delete a table

drop table db1.t1;
Deleting a database

Drop database db1;
Repair table

Repair table tb1 [USE_FRM];(repaired by/data/www/database name/file under this file)

Backup and recovery for MySQL:

Backup Mysqldump-uroot-p db > 1.sql

- D : Just back up the statement that created the table
Recover Mysql-uroot-p DB < 1.sql
Back up only one table mysqldump-uroot-p dbtb1 > 2.sql
Specify character set at backup mysqldump-uroot-p--default-character-set=utf8 db > 1.sql
Restore also specifies the character set mysql-uroot-p--default-character-set=utf8 db < 1.sql

May 8, 2015

By:champly

Linux Learning notes (MySQL operation)