MySQL日常管理

Ayning發表於2015-03-12
MySql

DB2最佳分頁語句

SELECT * FROM (
        SELECT inner2_.*, ROWNUMBER() OVER(ORDER BY ORDER OF inner2_) AS rownumber_ FROM 
                (SELECT * FROM IMS.IMS_PRODUCT WHERE status='G' FETCH FIRST 300 ROWS ONLY) 
                AS inner2_
        ) AS inner1_ 
WHERE rownumber_>1 ORDER BY rownumber_

 

DB2簡單離線備份命令

db2 backup db learning to e:    
備份成功。此備份映像的時間戳記是：20150316104830

db2 restore db learning from E:
C:\Program Files (x86)\IBM\SQLLIB\BIN>db2 restore db learning from E:
SQL2539W  警告！正在復原至與備份映像資料庫相同的現有資料庫。資料庫檔案將被刪除。

想要繼續嗎？（y/n） y
DB20000I  RESTORE DATABASE 命令成功完成。

 

INSERT INTO SELECT 語句

insert into ims.product(OEM_ID, MACHINE, MODEL, PSEDUO_TYPE, PSEDUO_MODEL, MODEL_SYNONYMS, STATUS, CREATED_TIME, LAST_UPDATE_TIME, LAST_UPDATE_BY) 
        select OEM_ID, MACHINE, MODEL, PSEDUO_TYPE, PSEDUO_MODEL, MODEL_SYNONYMS, STATUS, CREATED_TIME, LAST_UPDATE_TIME, LAST_UPDATE_BY from ims.ims_product

 

如何設定遠端連線MySQL

買了臺阿里雲伺服器, 需要從家裡電腦訪問, 那麼需要以下步驟:

首先通過在客戶機上telnet命令檢視伺服器上的3306埠是否開啟了遠端訪問: 

telnet ayning.com 3306
// 正在連線ayning.com...無法開啟到主機的連線。 在埠 3306: 連線失敗

或者在MySQL伺服器上檢視MySQL是否開啟

root@iZ23zo60yjpZ:~# netstat -an | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN     
tcp        0      0 127.0.0.1:45971         127.0.0.1:3306          ESTABLISHED
tcp        0      0 127.0.0.1:3306          127.0.0.1:45968         ESTABLISHED
tcp        0      0 127.0.0.1:45968         127.0.0.1:3306          ESTABLISHED
tcp        0      0 127.0.0.1:45969         127.0.0.1:3306          ESTABLISHED
tcp        0      0 127.0.0.1:3306          127.0.0.1:45972         ESTABLISHED
tcp        0      0 127.0.0.1:3306          127.0.0.1:45969         ESTABLISHED
tcp        0      0 127.0.0.1:3306          127.0.0.1:45970         ESTABLISHED
tcp        0      0 127.0.0.1:45972         127.0.0.1:3306          ESTABLISHED
tcp        0      0 127.0.0.1:45970         127.0.0.1:3306          ESTABLISHED
tcp        0      0 127.0.0.1:3306          127.0.0.1:45971         ESTABLISHED

發現都沒有開啟, 因此需要開發MySQL 3306埠遠端訪問功能. 

如何開啟MySQL 3306埠遠端訪問功能.

1, 修改Mysql配置檔案

編輯/etc/mysql/my.cnf檔案, 註釋掉下面的bind-address=127.0.0.1一行

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address          = 127.0.0.1

儲存並重啟MySQL

2, 修改資料庫User表
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.10.111' IDENTIFIED BY '' WITH GRANT OPTION;
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

那麼現在就可以遠端訪問MySQL了

 

相關文章