分類彙整: MySQL

Setting up a redundant MySQL with HAST and CARP

Common Address Redundancy Protocol ( CARP )

  1. 角色說明
    主要會有兩種角色, 一種是Host(  Primary ), 一種是Provider ( Secondary )
    Host 主要就是我們提供服務的機器
    Provider 就是當Host 掛掉後, 接手服務的機器
  2. 參數說明 ( 這部份看原文吧, 這是我看OpenBSD上找來的, FreeBSD上敘述的比較少, 請大家搭配著一起服用 )
    vhid
    The Virtual Host ID. This is a unique number that is used to identify the redundancy group to other nodes on the network. Acceptable values are from 1 to 255.
    password
    The authentication password to use when talking to other CARP-enabled hosts in this redundancy group. This must be the same on all members of the group.
    carpdev
    This optional parameter specifies the physical network interface that belongs to this redundancy group. By default, CARP will try to determine which interface to use by looking for a physical interface that is in the same subnet as the ipaddress and mask combination given to the carp(4) interface.
    advbase
    This optional parameter specifies how often, in seconds, to advertise that we’re a member of the redundancy group. The default is 1 second. Acceptable values are from 1 to 255.
    advskew
    This optional parameter specifies how much to skew the advbase when sending CARP advertisements. By manipulating advskew, the master CARP host can be chosen. The higher the number, the less preferred the host will be when choosing a master. The default is 0. Acceptable values are from 0 to 254.
    state
    Force a carp(4) interface into a certain state. Valid states are init, backup, and master.
    group, -group
    Add or remove a carp(4) interface to a certain interface group. By default all carp(4) interfaces are added to the carp group. Each group has a carpdemote counter affecting all carp(4) interfaces belonging to that group. As described below, it can be useful to group certain interfaces together for failover purposes.
    ipaddress
    This is the shared IP address assigned to the redundancy group. This address does not have to be in the same subnet as the IP address on the physical interface (if present). This address needs to be the same on all hosts in the group, however.
    mask
    The subnet mask of the shared IP.
  3. 實作
    要用CARP的功能有兩個方法
    一個是 rebuild kernel

    device carp

    另一個是load Kernel Module, 在/boot/loader.conf加入下面這行

    if_carp_load="YES"

    我個人是傾向用Kernel Module, 這以用freebsd-update 昇級時比較方便.

    接下來先在hasta ( Host ) 這台機器中, 編輯/etc/rc.conf 加入下面的設定

    hostname="hasta.example.org"
    ifconfig_fxp0="inet 192.168.1.51 netmask 255.255.255.0"
    cloned_interfaces="carp0"
    ifconfig_carp0="vhid 1 pass testpass 192.168.1.50/24"

    再編輯 hastb ( Provider ) 的 /etc/rc.conf

    hostname="hastb.example.org"
    ifconfig_fxp0="inet 192.168.1.52 netmask 255.255.255.0"
    cloned_interfaces="carp0"
    ifconfig_carp0="vhid 1 advskew 100 pass testpass 192.168.1.50/24"

    然後Host重開機, 等個幾秒再重開 Provider, 不重開機的話, 也可以試試

    # kldload if_carp.ko
    # ifconfig carp0 create
    # ifconfig carp0 down && ifcofig carp0 up ( 兩台都要, 先Host, 再Provider )

    這樣Host 應該就會拿到192.168.1.50的IP了, 從同網段的機器ping 一下, 看是不是有成功

  4. failback
    在 Host 那台執行

    # sysctl net.inet.carp.preempt=1

    就可以了, 若搭配了HAST, 會滿不建議這樣處理的, 最好還是人工去看狀況怎麼樣, 再做調整及切換

