PHP 程式學習筆記本

顯示具有 mysql 標籤的文章。顯示所有文章

2015年10月30日星期五

引用自: Tsung's Blog

詳見：Oracle Announces General Availability of MySQL 5.7

MySQL 5.7 GA Release 釋出

MySQL 5.7 的特色如下：(What’s New in MySQL 5.7)

3x Faster Performance
New Optimizer
Native JSON Support
Multi-source Replication
GIS Spatial Extensions
And other important enhancements
MySQL 5.7 的 Benchmarks 可見：MySQL :: Benchmarks

官方新聞

2014年12月19日星期五

[rails] USE Index in db query .

看到一篇文章:Using indexes in rails: Index your associations
題到因為 order by 與 where 不同 index , 但是只想取出 limit 100 資料時, 就可以強制使用 Use index 去先讓 order by 的欄位排序後取出 100 筆 , 這樣會比較快.

當然最完美解法是, 增加排序欄位與條件欄位的 index .

 
def self.use_index(index)
  from("#{self.table_name} USE INDEX(#{index})")
end

2014年1月6日星期一

MySQL 的 "SET NAMES xxx" 字元編碼問題分析

perl -pi -e 's/latin1/utf8/g' DBNAME.utf8.sql

參考網址:
http://www.vixual.net/blog/archives/310
http://blog.lyhdev.com/2011/06/mysql-mysqldump.html

2013年9月11日星期三

Reduce the size of ibdata in mysql?

來源: http://dba.stackexchange.com/questions/8982/what-is-the-best-way-to-reduce-the-size-of-ibdata-in-mysql

Keep in mind the busiest file in the InnoDB infrastructure is /var/lib/mysql/ibdata1
This file normally houses many classes of information (when innodb_file_per_table is 0)

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
- Rollbacks Segments
- Undo Tablespace
Table Metadata
See Pictorial Representation

Many people create multiple ibdata files hoping for better diskspace management and performance. It does not help.
Unfortunately, OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things:

Makes the table's data and indexes contiguous inside ibdata1
It makes ibdata1 grow because the contiguous data is appended to ibdata1

You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table. To shrink ibdata1 once and for all you must do the following
Step 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)
Step 02) Drop all databases (except mysql schema)
Step 03) Shutdown mysql
Step 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.
Step 05) Delete ibdata1, ib_logfile0 and ib_logfile1
At this point, there should only be the mysql schema in /var/lib/mysql
Step 06) Restart mysql
This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each
Step 07) Reload SQLData.sql into mysql
ibdata1 will grow but only contain table metadata
Each InnoDB table will exist outside of ibdata1
Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.
With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.
I have done this many times in my career as a MySQL DBA
In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.
Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!
If you would like to see how much actual data is stored in MyISAM and InnoDB, please run this query:

其他：人工誤刪除InnoDB ibdata數據文件如何恢复？

mysqldump 參數說明

MySql資料庫備份mysqldump參數選項

範例 : mysqldump --user=root --password=xxxxx --host=your_sql_host --no-create-info --where="created_at >= '20131201000000'" database_name table_name > backup_db.sql

將 latin1 轉成 utf8 資料

mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
    --skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql

mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
    --default-character-set=utf8 DB_NAME < DB_NAME-dump.sql

另一招一行完成:
mysqldump --add-drop-table -u root --password='xxx' database | sed -e 's/CHARSET\=latin1/CHARSET\=utf8\ COLLATE\=utf8_general_ci/g' | iconv -f latin1 -t utf8 | mysql -u root --password='password' database

1. 以 root 身分登入 mysql，將 x10001 資料庫中的 tblItem 資料輸出到 test.sql
mysqldump –password=123456 –allow-keywords -q -Q -c -e –databases x10001 –tables tblItem > test.sql

簡易參數:
-Q: 資料表和欄位名稱部分加入“
-q: 不使用緩衝(buffer)直接輸出到標準輸出裝置(stdout)
-c: 使用完整的 insert 敘述
-e: 使用新的 insert 多行語法
–password: 登入的密碼
–allow-keywords: 允許欄位名稱使用保留字，會自動將保留字前面加上 table 名稱
–databases: 指定傾印的資料庫名稱
–tables: 指傾印的 table 名稱

2. 假如只是要輸出資料表的結構，加入 -d 或 –no-data 參數
mysqldump –password=123456 –allow-keywords -d -Q -e –databases x10001 –tables tblItem > test.sql

3. 假如要傾印整個資料庫的資料表資訊，不要加 –table 參數
mysqldump –password=123456 –allow-keywords -d -q -Q -c -e –databases x10001 > test.sql

PS: 有顏色部分都是需要照自己的方式去設定.

實用程式，為備份或為把資料轉移到另外的SQL伺服器上傾倒一個資料庫或許多資料庫。傾倒將包含創建表或充實表的SQL語句。

shell> mysqldump [OPTIONS] database [tables]

如果你不給定任何表，整個資料庫將被傾倒。

通過執行mysqldump --help，你能得到你mysqldump的版本支援的選項表。

注意，如果你運行mysqldump沒有--quick或--opt選項，mysqldump將在傾倒結果前裝載整個結果集到記憶體中，如果你正在傾倒一個大的資料庫，這將可能是一個問題。

mysqldump支援下列選項：

--add-locks
在每個表傾倒之前增加LOCK TABLES並且之後UNLOCK TABLE。(為了使得更快地插入到MySQL)。

--add-drop-table
在每個create語句之前增加一個drop table。

--allow-keywords
允許創建是關鍵字的列名字。這由表名首碼於每個列名做到。

-c, --complete-insert
使用完整的insert語句(用列名字)。

-C, --compress
如果客戶和伺服器均支援壓縮，壓縮兩者間所有的資訊。

