how import an excel data into mysql safely

手上来了个任务，对一些表更新，更新相应的字段都存放在excel中，如果是直接更新相应的字段倒好办，直接拼SQL就可以了，却要关联到其他二个表

自然想到把文件内容导入到 database，再处理会相对快些
and do :open the excel file and save as csv file ,and load data local infile 'x' into table tbname fields terminated by ',' lines terminated by '\r\n' ignore 1 lines;
Unfortunately-^-^这个单词拼了半天，最后还是GG了，呵
sth error,there's many common in the last column

such as
1, 2003 ,44445, http:// ,here is ok
3 ,2002 ,45645, http:// ,here has, some ,

主要过程如下：
step 1:copy excel content into editplus or ue
Note: charset
step 2:change ' " ... 为 \' \"
step 3:tab -> ","
step 4: ^(reg)->(" $(reg)->"),
step 5: set names utf8;
drop table if exists tmp;
create table tmp
(
ChID int,
CID int,
word varchar(255),
index(ChID),
index(CID),
index(Kword)
);
select "start to insert word",now();
insert into tmp values

mysql < filename

END

如果你的文件比较规范，或是没有特殊字符，不妨考虑下第一种方法，另外注意一些项
如果table是myisam
mysql> show variables like 'con%insert';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| concurrent_insert | 1 |
+-------------------+-------+
1 row in set (0.00 sec)

如果table是innodb
mysql> show variables like '%checks%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
| innodb_checksums | ON |
| unique_checks | ON |
+--------------------+-------+
3 rows in set (0.00 sec)

good luck

mysql sql_mode date problem

今天在向数据仓库导数据(mysql -> oracle )时，发现有时间不正确的行，看了下，是由于mysql sql_mode的原因
mysql> use test
Database changed
mysql> SHOW VARIABLES LIKE "%sql_mode%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)

mysql> create table `date` ( id integer primary key,col2 date);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `date` VALUES (1, "2009-12-21");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `date` VALUES (3, "2009-2-31");
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'col2' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from `date` where id=3;
+----+------------+
| id | col2 |
+----+------------+
| 3 | 0000-00-00 |
+----+------------+
1 row in set (0.05 sec)

mysql> SET SESSION sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `date` VALUES (4, "2009-2-31");
ERROR 1292 (22007): Incorrect date value: '2009-2-31' for column 'col2' at row 1
mysql> select * from `date` where id=4;
Empty set (0.00 sec)

mysql> SET SESSION sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `date` VALUES (4, "2009-2-31");
Query OK, 1 row affected (0.00 sec)

mysql> select * from `date` where id=4;
+----+------------+
| id | col2 |
+----+------------+
| 4 | 2009-02-31 |
+----+------------+
1 row in set (0.00 sec)

mysql> SELECT id ,col2 + INTERVAL 0 DAY FROM `date`;
+----+-----------------------+
| id | col2 + INTERVAL 0 DAY |
+----+-----------------------+
| 1 | 2009-12-21 |
| 2 | 2009-02-21 |
| 3 | NULL |
| 4 | 2009-03-03 |
+----+-----------------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO `date` VALUES (5, "2008-2-31");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT id ,col2 + INTERVAL 0 DAY FROM `date`;
+----+-----------------------+
| id | col2 + INTERVAL 0 DAY |
+----+-----------------------+
| 1 | 2009-12-21 |
| 2 | 2009-02-21 |
| 3 | NULL |
| 4 | 2009-03-03 |
| 5 | 2008-03-02 |
+----+-----------------------+
5 rows in set (0.00 sec)

oracle领地

AskTom

Metalink

Oracle OTN Forum

有趣的perl

引用《大骆驼》中那一个例子：jacob has four wives

将一个列表当成一个标量 []
$wife{"Jacob"} = ["Leah", "Rachel", "Bilhah", "Zilpah"];
$wife{"Jacob"}[0] = "Leah";
$wife{"Jacob"}[1] = "Rachel";
$wife{"Jacob"}[2] = "Bilhah";
$wife{"Jacob"}[3] = "Zilpah";

every wife has her own kids
将散列结构也当成一个标量 {}
$kids_of_wife{"Jacob"} = {
"Leah" => ["Reuben", "Simeon", "Levi", "Judah", "Issachar", "Zebulun"],
"Rachel" => ["Joseph", "Benjamin"],
"Bilhah" => ["Dan", "Naphtali"],
"Zilpah" => ["Gad", "Asher"],
};

$kids_of_wife{"Jacob"}{"Leah"}[0] = "Reuben";
$kids_of_wife{"Jacob"}{"Leah"}[1] = "Simeon";
$kids_of_wife{"Jacob"}{"Leah"}[2] = "Levi";
$kids_of_wife{"Jacob"}{"Leah"}[3] = "Judah";
$kids_of_wife{"Jacob"}{"Bilhah"}[1] = "Naphtali";
$kids_of_wife{"Jacob"}{"Zilpah"}[0] = "Gad";
$kids_of_wife{"Jacob"}{"Zilpah"}[1] = "Asher";

