Mysql實戰常見問題彙總

引言

項目中使用Mysql經常會遇到修改編碼格式、導入sql、導出sql、windows授權、檢視删除死鎖程序等操作。

特總結如下：

1、Mysql批量導入sql

（1）選擇資料庫 
mysql>use abc; 
（2）設定資料庫編碼 
mysql>set names utf8; 
（3）導入資料（注意sql檔案的路徑） 
mysql>source /home/abc/abc.sql;           

2、Mysql導出為sql（導出資料和表結構）

mysqldump -u使用者名 -p 資料庫名 > 資料庫名.sql

#/usr/local/mysql/bin/mysqldump -uroot -p abc > abc.sql           

敲回車後會提示輸入密碼

或者通過navicat工具導出。

3、導出結果資料

select * from ly_info where enckeyword = "0"  into outfile '/tmp/ly_insert.txt';           

将結果轉為sql語句，參考：

http://blog.csdn.net/laoyang360/article/details/53236018

4、Mysql ut8格式修改，目的：防止出現亂碼。

最簡單的完美修改方法，修改mysql的my.cnf檔案中的字元集鍵值（注意配置的字段細節）：

1）在[client]字段裡加入default-character-set=utf8，

如下：

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8           

2）在[mysqld]字段裡加入character-set-server=utf8，

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
character-set-server=utf8           

3）在[mysql]字段裡加入default-character-set=utf8，

[mysql]
no-auto-rehash
default-character-set=utf8           

驗證方式如下：

使用SHOW VARIABLES LIKE ‘character%’;檢視，發現資料庫編碼全已改成utf8。

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+           

4）如果上面的都修改了還亂碼，那剩下問題就一定在connection連接配接層上。

解決方法是在發送查詢前執行一下下面這句（直接寫在SQL檔案的最前面）：

SET NAMES ‘utf8’;

它相當于下面的三句指令：

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;           

5、mysql遠端windows授權指令

錯誤：Access denied for user ‘root@IP’ (using password: YES)

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

6、查詢、更新操作

SELECT count(1) AS num, 'qd_last' AS info FROM `qd_info` WHERE enckeyword = "0";
update ya_info t1 , ls_info t2 set t1.id_new = t2.id_new  where t1._id = t2._id;
alter table qd_info add `word` varchar(256) NOT NULL DEFAULT '0', add `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;           

7、檢視死鎖表死鎖

show processlist ;
mysql> show processlist;
+--------+------+--------------------+------+---------+------+----------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+--------------------+------+---------+------+----------+------------------------------------------------------------------------------------------------------+
| 219733 | root | 10.200.1.137:38012 | test | Sleep | 1659 | | NULL |
| 219749 | root | 10.200.1.137:38071 | test | Query | 85 | Locked | SELECT count(1) AS num, 't_last' AS info FROM `com_info` WHERE enckeyword = "0"           

解決方法： 關閉用不到的連接配接

mysql> kill 219733 ;           

作者：銘毅天下

轉載請标明出處，原文位址：

http://blog.csdn.net/laoyang360/article/details/53523554