--delayed
用INSERT DELAYED命令插入行。

-e, --extended-insert
使用全新多行INSERT語法。（給出更緊縮並且更快的插入語句）

-#, --debug[=option_string]
跟蹤程式的使用(為了調試)。

--help
顯示一條幫助消息並且退出。

--fields-terminated-by=...
　
--fields-enclosed-by=...
　
--fields-optionally-enclosed-by=...
　
--fields-escaped-by=...
　
--fields-terminated-by=...

這些選擇與-T選擇一起使用，並且有相應的LOAD DATA INFILE子句相同的含義。見7.16 LOAD DATA INFILE語法。

-F, --flush-logs
在開始傾倒前，洗掉在MySQL伺服器中的日誌檔。

-f, --force,
即使我們在一個表傾倒期間得到一個SQL錯誤，繼續。

-h, --host=..
從命名的主機上的MySQL伺服器傾倒資料。缺省主機是localhost。

-l, --lock-tables.
為開始傾倒鎖定所有表。

-t, --no-create-info
不寫入表創建資訊(CREATE TABLE語句）

-d, --no-data
不寫入表的任何行資訊。如果你只想得到一個表的結構的傾倒，這是很有用的！

--opt
同--quick --add-drop-table --add-locks --extended-insert --lock-tables。應該給你為讀入一個MySQL伺服器的盡可能最快的傾倒。

-pyour_pass, --password[=your_pass]
與伺服器連接時使用的口令。如果你不指定“=your_pass”部分，mysqldump需要來自終端的口令。

-P port_num, --port=port_num
與一台主機連接時使用的TCP/IP埠號。（這用於連接到localhost以外的主機，因為它使用 Unix套接字。）

-q, --quick
不緩衝查詢，直接傾倒至stdout；使用mysql_use_result()做它。

-S /path/to/socket, --socket=/path/to/socket
與localhost連接時（它是缺省主機)使用的套接字檔。

-T, --tab=path-to-some-directory
對於每個給定的表，創建一個table_name.sql檔，它包含SQL CREATE 命令，和一個table_name.txt檔，它包含資料。注意：這只有在mysqldump運行在mysqld守護進程運行的同一台機器上的時候才工作。.txt檔的格式根據--fields-xxx和 --lines--xxx選項來定。

-u user_name, --user=user_name
與伺服器連接時，MySQL使用的用戶名。缺省值是你的Unix登錄名。

-O var=option, --set-variable var=option
設置一個變數的值。可能的變數被列在下面。

-v, --verbose
冗長模式。列印出程式所做的更多的資訊。

-V, --version
列印版本資訊並且退出。

-w, --where='where-condition'
只傾倒被選擇了的記錄；注意引號是強制的！
"--where=user='jimf'" "-wuserid>1" "-wuserid<1 br="">
最常見的mysqldump使用可能製作整個資料庫的一個備份：

mysqldump --opt database > backup-file.sql

但是它對用來自於一個資料庫的資訊充實另外一個MySQL資料庫也是有用的：

mysqldump --opt database | mysql --host=remote-host -C database

指定dump出來的語系
mysqldump --default-character-set=big5

or

mysqldump --default-character-set=latin1

2012年12月8日星期六

Mysql 忘記root 密碼

如果忘記了 MySQL 的 root 密碼，可以透過以下方法重新設定：

1. 停止 MySQL server。

# /etc/init.d/mysql stop

2. 啟動 MySQL server，並加上 –skip-grant-tables 參數，便可以略過輸入密碼的過程:

# mysqld -u root --skip-grant-tables & mysql

3. 用 root 連接到 MySQL server，輸入:

mysql -u root

4. 進入 MySQL server 後，輸入以下幾行 SQL 語句:

mysql> use mysql;
mysql> update user set password=PASSWORD("newpass") where User=’root’;
mysql> flush privileges;
mysql> quit

5. 重新啟動 MySQL 即可。

2012年9月6日星期四

[引用]101 Tips to MySQL Tuning and Optimization

來源:101 Tips to MySQL Tuning and Optimization

MySQL Server Hardware and OS Tuning:

1. Have enough physical memory to load your entire InnoDB file into memory – InnoDB is much faster when the file can be accessed in memory rather than from disk.
2. Avoid Swap at all costs – swapping is reading from disk, its slow.
3. Use Battery-Backed RAM.
4. Use an advanced RAID – preferably RAID10 or higher.
5. Avoid RAID5 – the checksum needed to ensure integrity is costly.
6. Separate your OS and data partitions, not just logically, but physically – costly OS writes and reads will impact your database performance.
7. Put your mysql temp space and replication logs on a separate partition than your data – background writes will impact your database when it goes to write/read from disk.
8. More disks equals more speed.
9. Faster disks are better.
10. Use SAS over SATA.
11. Smaller disks are faster than larger disks, especially in RAID configs.
12. Use Battery-Backed Cache RAID controllers.
13. Avoid software raids.
14. Consider using Solid State IO Cards (not disk drives) for your data partition – these cards can sustain over 2GB/s writes for almost any amount of data.
15. On Linux set your swappiness value to 0 – no reason to cache files on a database server, this is more of a web server or desktop advantage.
16. Mount filesystem with noatime and nodirtime if available – no reason to update database file modification times for access.
17. Use XFS filesystem – a faster, smaller filesystem than ext3 and has more options for journaling, also ext3 has been shown to have double buffering issues with MySQL.
18. Tune your XFS filesystem log and buffer variables – for maximum performance benchmark.
19. On Linux systems, use NOOP or DEADLINE IO scheduler – the CFQ and ANTICIPATORY scheduler have been shown to be slow vs NOOP and DEADLINE scheduler.
20. Use a 64-bit OS – more memory addressable and usable to MySQL.
21. Remove unused packages and daemons from servers – less resource stealing.
22. Put your host that use MySQL and your MySQL host in a hosts file – no dns lookups.
23. Never force kill a MySQL process – you will corrupt your database and be running for the backups.
24. Dedicate your server to MySQL – background processes and other services can steal from the db cpu time.