Highly Available Storage  ( HAST )

  1. 角色說明
    primary ( carp 中的 Host ) => 會把資料傳給slave 叫他乖乖的寫進去
    secondary ( carp 中的 provider ) => 乖乖的收 master 的資料來同步
  2. 實作
    編輯兩台機器上的/etc/hast.conf, 加入以下資料

    resource test {
        on hasta {
            local /dev/ad6 ( 看你那顆HD要來做同步 )
            remote 192.168.1.52
        }
        on hastb {
            local /dev/ad6
            remote 192.168.1.51
        }
    }

    及執行下列指令

    # hastctl create test
    # /etc/rc.d/hastd onestart

    在 “primay” 機器上

    hastctl role primary test

    在 “secondary” 機器上

    # hastctl role secondary test

    接下來在 primary 就newfs, mount

    # newfs -U /dev/hast/test
    # mkdir -p /hast/test
    # mount /dev/hast/test /hast/test

    最後在編輯兩台機器/etc/rc.conf

    # hastd_enable="YES"

    這樣系統Boot後HAST就會自已啟動了…

  3. Failover
    在之前的Wiki上是要搭配ifstated一起服用
    新的文件上則是透過devd來做
    作法如下
    編輯兩台機器的 /etc/devd.conf

    notify 30 {
        match "system" "IFNET";
        match "subsystem" "carp0";
        match "type" "LINK_UP";
        action "/usr/local/sbin/carp-hast-switch master";
    };
    
    notify 30 {
        match "system" "IFNET";
        match "subsystem" "carp0";
        match "type" "LINK_DOWN";
        action "/usr/local/sbin/carp-hast-switch slave";
    };

    詳細的意思可以man devd.conf
    接著重啟devd

    # /etc/rc.d/devd restart

    再來就是編輯/usr/local/sbin/carp-hast-switch 了( 兩台都要有 )

    #!/bin/sh
    
    # Original script by Freddie Cash <fjwcash@gmail.com>
    # Modified by Michael W. Lucas <mwlucas@BlackHelicopters.org>
    # and Viktor Petersson <vpetersson@wireload.net>
    
    # The names of the HAST resources, as listed in /etc/hast.conf
    resources="test"
    
    # delay in mounting HAST resource after becoming master
    # make your best guess
    delay=3
    
    # logging
    log="local0.debug"
    name="carp-hast"
    
    # wait_count
    wait_count=7
    
    # end of user configurable stuff
    
    case "$1" in
        master)
            logger -p $log -t $name "Switching to primary provider for ${resources}."
            sleep ${delay}
    
            # Wait for any "hastd secondary" processes to stop
            for disk in ${resources}; do
                while $( pgrep -lf "hastd: ${disk} (secondary)" > /dev/null 2>&1 && [ $wait_count -gt 0 ] ); do
    								logger -p $log -t $name "countdown => ${wait_count}."
    								wait_count=`expr $wait_count - 1`
                    sleep 1
                done
    						wait_count=7
    
                # Switch role for each disk
                hastctl role primary ${disk}
                if [ $? -ne 0 ]; then
                    logger -p $log -t $name "Unable to change role to primary for resource ${disk}."
                    exit 1
                fi
            done
    
            # Wait for the /dev/hast/* devices to appear
            for disk in ${resources}; do
                for I in $( jot 60 ); do
                    [ -c "/dev/hast/${disk}" ] && break
                    sleep 0.5
                done
    
                if [ ! -c "/dev/hast/${disk}" ]; then
                    logger -p $log -t $name "GEOM provider /dev/hast/${disk} did not appear."
                    exit 1
                fi
            done
    
            logger -p $log -t $name "Role for HAST resources ${resources} switched to primary."
    
            logger -p $log -t $name "Mounting disks."
            for disk in ${resources}; do
                mkdir -p /hast/${disk}
                fsck -p -y -t ufs /dev/hast/${disk}
                mount /dev/hast/${disk} /hast/${disk}
    	    ## start mysql server
    	    logger -p $log -t $name "start mysql"
    	    /bin/sh /usr/local/etc/rc.d/mysql-server start
            done
    
        ;;
    
        slave)
            logger -p $log -t $name "Switching to secondary provider for ${resources}."
    
            # Switch roles for the HAST resources
            for disk in ${resources}; do
                if ! mount | grep -q "^/dev/hast/${disk} on "
                then
                else
    	        ## stop mysql server
                    logger -p $log -t $name "stop mysql"
    	        /bin/sh /usr/local/etc/rc.d/mysql-server stop
    	        sleep 0.5
    	        ## umount
                    logger -p $log -t $name "umount ${disk}."
                    umount -f /hast/${disk}
                fi
                sleep $delay
                hastctl role secondary ${disk} 2>&1
                if [ $? -ne 0 ]; then
                    logger -p $log -t $name "Unable to switch role to secondary for resource ${disk}."
                    exit 1
                fi
                logger -p $log -t $name "Role switched to secondary for resource ${disk}."
            done
        ;;
    esac

    這個script我有改了一小部份, 加入了mysql 的啟動, 和最大等待次數

  4. 測試方法
    # ifconfig carp0 down && ifconfig carp0 up

    然後用 hastctl status test 觀看吧

  5. 錯誤回復
    確定那一台的資料比較新, 在舊的那台執行

    # hastctl role init <resource>
    # hastctl create <resource>
    # hastctl role secondary <resource>

    然後觀看 primary 的 HAST status 應該會發現 dirty: 的部份會重跑

