MySQL基本運維命令詳解

mysqldump -u root -p test_db > test_db.sql

# 匯出所有資料庫
mysqldump -u root -p –all-databases > mysql_all.sql 

mysqldump -u root -p test_db test1 > test_test1.sql

mysqldump -u root -p -d --add-drop-table test_db > test.sql 

mysql -u root -p

mysql>source wcnc_db.sql 

mysqldump -u username -p dbname < filename.sql 

mysql -u username -p -D dbname < filename.sql 

mysql -uroot -p //進入mysql控制檯  
mysql -uroot -p password //進入mysql控制檯  
mysql -p //進入mysql控制檯 

# 第一種
quit
# 第二種
exit

mysql> create database [資料庫名]; 

mysql> show databases; 

mysql> drop database [資料庫名]; 

mysql> use [資料庫名]; 

mysql> select database(); 

mysql> show tables;

mysql> show variables like '%char%';

mysql> create table [表名] (
	id varchar(20),
	name varchar(20)
); 

mysql> desc test;  
mysql> show columns from test;  
mysql> describe test;  
mysql> show create table test;  

mysql> drop table [表名]; 

mysql> insert into [表名] values ("001","張三"); 

# 檢視表test中所有資料
mysql> select * from test; 

# 查詢test表中id=001內容
mysql > select * from test where id=001; 

# 查詢test表中已id為0開頭的內容
mysql > select * from test where id like "0%"; 

# 檢視表test中前2行資料
mysql> select * from test order by id limit 0,2; 
# 或者
mysql> select * from test limit 0,2;

mysql> delete from test where id=001;

mysql> update test set name='admin' where id=002;

# 修改test表中name欄位的長度
mysql> alter table test modify column name varchar(30);

# 在表test中新增了一個欄位passtest，型別為int(4)，預設值為0
mysql> alter table test add passtest int(4) default '0';

# 在表test名字更改為test1
mysql> rename table test to test1;

mysql> use mysql;  
mysql> update mysql.user set password='新密碼' where user='使用者名稱';  
mysql> flush privileges;  
mysql> set password for 使用者名稱@localhost=password('你的密碼');  
mysql> flush privileges; 

# 增加一個test使用者，密碼為1234
mysql> insert into mysql.user(Host,User,Password) values("localhost","test",password("1234"));  
mysql> flush privileges;

# 刪除test使用者
mysql> delete from user where user='test' and host='localhost';  
mysql> flush privileges;

# 授權test使用者擁有test_db庫的所有許可權
grant all on test_db.* to test@localhost identified by '123456';

# 授權test使用者擁有test_db庫的select,update許可權
grant select,update on test_db.* to test@localhost;

# 加讀鎖
mysql> flush tables with read lock;

# 解鎖
mysql> unlock tables;

mysql > select user();

service mysqld stop  
/usr/local/mysql/bin/mysqld_safe –user=mysql –skip-grant-tables &  

mysql> use mysql;  
mysql> update user set password=password('123456') where user='root';  
mysql> flush privileges;

# 檢視當前使用的儲存引擎
mysql > show engines;

# 檢視mysql預設儲存引擎
mysql> show variables like '%storage_engine%';

# 檢視mysql系統版本
mysql> select version();

# 檢視mysql庫裡所有表
mysql>show tables from mysql;

# 檢視Mysql埠
mysql>show variables like 'port';

# 檢視mysql庫user表中user、host資訊
mysql> select user,host from mysql.user;