MySQL Configuration:

25. Use innodb_flush_method=O_DIRECT to avoid a double buffer when writing.
26. Avoid O_DIRECT and EXT3 filesystem – you will serialize all your writes.
27. Allocate enough innodb_buffer_pool_size to load your entire InnoDB file into memory – less reads from disk.
28. Do not make innodb_log_file_size too big, with faster and more disks – flushing more often is good and lowers the recovery time during crashes.
29. Do not mix innodb_thread_concurrency and thread_concurrency variables – these two values are not compatible.
30. Allocate a minimal amount for max_connections – too many connections can use up your RAM and lock up your MySQL server.
31. Keep thread_cache at a relatively high number, about 16 – to prevent slowness when opening connections.
32. Use skip-name-resolve – to remove dns lookups.
33. Use query cache if your queries are repetitive and your data does not change often – however using query cache on data that changes often will give you a performance hit.
34. Increase temp_table_size – to prevent disk writes.
35. Increase max_heap_table_size – to prevent disk writes.
36. Do not set your sort_buffer_size too high – this is per connection and can use up memory fast.
37. Monitor key_read_requests and key_reads to determine your key_buffer size – the key read requests should be higher than your key_reads, otherwise you are not efficiently using your key_buffer.
38. Set innodb_flush_log_at_trx_commit = 0 will improve performance, but leaving it to default (1), you will ensure data integrity, you will also ensure replication is not lagging
39. Have a test environment where you can test your configs and restart often, without affecting production.

MySQL Schema Optimization:

40. Keep your database trim.
41. Archive old data – to remove excessive row returns or searches on queries.
42. Put indexes on your data.
43. Do not overuse indexes, compare with your queries.
44. Compress text and blob data types – to save space and reduce number of disk reads.
45. UTF 8 and UTF16 is slower than latin1.
46. Use Triggers sparingly.
47. Keep redundant data to a minimum – do not duplicate data unnecessarily.
48. Use linking tables rather than extending rows.
49. Pay attention to your data types, use the smallest one possible for your real data.
50. Separate blob/text data from other data if other data is often used for queries when blob/text are not.
51. Check and optimize tables often.
52. Rewrite InnoDB tables often to optimize.
53. Sometimes, it is faster to drop indexes when adding columns and then add indexes back.
54. Use different storage engines for different needs.
55. Use ARCHIVE storage engine for Logging tables or Auditing tables – this is much more efficient for writes.
56. Store session data in memcache rather than MySQL – memcache allows for auto-expiring values and prevents you from having to create costly reads and writes to MySQL for temporal data.
57. Use VARCHAR instead CHAR when storing variable length strings – to save space since CHAR is fixed length and VARCHAR is not (utf8 is not affected by this).
58. Make schema changes incrementally – a small change can have drastic effects.
59. Test all schema changes in a development environment that mirrors production.
60. Do NOT arbitrarily change values in your config file, it can have disastrous affects.
61. Sometimes less is more in MySQL configs.
62. When in doubt use a generic MySQL config file.

Query Optimization:

63. Use the slow query log to find slow queries.
64. Use EXPLAIN to determine queries are functioning appropriately.
65. Test your queries often to see if they are performing optimally – performance will change over time.
66. Avoid count(*) on entire tables, it can lock the entire table.
67. Make queries uniform so subsequent similar queries will use query cache.
68. Use GROUP BY instead of DISTINCT when appropriate.
69. Use indexed columns in WHERE, GROUP BY, and ORDER BY clauses.
70. Keep indexes simple, do not reuse a column in multiple indexes.
71. Sometimes MySQL chooses the wrong index, use USE INDEX for this case
72. Check for issues using SQL_MODE=STRICT.
73. Use a LIMIT on UNION instead of OR for less than 5 indexed fields.
74. Use INSERT ON DUPLICATE KEY or INSERT IGNORE instead of UPDATE to avoid the SELECT prior to update.
75. Use a indexed field and ORDER BY instead of MAX.
76. Avoid using ORDER BY RAND().
77. LIMIT M,N can actually slow down queries in certain circumstances, use sparingly.
78. Use UNION instead of sub-queries in WHERE clauses.
79. For UPDATES, use SHARE MODE to prevent exclusive locks.
80. On restarts of MySQL, remember to warm your database, to ensure that your data is in memory and queries are fast.
81. Use DROP TABLE then CREATE TABLE instead of DELETE FROM to remove all data from a table.
82. Minimize the data in your query to only the data you need, using * is overkill most of the time.
83. Consider persistent connections instead of multiple connections to reduce overhead.
84. Benchmark queries, including using load on the server, sometimes a simple query can have affects on other queries.
85. When load increases on your server, use SHOW PROCESSLIST to view slow/problematic queries.
86. Test all suspect queries in a development environment where you have mirrored production data.

MySQL Backup Procedures:

87. Backup from secondary replicated server.
88. Stop replication during backups to prevent inconsistencies on data dependencies and foreign constraints.
89. Stop MySQL altogether and take a backup of the database files.
90. Backup binary logs at same time as dumpfile if MySQL dump used – to make sure replication does not break.
91. Do not trust an LVM snapshot for backups – this could create data inconsistencies that will give you issues in the future.
92. Make dumps per table for easier single table recovery – if data is isolated from other tables.
93. Use –opt when using mysqldump.
94. Check and Optimize tables before a backup.
95. When importing temporarily disable foreign constraints for a faster import.
96. When importing temporarily disable unique checks for a faster import.
97. Calculate size of database/tables data and indexes after each backup to monitor growth.
98. Monitor slave replication for errors and delay with a cron script.
99. Perform Backups regularly.
100. Test your backups regularly.