MySQL

  1. 請把DB 的 dir 設到HAST 上面
    我的/etc/rc.conf 設定如下

    ## MYSQL
    mysql_enable="YES"
    mysql_dbdir="/hast/test/mysql"
    mysql_args="--bind-address=192.168.1.50 --skip-name-resolve"

    然後把/usr/local/etc/rc.d/mysql-server 的權限設成000
    怕mysql 在開機後自已啟了

注意事項

  1. 不要在 dirty: 還有值的情況下去切換 primary, secondary 會爆炸的
  2. 請打開 promiscuous model 不然完全ping不到vip
  3. 可以把 secondary 這台機器的開機加上delay, 以防整個機房跳電, primary, secondary 同時開機造成split-brain
    編輯/boot/loader.conf

    ## delay secondary boot
    autoboot_delay="240"

參考資料

  1. http://www.freebsd.org/doc/handbook/carp.html
  2. http://www.freebsd.org/doc/handbook/disks-hast.html
  3. http://www.openbsd.org/faq/pf/carp.html
  4. http://developer.51cto.com/art/200509/3863.htm
  5. man hast
  6. man hastctl
  7. man carp
  8. man devd.conf
  9. /usr/src/sys/netinet/ip_carp.c ( 因為我man carp 怎麼樣都沒看到他說 net.inet.carp.preempt 設成1 就會failback……, 所以就開這個來看, 裡面的 http://paste.plurk.com/show/394406/ 有寫到 )

debug

  1. /var/log/message
  2. /var/log/debug.log

截圖
pic

Zend Framework DB Load Balancing (Master/Slave Database)

網路上找來找去……
沒看到有人寫相關的解法….
加上不想用mysql-proxy來解這部份…

所以打算從code 的部份下手
看到最後只能動Zend Framework 的code 了(還沒想到可以不動ZF 的code 而做到DB LB的方法)

我是先從function save 下手去找..

Zend/Table/Row/Abstract.php:    public function save()

[sourcecode language=’php’]
public function save()
{
/**
* If the _cleanData array is empty,
* this is an INSERT of a new row.
* Otherwise it is an UPDATE.
*/
if (empty($this->_cleanData)) {
return $this->_doInsert();
} else {
return $this->_doUpdate();
}
}
[/sourcecode]

ZF會自已做update or insert 的區分

再看
[sourcecode language=’php’]
/**
* @return mixed The primary key value(s), as an associative array if the
* key is compound, or a scalar if the key is single-column.
*/
protected function _doInsert()
{
/**
* A read-only row cannot be saved.
*/
if ($this->_readOnly === true) {
require_once ‘Zend/Db/Table/Row/Exception.php’;
throw new Zend_Db_Table_Row_Exception(‘This row has been marked read-only’);
}

/**
* Run pre-INSERT logic
*/
$this->_insert();

/**
* Execute the INSERT (this may throw an exception)
*/
$data = array_intersect_key($this->_data, $this->_modifiedFields);
$primaryKey = $this->_getTable()->insert($data);

/**
* Normalize the result to an array indexed by primary key column(s).
* The table insert() method may return a scalar.
*/
if (is_array($primaryKey)) {
$newPrimaryKey = $primaryKey;
} else {
$newPrimaryKey = array(current((array) $this->_primary) => $primaryKey);
}

/**
* Save the new primary key value in _data. The primary key may have
* been generated by a sequence or auto-increment mechanism, and this
* merge should be done before the _postInsert() method is run, so the
* new values are available for logging, etc.
*/
$this->_data = array_merge($this->_data, $newPrimaryKey);

/**
* Run post-INSERT logic
*/
$this->_postInsert();

/**
* Update the _cleanData to reflect that the data has been inserted.
*/
$this->_refresh();

return $primaryKey;
}
[/sourcecode]

$primaryKey = $this->_getTable()->insert($data);

程式在
Zend/Adapter/Abstract.php
這邊有
function update()
function insert()

來看
[sourcecode language=’php’]
public function insert($table, array $bind)
{
// extract and quote col names from the array keys
$cols = array();
$vals = array();
foreach ($bind as $col => $val) {
$cols[] = $this->quoteIdentifier($col, true);
if ($val instanceof Zend_Db_Expr) {
$vals[] = $val->__toString();
unset($bind[$col]);
} else {
$vals[] = ‘?’;
}
}

// build the statement
$sql = “INSERT INTO ”
. $this->quoteIdentifier($table, true)
. ‘ (‘ . implode(‘, ‘, $cols) . ‘) ‘
. ‘VALUES (‘ . implode(‘, ‘, $vals) . ‘)’;

// execute the statement and return the number of affected rows
$stmt = $this->query($sql, array_values($bind));
$result = $stmt->rowCount();
return $result;
}
[/sourcecode]

不管insert or update
都是透過query 這個method 去執行sql

所以就在這邊下手吧

[sourcecode language=’php’]
public function query($sql, $bind = array())
{
// connect to the database if needed
if ($sql instanceof Zend_Db_Select) {
if (preg_match(‘/UPDATE|INSERT|REPLACE/i’,$sql->__toString())) {
$this->closeConnection();
$this->_config[‘dbname’] = ‘DB_WRITE’;
}
} else {
if (preg_match(‘/UPDATE|INSERT|REPLACE/i’,$sql)) {
$this->closeConnection();
$this->_config[‘dbname’] = ‘DB_WRITE’;
}
}
$this->_connect();

// is the $sql a Zend_Db_Select object?
if ($sql instanceof Zend_Db_Select) {
$sql = $sql->assemble();
}

// make sure $bind to an array;
// don’t use (array) typecasting because
// because $bind may be a Zend_Db_Expr object
if (!is_array($bind)) {
$bind = array($bind);
}

// prepare and execute the statement with profiling
$stmt = $this->prepare($sql);
$stmt->execute($bind);
// return the results embedded in the prepared statement object
$stmt->setFetchMode($this->_fetchMode);

if ($sql instanceof Zend_Db_Select) {
if (preg_match(‘/UPDATE|INSERT|REPLACE/i’,$sql->__toString())) {
$this->closeConnection();
$this->_config[‘dbname’] = ‘DB_READ’;
$this->_connect();
}
} else {
if (preg_match(‘/UPDATE|INSERT|REPLACE/i’,$sql)) {
$this->closeConnection();
$this->_config[‘dbname’] = ‘DB_READ’;
$this->_connect();
}
}
return $stmt;
}
[/sourcecode]

就是很簡單的去parse $sql 是否有UPDATE,INSERT,REPLACE 之類的寫入語法
若有的話就改config

基本上就是這樣…

如果有人有更好的做法…
拜拖 指教一下小第………..
實在是不想弄到ZF 裡面的code 來達到DB LB的功能..

Performance Tuning Best Practices for MySQL

這是Jay Pipes 在Google 演講時有關MySQL Tuning 的Tips

Direct URL: http://video.google.com/videoplay?docid=2524524540025172110

  • [02:20] Don’t benchmark without a goal. Have a goal like “improve performance by 20%”. Otherwise you’ll waste a lot of time tuning milliseconds out of your application.
  • [02:50] Change just one thing at a time and re-run the benchmarks.
  • [03:40] Disable the query cache by setting the cache size to 0 when running MySQL benchmarks.
  • [05:22] The best tool for profiling MySQL queries is the EXPLAIN command. Understand it!
  • [06:40] Log slow queries and use mysqldumpshow to parse the log. It also has an option (–log-queries-not-using-indexes) of logging any query that does not use an index on a table.
  • [07:40] Jeremy Zawodny wrote the mytop utility for monitoring the threads and overall performance of MySQL servers.
  • [08:55 && 11:30] Repeated queries on an un-indexed field will kill your application faster than anything else.
  • [09:30] Don’t de-normalize just because you think it will be faster. Start with normalized database schemes.
  • [10:15] Server parameter tweaking is not a catch-all. Tuning server parameters can help but it’s very specific to certain situations.
  • [12:05] If you use MyISAM storage engine, exploit covering indexes.
  • [12:50] Ensure good selectivity on index fields.
  • [14:45] On multi-column indexes, pay attention to order of fields within the index definition.
  • [15:40] Be aware that as your database grows, the data in the indexed fields can gradate, deteriorating the usefulness of that index. As you data grows, always examine if the indexes you originally thought are still relevant to the data.
  • [17:02] Example of a common index problem, where an index is created on multiple fields.
  • [20:30] Use the smallest data types possible. Don’t use bigint, when int will do. Or, don’t use char(200), when a varchar or smaller char() would do. Using the right type will fit more records in memory or index key block, meaning fewer reads, resulting in faster performance.
  • [21:30] Consider horizontally spitting many-columned tables if they contain a lot of NULLs or rarely used columns.
  • [23:35] Get rid of surrogate keys (with example).
  • [24:05 && 33:20] Be an SQL programmer who thinks in sets, not procedural programming paradigms.
  • [24:35] InnoDB can’t optimize SELECT COUNT(*) queries. Use counter tables! That’s how to scale InnoDB.
  • [27:20] Always try to isolate index fields on one side of condition in a query (with example).
  • [28:20] Avoid using CURRENT_DATE() as it invalidates the cache.
  • [29:34] Example of using calculated fields when searching on top level domain. Idea – put a reversed TLD in the table.
  • [33:20] Avoid correlated subqueries. Think in sets not loops! Here is a great article on visualizing SQL joins.
  • [34:50] Example of using derived tables to avoid correlated subqueries.
  • [36:25] Be aware of global and per-thread server variables.
  • [37:50] Enable query cache if your application is doing a lot more reads than writes!
  • [28:50] MySQL uses MyISAM for internal data storage.
  • [40:00] MySQL loves ram!
  • [40:35] Q and A.

PS: Jay Pipes 是 “破 MySQL” 一書的作者。

Tuning MySQL ..

因為被slow query 搞的很煩….網站速度一直起不來
因為Database 設計上的問題…..(幾個比較常用的Table 大蓋都差不多有70萬個Record )
且因為我Table Engine 是 MyISAM
用Rep 的話….若是crash 要修…好像很麻煩…
所以變成要用Rep 的話最好能先轉成InnoDB
不過因為我Slow Query 很多…若是我Rep Master Lock 的話
Slave 也會卡住…..
也不是完全的解決了Slow Query這問題….
所以我想最完美的解法可能是
Rep + Aging…
不過要花不少時間來整個改……
當前沒這麼多時間讓我來用這些…
所以我目前的解法是…
加Ram 然後把MySQL 的Ram 讓他吃愈多愈好…
Ref
MYSQL優化詳細介紹!

7.5.2. Tuning Server Parameters

Section 5.1.3, “System Variables”

5.1.2. Command Options

Section 5.1.5, “Status Variables”.

5.2.4. The Slow Query Log

結論就是RAM 有多少吃多少…
讓MySQL可以用的Ram 愈多愈好就沒錯了…

PS
FreeBSD 因為單一Process 預設只能吃到512MB 的Ram
所以在/boot/loader.conf 也要調一下

FreeBSD limits the size of a process to 512MB, even if you have much more RAM available on the system. So you may get an error such as this:

Out of memory (Needed 16391 bytes)

In current versions of FreeBSD (at least 4.x and greater), you may increase this limit by adding the following entries to the /boot/loader.conf file and rebooting the machine (these are not settings that can be changed at run time with the sysctl command):

kern.maxdsiz=”1073741824″ # 1GB
kern.dfldsiz=”1073741824″ # 1GB
kern.maxssiz=”134217728″ # 128MB

For older versions of FreeBSD, you must recompile your kernel to change the maximum data segment size for a process. In this case, you should look at the MAXDSIZ option in the LINT config file for more information.

注意一下…調到超過2G 要注意一下..
我調到4G 有發生慘事….
刻在另一篇..

super-smack(MySQL Benchmark tool)

剛剛在Joehorn 的個人版上看到的….
我整個很lag…
ports 裡面已經有了……
裝好後..
cp /usr/local/share/super-smack/* ~
才會有
select-key.smack和update-select.smack 這兩個檔案….
執行的話..就在console 下
#super-smack -d mysql select-key.smack 80 100
我的blog 機是FreeBSD 7.0 R
CPU: Intel(R) Xeon(TM) CPU 2.60GHz (2591.61-MHz 686-class CPU),
RAM:2G
HD: 10K SCSI 36.4G
FS: UFS2
數據是
Query_type num_queries max_time min_time q_per_s
select_index 8000 102 0 1164.87
update_index 8000 73 0 1164.87

/root>ldd /usr/local/libexec/mysqld
/usr/local/libexec/mysqld:
librt.so.1 => /usr/lib/librt.so.1 (0x2858c000)
libz.so.4 => /lib/libz.so.4 (0x28591000)
libwrap.so.5 => /usr/lib/libwrap.so.5 (0x285a3000)
libcrypt.so.4 => /lib/libcrypt.so.4 (0x285aa000)
libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0x285c3000)
libm.so.5 => /lib/libm.so.5 (0x286b8000)
libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x286cd000)
libthr.so.3 => /lib/libthr.so.3 (0x286d8000)
libc.so.7 => /lib/libc.so.7 (0x286eb000)
另外一台工作機是
FreeBSD 6.2R
CPU: Intel(R) Xeon(R) CPU 5130 @ 2.00GHz (2000.08-MHz 686-class CPU)
RAM: 3G
HD:SAS 15k 好像..
Query_type num_queries max_time min_time q_per_s
select_index 8000 22 8 3012.96
update_index 8000 28 8 3012.96

db# ldd /usr/local/libexec/mysqld
/usr/local/libexec/mysqld:
libz.so.3 => /lib/libz.so.3 (0x28468000)
libwrap.so.4 => /usr/lib/libwrap.so.4 (0x28479000)
libcrypt.so.3 => /lib/libcrypt.so.3 (0x28480000)
libstdc++.so.5 => /usr/lib/libstdc++.so.5 (0x28498000)
libm.so.4 => /lib/libm.so.4 (0x28563000)
libpthread.so.2 => /lib/libpthread.so.2 (0x28579000)
libc.so.6 => /lib/libc.so.6 (0x2859e000)
我也想要破12K 呀…..

MySQL Active Report

一如往常..在看訂閱的rss 的時後..
其中這篇
有介紹幾個mysql 的工具..
其中mytop 已經快樂使用中…
另一個就是
MySQL Active Report
看起來挺不錯的..

一樣在ports 裡面也有..
#cd /usr/ports/databases/mysqlard/
#make install clean

裝好後的提示似乎不是很完整

1) Add the ‘mysqlar’ user with USAGE privileges to your mysql server:

$ mysql -u root -p mysql
mysql> GRANT USAGE ON *.* TO mysqlar@localhost;
mysql> FLUSH PRIVILEGES;

2) Add the mysqlard crontab to root’s crontab:

*/5 * * * * /usr/bin/env hourly=1 daily=1 weekly=1 monthly=1
/usr/local/bin/mysqlar_graph > /dev/null

3) Add a line like this to your apache’s httpd.conf:

