Recently in SQL Category

Got fatal error 1236 from master when reading data from binary log:
'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

mysql> show global variables like 'gtid_purged'; # show master's gtid_purged

mysql> stop slave;
mysql> set global gtid_purged = '3caafe57-7a46-11e6-a5c0-00505682159e:1-50'; # set slave's gtid_purged as master
mysql> reset master;
mysql> start slave;

MySQL 5.7 第一次啟動時會產生一個臨時密碼, 放在 /var/log/mysqld.log
grep password /var/log/mysqld.log
就可以看到這一行
[Note] A temporary password is generated for root@localhost: xxxxxxxxxxxx

用 root 登入後再改密碼
set password=password('xxxxxxxxxxxx');

ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxxxxxxxxx';

MySQL ODBC

user-pic
Vote 0 Votes

在 Windows 下使用 MySQL ODBC 需留意
32bit 的程式需用 32bit 的 ODBC (作業系統是 64bit 也是如此)
64bit 的程式需用 64bit 的 ODBC
(32bit ODBC 及 64 bit ODBC 可以共存)

在 64bit Windows 下
64bit ODBC 資料來源管理員 C:\Windows\System32\odbcad32.exe
32bit ODBC 資料來源管理員 C:\Windows\SysWOW64\odbcad32.exe

5 Hosts MySQL Cluster

user-pic
Vote 0 Votes

1 Management Server (MGM) 192.168.1.1
2 SQL Nodes (mysqld API) VIP 192.168.1.2, SQL1 IP 192.168.1.3, SQL2 IP 192.168.1.4
2 Data Nodes (NDB) NDB1 IP 192.168.1.5, NDB2 IP 192.168.1.6

共同作業, 所有主機都需裝 MySQL-Cluster-server 及 MySQL-Cluster-client (非必要, 但建議安裝)
wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-Cluster-7.3/MySQL-Cluster-client-gpl-7.3.3-1.el6.x86_64.rpm
wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-Cluster-7.3/MySQL-Cluster-server-gpl-7.3.3-1.el6.x86_64.rpm
rpm -ivh MySQL*.rpm

MGM 設定
mkdir /var/lib/mysql-cluster
cd /var/lib/mysql-cluster
vi config.ini (參考 Initial Configuration of MySQL Cluster)

[ndbd default]
NoOfReplicas=2
DataMemory=1G
IndexMemory=1G

[ndb_mgmd]
hostname=192.168.1.1
datadir=/var/lib/mysql-cluster

[ndbd]
hostname=192.168.1.5
datadir=/usr/local/mysql/data

[ndbd]
hostname=192.168.1.6
datadir=/usr/local/mysql/data

[mysqld]
hostname=192.168.1.3

[mysqld]
hostname=192.168.1.4

設定初始化, config.ini 有異動時執行
ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial

啟動 MGM
ndb_mgmd -f /var/lib/mysql-cluster/config.ini

加到 /etc/rc.local 下次開機啟動
echo ndb_mgmd -f /var/lib/mysql-cluster/config.ini >> /etc/rc.local

Data Nodes 及 SQL Nodes 設定
Data Nodes 及 SQL Nodes 的設定均相同, 設定 MGM 的 IP
vi /etc/my.cnf

[mysqld]
ndbcluster

[mysql_cluster]
ndb-connectstring=192.168.1.1

Data Notes 要 mkdir -p /usr/local/mysql/data

啟動 Data Nodes
ndbd

加到 /etc/rc.local 下次開機啟動
echo ndbd >> /etc/rc.local

啟動 SQL Nodes
service mysql start (與一般 MySQL 啟動一樣)