And finally 101: Perform MySQL monitoring: Moni tis Unveils The World’s First Free On-demand MySQL Monitoring.

For key MySQL health metrics also look at our white paper: The Monitis Internal MSQL Server Monitoring

Monitis is an all-in-one hosted systems monitoring platform for small and medium-sized businesses. 70,000 sysadmins and IT managers use Monitis platform to monitor their websites, applications and networks. Monitis provides free 15-days trial.

2012年9月4日星期二

[Mysql ]QueryCache原理

Mysql Query Cache setting

mysql> SET GLOBAL query_cache_size = 1000000;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| query_cache_size | 999424 |
+------------------+--------+

原理
QueryCache(下面簡稱QC)是根據SQL語句來cache的。一個SQL查詢如果以select開頭，那麼MySQL服務器將嘗試對其使用QC。每個Cache都是以SQL文本作為key來存的。在應用QC之前，SQL文本不會被作任何處理。也就是說，兩個SQL語句，只要相差哪怕是一個字符（例如大小寫不一樣；多一個空格等），那麼這兩個SQL將使用不同的一個CACHE。
不過SQL文本有可能會被客戶端做一些處理。例如在官方的命令行客戶端裡，在發送SQL給服務器之前，會做如下處理：

Mysql >   show variables like '%query_cache%';

| have_query_cache            | YES    |
| query_cache_limit           | 1048576 |
| query_cache_min_res_unit    | 4096 |
| query_cache_size             | 81920    |
| query_cache_type         | ON     |
| query_cache_wlock_invalidate | OFF |

相關設定: http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html

過濾所有註釋
去掉SQL文本前後的空格,TAB等字符。注意，是文本前面和後面的。中間的不會被去掉。

下面的三條SQL裡，因為SELECT大小寫的關係，最後一條和其他兩條在QC裡肯定是用的不一樣的存儲位置。而第一條和第二條，區別在於後者有個註釋，在不同客戶端，會有不一樣的結果。所以，保險起見，請儘量不要使用動態的註釋。在PHP的mysql擴展裡，SQL的註釋是不會被去掉的。也就是三條SQL會被存儲在三個不同的緩存裡，雖然它們的結果都是一樣的。

SELECT * FROM people where name='surfchen';

SELECT * FROM people where /*hey~*/name='surfchen';

SELECT * FROM people where name='surfchen';

目前只有select語句會被cache，其他類似show,use的語句則不會被cache。

因為QC是如此前端，如此簡單的一個緩存系統，所以如果一個表被更新，那麼和這個表相關的SQL的所有QC都會被失效。假設一個聯合查詢裡涉及到了表A和表B，如果表A或者表B的其中一個被更新（update或者delete），這個查詢的QC將會失效。

也就是說，如果一個表被頻繁更新，那麼就要考慮清楚究竟是否應該對相關的一些SQL進行QC了。一個被頻繁更新的表如果被應用了QC，可能會加重數據庫的負擔，而不是減輕負擔。我一般的做法是默認打開QC，而對一些涉及頻繁更新的表的SQL語句加上SQL_NO_CACHE關鍵詞來對其禁用CACHE。這樣可以儘可能避免不必要的內存操作，儘可能保持內存的連續性。

那些查詢很分散的SQL語句，也不應該使用QC。例如用來查詢用戶和密碼的語句——「select pass from user where name='surfchen'」。這樣的語句，在一個系統裡，很有可能只在一個用戶登陸的時候被使用。每個用戶的登陸所用到的查詢，都是不一樣的SQL文本，QC在這裡就幾乎不起作用了，因為緩存的數據幾乎是不會被用到的，它們只會在內存裡佔地方。

存儲塊
在本節裡「存儲塊」和「block」是同一個意思
QC緩存一個查詢結果的時候，一般情況下不是一次性地分配足夠多的內存來緩存結果的。而是在查詢結果獲得的過程中，逐塊存儲。當一個存儲塊被填滿之後，一個新的存儲塊將會被創建，並分配內存（allocate）。單個存儲塊的內存分配大小通過query_cache_min_res_unit參數控制，默認為4KB。最後一個存儲塊，如果不能被全部利用，那麼沒使用的內存將會被釋放。如果被緩存的結果很大，那麼會可能會導致分配內存操作太頻繁，系統系能也隨之下降；而如果被緩存的結果都很小，那麼可能會導致內存碎片過多，這些碎片如果太小，就很有可能不能再被分配使用。

除了查詢結果需要存儲塊之外，每個SQL文本也需要一個存儲塊，而涉及到的表也需要一個存儲塊（表的存儲塊是所有線程共享的，每個表只需要一個存儲塊）。存儲塊總數量=查詢結果數量*2+涉及的數據庫表數量。也就是說，第一個緩存生成的時候，至少需要三個存儲塊：表信息存儲塊，SQL文本存儲塊，查詢結果存儲塊。而第二個查詢如果用的是同一個表，那麼最少只需要兩個存儲塊：SQL文本存儲塊，查詢結果存儲塊。

通過觀察Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每個緩存結果占用的存儲塊。它們的比例如果接近1:2，則說明當前的query_cache_min_res_unit參數已經足夠大了。如果Qcache_total_blocks比Qcache_queries_in_cache多很多，則需要增加query_cache_min_res_unit的大小。

Qcache_queries_in_cache * query_cache_min_res_unit（sql文本和表信息所在的block佔用的內存很小，可以忽略）如果遠遠大於query_cache_size-Qcache_free_memory，那麼可以嘗試減小query_cache_min_res_unit的值。