Alias /mysqlar/ /usr/local/www/mysqlard/
<Directory “/usr/local/www/mysqlard”>
Options Indexes FollowSymLinks MultiViews ExecCGI
AllowOverride All
Order allow,deny
Allow from all
DirectoryIndex mysqlar.php
</Directory>

照著ports 上的提示做完後
還有兩個地方要做
1.
cp /usr/local/www/mysqlard/mysqlar.daily /etc/periodic/daily
cp /usr/local/www/mysqlard/mysqlar.weekly /etc/periodic/weekly/
cp /usr/local/www/mysqlard/mysqlar.mounthly /etc/periodic/monthly/
2.
#vim /etc/rc.conf
mysqlard_enable=”YES”
#/usr/local/etc/rc.d/mysqlard start

這樣就大功告成了….

找出Table 中 資料相同的Record

因為有遇到這個問題
所以特別寫出來備忘
SELECT *
FROM bb_downloads a
WHERE
(SELECT count( * )
FROM bb_downloads b
WHERE b.post_id = a.post_id
AND b.url = a.url
) >1
ORDER BY `post_id`,`download_times` DESC

reference

UPDATE: 2007/10/14
找出不相同的資料
非常的easy
select r.user_id from rtable r WHERE r.user_id not in (select a.user_id from atable a)
刪除不相同的資料
delete from rtable WHERE user_id not in (select a.user_id from atable a)

