Saturday, April 26, 2014

MySQL的分區時間戳 - 日期時間

Original post: http://anothermysqldba.blogspot.com/2014/04/mysql-partitions-timestamp-datetime.html

所以最近我意識到,我還沒有談過很多關於MySQL的分區。
在MySQL分區很多好的博客文章已經存在,並且我已經列出了幾個在下面。
我碰巧遇到以下的情況,我希望它證明了幫助他人。

而timestamp數據類型通常用於日期時間的作品更好地與分區。
(時間戳NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

所以下面是一個例子,如何如何解決這個問題。

首先我們有這個簡單的表。

CREATE TABLE `t1` (
`t1_id` int(11) NOT NULL AUTO_INCREMENT,
`field1` varchar(25) DEFAULT NULL,
`field2` int(10) DEFAULT '0',
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`t1_id`),
KEY `tr` (`time_recorded`)
) ENGINE=InnoDB AUTO_INCREMENT=856964


我們希望在time_recorded場分區。 為了讓我們直接將打破它每月。


ALTER TABLE t1
PARTITION BY RANGE ( TO_DAYS(time_recorded) ) (
PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
);
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed


所以,現在什麼....

嗯,我知道我們需要的主鍵更新,如果我們想要的分區。

ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (`t1_id`,`time_recorded`), LOCK=NONE;
Query OK, 0 rows affected (38.96 sec)
Records: 0 Duplicates: 0 Warnings: 0

請注意,我有鎖= NONE這是因為我使用的MySQL 5.6
主鍵更改將不會允許我雖然添加的分區,我還是需要調整的數據類型。 我正在使用共享這個時候LOCKED =。 請仔細閱讀上面的超鏈接獲取更多信息。 如果你碰巧撿不工作一個類型,它通常意味著一個可行的解決方案。

ALTER TABLE t1 CHANGE time_recorded time_recorded datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, LOCK=SHARED;
Query OK, 854312 rows affected (41.89 sec)
Records: 854312 Duplicates: 0 Warnings: 0


所以,現在我們可以添加我們的分區。

ALTER TABLE t1
-> PARTITION BY RANGE ( TO_DAYS(time_recorded) ) (
-> PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
-> PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
-> PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
-> PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
-> PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
-> PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
-> PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
-> PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
-> PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
-> PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
-> PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
-> PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
-> PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
-> );
Query OK, 854312 rows affected (50.74 sec)
Records: 854312 Duplicates: 0 Warnings: 0


我們可以選擇,DELETE,UPDATE,INSERT和每個分區等。 更多關於這裡: https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html

SELECT COUNT(t1_id) FROM t1 PARTITION (Jan2014);
+--------------+
| COUNT(t1_id) |
+--------------+
| 661752 |
+--------------+
1 row in set (0.55 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Feb2014);
+--------------+
| COUNT(t1_id) |
+--------------+
| 64952 |
+--------------+
1 row in set (0.04 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Mar2014);
+--------------+
| COUNT(t1_id) |
+--------------+
| 71336 |
+--------------+
1 row in set (0.04 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Apr2014);
+--------------+
| COUNT(t1_id) |
+--------------+
| 56272 |
+--------------+
1 row in set (0.05 sec)


而這個工程,我們現在要做有一個分區。 我們現在也必須要考慮的分區維護。 格林的博客上給出了一個非常漂亮的自動滴的例子和分區的增加。 誠然,如果你不想放棄你將需要調整的例子分區。
我已經調整了我的表的例子。 請仔細閱讀格林的博客更多的細節。


DROP PROCEDURE IF EXISTS Rotate_t1_Partition;
DELIMITER ;;
CREATE PROCEDURE Rotate_t1_Partition (newPartValue DATETIME)
BEGIN
-- Setup
DECLARE keepStmt VARCHAR(2000) DEFAULT @stmt;
DECLARE partitionToDrop VARCHAR(64);

-- Find and drop the first partition in the table.
SELECT partition_name
INTO partitionToDrop
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_schema='forums_mysql'
AND table_name='t1'
AND partition_ordinal_position=1;
SET @stmt = CONCAT('ALTER TABLE t1 DROP PARTITION ', partitionToDrop);
PREPARE pStmt FROM @stmt;
EXECUTE pStmt;
DEALLOCATE PREPARE pStmt;

-- Add a new partition using the input date for a value limit.
SET @stmt = CONCAT('ALTER TABLE t1 ADD PARTITION (PARTITION ', DATE_FORMAT(newPartValue - interval 1 MONTH, '%b%Y'), ' VALUES LESS THAN (TO_DAYS(\'', DATE_FORMAT(newPartValue, '%Y-%m-%d'),'\')))');
PREPARE pStmt FROM @stmt;
EXECUTE pStmt;
DEALLOCATE PREPARE pStmt;

-- Cleanup
SET @stmt = keepStmt;
END;;
DELIMITER ;


所以,我現在可以更新這個表很容易地以下。

CALL Rotate_t1_Partition('2015-03-01');
Query OK, 0 rows affected (1.11 sec)