調整大小
如果Qcache_lowmem_prunes增長迅速，意味著很多緩存因為內存不夠而被釋放，而不是因為相關表被更新。嘗試加大query_cache_size，儘量使Qcache_lowmem_prunes零增長。
啟動參數
show variables like 'query_cache%'可以看到這些信息。
query_cache_limit:如果單個查詢結果大於這個值，則不Cache
query_cache_size:分配給QC的內存。如果設為0，則相當于禁用QC。要注意QC必須使用大約40KB來存儲它的結構，如果設定小於40KB,則相當于禁用QC。QC存儲的最小單位是1024 byte，所以如果你設定了一個不是1024的倍數的值，這個值會被四捨五入到最接近當前值的等於1024的倍數的值。
query_cache_type:
0 完全禁止QC，不受SQL語句控制（另外可能要注意的是，即使這裡禁用，上面一個參數所設定的內存大小還是會被分配）；
1啟用QC，可以在SQL語句使用SQL_NO_CACHE禁用；
2可以在SQL語句使用SQL_CACHE啟用。

query_cache_min_res_unit:每次給QC結果分配內存的大小
狀態
show status like 'Qcache%'可以看到這些信息。
Qcache_free_blocks:當一個表被更新之後，和它相關的cache blocks將被free。但是這個block依然可能存在隊列中，除非是在隊列的尾部。這些blocks將會被統計到這個值來。可以用FLUSH QUERY CACHE語句來清空free blocks。
其他幾個狀態變量的意義：
Qcache_free_memory    表示查詢緩存區現在還有多少的可用內存,如果很小，考慮增加query_cache_size
Qcache_hits        表示查詢緩存區的命中個數，也就是直接從查詢緩存區作出響應處理的查詢個數
Qcache_inserts    表示查詢緩存區此前總過緩存過多少條查詢命令的結果
Qcache_lowmem_prunes   表示查詢緩存區已滿而從其中溢出和刪除的查詢結果的個數
Qcache_not_cached    自mysql進程啟動起，沒有被cache的只讀查詢數量（包括select,show,use,desc等）
Qcache_queries_in_cache 當前被cache的SQL數量
Qcache_total_blocks:在QC中的blocks數。一個query可能被多個 blocks存儲，而這幾個blocks中的最後一個，未用滿的內存將會被釋放掉。例如一個QC結果要佔6KB內存，如果 query_cache_min_res_unit是4KB，則最後將會生成3個blocks，第一個block用來存儲sql語句文本，這個不會被統計到query+cache_size裡，第二個block為4KB，第三個block為2KB（先allocate4KB，然後釋放多餘的2KB）。每個表，當第一個和它有關的SQL查詢被CACHE的時候，會使用一個block來存儲表信息。也就是說，block會被用在三處地方：表信息，SQL文本，查詢結果。
優化提示：如果Qcache_lowmem_prunes 值比較大，表示查詢緩存區大小設置太小，需要增大。
如果Qcache_free_blocks 較多，表示內存碎片較多，需要清理，flush query cache
根據我看的《High Performance MySQL》中所述，關於query_cache_min_res_unit大小的調優
，書中給出了一個計算公式，可以供調優設置參考：
query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
還要注意一點的是，FLUSH QUERY CACHE 命令可以用來整理查詢緩存區的碎片，改善內存使用狀況，
但不會清理查詢緩存區的內容，這個要和RESET QUERY CACHE相區別，不要混淆，後者才是清除查詢緩存區中
的所有的內容。

2012年6月18日星期一

[Mysql] 刪除資料庫內重複的資料欄位.

若是資料庫內有重複的email時, 要做刪除可以使用這個sql 去執行. 但是下之前最好確認過或是備份資料, 否則資料可能會一去不復返...

DELETE FROM users USING users, users AS vtable WHERE vtable.id > users.id AND users.email = vtable.email

2012年6月4日星期一

mysql 日,週,月統計

透過使用 date_format 與 group 的技巧來取得, 日期的統計數字, 不過看起來還是有一點缺點的.

if ('day' == $Period) {
  $sql = "select `Ranking`,RankingType, date_format(`RankingDate`, '%m/%d') as dt, `RankingDate` from `Ranking` where 1 RankingType order by `RankingDate` DESC,RankingType";
 }
 elseif ('week' == $Period) {
  $sql = "select `Ranking`,RankingType, date_format(date_add(`RankingDate`,interval 6-date_format(`RankingDate`,'%w') day),'%m/%d') as dt, date_format(`RankingDate`,'%X %V') as dt2, `RankingDate` from `Ranking` where 1 group by dt2 ,RankingType order by `RankingDate` DESC,RankingType";
 }
 elseif ('month' == $Period) {
  $sql = "select `Ranking`,RankingType, date_format(`RankingDate`,'%Y-%m') as dt, `RankingDate` from `Ranking` where 1 group by dt ,RankingType order by `RankingDate` DESC,RankingType";
 }

2012年5月2日星期三

[轉貼] Mysql 5.1.22 Replication Master to Slave

Mysql 5.1.22 Replication Master to Slave

預設環境Master = 192.168.1.102
Slave = 192.168.1.103要進行資料同步的資料庫 = shianMysql 安裝位置 = /usr/local/mysql注意: 我所安裝的Mysql是用Tarball方式安裝,所以下面的Mysql路徑會有所不一樣 , 請依照你的主機路徑做變更
安裝流程1. 在Master上開一個帳號給Slave
2. 關閉 Master 的 MySQL
3. 備份要同步化的資料庫
4. 編輯 /etc/my.cnf 檔
5. 啟動 Master 的MySQL
6. 查看Master 是否有設定正確
7. 將備份的資料庫檔 scp 到 Slave
8. 關閉 Slave 的 MySQL
9. 編輯 /etc/my.cnf 檔
10. 解開備份資料庫檔案
11. 啟動 Slave 的 MySQL
12. 測試同步化是否成功

