Linux Study Notes (MySql operations)

Linux Study Notes (MySql operations)

Forget the MySql password:

Edit the mysql main configuration file my. cnf and add the parameter skip-grant in the [mysqld] Field

Restart the Database Service so that you can access the database without authorizing mysql-uroot.

Change the user password to use mysql;

Update user setpassword = password ('Password') Where user = 'root ';

Flushprivileges; (refresh)

Finally, modify/etc/my. cnf to remove skip-grant and restart the mysql service.

 

View root users:

Mysql-uroot-pPassword-E "use mysql; select user, host from user where user = 'root ';"

 

Command history of mysq:/root/. mysql_history

This is not safe. delete the file first and point to the black hole. No command history will be generated.

 

Mysql:

-H: Add ip

-S: the specified sock file can be used.

-P: add the port number.

-U: Add User Name

-P: Add Password

Show databases;: displays the database

Use database;: select a database

Show tables;: View tables in the database

 

View All databases of show databases;
View the table use db; show tables of a database;
View the desc tb field of the table;
View the table creation statement show create table tb;
Select user ();
Select database ();
Create database db1;
Create table t1 ('id' int (4), 'name' char (40 ));
View the database version select version ();
View the mysql status show status;
Modify mysql Parameters

Show variables like '% timeout % ';

Set global table_open_cache = 128;

View mysql queues

Showprocesslist; (equivalent to ps)

Create and authorize a common user

Grant all on *. * to user identified'Password';

Grant all on db1. * to 'user' @ '10. 0.2.100 'identified'Password';

Grant all on db1. * to 'user3' @ '% 'identified'Password';
Change Password

UPDATE mysql. user SET password = PASSWORD ("newpwd") WHERE user = 'username ';
Query

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 table

Drop table db1.t1;
Delete Database

Drop database db1;
Repair table

Repair table tb1 [use_frm]; (via/data/www/database name/files under this file)

Mysql backup and recovery:

Back up mysqldump-uroot-p db> 1. SQL

-D: only the statement for backing up and creating tables
Restore mysql-uroot-p db <1. SQL
Back up only one table mysqldump-uroot-p dbtb1> 2. SQL
Specify the character set mysqldump-uroot-p -- default-character-set = utf8 db> 1. SQL during Backup
Recovery also specifies the character set mysql-uroot-p -- default-character-set = utf8 db <1. SQL