Replication Basic

Setting up a server as master
The steps that are needed to configure a server to be a master are:
1. Add log­bin and server­id options to my.cnf file
[mysqld]
server­id = 1
log­bin = master­bin.log

2. Start server and connect a client to the server
./mysqld ­­defaults­file=master.cnf
./mysql ­­defaults­file=master.cnf ­uroot

3. Add a replication user
master> CREATE USER repl_user@localhost

4. Give the replication user REPLICATION SLAVE privileges
master> GRANT REPLICATION SLAVE ON *.*
­> TO repl_user@localhost IDENTIFIED BY ‘xyzzy’;

Setting up a server as slave
To set up a server to act as a slave, the following steps have to be done:
1. Add configuration options for the relay log to the configuration file
[mysqld]
relay­log­index = slave­relay­bin.index
relay­log = slave­relay­bin

2. Direct the slave server to a master server
./mysqld ­­defaults­file=slave.cnf

3. Start the slave
./mysqld ­­defaults­file=slave.cnf
slave> CHANGE MASTER TO
­> MASTER_HOST = 'localhost',
­> MASTER_PORT = 12000,
­> MASTER_USER = 'repl_user',
­> MASTER_PASSWORD = 'xyzzy';
slave> START SLAVE;

4. Test that replication works
create a table and add some content on master,and select from slave

5. Check what hosts are connected to a master using SHOW SLAVE HOSTS

The binary log
how replication is done using the binary log.
1. Get a list of the binary logs on the master
2. Investigate(查看) contents of the binary log
3. What is the difference between the binary log formats?

Each binary log file consists of a sequence of event
If the binary log file active, there is no rotate event written last (yet),the header event indicates that this binary log file is not yet closed.
Whenever the binary logs are rotated, a rotate event is written last in the binary log, the binary
log is marked as complete in the header event, and a new binary log file is created and a
format description log event is written to it.


master> SHOW BINARY LOGS;

master> SHOW BINLOG EVENTS;
can be purged either by number or by date
mysql> PURGE BINARY LOGS TO 'master­bin.000002';
to purge all binary log files except the current one
mysql> PURGE BINARY LOGS BEFORE NOW();

mysql> set @id=100;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into stu values(@id);
Query OK, 1 row affected (0.06 sec)

mysql> select * from stu;
+------+
| id |
+------+
| 100 |
+------+
1 row in set (0.00 sec)

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+------------------------------------------+
| mysql-bin.000006 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.26-rc-log, Binlog ver: 4 |
| mysql-bin.000006 | 106 | Query | 1 | 192 | use `etl`; create table stu(id int) |
| mysql-bin.000006 | 192 | User var | 1 | 235 | @`id`=100 |
| mysql-bin.000006 | 235 | Query | 1 | 324 | use `etl`; insert into stu values(@id) |
+------------------+-----+-------------+-----------+-------------+------------------------------------------+
4 rows in set (0.00 sec)

Using mysqlbinlog
../bin/mysqlbinlog mysql-bin.000006

Replication files on slave
The most important files on the slave are the relay log files,The relay log is written by the slave I/O thread,and read by the slave SQL thread.
containing information about the progress of replication;
the files are master.info, and relay­log.info.
master.info:hold information about the master being replicated from and how much of the master binary log that has been replicated.
relay­log.info:keep track of how much of the binary log has been applied.
In this file, the figures give what position in the relay log that has been applied, and
what position and the master binary log that it corresponds to.
slave­relay­bin.000001 # Relay log name
856 # Relay log pos
master­bin.00001 # Master log name
802 # Master log pos

Basic replication use scenarios
1. Redundancy, or high-availability
2. Load balancing reads
3. Offline processing to avoid stopping the master

Using a slave for
taking a backup
reporting or analysis
read scale-out

Adding new slaves
we clone an existing slave by taking a backup of the slave, restore the
backup on the new slave, and then start the slave replicating from the position that
corresponds to the position in the binary log that the backup corresponds to.

How to clone a slave
decide on a point in the master binary log where we want to stop the slave and use the START SLAVE UNTIL command to have the slave stop exactly at a specified position.
The easiest way of getting a position in the master binary log
FLUSH TABLES WITH READ LOCK on the master to flush all tables and add a
read lock to the tables of the database,After we have that position, we can use the START
SLAVE UNTIL to have the slave stop at the right position..
The steps that has to be done to get a backup and a binlog position for what the backup
corresponds to are:
1. Stop slave
slave> STOP SLAVE;
2. Flush tables on master with a read lock
master> FLUSH TABLES WITH READ LOCK;
3. Get master position
master> SHOW MASTER STATUS;
+­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­+­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­+­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­+
| master­bin.000003 | 742 | | |
+­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­+­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­+
4. Unlock tables to let the master start running again
master> UNLOCK TABLES;
5. Start the slave to run until the binlog position from point 3
slave> START SLAVE UNTIL
­> MASTER_LOG_FILE='master­bin.000003',
­> MASTER_LOG_POS=742;
Query OK, 0 rows affected (0.01 sec)
6. Wait for the slave to stop
slave> DO MASTER_POS_WAIT('master­bin.000003', 742);
7. Take a backup of the slave and take a note of the binary log file and position that it corresponds to
$ cat >slave/data/binlog_pos.dat
master­bin.000003
742
$ tar cxf slave­backup.tar.gz slave/data
8. Start the slave again
slave> START SLAVE;