開始安裝
一. 設定Master:1. 開放一個帳號給Slave連接使用
# /usr/local/mysql/bin/mysql -u root -p# GRANT REPLICATION SLAVE on *.* to cslave@192.168.1.103 identified by 'cslave0504′;註:
GRANT REPLICATION SLAVE 為MySQL 4.0.2 以後的版本,如果是之前的版本則使用 GRANT FILE
cslave = 帳號
192.168.1.103 = Slave ip
cslave0504 = 密碼
# quit
2. 將MySQL關掉
# /usr/locale/mysql/share/mysql/mysql.server stop
3. 備份要同步化的資料庫
# cd /usr/locale/mysql/var/# tar -zcpf shian.tar.gz shian/註: tar 指令加了參數 p, 將檔案權限一併記錄起來, 解壓縮的時候才不用再變更檔案屬性

4. 編輯 /etc/my.cnf註: 如果在/etc下沒有my.cnf檔,可以從/usr/local/mysql/share/mysql下複製 *.cnf 到 /etc/my.cnf
# cp /usr/local/mysql/share/mysql/my-small.cnf /etc/my.cnf # vi /etc/my.cnf在 [mysqld] 區段加入下面這些參數:
server-id = 1
log-bin = /usr/local/mysql/var/vmcentos-bin
binlog-do-db = shian
註:server-id = 1 <= x其中x為主機id=1,2,3 1為最大所以在master上設server-id為1
log-bin = /usr/local/mysql/var/vmcentos-bin <= 記錄 Master 所有更動 LOG 記錄下來，讓 Slave 過來同步, 上面所寫的位置為我MySQL的位置 ,vmcentos:主機名稱
binlog-do-db = shian <= 對 shian 這資料庫做同步化的記錄 ,如需多個資料庫則必須再新增一行 binlog-do-db = XXX

5. 啟動Mysql
# /usr/local/mysql/share/mysql/mysql.server start

6. 查看 Master 是否有同步化 shian 資料庫
# /usr/local/mysql/bin/mysql -u root -p# SHOW MASTER STATUS;
+———————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+———————+———-+————–+——————+
| vmcentos-bin.000001 | 596 | shian | |
+———————+———-+————–+——————+
如果在 Binlog_Do_DB 有出現所指定要同步化的資料庫如shian , 則表示 Master 正在同步化 shian 資料庫

7. 將備份的資料庫 shian 丟給其它的 Slave server 上# cd /usr/local/mysql/var# scp shian.tar.gz shian@192.168.1.103:/tmp

二. 設定Slave:1. 將MySQL關掉
# /usr/locale/mysql/share/mysql/mysql.server stop
2. 編輯 /etc/my.cnf
如果在/etc下沒有my.cnf檔,可以從/usr/local/mysql/share/mysql下複製 *.cnf 到 /etc/my.cnf
# cp /usr/local/mysql/share/mysql/my-small.cnf /etc/my.cnf# vi /etc/my.cnf在 [mysqld] 區段加入下面這些參數:
server-id=3
log-bin=suse-bin
relay-log=suse-relay-bin
replicate-do-db=shian
replicate-same-server-id
master-host=192.168.1.102
master-user=cslave
master-password=cslave0504
master-port=3306
master-connect-retry=60註:
server-id=3 <= 為主機id=3
log-bin=suse-bin <= Slave 二進位記錄檔
relay-log=suse-relay-bin <= Slave 二進位記錄檔
replicate-do-db=shian <= 指定要同步化的資料庫, 如需多個資料庫則多加 replicate-do-db=xxx 即可
replicate-same-server-id
master-host=192.168.1.102 <= Master ip
master-user=cslave <= Master 所給的帳號
master-password=cslave0504 <= Master 所給的密碼
master-port=3306 <= Master 的 MySQL port
master-connect-retry=60 <= 錯誤發生重試的間隔時間(60秒)

3. 解開 Master 備份好的資料庫檔案
# cd /usr/local/mysql/var# tar -zxvf /tmp/shian.tar.gz
註: 如果解出來的檔案、目錄權限不對的話, 可以用 chown、chmod 來變更