你也可以,因為格林的博客指出的那樣,你可以使用這個用NOW()+間隔1個月,你決定為你的分區永遠的時間框架。 格蘭特,你必須記住,如果你傳遞已經是一個分區的日期將會失敗。

要繼續與自動化,您可以將它添加到一個事件。

CREATE EVENT Rotate_t1_Partition
-> ON SCHEDULE EVERY 1 MONTH
-> DISABLE ON SLAVE
-> COMMENT 'Remove oldest partition and add a new one '
-> DO
-> CALL Rotate_t1_Partition(NOW() + interval 1 MONTH);
Query OK, 0 rows affected (0.04 sec)


記住對於上面的例子中的有效時間。 我只是用這個作為一個例子。

Saturday, April 19, 2014

Heartbleed安全與MySQL

Original post: http://anothermysqldba.blogspot.com/2014/04/heartbleed-secure-mysql.html

以及很多關注,是理所當然的,已取得有關heartbleed最近的bug。

我不認為我應該嘗試添加比那些我認為專家已經提到過得多。 如果你還沒有審查以下職位你應該。
它歸結為幾個不同的方面。
  • 更新和第一保護您的操作系統。
    • 如果使用SSL與MySQL然後
      • 停止MySQL的
      • 創建你的新連接的證書。
        • 這應該包括新的到期日期等。
      • 啟動MySQL
修補你的OpenSSL是非常直截了當。 你需要更新到的openssl-1.0.1e-16 +
這很可能是你的代碼庫都會有這樣的更新,至少這是我迄今為止看到的。


# yum list openssl resulted in 1.0.1e-16.el6_5.7


通過的Fedora 20為例

# rpm -q openssl
openssl-1.0.1e-30.fc20.x86_64

#yum update openssl

安裝1:1.0.1電子37.fc20.1

因此,步驟很容易遵循,它是由你來確保你的系統是安全的。

Monday, April 7, 2014

WebScaleSQL安裝解決了...第2部分

Original post: http://anothermysqldba.blogspot.com/2014/04/webscalesql-installation-solved-part-2.html

這是一個後續行動: WebScaleSQL安裝嘗試...第1部分

所以,作為一個典型的怪胎,開發商,白痴或什麼..我沒能RTFM或在這種情況下, 常見問題解答

所以一旦我注意,並沒有只是匆匆進去,我安裝WebScaleSQL正確。
最大的問題是,我使用的是Linux操作系統(甲骨文的Linux VM),它實在是太舊了Oracle庫做一點幫助把它升級。 當我想開始的東西,每個人都可以很容易地使用,只是沒有它。

我下載的Fedora 20 你可以看到,他們已經在使用的GCC 4.8.2。
因此,問題是很簡單的,如果你要使用新技術,更新您的操作系統也是如此。

如果你真的想升級你的GCC和留在你當前的操作系統上這些超鏈接可能是有用的:
下面是我走上得到它安裝的步驟。 一旦依賴到位它是一個基本的源代碼安裝。

#cd /usr/local/
#yum -y install gcc git readline-devel gcc-c++ bison-devel bison cmake ncurses-devel
# gcc -v
gcc version 4.8.2 20131212 (Red Hat 4.8.2-7) (GCC)

#git clone https://github.com/webscalesql/webscalesql-5.6.git
#ln -s webscalesql-5.6 mysql
#groupadd mysql
#useradd -r -g mysql mysql
#cd mysql/
#cmake . -DENABLE_DOWNLOADS=1
-- Successfully downloaded http://googlemock.googlecode.com/files/gmock-1.6.0.zip
-- Configuring done
-- Generating done
-- Build files have been written to: /usr/local/src/webscalesql-5.6

#make
Scanning dependencies of target INFO_BIN
[ 0%] Built target INFO_BIN
Scanning dependencies of target INFO_SRC
[ 0%] Built target INFO_SRC
Scanning dependencies of target abi_check
[ 0%] Built target abi_check
Scanning dependencies of target zlib
[ 1%] Building C object zlib/CMakeFiles/zlib.dir/adler32.co
.....
[100%] Building CXX object mysql-test/lib/My/SafeProcess/CMakeFiles/my_safe_process.dir/safe_process.cc.o
Linking CXX executable my_safe_process
[100%] Built target my_safe_process
#make install
#chmod +x scripts/mysql_install_db
#yum -y install perl-Data-Dumper-Names
#./scripts/mysql_install_db --user=mysql
#chown -R mysql data
#./bin/mysqld_safe &
# ./bin/mysql

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.17 |
+-----------+

mysql> show variables like '%read_only%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| tx_read_only | OFF |
+------------------+-------+

cd mysql-test ; perl mysql-test-run.pl


現在好了,我們得到了它啟動和運行...我們可以探索它...

Friday, April 4, 2014

MySQL的解釋和SQL_NO_CACHE

Original post: http://anothermysqldba.blogspot.com/2014/04/mysql-explain-sqlnocache.html