Creating the new slave
$ ./mysqld ­­defaults­file=slave2.cnf
$ tar zxf slave­backup.tar.gz slave2/data
$ cat slave2/data/binlog_pos.dat
$ ./mysql ­­defaults­file=slave1.cnf ­uroot
slave2> CHANGE MASTER TO
­> MASTER_HOST = 127.0.0.1,
­> MASTER_PORT = 12000,
­> MASTER_USER = 'repl_user',
­> MASTER_PASSWORD = 'xyzzy',
­> MASTER_LOG_FILE = 'master­bin.000003',
­> MASTER_LOG_POS = 742;
slave2> START SLAVE;

SHOW SLAVE STATUS

Replication for high-availability
Dual masters
[mysqld]
...
log­-slave­-updates

A commonly used but incorrect way to stop the slave
we assume that the master is under heavy use, and there are writes going on all the time

save_master_pos;
sync_with_master;
slave> DO MASTER_POS_WAIT('master­bin.000001', 440);

master> SHOW MASTER STATUS;
slave> DO MASTER_POS_WAIT(...);
slave> STOP SLAVE;
$ tar zcf slave­backup.tar.gz slave/data
slave> START SLAVE;
The problem is that the slave keeps running after the wait, which means
that there potentially can be several inserts done into the binary log between that statement
and stopping the slave.

正在读一本书 Head First SQL

SQL的经典著作，之前没用心读过，现在重新拜读，全书共十二章

新疆事件--让人心寒呀

http://www.tagstory.com/video/video_post.aspx?media_id=V000338241
一段视频，暴力事件！

修改mysql proc definer

Database: mysql
Table: proc

MySQL Query Profiler

对于一个SQL执行之后花费的CPU等资源可以使用系统自带的profiler来查看（>=5.0.37）
mysql> show profiles;
Empty set (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.01 sec)

mysql> set profiling=on;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ETL |
| mysql |
| test |
+--------------------+
4 rows in set (0.07 sec)

mysql> use ETL
Database changed
mysql> show tables;
+---------------+
| Tables_in_ETL |
+---------------+
| Customer |
| Mer |
+---------------+
2 rows in set (0.00 sec)

mysql> select count(*) from Mer;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.02 sec)

mysql> show profiles;
+----------+------------+--------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------+
| 0 | 0.01201500 | set profiling=on |
| 1 | 0.07709700 | show databases |
| 2 | 0.00003500 | SELECT DATABASE() |
| 3 | 0.00318600 | show tables |
| 4 | 0.01772300 | select count(*) from Mer |
+----------+------------+--------------------------+
5 rows in set (0.01 sec)

mysql> show profile for query 4;
+--------------------+------------+
| Status | Duration |
+--------------------+------------+
| Opening tables | 0.00064400 |
| System lock | 0.01148000 |
| Table lock | 0.00050300 |
| init | 0.00002400 |
| optimizing | 0.00084600 |
| executing | 0.00070300 |
| end | 0.00179700 |
| query end | 0.00002100 |
| freeing items | 0.00001300 |
| closing tables | 0.00100200 |
| logging slow query | 0.00067500 |
| cleaning up | 0.00001500 |
+--------------------+------------+
12 rows in set (0.01 sec)

如何获得DML语句的执行计划呢，？

为了启动的纪念 perl 正则

有时想不起来，记一下：（源自perl by example edition 4）