4. 啟動 MySQL
# /usr/local/mysql/share/mysql/mysql.server star
三. 測試是否同步化成功進入Mysql後下 SHOW SLAVE STATUS\G; 這指令 ,
查看 Slave_IO_Running: Yes , Slave_SQL_Running: Yes, 這二個選項都為Yes表示同步化成功,
然後在 Master 主機上新增一個 table 後並新增一筆資料, 然後在 Slave 如也有新增同樣的 table 並也有相同的資料,則表示同步化成功,
如果 Slave 主機出問題或關機 ,並不會影響 Master 運作, 等 Slave 恢復正常後會自己做同步化Master的動作
四. 錯誤訊息1. No argument was provided to –log-bin, and –log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '–log-bin=suse-bin' to avoid this problem.
A: 在 /etc/my.cnf 的[mysqld]裡加上這參數 log-bin=suse-bin, (suse為我的主機名,所以每一台會不一樣)
2. Neither –relay-log nor –relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '–relay-log=suse-relay-bin' to avoid this problem.
A: 在 /etc/my.cnf 的[mysqld]裡加上這參數 relay-log=suse-relay-bin (suse為我的主機名,所以每一台會不一樣)
3. [ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
A: 在 /etc/my.cnf 的[mysqld]裡加上這參數 replicate-same-server-id
參數說明可以參考這篇 http://twpug.net/docs/mysql-5.1/replication.html

五. 結語如果在啟動MySQL失敗或無法同步化資料庫時,可以先查看LOG檔, /usr/local/mysql/var/vmcentos.err 這是我的記錄檔的位置, 如果有記錄檔裡有error or waring訊息,表示有錯誤必須排除,訊息不難懂看一下就可知道哪裡有錯誤
原文著作者 : http://blog.shian.tw/mysql-5122-replication-master-to-slave.html/trackback

2011年11月29日星期二

Mysql 操作語法

操作功能	SQL 語法	說明
建立資料庫	create database 資料庫名稱;
列出所有資料庫	show databases;
刪除資料庫	drop database 資料庫名稱;
使用資料庫	use 資料庫名稱;
建立資料表	create table 資料表名稱( sn integer auto_increment primary key, name char(20), mail char(50), home char(50), messages char(50) );	常用資料庫資料型態 1. INT (整數) 2. CHAR (1~255字元字串) 3. VARCHAR (不超過255字元不定長度字串) 4. TEXT (不定長度字串最多65535字元)
列出資料表欄位資訊	describe 資料表名稱;
修改資料表欄位	alter table 資料表名稱 charange column 原來欄位名稱新欄位名稱資料型態;
新增資料表欄位	alter table 資料表名稱 add column 欄位名稱資料型態;
刪除資料表欄位	alter table 資料表名稱 drop column 欄位名稱;
刪除資料表	drop 資料表名稱 ;
插入欄位資料	insert into 資料表名稱(欄位1,欄位2,欄位3,欄位4, ...... 欄位N) values('值1','值2','值3','值4', ...... '值N');
更新修改欄位資料	update 資料表名稱 set 欄位1='值1',欄位2='值2',欄位3='值3',... 欄位N='值N' where 條件式 (例如 sn='5' 或 name='蘇國憲' );
查詢單一欄位資料	select 欄位名 from 資料表名稱;
查詢多個欄位資料	select 欄位名, 欄位名, 欄位名 from 資料表名稱;
查詢欄位資料的唯一值	select distinct 欄位名 from 資料表名稱;	重複值只列一次
查詢所有欄位資料	select * from 資料表名稱 ;
條件式查詢	select * from 資料表名稱 where 條件式 (例如 sn='5') ;	（=, <, >, !=）
條件式查詢 and	select * from 資料表名稱 where 條件式1 and 條件式2;
條件式查詢 or	select * from 資料表名稱 where 條件式1 or 條件式2;
查詢某一範圍 between	select * from 資料表名稱 where 欄位名 between 值1 and 值2	值為數字
查詢空值欄位的資料	select * from 資料表名稱 where 欄位名 is null	not null
查詢特定筆數資料	select * from 資料表名稱 limit 8, 10;	第9筆開始選取10筆
查詢結果遞增排序	select * from 資料表名稱 order by 欄位名;
查詢結果遞減排序	select * from 資料表名稱 order by 欄位名 desc ;
查詢比對字串列出單一欄位	select 欄位名 from 資料表名稱 where 欄位名 like '%字串%';
查詢比對字串列出所有欄位	select * from 資料表名稱 where 欄位名 like '%字串%';
刪除條件值資料	delete from 資料表名稱 where 條件式 (例如 sn='5' 或 id='91001' );
刪除條件值資料	delete from 資料表名稱 where 條件式1 and 條件式2;
刪除條件值資料	delete from 資料表名稱 where 條件式1 or 條件式2;
比對刪除條件值資料	delete from 資料表名稱 where 欄位名 like '%字串%';

2011年10月17日星期一

線上環境的 MySQL 預設值修改(Jeremy 建議)

MySQL 灌好後, 有某些預設值是有爭議的(或者說, 是很沒意義的), Jeremy 提出了幾點建議~
下述取自 Jeremy Zawodny 的文章: Fixing Poor MySQL Default Configuration Values.
直接取重點, 就是把下面這幾行加到 /etc/mysql/my.cnf 最後面, 或者 /etc/mysql/conf.d/jeremy.cnf 裡.

max_connect_errors = 1844674407370954751
connect_timeout = 20
skip-name-resolve
slave_net_timeout = 30

為何要加這些, 可以參考原文, 或者參考 DK 此篇文章: 無論如何都應該修改的 MySQL 預設值.

2011年9月5日星期一

在MySQL裡讓中文依筆畫排序

為了因應多語系的網頁設計,在資料庫應用上都會採用UTF-8來做為資料編碼.不過UTF-8編碼的中文字卻不能依照中文字的筆劃來做排序.然而這個筆劃排序問題,在Big5編碼下是可以辦到的.

SELECT * FROM `user` ORDER BY CONVERT(`user_name` USING big5),user_name DESC

2011年7月13日星期三

[MySQL] 時間函數操作.

若是你的資料庫時間是使用UNIX 時間戳記來記錄.
要在MySql 下就需要用到 FROM_UNIXTIME 此函數來轉換.
再搭配 year() , month() ,quarter(), week() .

一、年度查詢
查詢本年度的數據
SELECT *
FROM Statistics
WHERE year( FROM_UNIXTIME( `CreateTime` ) ) = year( curdate() )

二、查詢季度數據
查詢數據附帶季度數
SELECT Id, quarter( FROM_UNIXTIME( `CreateTime` ) )
FROM `Statistics`

其他的同前面部分：查詢本季度的數據
SELECT *
FROM Statistics
WHERE quarter( FROM_UNIXTIME( CreateTime ) ) = quarter( curdate( ))

三、查詢月度數據
本月統計(MySQL)

select * from Statistics where month( FROM_UNIXTIME( CreateTime ) ) =

month(curdate()) and year( FROM_UNIXTIME( CreateTime )) = year(curdate())

本週統計(MySQL)

select * from Statistics where month(FROM_UNIXTIME( CreateTime )) =

month(curdate()) and week(FROM_UNIXTIME( CreateTime )) = week(curdate())

四、查詢統計數據
日統計資料.

select sale, date_format(`SaleDate`, '%m/%d') as dt, `SaleDate` from `Sales` where 1 and SaleDate >= '2011-05-01' group by dt order by `SaleDate`;

週統計資料.

select sale, date_format(date_add(`SaleDate`,interval 6-date_format(`SaleDate`,'%w') day),'%m/%d') as dt, date_format(`SaleDate`,'%X %V') as dt2, `SaleDate` from `Sales` where 1 and SaleDate >= '2011-05-01' group by dt2 order by `SaleDate`;

月統計資料.

select sale, date_format(`RankingDate`,'%Y-%m') as dt, `SaleDate` from `Sales` where 1 and SaleDate >= '2011-05-01' group by dt order by `SaleDate`;

2011年4月13日星期三

[引用] Mysql engine type : Myisam Innodb memory

來源: http://miggo.pixnet.net/blog/post/30855147
Mysql的 Engine Type有幾種常用到的，以下是
MySQL 5.0 Reference Manual中之資料，邊看邊快速的紀錄一下各項的差異和使用時機
設定引擎建，議用 ENGINE = MyISAM 方式指定，Type = 為較舊的方法(也支援)

■ MyISAM :

1.效能優，預設引擎，為 Mysql最早使用的引擎
2.使用MyISAM將具備三種檔案：
.frm (table format) .MYD(data file) .MYI(index file)
3.所有資料是以low byte first儲存，數字型key是以high byte first儲存以利索引
4.支援大檔 ( > 63 bit)
5.支援42億(2^32次方) 資料筆數，如果MySQL編譯有加入--with-big-tables 選項可支援多一倍(2^32次方 * 2)資料量
6.不支援事務處理，例如關聯式資料庫
7.AUTO_INCREMENT 更快（至少10%），序列的值被刪除之後就不能再利用

■ InnoDB:

1.具備Commit, Rollback和當掉復原的事務處理能力，可保護使用者資料
2.可進行row-level的鎖定同時維持nonlocking reads，以支援多人同時連線狀態
3.可支援FOREIGN KEY
4.InnoDB是專門為了大容量資料的最大效率進行設計的，CPU效能是其他的disk-based資料庫引擎所不及的
5.InnoDB儲存他的表和 index在一個表空間中，此表空間可能包含幾個檔(有可能在不同disk partitions)，此點和MyISAM不同，MyISAM是一個表一個檔案，這差一點造成InnoDB表個可以非常大，即使是超過系統的file size(例如2GB)都可處理
6.此為Windows安裝檔的默認值
7.InnoDB上可處理存儲超過1TB的數據，可支援例如平均每秒800次插入/更新的負荷
8.支援關聯資料庫

■ InnoDB使用上的限制：

1.不要將MyISAM轉成InnoDB table
2.不要在NFS格式上將 InnoDB設定成data files或log file，檔案容易被鎖住
3.一個表不能包含超過1000 欄
4.內部key最大長度為 3500 bytes，MySQL限制為3072 Bytes
5.每筆資料最長約為8000 Bytes，變長的欄位(例如Varchar/BLOB/TEXT)需小於4000 Bytes.
6.即使InnoDB支援row size大於65535，也不能定義一個包含VARBINARY/VARCHAR的欄位總合大於65535 (會出現錯誤訊息)
7.在依些舊系統中，檔案需小於 2GB，這並不限制InnoDB的使用，但你如果需要大的tablespace則需額外進行設定
8.InnoDB的log file總共大小需小於4GB
9.最小的tablespace 大小為10MB，最大的大小為64TB，這也是table的最大值
10.不支援FULLTEXT
11.InnoDB不保留每個表個的總數統計，也就是count(*)則必須進行index計算
12.在InnoDB 中，AUTO_INCREMENT欄位如果存在，則"必須"定義一個index欄位包含AUTO_INCREMENT欄；在MyISAM 中，AUTO_INCREMENT欄位"可能"為index中的一欄
13.DELETE FROM Table為逐筆資料刪除，非整個table刪除
14.在InnoDB 中，TRUNCATE Table相同於DELETE Table，AUTO_INCREMENT不重置

■ Memory(相同於HEAP):

1.將所有數據保存在RAM中，可提供極快的訪問，但如果關機資料就會消失無法儲存
2.每一個MEMORY Table關聯一個Disk file(.frm)
3.常使用在 create temporary table上，連線結束後就釋放空間
4.預設使用hash indexes(速度非常快且對於建立臨時表格非常有效)
5.MEMORY不支持BLOB 或TEXT列
6.MEMORY使用定長的儲存格式列
7.可支援 AUTO_INCREMENT

■ 相關資料庫語法:

SHOW ENGINES ; //顯示可使用的Engine
SET storage_engine=MYISAM; // 設定此連線時，create table預設的Engine
SHOW CREATE TABLE tbl_name; //顯示資料庫建立語法

2011年1月21日星期五

資安論壇：【資料庫 (Database、SQL) 安全討論版】

訂閱：文章 (Atom)

2015年10月30日 星期五

MySQL 5.7 GA Release 釋出

官方新聞

2014年12月19日 星期五

2014年1月6日 星期一

2013年9月11日 星期三

2012年12月8日 星期六

2012年9月6日 星期四

來源:101 Tips to MySQL Tuning and Optimization

MySQL Server Hardware and OS Tuning:

MySQL Configuration:

MySQL Schema Optimization:

MySQL Backup Procedures:

2012年9月4日 星期二

2012年6月18日 星期一

2012年6月4日 星期一

2012年5月2日 星期三

2011年11月29日 星期二

2011年10月17日 星期一

2011年9月5日 星期一

2011年7月13日 星期三

2011年4月13日 星期三

2011年1月21日 星期五