MySQL 4.1.x編碼問題(UTF8)

剛剛寫code發現在phpMyAdmin裡面的中文看起來非常正常 不過程式print 卻都是亂碼,很怪!! 後來google 了一下,發現是
有 SUPER 權限的 user 在連線的時候不會去執行 init_connect

所以說即使我的 init_connect=’SET NAMES utf8′ 還是沒用
因為我db是用root連的……….
所以才會是亂碼~~ :*-):
直接去開了一個新帳號來用就好了。

ref.

MySQL 4.1.x SET NAMES UTF8

MySQL backup and restore

因為翰x要移機,所以要把db 上的資料轉出來,不過因為用mysql db < db.sql
常常會出他媽的鬼問題,很杜爛!!!
所以單純把資料dump 成 text file 會是一個比較好的方式,然後再透過mysqlimport
或是LOAD DATA INFILE
我寫了兩個小小的scripts
就是dump和 restore
有須要的人請自行服用 ::-p:
1.import_db.sh

db_dir=$1
db=$2
for file in `find $1 -type f -depth 1 | egrep txt`
do
table=`echo $file |cut -d “/” -f 5|cut -d “.” -f 1`
echo ‘import data to ‘$table’ finished ‘
/usr/local/bin/mysql -u root -p -e “load data infile ‘$file’ into table $table” $db
done