Character Class: Single Characters and Digits
.	Matches any character except a newline
[a–z0–9]	Matches any single character in set
[^a–z0–9]	Matches any single character not in set
\d	Matches one digit
\D	Matches a nondigit, same as [^0–9]
\w	Matches an alphanumeric (word) character
\W	Matches a nonalphanumeric (nonword) character
Character Class: Whitespace Characters
\s	Matches a whitespace character, such as spaces, tabs, and newlines
\S	Matches nonwhitespace character
\n	Matches a newline
\r	Matches a return
\t	Matches a tab
\f	Matches a form feed
\b	Matches a backspace
\0	Matches a null character
Character Class: Anchored Characters
\b	Matches a word boundary (when not inside [ ])
\B	Matches a nonword boundary
^	Matches to beginning of line
$	Matches to end of line
\A	Matches the beginning of the string only
\Z	Matches the end of the string or line
\z	Matches the end of string only
\G	Matches where previous m//g left off
Character Class: Repeated Characters
x?	Matches 0 or 1 x
x*	Matches 0 or more occurrences of x
x+	Matches 1 or more occurrences of x
(xyz)+	Matches 1 or more patterns of xyz
x{m,n}	Matches at least m occurrences of x and no more than n occurrences of x
Character Class: Alternative Characters
was|were|will	Matches one of was, were, or will
Character Class: Remembered Characters
(string)	Used for backreferencing
\1 or $1	Matches first set of parentheses
\2 or $2	Matches second set of parentheses
\3 or $3	Matches third set of parentheses
Character Class: Miscellaneous Characters
\12	Matches that octal value, up to \377
\x811	Matches that hex value
\cX	Matches that control character; e.g., \cC is -C and \cV is -V
\e	Matches the ASCII ESC character, not backslash
\E	Marks the end of changing case with \U, \L, or \Q
\l	Lowercase the next character only
\L	Lowercase characters until the end of the string or until \E
\N	Matches that named character; e.g., \N{greek:Beta}
\p{PROPERTY}	Matches any character with the named property; e.g., \p{IsAlpha}/
\P{PROPERTY}	Matches any character without the named property
\Q	Quote metacharacters until \E
\u	Titlecase next character only
\U	Uppercase until \E
\x{NUMBER}	Matches Unicode NUMBER given in hexadecimal
\X	Matches Unicode "combining character sequence" string
\[	Matches that metacharacter
\\	Matches a backslash

slave忽略复制错误

mysql> show variables like '%slave%';
+---------------------------+---------------+
| Variable_name | Value |
+---------------------------+---------------+
| init_slave | |
| log_slave_updates | OFF |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /Disk/mysql/tmp |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| sql_slave_skip_counter | |
+---------------------------+---------------+
9 rows in set (0.00 sec)

通常需要跳过一笔或n笔，使用
set global sql_slave_skip_counter=1; or
set global sql_slave_skip_counter=n;

start slave;

如想跳过所有错误修改配置文件
slave_skip_errors = ALL

restart mysql

MySQL Master-Master Replication Manager (转)

转Tsung's Blog

什麼是 MySQL Master-Master Replication Manager(MMM)?

• MMM (MySQL Master-Master Replication Manager) 是整合 Script 做 監控/管理 MySQL Master-Master replication cluster 的設定配置.
• Master-Master 的意思不是同時有兩台 Master 應付寫入的動作, 而是要做備援, 若 Master 死掉, 可於最短的時間內(幾秒內), 將備援的 Master 替換上線, 而這台備援的 Master, 平常也可以當 Slave (Reader).
• 當 Slave 的 Replication Failed 時, 會自動讓此 Slave 停止提供服務, 避免不同步的狀況發生.
• MMM 可解決 Master 死掉時, 需要停機或停止所有寫入的問題.

MMM 的特色

上述的事情, 只要在程式判斷 Master 死掉, 之後就永遠寫 Slave 等, 就可以做到(Master 回復時, 再手動去做切換), 但是 MMM 能夠多處理哪些東西呢? MMM 的設定/原理大概是怎麼樣呢?

• MMM 的基本設定是 Master-Master (就是兩台 MySQL Server 互相設對方為自己的 Master, 自己是對方的 Slave), 所以在任何一台正常回復, 會將資料自動透過 Replication 同步.
• MMM 能夠在 Master(db1) 死掉時, 在最短的時間, 自動切換到另一台當 Master(db2). 當 Master(db1) 回復時, 會直接當現在 Master(db2) 的 Slave, 不過不會直接上線, 要等資料 sync 同步後, MMM 才會允許手動設定上線.
• 另可參考: Typical Use Cases 使用情境(常見使用架構)

MMM 的運作原理

MMM 主要做法是 Master(db1), Master(db2) 設定固定 IP, 然後 Monitor 也設定一個固定 IP, 再來 Monitor 的設定再加上兩個 IP(Writer, Reader), 程式讀取/寫入 都是透過 Monitor 另外設的這兩個 IP, 當任何一台機器死掉(不管是 Reader/Writer), Monitor 會自動將 IP 設
到另一台機器上.

註: 透過 ARP(Address Resolution Protocol) 和 iproute 將 IP 指到另一台.

設定依正常設定, 兩台 MySQL Server 各設自己原本的 IP, Monitor 會依照設定檔另外再綁上各自讀寫用的 IP, 跟之前主要的差異是, 兩台
MySQL Server 要多跑 mmm_agent daemon, Monitor 要跑 mmm_mon daemon.

MMM 是透過下述 check MySQL servers 是否活著:

1. IP connectivity (fping)
2. MySQL connectivity (mysql connect and SELECT NOW())
3. MySQL IO and SQL threads status (SHOW SLAVE STATUS)
4. MySQL replication backlog - seconds behind master (SHOW SLAVE STATUS)

MMM 的官方資源

• MMM 官方網站說明: MySQL Master-Master Replication Manager
• MMM 下載: mysql-master-master - Google Code
• MMM 問答討論區: MySQL Multi Master Manager Development | Google Groups

MMM 的更多介紹、說明

• MySQL Master-Master replication manager released
• Master-Master Replication Example using MMM
• mysql-master-master wiki list- Google Code - 官方 Wiki 的說明文件
• Introduction - mysql-master-master
• MMM Documentation table of contents
• Using MMM to ALTER huge tables

此文假設已經玩過 MySQL Replication, 所以 Replication 設定的部份不多做解釋, 若有不懂的, 可以參考: MySQL 設定 Replication (Master - Slave)

MySQL Master-Master Replication Manager 前置作業

MySQL Master-Master 的架構, 需要準備的資訊如下:

• 機器 3台以上(最少3台): 2台做 MySQL Server, 1台做 Monitor(監控機器可與 Apache 等共用即可).
• IP 5個以上(2N+1): MySQL Server 有幾台, 需要的 IP 是 MySQL Server 台數 x 2, 再加上 Monitor 要一個 IP.

MMM 前置作業手上該有的資訊:

• db-1 192.168.1.181
• db-2 192.168.1.182
• db-mon 192.168.1.183
• db-r 192.168.1.184 # 這個不用設進 db-1、db-2, db-mon 會自動設給它.(但需先決定db-1, db-2 哪台要先當 reader 或 writer)
• db-w 192.168.1.185 # 這個不用設進 db-1、db-2, db-mon 會自動設給它.(但需先決定db-1, db-2 哪台要先當 reader 或 writer)
• MySQL Replication 要設定的帳號: replication、密碼: slave.
• Monitor 存取 MySQL 需要的帳號: rep_monitor、密碼: RepMonitor.
• MySQL Agent 要設定的帳號: rep_agent、密碼: RepAgent.

開始之前, 除了上述該有的資訊外, 手上應該要有 3台機器, 3台機器設定分別如下:

• db-1 192.168.1.181
• db-2 192.168.1.182
• db-mon 192.168.1.183
• 若有要用到 mmm_clone, mmm_backup, mmm_restore 等功能, 需要 LVM 支援, 除此之外, 沒有 LVM 還是能正常監控/轉換等, 下述環境也是在沒有 LVM 的狀況下測試的.

MySQL Master-Master Replication Manager 環境建置、架設

下述設定參考自: Master-Master Replication Example using MMM (設定檔參考: Configuration Examples)

註: 下述環境、設定檔位置 是以 Debian Lenny 為主.

建置環境步驟

建置環境步驟主要如下述:

1. db-1, db-2 安裝 mysql-server
2. db-1, db-2 互設對方為 Master, 自己是對方的 Slave
3. 抓取 mmm 的檔案, 裝需要的 Package 後, 執行 install.pl
4. 設定 mmm_agent.conf 後, 於 db-1, db-2 跑 mmm_agent
5. db-mon 安裝需要的 Package 後, 執行 install.pl
6. 設定 mmm_mon.conf 後, 於 db-mon 跑 mmm_mon
7. 將 db-1, db-2 設定上線 mmm_control set_online db1, mmm_control set_online db2
8. 測試 mmm_control show 是否正常, 將 /usr/local/mmm/scripts/init.d, logrotate.d 設定到 /etc 去.
9. 使用 rcconf 設定開啟啟動即可.

實際執行步驟 - db-, db-2 互設 Replication, db-mon 建置

db-1 192.168.1.181

1. apt-get install mysql-server
2. vim /etc/mysql/my.cnf

   # bind-address = 127.0.0.1
   server-id = 1
   log_bin = /var/log/mysql/mysql-bin.log

3. /etc/init.d/mysql restart

做完此步驟, 請跳到 db-2 也先把此步驟做完.(順便抄下 db-2 的 show master status)

db-2 做完上述步驟後, 再繼續下面:

1. mysql -u root
2. mysql> GRANT ALL PRIVILEGES on *.* to 'rep_monitor'@'192.168.1.183' identified by 'RepMonitor';
3. mysql> GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'192.168.1.181' identified by 'RepAgent';
4. mysql> GRANT replication slave on *.* to 'replication'@'%' identified by 'slave';
5. mysql> change master to master_host='192.168.1.182', master_port=3306, master_user='replication', master_password='slave'; # 文件上寫的做法
6. mysql> change master to master_host='192.168.1.182', master_port=3306, master_user='replication', master_password='slave', master_log_file='mysql-bin.000004', master_log_pos=98; # 我喜歡保守點的做法.(抓 db-2 mysql> show master status 資料)
7. /etc/init.d/mysql restart
8. mysql -u root
9. mysql> slave start;
10. mysql> show slave status \G

這樣子應該 Replication 已經設定完成, Master 是 db2, 自己是 Slave, 再下來就是架設 MMM 囉~

架設 MMM 步驟如下:

1. apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl # for perl
2. apt-get install iproute
3. wget http://mysql-master-master.googlecode.com/files/mmm-1.0.tar.bz2
4. tar xvf mmm-1.0.tar.bz2
5. cd mmm-1.0
6. ./install.pl
7. cp /usr/local/mmm/etc/examples/mmm_agent.conf.example /usr/local/mmm/etc/mmm_agent.conf
8. vim /usr/local/mmm/etc/mmm_agent.conf # 下述只將修改部份列出

   cluster_interface eth0
   
   # Define current server id
   this db1
   mode master
   
   # For masters
   peer db2
   
   # Cluster hosts addresses and access params
   host db1
   ip 192.168.1.181
   port 3306
   user rep_agent
   password RepAgent
   
   host db2
   ip 192.168.1.182
   port 3306
   user rep_agent
   password RepAgent

9. mmmd_agent # 執行 agent, 或者 /usr/local/mmm/scripts/init.d/mmm_agent start
10. ps aux | grep mmmd

    root 16115 0.0 0.0 41936 6904 ? S 15:02 0:00 perl /usr/local/sbin/mmmd_agent

11. 修改完成的設定檔參考可下載: mmm_agent.conf
12. 再來就繼續下述 db-2 的設定囉~

db-2 192.168.1.182

1. apt-get install mysql-server
2. vim /etc/mysql/my.cnf
3. # bind-address = 127.0.0.1
   server-id = 2
   log_bin = /var/log/mysql/mysql-bin.log
4. /etc/init.d/mysql restart

做完此步驟, 再回 db-1 繼續. (順便抄下 db-1 的 show master status)

1. mysql -u root
2. mysql> GRANT ALL PRIVILEGES on *.* to 'rep_monitor'@'192.168.1.183' identified by 'RepMonitor';
3. mysql> GRANT replication slave on *.* to 'replication'@'%' identified by 'slave';
4. mysql> GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'192.168.1.182' identified by 'RepAgent';
5. mysql> change master to master_host='192.168.1.181', master_port=3306, master_user='replication', master_password='slave'; # 文件上寫的做法
6. mysql> change master to master_host='192.168.1.181', master_port=3306, master_user='replication', master_password='slave', master_log_file='mysql-bin.000004', master_log_pos=98; # 我喜歡保守點的做法.(抓 db-1 mysql> show master status 資料)
7. /etc/init.d/mysql restart
8. mysql -u root
9. mysql> slave start;
10. mysql> show slave status \G

這樣子應該 Replication 已經設定完成, Master 是 db1, 自己是 Slave, 再下來就是架設 MMM 囉~(下述步驟與上面一致, 只有設定檔有差異而已)

架設 MMM 步驟如下:

1. apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl # for perl
2. apt-get install iproute
3. wget http://mysql-master-master.googlecode.com/files/mmm-1.0.tar.bz2
4. tar xvf mmm-1.0.tar.bz2
5. cd mmm-1.0
6. ./install.pl
7. cp /usr/local/mmm/etc/examples/mmm_agent.conf.example /usr/local/mmm/etc/mmm_agent.conf
8. vim /usr/local/mmm/etc/mmm_agent.conf

   cluster_interface eth0
   
   # Define current server id
   this db2
   mode master
   
   # For masters
   peer db1
   
   # Cluster hosts addresses and access params
   host db1
   ip 192.168.1.181
   port 3306
   user rep_agent
   password RepAgent
   
   host db2
   ip 192.168.1.182
   port 3306
   user rep_agent
   password RepAgent

9. mmmd_agent # 執行 agent, 或者 /usr/local/mmm/scripts/init.d/mmm_agent start
10. ps aux | grep mmmd

    root 8837 0.0 0.0 41936 6904 ? S 15:02 0:00 perl /usr/local/sbin/mmmd_agent

11. 修改完成的設定檔參考可下載: mmm_agent.conf

db-mon 192.168.1.183

1. apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl # for perl
2. apt-get install iproute
3. apt-get install subversion # 目前 1.0 stable 的 mmm_mon 程式有 bug, 所以需要直接 checkout trunk 的來用
4. svn checkout http://mysql-master-master.googlecode.com/svn/trunk/ mysql-master-master-read-only
5. cd mysql-master-master-read-only/
6. ./install.pl
7. cp /usr/local/mmm/etc/examples/mmm_mon.conf.example /usr/local/mmm/etc/mmm_mon.conf
8. vim /usr/local/mmm/etc/mmm_mon.conf

   email root@localhost # 修改成有狀況要通知的 Email.
   
   host db1
   ip 192.168.1.181
   port 3306
   user rep_monitor
   password RepMonitor
   mode master
   peer db2
   
   host db2
   ip 192.168.1.182
   port 3306
   user rep_monitor
   password RepMonitor
   mode master
   peer db1
   
   # Mysql Reader role
   role reader
   mode balanced
   servers db1, db2
   ip 192.168.1.185, 192.168.1.184
   
   # Mysql Writer role
   role writer
   mode exclusive
   servers db1, db2
   ip 192.168.1.185

9. /usr/local/mmm/scripts/init.d/mmm_mon start
10. mmm_control show
11. mmm_control set_online db1 # 讓 db1 上線
12. mmm_control set_online db2 # 讓 db2 上線
13. mmm_control show # 看到下述就成功了~

    Config file: mmm_mon.conf
    Daemon is running!
    Servers status:
    db1(192.168.1.181): master/ONLINE. Roles: reader(192.168.1.185;), writer(192.168.1.185;)
    db2(192.168.1.182): master/ONLINE. Roles: reader(192.168.1.184;)

14. 修改完成的設定檔參考可下載: mmm_mon.conf

設定 log rotate

下面這些步驟分別在 db1, db2, mon 設定即可.

1. cp /usr/local/mmm/scripts/logrotate.d/mmm /etc/logrotate.d/
2. vim /etc/logrotate.d/mmm

   /opt/mmm/var/*.log { 修改成 /usr/local/mmm/var/*.log
   olddir /opt/mmm/var/old 修改成 olddir /usr/local/mmm/var/old

設定開機自動啟動

db1, db2 設定開機自動啟動

1. cp /usr/local/mmm/scripts/init.d/mmm_agent /etc/init.d/
2. apt-get install rcconf
3. rcconf
4. 選取 mmm_agent 即可

mon 設定開機自動啟動

1. cp /usr/local/mmm/scripts/init.d/mmm_mon /etc/init.d/
2. apt-get install rcconf
3. rcconf
4. 選取 mmm_mon 即可

再下來就只要會 mmm_control show, mmm_control set_online, mmm_control set_offline 即可.

當機/重開機的 SOP

若有重開機等狀況, 檢查步驟:

1. mysql -u root # 看 MySQL 是否有啟動, 若沒啟動 /etc/init.d/mysql start
2. ps aux | grep mmm # 看 mmm_agent 或 mmm_mon 是否有啟動, 若沒啟動 /etc/init.d/mmm_[agent|mon] start
3. 再來在 mon 的機器下: mmm_control show, 若沒上線, 發現已經在 AWAITING_RECOVERY 狀態, 就可以 set_online 讓他上線囉~

測試

1. db1, db2 mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO 'mmm'@'%' IDENTIFIED BY 'mmm_password';
2. db1, db2 mysql> FLUSH PRIVILEGES;
3. 寫程式去對 192.168.1.184, 192.168.1.185 做寫入/讀取的動作, 並試著重開等看看反應~
4. 註: 目前測試狀況, 機器死掉時, 在 2秒內就會自動切換過去. :)

MMM LVM 設定

因為機器安裝時沒設 LVM, 所以沒辦法測試, 不過需要安裝的 Package 和設定大概如下:

1. apt-get install lvm2 dmsetup mdadm reiserfsprogs xfsprogs
2. MMM LVM 設定範例: mmm_lvm.conf # 此設定範例需要另外建立 /backup, /mmm_snapshot 目錄
3. 設 LVM 可以直接在 db2 將 db1 的資料 clone 過來, ex: db2# mmm_clone --host db1 --clone-mode master-master # db2 的 MySQL 會幫你停掉, 然後再 scp db1 data 過來.

相關網頁

• MySQL Master-Slave架構下使用MMM的必要性
• 實踐：使用MMM搭建Mysql集群
• MySQL Replication Manager
• MySQL Master-Master Replication Manager - 2009/06 架設完成新寫的文章

MMM 的問與答

將其中一台 MySQL 關掉, Mon 該如何動作?

1. Mon 會自動把 IP 轉到另外一台, 等 MySQL 回復後自然會還原.
2. mmm_control show # 若沒有自動還原
3. mmm_control set_online db1 # 將沒有還原(假設是 db1)那台的設定上線.

先將 mmmd_agent kill 掉, 再把 MySQL kill 掉, 然後再把 MySQL start, Mon 會如何動作?

1. 因為 Mon 是自動去連 MySQL 的, 所以不會因為 mmmd_agent 砍掉而有影響, MySQL kill 掉時, Mon 一樣會知道那台是掛掉的,(master/HARD_OFFLINE)
2. 此時於 另外一台 MySQL 寫的資料, 於此台 MySQL start 時, 會自動寫入, 不過 Mon 無法將這台回復的機器登記入可以讀寫,(master/AWAITING_RECOVERY)
3. 因為這台的 mmmd_agent 還沒起來, 沒辦法知道是否已經跟上 replication 沒.
4. 所以要先啟動 mmmd_agent, Mon 再 mmm_control set_online db1.

Mon 的 mmmd_mon kill 掉, 對其它有沒有影響?

沒有影響, /usr/local/mmm/scripts/init.d/mmm_mon start 即可.

Master 測試重新開機 shutdown -n -f -r now

重新開機完後的處理步驟:

• 於 重開機的機器(Master)
  1. /etc/init.d/mysql start # 這種重開法, MySQL 不一定能正常啟動.
  2. ps aux | grep mmm # 看 mmmd_agent 是否有正常啟動.
• 於 Mon 的機器
  1. mmm_control show # 會發現啟動後, master 的狀態會由 HARD_OFFLINE -> AWAITING_RECOVERY
  2. mmm_control set_online db1 # 讓它啟動接受服務.

Mon 測試重新開機 shutdown -n -f -r now

重新開機完後的處理步驟:

於 Mon 的機器

1. ps aux | grep mmm # 看 mmm_mon 是否有正常啟動, 沒有正常啟動就執行 /etc/init.d/mmm_mon start
2. mmm_control show # 看監控的機器是否都還正常

Master 死掉, 然後造成 Reader 和 Writer 互換, 若 Mon 重新啟動會回復原始設定嗎? 若不會, 想要互換要怎麼換?

1. 不會回復原始設定, 用的是最後跑的狀態.
2. 想要將設定的互換, 步驟如下:
   1. master mysql restart
   2. slave mysql> start slave;
   3. 這 樣子就會互換了 (因為 Master MySQL restart, Slave 那台 replication 會停掉, 所以 Mon 會將所有 IP 都指向 Master, 再將 Slave replication 搞定, 就會再把 reader 指回 slave 了)
3. 設定互換的另一種方法, 就是把另外一台先設 mmm_control set_offline, 再 set_online 即可.
4. 設定互換的另一種方法, 使用 mmm_control move_role writer db1, 設定設回 Writer 去.(不過此方法我測試似乎不會動)

寫入, 讀取應該要用的IP, 要用本機IP 還是設定的 db-r, db-w IP?

寫入/讀取要用的 IP, 是要用設定的 db-r, db-w 的IP, 設定的 db-r 的 IP(只要 Writer 沒有指到此 IP), INSERT/UPDATE/DELETE 的動作會把你擋掉, 會出現 ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement 的錯誤訊息.(不過 CREATE/DROP Table 等動作不會被擋掉.)

在 Master 上的 mmm/bin/* 有很多檔案, 要怎麼讓他可以使用?

要先讓它有存取 MySQL 的權限, 於 mysql> 就這樣子下: (帳號/密碼/IP 請自行修改)

1. mysql> GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'192.168.1.181' identified by 'RepAgent';
2. mysql> GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'192.168.1.182' identified by 'RepAgent';
3. mysql> FLUSH PRIVILEGES;
4. 不過 mysql_deny_write 並不會讓它不能寫入.(此程式會幫你執行: set global read_only=1, 不過 CREATE/DROP 還是可以使用. XD)

想要一台 Mon 管理多台 agent 要怎麼設定?

1. 將 /usr/local/mmm/etc/mmm_mon.conf 改名成 mmm_mon_c1.conf
2. 就可以 mmm_control @c1 show 來管理 c1 的 Cluster, 就可由此來做其它 Cluster 的 Mon 設定囉~

mmm_control 哪些狀態可以將機器 set_online?

master/AWAITING_RECOVERY 或 master/ADMIN_OFFLINE 的狀態.

mmm_contorl 全部狀態有哪些?

mmm_control show 會看到機器有下述的狀態: (原文可見: HowToUse - mysql-master-master)

• master/ONLINE - 此機器沒有任何問題, 正常執行.
• 
  
• master/AWAITING_RECOVERY - 此機器的 MySQL 可能有重開, 並沒有被設定上線, 於 replication 跟上進度後(若 replication 有損壞的狀況, 需自行修復), 使用 mmm_control @C1 set_online 機器名稱 的命令讓他上線.
• master/ADMIN_OFFLINE - 管理者設定讓這台機器下線.
• master/HARD_OFFLINE - 此 MySQL 是沒辦法 ping 到, 可能是網路問題或者機器當掉或重開等等.
• master/REPLICATION_FAIL - MySQL 的 replication error. 遇到先修復 replication 的狀況, 才能讓它再上線.
• master/REPLICATION_DELAY - MySQL 死掉時, 可能 MMM 自動移動 reader 的角色到 Master 去, 但是 Reader 起來後, Replication 還根不上進度, 需要等它跟上才能恢復上線.

於 lvm 的環境下, 如何快速建立一台 Master (Master-Master) 架構?

1. LVM 安裝: apt-get install lvm2 dmsetup mdadm reiserfsprogs xfsprogs # LVM 詳細安裝步驟可參考: A Beginner's Guide To LVM
2. 設定好 /usr/local/mmm/etc/mmm_lvm.conf
3. 就可以於 db2# mmm_clone --host db1 --clone-mode master-master (此指令會將 db2 的 MySQL 停掉, 再 scp 過來)
4. 註: 不過這個我也沒試過.

讀取都是透過 Mon 指定的那兩個 Writer/Reader 的 IP, 但是 Mon 死掉的話, 會不會就無法寫入/讀取?

不會, 因為 Mon 是透過 Address Resolution Protocol (ARP), 而 ARP 在每台機器是都會有暫存區, 存: IP <-對應-> Mac Address 的資料, 所以 Mon 死掉在 ARP Cache 過期前, 都不會有影響.

多主一从架构实现

Tungsten replicator

基于JAVA，支持异构DB之间的复制
有机会试一下