在 MGM 查看狀態
# ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.1.3 (mysql-5.6.14 ndb-7.3.3, Nodegroup: 0, *)
id=3 @192.168.1.4 (mysql-5.6.14 ndb-7.3.3, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.1 (mysql-5.6.14 ndb-7.3.3)

[mysqld(API)] 2 node(s)
id=4 @192.168.1.5 (mysql-5.6.14 ndb-7.3.3)
id=5 @192.168.1.6 (mysql-5.6.14 ndb-7.3.3)

以上是 MySQL cluster 的部份, 但是這樣有一個問題, 若程式平常是連第1台 SQL Node,
若第一台掛掉了, 就要改程式指向第2台 SQL Node,
若在兩台 SQL Node 跑 keepalived, 起一個 VIP 192.168.1.2, 程式是連 VIP 即可解決此問題
設定可參考 Linux下keepalived+mysql實現高可用

如此任何一台主機掛點都不會影響服務, MGM 是仲裁角色, 避免 Split Brain 狀況,
MGM 雖然只有一台, 但是它掛點並不會影響服務.

任何一台主機都可以關機維護, 若要全部關機重開, 啟動順序是 MGM, Data Nodes (NDB), SQL Nodes (API)

把現有資料轉存到 Data Node
ALTER TABLE tbl_name ENGINE=NDBCLUSTER;

以 ENGINE=NDBCLUSTER 建立 Table 資料才會存到 Data Node
CREATE TABLE tbl_name (col_name column_definitions) ENGINE=NDBCLUSTER;

Symptom:
Drop table shows "Unknown table"
Create table only create tblname.idb, but not tblname.frm, select the tblname shows doesn't exist

Fix procedure:
1. Stop MySQL
  service mysql stop
2. Remove ib_logfile0 and ib_logfile1
  cd /var/lib/mysql
  rm ib_logfile0 ib_logfile1
3. Remove tblname files
  cd /var/lib/mysql/dbname
  rm tblname*
4. Start MySQL
  service mysql start

MySQL initial random password

user-pic
Vote 0 Votes

較新版的 MySQL 在裝 RPM 時, 都會產生一個 random password
A random root password has been set. You will find it in '/root/.mysql_secret'.

在 MySQL 5.5 以上, 若欄位 Type 是 time,date,datetime
在 select 時若使用 like '%中文%' 會出現 Illegal mix of collations for operation 'like'
在寫程式時要對每個欄位進行搜尋, 在執行時可能就會出現時間欄位 like '%中文%' 這種語法,
這在舊版的 MySQL 是不會出現錯誤的.
升到 MySQL 5.5 以上, 必需改成 like binary '%中文%' 即可避免出現錯誤.

Change sql_mode in MySQL 5.6

user-pic
Vote 0 Votes

MySQL 5.6 default sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
這是嚴格模式, 會造成舊版升上來的用戶一些困擾,
例如:
欄位 id 是 int, insert test (id) values ''; 會有錯誤, 因為空字串沒有值, 非嚴格模式會自動變成 0
(Incorrect integer value: '' for column 'id' at row 1)
一個 table 有多個欄位, 若只 insert 其中部份欄位的值, 其他欄位沒有設 default value, 會有錯誤,
非嚴格模式會自動帶入空字串或 0
(In strict mode, the statement is rejected if any of the omitted columns have no default value)

若想要拿掉 sql_mode, 在 /etc/my.cnf 加一行 sql_mode=
若無效的話表示還有其他地方有 my.cnf, 若是裝官方的 rpm, 會是在 /usr/my.cnf, comment 掉
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

改完重啟看是否拿掉了
show variables like 'sql_mode'

tshark -V | egrep -iw "select|update|delete"

可觀察整台機器送了那些 command 出去
tshark 是 wireshark 的 CLI 版本, 在 wireshark 套件中

The INFORMATION_SCHEMA PROCESSLIST Table

The following statements are equivalent:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

SHOW FULL PROCESSLIST

About this Archive

This page is an archive of recent entries in the SQL category.

Software is the previous category.

Travel is the next category.

Find recent content on the main index or look in the archives to find all content.

Monthly Archives