Category Archives: MySQL

[MySQL] Find in fixed string

Posted on 2016 年 05 月 27 日 by cowman.chiang

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

Posted on 2015 年 12 月 22 日 by cowman.chiang

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 -uroot -ppassword -e"show slave statusG"; done

1 2	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 -uroot -ppassword -e"show slave statusG"; done

Ref. MySQL Replication Error 1062: ‘Duplicate entry for PRIMARY key’

Posted in MySQL | Leave a comment

Wisemapping in Ubuntu 14.04 x64 server

Posted on 2014 年 11 月 11 日 by cowman.chiang

Wisemapping 環境 : Ubuntu 14.04 x64 server 檢查系統是否已裝了 java development kit，如果是openjdk就先移除掉

java -version

1	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

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

1	wget https://bitbucket.org/wisemapping/wisemapping-open-source/downloads/wisemapping-v3.0.4.zip

安裝 unzip

sudo apt-get install unzip

1	sudo apt-get install unzip

解壓縮 wisemapping 檔案

unzip wisemapping-v3.0.4.zip

1	unzip wisemapping-v3.0.4.zip

切換到 wisemapping 目錄中

cd wisemapping-v3.0.4

1	cd wisemapping-v3.0.4

測試執行

java -Dorg.apache.jasper.compiler.disablejsr199=true -jar start.jar
# 看到 INFO:oejs.AbstractConnector:Started SelectChannelConnector@0.0.0.0:8080 代表正常啟動
# 可用瀏覽器觀看 http://ip:8080/wisemapping

java -Dorg.apache.jasper.compiler.disablejsr199=true -jar start.jar

# 看到 INFO:oejs.AbstractConnector:Started SelectChannelConnector@0.0.0.0:8080 代表正常啟動

# 可用瀏覽器觀看 http://ip:8080/wisemapping

安裝 mysql server、nginx

sudo apt-get install mysql-server nginx

1	sudo apt-get install mysql-server nginx

編輯 … Continue reading →

Posted in MySQL, Ubuntu, Wisemapping | Leave a comment

Etherpad-lite in Ubuntu 14.04 x64 server

Posted on 2014 年 11 月 10 日 by cowman.chiang

etherpad 安裝基本套件需求

sudo apt-get install gzip git-core curl python libssl-dev pkg-config build-essential

1	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 -

1	sudo curl -sL https://deb.nodesource.com/setup \| sudo bash -

安裝nodejs

sudo apt-get install nodejs

1	sudo apt-get install nodejs

新增etherpad的使用者

sudo useradd etherpad -m -s /bin/bash -r
sudo passwd etherpad

1 2	sudo useradd etherpad -m -s /bin/bash -r sudo passwd etherpad

將etherpad使用者加入sudoers中

sudo usermod -a -G sudo etherpad

1	sudo usermod -a -G sudo etherpad

切換到etherpad使用者

su - etherpad

1	su - etherpad

下載etherpad檔案

wget https://github.com/ether/etherpad-lite/zipball/master

1	wget https://github.com/ether/etherpad-lite/zipball/master

安裝unzip

sudo apt-get install unzip

1	sudo apt-get install unzip

解壓縮etherpad壓縮檔

unzip master

1	unzip master

將目錄名稱變好看一點

mv ether-etherpad-lite-3ebcaad etherpad-lite

1	mv ether-etherpad-lite-3ebcaad etherpad-lite

切換到etherpad-lite目錄

cd etherpad-lite

1	cd etherpad-lite

複製設定檔範本

cp settings.json.template setting.json

1	cp settings.json.template setting.json

設定etherpad基本環境

vim setting.json
# 找到以下的設定加以替換
   "requireSession" : false,
   "sessionNoPassword" : false,
   "requireAuthentication": false,
   "requireAuthorization": true,
   "users": {
      "admin": {
         "password": "password",
         "is_admin": true
      },
      "user": {
         "password": "password",
         "is_admin": false
      }
   },

vim setting.json

# 找到以下的設定加以替換

"requireSession" : false,

"sessionNoPassword" : false,

"requireAuthentication": false,

"requireAuthorization": true,

