You are here: Home > FAQ > MySQL database to share common commands super-utility version

FAQ

MySQL database to share common commands super-utility version

1. MySQL common commands
create database name
use databasename
drop database name  
show tables
describe table name
select distinct to remove duplicate files  
mysqladmin drop databasename
select version(),current_date;

2. Modify password:
shell>mysql -u root -p
mysql> update user set password=password(”xueok654123″) where user=’root’;
mysql> flush privileges //  
mysql>use dbname  
mysql>show databases  
mysql>show tables  
mysql>describe user  
3. Grant
Create a can connect to the server from anywhere of a complete super users, but something must use a password to do this

mysql> grant all privileges on *.* to [email protected] identified by ’something’ with

Add new user:
Grant select on database.* to [email protected] server identified by “password”
GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY ’something’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO [email protected]”%” IDENTIFIED BY ’something’ WITH GRANT OPTION;

Delete the authorization
mysql> revoke all privileges on *.* from [email protected]”%”;
mysql> delete from user where user=”root” and host=”%”;
mysql> flush privileges;

Creat a user custom in specific client it363.com login,can access fangcanbd, a particular data base

mysql >grant select, insert, update, delete, create,drop on fangchandb.* to [email protected] it363.com identified by ‘ passwd’

Rename table:
mysql > alter table t1 rename t2;

4. Mysqldump

Backup database

shell> mysqldump -h host -u root -p dbname >dbname_backup.sql

Recover database

shell> mysqladmin -h myhost -u root -p create dbname

shell> mysqldump -h host -u root -p dbname < dbname_backup.sql

To build table discharged instructions:
shell> mysqladmin -u root -p -d databasename > a.sql

To unistall the data of discharged SQL commands, but no need to build table command:
shell> mysqladmin -u root -p -t databasename > a.sql

If you just want the data but not any SQL commands:
   mysqldump -T./ phptest driver

The only specifies the -t parameters can be discharged plain text files, said data directory discharged. ./ said the current directory, namely the same directory as the mysqldump. If you do not specify a driver table, it will discharge the entire database data. Each table will generate two files, one for. SQL file, include building table. the other  for .TXT file. Only contains data, no SQL commands.

5. Queries can be stored in a file and tell mysql queries read from the file instead of waiting for the keyboard. Available outside shell program type redirect utilities to finish the work. For example, if the file my_file.sql have queries, in the SQL they can be performed as follows:

If you want to write in advance in SQL. TXT:
mysql > mysql -h myhost -u root -p database < sql.txt