MySQL SSH Tunnel

聽Jethro和Ike討論 Naticat的強大，如：SSH Tunnel、structure sync 

因為我常用的是phpmyadmin，所以找一下SSH Tunnel + phpmyadmin的解決方法

我有一台VPS機器，ssh需要私鑰登入，MySQL port沒有對外，如何使用phpmyadmin連線？

打開ssh通道

http://stackoverflow.com/questions/15773731/ssh-tunnel-via-phpmyadmin 

$ ssh -N <username>@<Bastian server IP> -L 3307:<internal ip of mysql server>:3306

-N 你不執行任何命令，僅端口轉發( port forward )

3307 本地要轉發的port

Bastian server IP 必須能連到MySQL 伺服器

3306 遠端機器的MySQL端口

因為我電腦使用Windows 7 ，所以這道命令在虛擬機上下的

ex. 

$ ssh -i vps.key -N user@vps.ip -L 3307:localhost:3306

3307:localhost:3306 是指虛擬機的3307端口 轉發到 vps.ip上的localhost 3306端口

檢測

$ telnet localhost 3307

...

5.6.28-76.1-log ... mysql_native_password

VPS上的mysql版本是5.6.28 沒錯

連線

$ mysql -u root -p  -h localhost -P 3307

經過幾次測試後， -P 是無效的，因為虛擬機和VPS的MySQL密碼和版本不一樣，但是都要輸入虛擬機的密碼，只能連到虛擬機的MySQL

原因：

http://serverfault.com/questions/306421/why-mysql-command-line-tool-ignores-port-parameter

當使用 localhost 參數時， MySQL使用sockets。請改用127.0.0.1

$ mysql -u root -p  -h 127.0.0.1 -P 3307

後記，因為虛擬機上沒裝phpmyadmin，所以Windows 7 在 config.inc.php 設不了 127.0.0.1:3307，只能設 192.168.x.x:3307 (虛擬機IP) ，去連VPS的MySQL。但是VPS的MySQL只允許 localhost或127.0.0.1登入

結論，使用Naticat的SSH Tunnel功能在此場景會比較方便，不過私鑰記得轉成ppk去連線

MySQL新增使用者

phpmyadmin
任意主機所有權限
登陸後點上方的 "伺服器: localhost" => "使用者帳號" =>  新增使用者帳號 =>
填寫"帳號"、主機名稱、密碼、重新輸入、還有選擇權限 => 執行

刪除使用者
登陸後點上方的 "伺服器: localhost" => "使用者帳號" => 勾選使用者 => 點擊下方" 刪除已選擇的使用者帳號"方塊的"執行"

CLI
任意主機所有權限
mysql> CREATE USER 'user'@'%' IDENTIFIED WITH mysql_native_password;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
mysql> SET PASSWORD FOR 'user'@'%' = PASSWORD('password');

或簡化
mysql> CREATE USER 'user'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';
20160607出現以下錯誤可以用上面方法解
$ mysql -u root -p -h 127.0.0.1
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)


或只指定部分權限
mysql> CREATE USER 'user'@'%' IDENTIFIED BY 'password';
mysql> GRANT SELECT, INDEX, SHOW DATABASES ON *.* TO 'user'@'%';


刪除使用者
mysql> drop user 'user'@'%';
注意： 要用drop，下面這組直接刪mysql.user無效
mysql> delete from mysql.user where user='user' and host='%';
mysql> flush privileges;

參考資料：
http://coding.memory-forest.com/%E7%94%A8%E5%91%BD%E4%BB%A4%E5%88%97%E7%AE%A1%E7%90%86mysql%E4%BD%BF%E7%94%A8%E8%80%85.html  用命令列管理MySQL使用者

升級XAMPP的phpmyadmin

XAMPP v3.2.1 的phpmyadmin有個bug

搜尋出結果後，放著過一陣子會重刷那一頁，導致之前的SQL和結果不見

所以 先去 phpmyadmin 官網 下載最新版，我解壓縮到 E:\www 下面
然後將 file:C:/xampp/phpMyAdmin/config.inc.php 複製到 E:/www/phpMyAdmin-4.5.0.2-all-languages/ 下面

設定Alias 

因為XAMPP 的 file:C:/xampp/apache/conf/extra/httpd-xampp.conf 裡面含有這行設定

Alias /phpmyadmin "C:/xampp/phpMyAdmin/"

所以我就 在file:C:/xampp/apache/conf/extra/httpd-vhosts.conf 中新增這個設定

Alias /phpmyadmin45 E:\www\phpMyAdmin-4.5.0.2-all-languages

http://localhost/phpmyadmin/ => 訪問XAMPP舊版phpmyadmin ( 4.3.11 )

http://localhost/phpmyadmin45/ => 訪問新版的phpmyadmin (  4.5.0.2 )

但是新版phpmyadmin有個bug，就是左邊資料庫搜尋資料表的 navigation 不會叫Ajax ( http://localhost/phpmyadmin/navigation.php?ajax_request=1&server=3&token=aaa31d2b7f2f8c54544092194e1e9fe8 ) 去搜尋所有資料表名字，只會搜尋該頁的

解法：

http://stackoverflow.com/questions/14245441/how-to-list-all-tables-in-phpmyadmins-left-menu

在 file:E:/www/phpMyAdmin-4.5.0.2-all-languages/config.inc.php 中 加入

$cfg['MaxNavigationItems'] = 200; // 設一個較大的數字，不讓他分頁，就能搜尋所有資料表了

或

phpmyadmin首頁 => 設置 => 導航面板 => 节点中最大项数( The number of items that can be displayed on each page of the navigation tree. )

註： 

第二種設法 是把該設定值存到 phpmyadmin資料庫 的 pma__userconfig.config_data 欄位

ex. 

{"MaxNavigationItems":210,"Server\/hide_db":"","Server\/only_db":"","lang":"zh_CN","collation_connection":"utf8mb4_unicode_ci"}