"users": {

"admin": {

"password": "password",

"is_admin": true

"user": {

"password": "password",

"is_admin": false

}

試執行，可看 http://ip:9001 是否正常

./bin/run.sh

1	./bin/run.sh

安裝mysql、nginx

sudo apt-get install mysql-server nginx

1	sudo apt-get install mysql-server nginx

建立etherpad資料庫、設定使用者權限

mysql -uroot -p
&gt; create database etherpad;
&gt; grant all privileges on etherpad.* to 'etherpad'@'localhost' identified by 'password';
&gt; exit

mysql -uroot -p

> create database etherpad;

> grant all privileges on etherpad.* to 'etherpad'@'localhost' identified by 'password';

> exit

設定etherpad資料存放使用mysql

vim setting.json
   # 註解使用dirty的部分
   //"dbType" : "dirty",
   //"dbSettings" : {
   //                 "filename" : "var/dirty.db"
   //               },
   # 加入使用mysql的部分
   "dbType" : "mysql",
   "dbSettings" : {
                    "user"    : "etherpad",
                    "host"    : "localhost",
                    "password": "password",
                    "database": "etherpad"
                  },

vim setting.json

# 註解使用dirty的部分

//"dbType" : "dirty",

//"dbSettings" : {

// "filename" : "var/dirty.db"

// },

# 加入使用mysql的部分

"dbType" : "mysql",

"dbSettings" : {

"user" : "etherpad",

"host" : "localhost",

"password": "password",

"database": "etherpad"

試執行，並請連至 http://ip:9001 增加幾筆資料 … Continue reading →

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

MySQL 單向備份

Posted on 2014 年 10 月 24 日 by cowman.chiang

參考：如何在 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

#在[mysqld]區段中加入

server-id=1

log-bin=mysql-bin

#需要同步的數據庫名

binlog-do-db= radius

#避免同步的數據庫名

binlog-ignore-db= mysql

重新啟動 mysql server

service mysqld reboot

1	service mysqld reboot

檢查Master設定是否正常

mysql -uroot -p
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 11530633 | radius       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.06 sec)

mysql -uroot -p

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

1 row in set (0.06 sec)

於Master創建抄寫用的使用者

mysql> CREATE USER '使用者'@'來源位置' IDENTIFIED BY '密碼';

1	mysql> CREATE USER '使用者'@'來源位置' IDENTIFIED BY '密碼';

於Master給予抄寫使用者權限 … Continue reading →

Posted in MySQL | Leave a comment

MySQL 初始化設定

Posted on 2014 年 05 月 27 日 by cowman.chiang

[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 root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] n
 ... skipping.

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] n
 ... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] n
 ... skipping.

Cleaning up...



All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using 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 root user. If you've just installed MySQL, and

you haven't set the root password yet, the password will be blank,

so you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL

root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n

... skipping.

By default, a MySQL installation has an anonymous user, allowing anyone

to log into MySQL without having to have a user account created for

them. This is intended only for testing, and to make the installation

go a bit smoother. You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n] n

... skipping.

Normally, root should only be allowed to connect from 'localhost'. This

ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n

... skipping.

By default, MySQL comes with a database named 'test' that anyone can

access. This is also intended only for testing, and should be removed

before moving into a production environment.

Remove test database and access to it? [Y/n] n

... skipping.

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n] n

... skipping.

Cleaning up...

All done! If you've completed all of the above steps, your MySQL

installation should now be secure.

Thanks for using MySQL!

Posted in Linux, MySQL | Leave a comment

MySQL Trigger 備份

Posted on 2014 年 02 月 25 日 by cowman.chiang

目前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

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

Posted on 2014 年 02 月 21 日 by cowman.chiang

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

Posted in MySQL, PHP | Leave a comment

查詢資料表中重複的資訊

Posted on 2014 年 02 月 21 日 by cowman.chiang

每次要用時都會小忘記一下…. 來記錄看以後會不會記得 (謎之聲：不會….)

select * from unit where `schoolid` in 
      (select `schoolid` from unit group by `schoolid` having count(`schoolid`)>1 ) 
      order by `schoolid`

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模糊搜尋欄位

Posted on 2014 年 02 月 17 日 by cowman.chiang

在使用 select … where … like 的時侯通常會將搜尋的關鍵字以”%”包起來但是假設關鍵字是欄位值的話? 該怎麼包呢? 這時候可以使用 concat(‘%’, 欄位, ‘%’) 將關鍵字欄位值用%包起來 Ex.

SELECT * FROM `nas`, `proxy` where `nas`.`nasname` 
    like concat('%',`proxy`.`proxyserver`,'%') order by `nas`.`id` asc;

1 2	SELECT * FROM `nas`, `proxy` where `nas`.`nasname` like concat('%',`proxy`.`proxyserver`,'%') order by `nas`.`id` asc;

Posted in MySQL | Leave a comment

Category Archives: MySQL

Categories