Python連接Mariadb資料庫(windows環境)

先安裝Python的Mysql連線模組(請用管理員身份開啟命令字元)

pip install MySQL-python

出現錯誤，必須要安裝Microsoft Vistual C++ 14.0

https://www.microsoft.com/zh-TW/download/details.aspx?id=48159

查詢TABLE中TRIGGER和移除

原本要使用MariaDB內建函式來產生UUID，後來覺得不妥，所以要移除

=====================================================

REATE TABLE USER(
USER_ID CHAR(36) NOT NULL DEFAULT 0,
EMAIL VARCHAR(100) NOT NULL UNIQUE,
PWD VARCHAR(200),
NAME VARCHAR(30),
NICK_NAME VARCHAR(50),
SEX CHAR(1),
HEIGHT DECIMAL(5,1),
WEIGHT DECIMAL(5,1),
BIRTHDAY DATE,
AGE INT,
HOME_PHONE VARCHAR(30),
MOBILE VARCHAR(30),
EMAIL2 VARCHAR(100) NOT NULL DEFAULT '',
PERSONAL_KEY VARCHAR(100) NOT NULL,
PRIMARY KEY (USER_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;





CREATE TRIGGER TRIG_USER
  BEFORE INSERT ON USER
  FOR EACH ROW
  SET new.USER_ID = uuid();


==================================================



查詢TABLE中的TRIGGER

SHOW TRIGGERS LIKE 'USER';




移除TRIGGER

DROP TRIGGER IF EXISTS USER;

設定mariadb server 建立資料庫時預設使用utf8mb4格式

修改 /etc/mysql/my.cnf

sudo vim /etc/mysql/my.cnf

在[mysqld]中加上此行

=============================

[mysqld]

character-set-server=utf8mb4

collation-server=utf8mb4_general_ci

 =============================

Mariadb中Table資料的匯出與匯入（含DB匯出匯入）

開發時，有時需要倒一下線上機的資料來測試時


線上機( Ubuntu ) :

mysql -u root -p

SELECT * FROM [TABLE_NAME] INTO OUTFILE "/PATH/FILE_NAME.TXT";


本地測試機 (WINDOWS):

mysql -u root -p

LOAD DATA INFILE "D:\\PATH\\FILE_NAME.TXT" INTO TABLE [TABLE_NAME];


註.反斜線要兩個，不然會失敗


【匯出多個資料表到一個檔案中】

mysqldump -u user -p database_name table_1 table_2 table_3 > filename.sql


================【異機還原】===============

【DB匯出】

mysqldump -u[帳號] -p[密碼]  [DB名稱]>[檔案名稱]

mysqldump -uroot -proot nc > nc.sql

【DB匯入】

mysql -u[帳號] -p[密碼]  [DB名稱] < [檔案名稱]

mysql -uroot -proot nc < nc.sql


註1.

第一次安裝時mariadbDB預設密碼是root，變更密碼後忘了給'root'@'localhost'權限，結果出現Access denied for user 'root'@'localhost'，搞很久最後加上sudo卻才可以登入真奇怪

 sudo  mysql -u root -p


查詢目前port

netstat -anp | grep :3306


重新載入設定檔而不重啟服務

sudo /etc/init.d/mysql reload

nodejs post data to mariadb 中文字相容性問題

檢查mariadb是否為utf-8格式

 mysqladmin -u root -p var | grep -E 'character|collation' | tr -s ' '

nodejs 

var client = require('mariasql');

記得要加上charset :'utf8'

範例

=================================

var client = require('mariasql');

var c =new client({

host:'your ip or dns',

user:'root',

password:'your password',

db:'your database name',

charset :'utf8'

});

=================================

查詢mariadb大小寫敏感性的設定

登入mariadb後

show variables like 'lower%'


windows 預設是1不區分大小寫

unix 預設是0有區分大小寫

參考文件：

http://blog.csdn.net/jesseyoung/article/details/40617031

MariaDB 帳號建立與安全性管理指令

【登入】

mysql -u 帳號 -p


【遠端登入】

mysql -h 遠端位置 -u 帳號 -p



【本機登入】

sudo mysql -u root



在mysql>下

【查帳號】

select user from user;

【查詢帳號權限】

show grants for 帳號;

show grants for 'root'@'localhost'


【建立帳號並本機登入】

create user 帳號 identified by '密碼'


【建立帳號並可遠端登入】

create user '[USER_NAME]'@'%' 帳號 identified by '密碼';

create user 'myuser'@'%' identified by 'password';


【啓用root本機登入權限】

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '<PW>';

FLUSH PRIVILEGES;


【給該帳號和root 一樣的權限】

grant all on *.* to 帳號



【給帳號某個db的權限】

grant all on [DB_NAME].* to [帳號]

Mariadb建立使用者時出現Password hash should be a 41-digit hexadecimal number錯誤

create user '[你的帳號]'@'%' identified by password '[你的密碼]';


使用指令建立使用者時出現

Password hash should be a 41-digit hexadecimal number


解決方式：

select password('[你的密碼明碼]');

將顯示的加密密碼貼到原本明碼的地方就可以建立了

create user '[你的帳號]'@'%' identified by password '[你的加密密碼]';


最後再給權限就可以正常登入了


給全部DB權限
grant all on *.* to '[帳號]@'%';

給某一DB權限
grant all on [DB名稱].* to '[帳號]'@'%';

升級MariaDB指令

先查詢一下目前作業系統：

lsb_release -a 

查詢MariaDB版本：

mysql --version 

開始升級mariadb：


執行apt-get update

sudo apt-get update



針對MariaDB進行升級

sudo apt-get dist-upgrade mariadb-server（含建議更新）
sudo apt-get install --only-upgrade mariadb-server（僅更新套件）

最後再檢查一下版本

mysql --version




收工！

[第二章]在Ubuntu上執行.NET程式安裝Mono平台步驟-Mariadb建置篇

==================================================
MariaDB建置步驟：
==================================================

匯入金鑰

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db

將Mariadb加入source list

sudo apt-get install software-properties-common 
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db 
sudo add-apt-repository 'deb http://ftp.osuosl.org/pub/mariadb/repo/10.0/ubuntu trusty main'

看你目前使用的系統版本

lsb_release -a

用apt-get install 直接安裝

sudo apt-get update

sudo apt-get install mariadb-server

有興趣安裝Galera Cluster(DB叢集)請直接參考下面原文內容

更新內容請參考：https://mariadb.com/kb/en/mariadb/installing-mariadb-deb-files/

[MariaDB] 常用指令

＝＝＝終端機命令列上執行＝＝＝


版本查詢：mysql --version

MariaDB啟動的參數內容：mysqld --verbose --help


＝＝＝ MY SQL 內執行＝＝＝

顯示目前MariaDB的設定值：show variables;show status;

顯示當前資料庫的表格名稱：show tables;

顯示表格的架構碼：describe [TableName] ;

顯示目前連線狀況：show processlist ; 中斷連線 kill [id]


＝＝＝ 全文索引相關 ＝＝＝＝

將索引關掉（大量新增資料時）：alter table [TableName] disable keys ; 開啟 enable keys;









==================DB 操作==========================

SHOW DATABASES;   目前所有資料庫
SHOW TABLES;   目前所有表格
DESCRIBE [TABLE_NAME] 顯示表格的架構


建立與另一個表格相同架構(含PRIVATE KEY)

CREATE TABLE [TABLE_NAME] LIKE [OLD_TABLE]


建立與另一個表格相同架構(不含PRIVATE KEY的空架構)

CREATE TABLE

SELECT * FROM [TABLE_NAME]

=============================查詢==========================

查詢字串後面加上 \G ，改以條列方式呈現

查詢目前AUTO_INCREMENT 增長的ID

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='[QUERY TABLE]'

重新設定AUTO_INCREMENT ID

ALTER [TABLE_NAME] AUTO_INCREMENT=[NUMBER]

=======================INDEX 索引============================

顯示表格中的索引INDEX資料:

SHOW INDEX FROM [TABLE_NAME]  \G

分析SELECT句子中的INDEX狀況

或 SHOW INDEX FROM [TABLE_NAME] WHERE KEY_NAME = '[INDEX_NAME]'

EXPLAIN SELECT * FROM [TABLE_NAME]  \G

新增表格中的索引欄位

ALTER TABLE [TABLE_NAME] ADD INDEX [INDEX_NAME] (COLUMN1,COLUMN2)





=================================================
新增欄位:

ALTER TABLE [TABLE_NAME] ADD COLUMN [COLUMN_NAME]  [TYPE_NAME]

ALTER TABLE USER ADD COLUMN NAME VARCHAR(20)

刪除欄位:

ALTER TABLE [TABLE_NAME] DROP COLUMN [COLUMN_NAME]


新增欄位放置在某欄位後面

ALTER TABLE [TABLE_NAME] ADD COLUMN [COLUMN_NAME]  [TYPE_NAME] AFTER [COLUMN_NAME]

新增欄位放置在第一欄

ALTER TABLE [TABLE_NAME] ADD COLUMN [COLUMN_NAME]  [TYPE_NAME] FIRST





==========================修改===============================

改變原有欄位的名稱、型態

ALTER TABLE [TABLE_NAME] CHANGE COLUMN [OLD_COLUMN_NAME] [NEW_COLUMN_NAME] [COLUMN_TYPE] NOT NULL DEFAULT [DEFAULT_VALUE]

修改欄位預設值

ALTER TABLE [TABLE_NAME] ALTER [COLUMN_NAME] SET DEFAULT [VALUE]

修改表格名稱(表格層)

RENAME [TABLE_NAME] TO [NEW_TABLE_NAME]

修改表格名稱(資料庫層)

RENAME  [TABLE_NAME_1] TO [TABLE_NAME_2] ,

[DATABASE].[TABLE_NAME_0] TO [DATABASE.[TABLE_NAME_1]]


============================備份、還原===========================

備份
mysqldump --user='[USER_NAME]' -p  \
[DATABASE_NAME] [TABLE_NAME]  > BACKUP.SQL

備份整個資料庫
mysqldump -u root -p --no-data [DATABASE_NAME] > schema.sql


還原
mysql --user='[USER_NAME]' -p \
[DATABASE_NAME] < [BACKUP_FILE]

[MariaDB]變更MariaDB的預設儲存引擎InnerDB到TokudB Part 2

為什縻要將預設引擎innerDB改為tokuDB請參考兩者之間的比較（光新增資料效率就差了100倍/秒）

http://www.tokutek.com/tokudb-for-mysql/tokudb-vs-innodb/



查詢mysql版本：mysql --version

10.x.x-MaridB代表新安裝的

5.x.x - MariaDB 代表之前有裝過mysql

執行：dpkg-query -l libevent-*

確定有 libevent-1.4版，沒有的話執行（Ubuntu14預設是裝2.0版，要加裝1.4版不然執行TokuDB時會有錯誤）

先切換身份到root : sudo su ，接著執行

apt-get install libevent-1.4


下載tokuDB(下面網址選mariadb那一個)

http://www.tokutek.com/tokudb-for-mysql/download-community/

將檔案儲存在暫存的資料庫，我安裝在/temp中，沒有的話，執行：mkdir /temp

接著參考此篇安裝步驟：http://docs.tokutek.com/tokudb/tokudb-index-installation.html

1.建立mysql 群組指定為927(如果/etc/group己經有此群組請跳過)

groupadd -g 927 mysql

2.建立mysql 帳號並加入至927的mysql群組中(如果/etc/passwd中己經有此帳號請跳過)

useradd -r -u 927 -g mysql mysql

3.接著建立tokuDB要使用的資料夾

mkdir -pv /opt/tokutek

4.進到該資料夾中將剛剛下載的tokuDB的壓縮檔解壓縮

cd /opt/tokutek

tar xvzf /temp/[你下載的tokuDB壓縮檔名稱]

例.tar xvzf /temp/mariadb-5.5.40-tokudb-7.5.3-linux-x86_64.tar.gz

5.將解開後很長的檔案資料名稱連結到mysql這個名稱

ln -sv [你下載的tokuDB壓縮檔名稱] mysql

例.ln -sv mariadb-5.5.40-tokudb-7.5.3-linux-x86_64 mysql

6.將解壓縮後的資料夾變更擁有者和群組權限

chown -Rv mysql:mysql [你下載的tokuDB壓縮檔名稱]

例.chown -Rv mysql:mysql   mariadb-5.5.40-tokudb-7.5.3-linux-x86_64

7.將support-files資料夾中tokuDB提供的設定檔my-small.cnf蓋掉mysql中的my.cnf檔案（原文中的/etc/my.cnf是錯的，應該是/etc/mysql/my.cnf），記得先備份原有檔案my.cnf為my.cnf.bak

cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak

cp -v support-files/my-small.cnf /etc/mysql/my.cnf

8.接著在/etc/mysql/my.cnf中[mysqld]區段後增加以下內容

datadir = /var/lib/mysql
basedir = /opt/tokutek/mysql
user = mysql
log_error = /var/log/mysql/error.log

以及將原有的socket參數改回原本mysql的路徑

/var/run/mysqld/mysqld.sock

9.重新初始化原有的系統資料表(目前路徑還在/opt/tokutek/mysql喲)

scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql --basedir=/opt/tokutek/mysql

10.將原本/etc/init.d/mysql改成連結到tokuDB的mysql.server（一樣記得將原有的mysql備份起來）

mv /etc/init.d/mysql /etc/init.d/mysql.bak

ln -sv /opt/tokutek/mysql/support-files/mysql.server /etc/init.d/mysql

11.接著還要將hugepage關掉，不然/var/log/mysql/error.log中還會看到這個錯誤

請參考：[第三章]使用TokuDB前先將Huge pages 參數關掉

12.最後啟動mysql 並確認TokuDB是否正常

/etc/init.d/mysql restart

mysql -u [mysql的管理員帳號] -p

例. mysql -u root -p

接著輸入 show plugins會看到tokuDB都是運作中

show plugins;

再來show engines，會看到預設是tokuDB

show engines;

大功告成

＝＝＝＝＝＝＝＝＝＝＝＝＝＝測試＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

試著建立一個新表看看引擎是否預設為TokuDB

create database testdb ;

create table users
(
    id int not null,
    email char(50) not null,
    name char(20) not null
);


接著看users的資料表引擎

show table status from [DatabaseName] where name='[TableName]'

例. show table status from testdb where name='users'

OK !沒問題顯示為TokudB Engine

試著修改表格的engine

alter table [TableName] engine ='[Engine Name]'

例. alter table testdb.user engine ='Aria';

要將預設引擎變更可以執行

set default_storage_engine=[EngineName]

[MariaDB]使用TokuDB前先將Huge pages 參數關掉 Part 3

訊息：TokuDB: Huge pages are enabled, disable them before continuing

可以參考這一篇

How to enable TokuDB in MariaDB


懶得看可以參考以下：




終端機下輸入

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

接著建立一個tokudb.cnf文字檔

======================文字檔內容===================
# See https://mariadb.com/kb/en/how-to-enable-tokudb-in-mariadb/
# for instructions how to enable TokuDB
#
# See https://mariadb.com/kb/en/tokudb-differences/ for differences
# between TokuDB in MariaDB and TokuDB from http://www.tokutek.com/

plugin-load=ha_tokudb

==================================================

接著放在/etc/mysql/conf.d/tokudb.cnf （Ubuntu & Debian）

接著輸入

cat /sys/kernel/mm/transparent_hugepage/enabled

你可以看到原本的[always]被括住了，修改後變[never]被括住了

登入MariaDB後手動啟動tokuDB

INSTALL SONAME 'ha_tokudb';

再執行

show engines;

就可以看到tokuDB在引擎清單



2014.11.1
Q.系統重新啟動後，原本的HugePage又回復成always，要再查一下如何把參數設在啟動中

「修改個人登入設定」

直接將
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
寫在個人設定檔之中

vi ~/.bashrc 最底下

輸入 source ~/.bashrc 重新讀取bash(不用再登出登入)

再看一下是否正常括在[never]的地方
cat /sys/kernel/mm/transparent_hugepage/enabled

「修改系統設定」

在/etc/profile.d/dishugepage.sh，建立一個sh檔

新增底下內容：
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
                echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
               echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
系統重啟後檢查是否正常
cat /sys/kernel/mm/transparent_hugepage/enabled



方法二：修改/etc/rc.local

http://www.chriscalender.com/disabling-transparent-hugepages-for-tokudb/

[MariaDB]Ubuntu上安裝MariaDB與設定 Part 1

終端機下直接下

sudo apt-get update

sudo apt-get install mariadb-server


不記得apt-get 指令的請看這裡



修改設定my.cnf

sudo vim /etc/mysql/my.cnf


將bind-address 註解掉(前面加上#)，這樣才可以遠端連線到這台資料庫(如果你的web和db在同 一台那就不用改了)

執行 mysql --version  就可以看到目前版本

如果之前有安裝過mysql ，版本會是5.x 如果是新安裝的則是10.x

[Code First,Entity Framework6,MySql,MariaDB] max key length is 767 bytes

資料庫底層改用MariaDB，之後出現max key length is 767 bytes的錯誤

參考此篇：

http://stackoverflow.com/questions/24981593/specified-key-was-too-long-max-key-length-is-767-bytes-mysql-error-in-entity-fr


另外出現此錯誤

參考此篇：

http://forums.mysql.com/read.php?38,622151,622151


用裡面中的方法2，將下面這句改放在Globa.asax中 Application_Start()

DbConfiguration.SetConfiguration(new MySql.Data.Entity.MySqlEFConfiguration());

即可正常顯示

char欄位插入中文字與英文字的差別

建立一個char(5)的文字欄位

試著新增‘中字長度六個’的文字

顯示警告，仍然新增成功，將多餘的字元截斷

新增‘1234567890a’的11個英文字

出乎意料，英文字也是在第六個字就截斷了

所以char(5)在中文字和英文字的結果都是一樣的

查詢文字欄位的長度

select length('中')，佔3個byte

select bit_length('中')，佔了24個bit

select char_length('中')，得到1個中文字

結論：計算中文字長度要用char_length（）函數

[Table Schema]文字欄位的考量，應該選擇char還是varchar？

varchar因為有動態選擇大小的好處，所以以前都以varchar為文字欄位的第一選擇，看了一些書上的設計都以char為主要文字考量，上網查了一下資料

最後了解選擇char主要的考量是效能，有興趣的可以參考

MySQL varchar 長度和效能問題


char欄位會將未填滿的空間以空格填滿，所以第一考量是查詢時的where條仵是否正常（總不 能每次都要Trim掉空格吧）


先以describe [TableName] 查詢表格的schema

裡面有兩筆資料

以正常條仵查詢，結果：正常

select * from event_logs where message='name'

查詢條件「後面」增加空格，結果：正常

select * from event_logs where message='name  '

查詢條件「前面」增加空格，結果：不正常

select * from event_logs where message='  name'

看起來char欄位在查詢時會自動將後面空格略過，以length()函數查詢長度也是不含空格的字數

select length(message) from event_logs where message='name  '

結論：接下來文字欄位應該都以char為設計欄位了