Category Archives: MySQL

[MySQL] Find in fixed string

Ref. MySQL 的 FIND_IN_SET函數 SELECT * FROM table WHERE FIND_IN_SET(ID, ‘2,5,6,7,8,9,11,21,33,45’)

Posted in MySQL | Leave a comment

[MySQL] Resolve duplicate entry error in MySQL Replication

while [ 1 ]; do if [ $(mysql -uroot -ppassword -e”show slave status G;” | grep “Duplicate entry” | wc -l) -eq 1 ] ; then mysql -uroot -ppassword -e”stop slave; set global sql_slave_skip_counter=1; start slave;”; fi; sleep 1; mysql … Continue reading

Posted in MySQL | Leave a comment

Wisemapping in Ubuntu 14.04 x64 server

Wisemapping 環境 : Ubuntu 14.04 x64 server 檢查系統是否已裝了 java development kit,如果是openjdk就先移除掉 java -version 安裝 Java Development Kit (版本>7) sudo apt-get install software-properties-common python-software-properties sudo add-apt-repository ppa:webupd8team/java sudo apt-get update sudo apt-get install oracle-java7-installer 下載 wisemapping binary檔案 wget https://bitbucket.org/wisemapping/wisemapping-open-source/downloads/wisemapping-v3.0.4.zip 安裝 unzip … Continue reading

Posted in MySQL, Ubuntu, Wisemapping | Leave a comment

Etherpad-lite in Ubuntu 14.04 x64 server

etherpad 安裝基本套件需求 sudo apt-get install gzip git-core curl python libssl-dev pkg-config build-essential 更新安裝nodejs的來源 sudo curl -sL https://deb.nodesource.com/setup | sudo bash – 安裝nodejs sudo apt-get install nodejs 新增etherpad的使用者 sudo useradd etherpad -m -s /bin/bash -r sudo passwd etherpad 將etherpad使用者加入sudoers中 sudo usermod … Continue reading

Posted in etherpad, MySQL, Nagios, Ubuntu | Leave a comment

MySQL 單向備份

參考: 如何在 MySQL 5.5 設定單向資料庫複寫機制 (Replication) Setting up MySQL replication without the downtime 定義 Master => 被複製抄寫的伺服器 Slave => 執行抄寫的伺服器 修改 Master 端的 mysql 設定 vim /etc/my.cnf (或 vim /etc/ysql/my.cnf) #在[mysqld]區段中加入 server-id=1 log-bin=mysql-bin #需要同步的數據庫名 binlog-do-db= radius #避免同步的數據庫名 binlog-ignore-db= mysql 重新啟動 … Continue reading

Posted in MySQL | Leave a comment

MySQL 初始化設定

[root@localhost ~]# mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we’ll need the current password for the … Continue reading

Posted in Linux, MySQL | Leave a comment

MySQL Trigger 備份

目前MySQL每天都有透過mysqldump將資料倒出備份到另外一台NAS去 就會很害怕trigger會被捨棄掉不備份….. 還好~查了一下trigger是會被照顧的 只有store procedure跟function不會 恰恰好這兩個我都沒用到XD “mysqldump will backup by default all the triggers but NOT the stored procedures/functions.” Source

Posted in MySQL | Leave a comment

查詢關鍵字是否存在於以逗號分隔的資料欄位中

這樣有個好處 table的欄位可以開少一點 只是拉出來時如果要個別拆開 就需要程式語言有支援了…. (還有php有支援XD) 只是這邊有一點要小心,逗號前後的空格都有可能會影響比對的部分

Posted in MySQL, PHP | Leave a comment

查詢資料表中重複的資訊

每次要用時都會小忘記一下…. 來記錄看以後會不會記得 (謎之聲:不會….) select * from unit where `schoolid` in (select `schoolid` from unit group by `schoolid` having count(`schoolid`)>1 ) order by `schoolid`

Posted in MySQL | Leave a comment

MySQL模糊搜尋欄位

在使用 select … where … like 的時侯通常會將搜尋的關鍵字以”%”包起來 但是假設關鍵字是欄位值的話? 該怎麼包呢? 這時候可以使用 concat(‘%’, 欄位, ‘%’) 將關鍵字欄位值用%包起來 Ex. SELECT * FROM `nas`, `proxy` where `nas`.`nasname` like concat(‘%’,`proxy`.`proxyserver`,’%’) order by `nas`.`id` asc;

Posted in MySQL | Leave a comment