登入

mysql -h192.168.1.110 -uroot -ppassword

登出

quit/exit

查看數據庫

show databases;

用戶權限

#添加

grant select on db.table to 'user'@'host';

grant select,update on *.* to 'test'@'%';

#撤銷

revoke all on *.* from 'test'@'%';

#查看

show grants;

show grants for user@localhost

#刪除用戶

delete from mysql.user where user='' and host='';

#設置密碼

update mysql.user set password=PASSWORD('123456') where user='root';

#配置遠程連接

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

導出

mysqldump -u　用戶名 -p 數據庫名 > 數據庫名.sql

mysqldump -u　root -p dbname > filepath.sql

#To export tofile(data only)

mysqldump -u [user]-p[pass]--no-create-db --no-create-info mydb > mydb.sql

#To export tofile(structure only)

mysqldump -u [user]-p[pass]--no-data mydb > mydb.sql

#To import todatabase

mysql -u [user]-p[pass] mydb < mydb.sql

導入

mysql> use dbname #切到要導入的數據庫

mysql> source filepath.sql

變量查看/修改

show variables like '%slow%';

set global slow_query_log= 'ON';

show status like 'Qca%';

#查看SQL中select條數

show status like 'Com_sel%'; #顯示的是一次會話的值！

show global status like "Com_select";

找回密碼？

1、kill -TERM mysqld

2、conf中加入skip-grant-tables

3、service mysqld restart

4、mysql -uroot -p

5、修改密碼

6、改回原配置并重啟

PURGE BINARY LOGS TO 'mysql-bin.010';

PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

清空表并使自增歸0

TRUNCATE TABLE tablename

Mac無法登陸

Can't connect to MySQL server on '127.0.0.1' (61)

StevenMacBookAir:~ Hobo$ sudo su -

StevenMacBookAir:~ root# nohup /usr/local/mysql/bin/mysqld_safe &

[1] 464

StevenMacBookAir:~ root# appending output to nohup.out

exit

logout

StevenMacBookAir:~ Hobo$

總結

以上是生活随笔為你收集整理的mysql命令实践_MySQL:常用命令行的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。