所以今天我幫助的人與他們的數據庫的性能和過一些寫得不好的查詢就跑。 現在,肯定每個人都會犯錯,我們的目標是盡你所能,以避免它們。

因此,只是一對夫婦的有用的提示做你讓一個鬆散的查詢到您的環境之前。

通過首先解釋總是執行查詢。 這樣簡單的事情做的第一個解釋將確認沒有錯別字(因為它不會運行),並允許您優化查詢。
幾個環節已經存在有關如何使用說明:
我們的目標很簡單。 要在possible_keys和鑰匙(不為null)有效的鑰匙,當它涉及到key_len你不希望每個表是行100S。 如果你能拿到第一key_len是200(只是一個數字我拿起為例)然後下面是5,4,3,2,1,而不是其他200那麼你的查詢應該運行良好。 這是一個非常簡單的和高層次的聲明,我建議你查看列出來了解更多的解釋的超鏈接。 我今天看到查詢有5 +連接和一個子選擇(通過一個連接是更好的where語句)和200 +行,每key_len。 一些調整,可以讓您查詢從200秒下降到1秒或更少。 永遠,永遠,永遠不要解釋。

接下來的提示,嘗試用SQL_NO_CACHE測試你的查詢。 這使您可以測試真實的查詢和優化的最佳方式即可。 一旦它變得緩存(這是否是),那麼它將只運行得更快為您服務。
最後一個音符......看看MySQL的SQL性能的技巧,曾經存在的偽造網站,但現在正處於- https://wikis.oracle.com/pages/viewpage.action?pageId=27263381

Tuesday, April 1, 2014

MySQLUserclone或只是複製的MySQL用戶

Original post: http://anothermysqldba.blogspot.com/2014/04/mysqluserclone-or-just-copy-mysql-users.html

我最近跨越跑了MySQL的論壇發帖說一直在尋找如何將用戶遷移到新的系統。

雖然這可能是做了許多方法,我想這給了我一個機會發揮和演示的mysqluserclone工具。

所以,我有兩個數據庫,只有一個Wiki用戶:


root@localhost [(none)]> select VERSION();
+------------+
| VERSION() |
+------------+
| 5.6.10-log |
+------------+
root@localhost [(none)]> show grants for wikiuser@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wikiuser@localhost |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wikiuser'@'localhost' IDENTIFIED BY PASSWORD '1e09502e61120480' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES ON `wikidb`.* TO 'wikiuser'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+

select VERSION();
+------------+
| VERSION() |
+------------+
| 5.5.30-log |
+------------+
show grants for wikiuser@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'wikiuser' on host 'localhost'



所以現在使用mysqluserclone工具


Usage: mysqluserclone --source=user:pass@host:port:socket --destination=user:pass@host:port:socket joe@localhost sam:secret1@localhost

# mysqluserclone --source=root:<PASSWORD>@localhost:3306 --destination=root:@localhost:3307 wikiuser@localhost wikiuser@localhost
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Cloning 1 users...
# Cloning wikiuser@localhost to user wikiuser@localhost
# ...done.

root@localhost [(none)]> select VERSION();
+------------+
| VERSION() |
+------------+
| 5.5.30-log |
+------------+
1 row in set (0.00 sec)

root@localhost [(none)]> show grants for wikiuser@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wikiuser@localhost |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wikiuser'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES ON `wikidb`.* TO 'wikiuser'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
請注意,它缺少一個密碼,因為我並沒有為用戶設置一個密碼。 哎呀,是不是不夠好。

授予它的偉大工程,如果你想設置新密碼的新用戶帳戶。 我可以通過新密碼作為克隆命令的一部分

mysqluserclone --source=root:<PASSWORD>@localhost:3306 --destination=root:@localhost:3307 wikiuser@localhost wikiuser:<PASSWORD>@localhost


但是,如果我不知道密碼? 我要確保所有的客戶有他們所有的密碼相同。

我還可以做下面的過程。
  • 從DB 1收集補助
  • 編輯文件助學金我想移動
  • 編輯用戶文件添加註釋,使安全的SQL
  • 將文件加載到數據庫2
select CONCAT('SHOW GRANTS FOR `',USER,'`@',HOST,';') as showgrants FROM mysql.user INTO OUTFILE '/tmp/showgrants.sql';

vi /tmp/showgrants.sql

mysql < /tmp/showgrants.sql > /tmp/user_grants.sql

vi /tmp/user_grants.sql
:%s/Grants for/#Grants for/g
:%s/\n/;\r/g
mysql --port=3307 -u root -p < /tmp/user_grants.sql
> show grants for wikiuser@localhost;
+----------------------------------------------------------------------------------------------------------+
| Grants for wikiuser@localhost |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wikiuser'@'localhost' IDENTIFIED BY PASSWORD '1e09502e61120480' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES ON `wikidb`.* TO 'wikiuser'@'localhost' |
+----------------------------------------------------------------------------------------------------------+



現在,我也有同樣的用戶,主機和密碼。