2.dump_db.sh

date=`date +%Y%m%d`
db=$1
dump_dir=$2
charset=$3
#Dump DB structure
/usr/local/bin/mysqldump -uroot -p -d $db > $dump_dir/$db.sql
# Dump Data
chown mysql:mysql $dump_dir
/usr/local/bin/mysqldump -u root -p -t –default-character-set=$charset –tab=$dump_dir $db
#tar -zcf /tmp/$db.$date.tar.gz $dump_dir

我的shell script 寫的很爛~~
別見笑~哈 :8-):

PS:剛剛在刻這篇的時後,邊和lyz講電話,一邊在shell 上工作
沒想到,他媽的不小心
rm -rf * .txt(是想刪掉所有的txt的)
結果因為多打了一個space變成我把資料全刪了
我當時在 ~ 一堆東西沒了~~~幹!!! ::S:

MySQL replication

昨天和貓哥談到了關於備份移機的事,
有談到關於mysql replication
so 找了一下有關於
mysql replication 的資料
簡單來說mysql replication
就是主要架設一台Mysql Master
和一些slave。
而slave 會主動和master 要資料,
且可以提供query ,來分散一些master 的load
當master 有更新時,slave 就會和master 要資料更新。
so 也就達到備份的效果
而mysql replication 有不少作法
如one-way, multi-way , circle

One Way replicaton
例如A跟B
B不write, 只有A write, 然後A是master
這叫做one-way
Multi Way replication
如果A, B都能write, 互相以對方為master
就是multi-way
Circle
而Circle 又分
circle, double circle, semi-double circle
一般circle
A(master) < ===> (slave)B(master) < ===> (slave)C(master) < ===> A
double circle
是兩個circle, 然後兩個circle的master node之間做multi-way
semi-double
是一邊做circle, 然後circle 跟外頭的node做multi-way replication

以下是一些資料
http://dev.mysql.com/doc/refman/5.0/en/replication.html
http://blog.johnpupu.tw/wp-content/uploads/2007/01/sop-mysql.pdf