apt-get install mysql-server mysql-client build-essential

Make sure that mysql will be started by heartbeat, not at boot-time on either server.

update-rc.d -f mysql remove

Verify the partitions we are using are exactly the same on both servers. We will be using sda6
(256MB) and sda7(94GB): Actual partitions should be something like this:

fdisk -l
Device Boot Start End Blocks Id System
/dev/sda1 * 1 24 192748+ 83 Linux
/dev/sda2 25 1969 15623212+ 82 Linux swap / Solaris
/dev/sda3 1970 19320 139371907+ 5 Extended
/dev/sda5 1970 6954 40041981 83 Linux
/dev/sda6 6955 6984 240943+ 83 Linux
/dev/sda7 6985 19320 99088888+ 83 Linux

sda6 will be our meta-disk sda7 will be our distributed replication block device Verify that sda6
and sda7 are not mounted:

mount -l

/dev/sda5 on / type ext3 (rw,errors=remount-ro)
tmpfs on /lib/init/rw type tmpfs (rw,nosuid,mode=0755)
proc on /proc type proc (rw,noexec,nosuid,nodev)
sysfs on /sys type sysfs (rw,noexec,nosuid,nodev)
procbususb on /proc/bus/usb type usbfs (rw)
udev on /dev type tmpfs (rw,mode=0755)
tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev)
devpts on /dev/pts type devpts (rw,noexec,nosuid,gid=5,mode=620)
/dev/sda1 on /boot type ext3 (rw)

Determine the kernel in use to install headers:

uname -r
apt-get install linux-headers-<<what was returned above>>

ETH0 will be the public interface on 10.10.10.XXX, while ETH1 will be on 192.168.1.xxx, a separate
network or crossover cable connected between the two machines. DRBD will run using eth1 of both
machines. On both servers, perform the following:

apt-get install drbd0.7-module-source drbd0.7-utils ipvsadm heartbeat
cd /usr/src/
tar xvfz drbd0.7.tar.gz
cd modules/drbd/drbd
make
make install
mv /etc/drbd.conf /etc/drbd.conf.sample

Edit /etc/drbd.conf and create the following contents:

resource r0 {
protocol C;
incon-degr-cmd “halt -f”;
startup {
degr-wfc-timeout 120; # 2 minutes.
}
disk {
on-io-error detach;
}
net {
}
syncer {
rate 10M;
group 1;
al-extents 257;
}
on mysqldb01 { # the hostname of server 1 (uname -n)
device /dev/drbd0; #
disk /dev/sda7; # data partition on server 1
address 192.168.1.10:7788; # ETH1 IP Address
meta-disk /dev/sda6[0]; # 256MB partition for DRBD on server 1
}
on mysqldb02 { # ** EDIT ** the hostname of server 2 (uname -n)
device /dev/drbd0; #
disk /dev/sda7; # ** EDIT ** data partition on server 2
address 192.168.1.11:7788; # ETH1 IP Address
meta-disk /dev/sda6[0]; # 256MB partition for DRBD on server 2
}
}

Start drbd on both servers

/etc/init.d/drbd start

Verify they are running. Both should be in a secondary state.

cat /proc/drbd
version: 0.7.24 (api:79/proto:74)
SVN Revision: 2875 build by root@mysqldb01, 2007-12-14 02:55:51
0: cs:Connected st:Secondary/Secondary ld:Inconsistent
ns:0 nr:0 dw:0 dr:0 al:0 bm:12096 lo:0 pe:0 ua:0 ap:0
1: cs:Unconfigured

Make server1 the primary and format the the device. You want to format the drbd device, not the sda
device. The server you are formatting must be the primary. You cannot format the drbd device if it
is in secondary mode. There is no need to perform the format operation on the secondary as it will
be sync’d as well.

drbdsetup /dev/drbd0 primary –do-what-I-say
mkfs.ext3 /dev/drbd0
drbdadm connect all

Verify that they are now sync’ing

watch cat /proc/drbd

version: 0.7.24 (api:79/proto:74)
SVN Revision: 2875 build by root@mysqldb01, 2007-12-14 02:55:51
0: cs:SyncTarget st:Secondary/Primary ld:Inconsistent
ns:0 nr:9660908 dw:9660908 dr:0 al:0 bm:12685 lo:0 pe:0 ua:0 ap:0
[=>..................] sync’ed: 9.5% (87332/96415)M
finish: 1:54:03 speed: 12,968 (10,140) K/sec
1: cs:Unconfigured

Next, setup the mysql datafiles on the drbd device. Stop mysql on both servers

/etc/init.d/mysql stop

Move the mysql data files and test mounting on mysqldb01

mkdir /mnt/move
mount -t ext3 /dev/drbd0 /mnt/move
mv /var/lib/mysql/* /mnt/move/.
umount /mnt/move
mount -t ext3 /dev/drbd0 /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql

Clear databases from mysqldb02 so we can be sure we’re starting from the proper set.

rm -R /var/lib/myssql/*

Start Mysql on mysqldb01 and verify that you can connect

/etc/init.d/mysql start
mysql -u root -p

Test drive MySQL on mysqldb01 a little bit, create a new database and a table in the database. The
following is the basic process to manually switch from the primary server to the secondary. We will
use it to verify everything is working as expected before proceeding to the heartbeat. Stop MySQL on
mysqldb01 and unmount drbd0

/etc/init.d/mysql stop
umount /var/lib/mysql
drbdadm secondary r0

On mysqldb02, make it the primary, mount the drbd and then start mysql

drbdadm primary r0
mount -t ext3 /dev/drbd0 /var/lib/mysql
ls /var/lib/mysql
/etc/init.d/mysql start

On mysqldb02, login to mysql and verify that the database and table we created exists. Create some
new records in our test table.

mysql -u root -p

If everything is working as planned, stop mysql on mysqldb02, unmount the drbd and switch the
services back to mysqldb01 to verify that works. On mysqldb02:

/etc/init.d/mysql stop
umound /var/lib/mysql
drbdadm secondary r0

On mysqldb01

drbdadm primary r0
mount -t ext3 /dev/drbd0 /var/lib/mysql
/etc/init.d/mysql start

Test connecting to Mysql as we did before. Verify any changes you made on mysqldb02 exist on
mysqldb01. This will be a good time to set a root password since the server won’t be listening on
localhost any longer:

GRANT ALL PRIVILEGES ON *.* TO ‘root’@'%’ IDENTIFIED BY ’some_pass’ WITH GRANT
OPTION;
FLUSH PRIVILEGES;

Edit the /etc/mysql/my.cnf file and change the IP address of the “bind-address”
setting to be that which we will use for the heartbeat. Now, since the heartbeat will control both
drbd and MySQL, stop MySQL and unmount the drbd

/etc/init.d/mysql stop
umount /var/lib/mysql

Create entries in /etc/hosts for the two servers which will be in the cluster. Use the ip address
which is bound to the public interface (eth0) which the heartbeat will use.

Create the /etc/ha.d/ha.cf file on both servers as follows:

debugfile /var/log/ha-debug
logfile /var/log/ha-log
logfacility local0
keepalive 2
deadtime 30
warntime 10
initdead 120
udpport 694
bcast eth0
auto_failback on
node mysqldb01 ## make sure both names are accessible - check /etc/hosts
node mysqldb02
ping 10.10.10.1 ## ETH0, public network
apiauth ipfail gid=haclient uid=hacluster

Create the /etc/ha.d/haresources. This file is the same on both servers. It lists the primary
servername, tells it to use LVS, sets the IP address of the heartbeat, the drbddisk name from
/etc/drbd.conf, the drbd device name and mount point and finally the server to start.

mysqldb01 LVSSyncDaemonSwap::master IPaddr::10.10.10.12/24/eth0 drbddisk::r0
Filesystem::/dev/drbd0::/var/lib/mysql::ext3 mysql

Create authkeys on both servers in /etc/ha.d/authkeys. Heartbeat will complain if you don’t
secure the file.

auth 3
3 md5 plaintextpassword

chmod 600 /etc/ha.d/authkeys

Start heartbeat on both servers, starting with mysqldb01:

/etc/init.d/heartbeat start

Errors will be in /var/log/ha-debug and /var/log/ha-log if you have problems. You should now be able
to connect to mysql on the heartbeat’s ip address. Shutdown heartbeat on mysqldb01, and you
should notice that /var/lib/mysql is now mounted on mysqldb02 and you can still connect to the
database.

Upgrading Mysql with DRBD and Heartbeat

After recently running into the dilemma of how to upgrade something that doesn’t like you to shut
it down, I’ve resolved it thus: First, upgrade the secondary database server. The MySQL upgrade
will actually fail since Debian cannot start and stop a database that isn’t running and whose
file storage isn’t mounted. After the secondary is mounted, commence to upgrading the primary.
The data files will now be upgraded, and the database shouldn’t be down long enough to create a
fail over on the heartbeat while the containers are upgraded.

เอาวิธีแรก ที่ใช้กันง่ายๆก่อนนะ

• รันด้วย /usr/sbin/mysqld --skip-grant-tables --user=root &
• ต่อจากนั้นก็เข้า mysql -u root -p
• เปลี่ยน password ด้วย UPDATE user SET password=password('secret') WHERE user='root';
• restart mysql กลับไปใช้แบบเดิม ก็เป็นอันเสร็จสิ้น

ส่วนวิธีที่สองไปอ่านเจอที่อื่น

• เพิ่ม “skip-grant-tables” ลงใน my.cnf โดยเพิ่มในส่วนของ [mysqld] สำคัญมากห้ามลืม อย่าใส่ผิดที่ ไม่อย่างนั้นจะไม่สามารถ reset password ได้
• restart mysql ก่อน เพื่อจะได้มีผล
• จากนั้นก็ใช้ login แล้วก็ เปลี่ยน password ด้วยคำสั่งข้างต้น
• พอแก้เรียบร้อยแล้วก็ comment บรรทัด skip-grant-tables แล้วก็ restart ก็เป็นอันเสร็จสิ้น

Password Protecting Web Directories

QUICK TIP : ถ้าคุณใช้ FrontPage ,คุณควรที่จะใช้เครื่องมือป้องกันพาสเวิร์ดที่มากับ FrontPage ยกเว้น เมื่อใช้กับพาสเวิร์ดป้องกันไดเรกทอรี webstats ของคุณ

IMPORTANT: คุณจะต้องการใช้ทั้ง FTP และ SSH ใช้กับฟีเจอร์นี้. หมายความว่าถ้าคุณมี Value Plan,คุณไม่ควรใช้ฟีเจอร์นี้.

Using .htaccess

สมมุติว่าโดเมนเนมคุณคือ foobar.com และต้องการใช้การป้องกันไดเรกทอรีที่ http://foobar.com/members/ คุณควรจะทำต่อไปนี้:

1) ใช้เท็กอิดิเตอร์ เช่น Notepad สร้างไฟล์ที่ชื่อ “.htaccess”. อย่าลืม จุดข้างหน้าของ htaccess.จากนั้นในไฟล์ควรจะประกอบสิ่งต่าง ๆ ต่อไปนี้

—COPY EVERYTHING BELOW——————–
AuthUserFile /home/foobar/.htpasswd
AuthGroupFile /dev/null
AuthName ByPassword
AuthType Basic
<Limit GET POST>
require user Spock
</Limit>
—COPY UP TO THE LINE ABOVE—————

สำคั?มาก : อย่าลืมแทนที่ “foobar” เป็นโดเมนเนมของคุณ. อย่าใส่ .com หรือ .net ต่อท้าย.

2) เซฟไฟล์ในโหมดเท็กซ์ไฟล์ธรรมดา

3) อัพโหลดไฟล์โดย FTP สู่ /www/foobar/member/ ต้องแน่ใจว่าคุณอัพโหลดในโหมด ASCII (plain text)

4) ล็อกออนเข้าสู่แอคเคาน์ของคุณในเซร์ฟเวอร์ของเราผ่าน SSH.

5) พิมพ์คำสั่งข้างล่างลงไป ที่บรรทัดคำสั่งจากนั้นเคาะ Enter

htpasswd -c /home/foobar/.htpasswd Spock

จะสร้างไฟล์ชื่อ .htpasswd ในไดเรกทอรีโอมของคุณ.

6) คุณจะมีพร้อมให้พิมพ์พาสเวิร์ดสำหรับ Spock.

7) ต้องการอนุ?าติเพิ่มยูสเซอร์ที่เรียกว่า Kirk.
พิมพ์คำสั่งข้างล่างนี้เพิ่มเติมเข้าไป

htpasswd /home/foobar/.htpasswd Kirk

9) คุณควรที่จะเพิ่ม “require user Kirk” ใน .htaccess ของคุณ มันควรจะเหมือนข้างล่างนี้ :

—COPY EVERYTHING BELOW——————–
AuthUserFile /home/foobar/.htpasswd
AuthGroupFile /dev/null
AuthName ByPassword
AuthType Basic
<Limit GET POST>
require user Spock
require user Kirk
</Limit>
—COPY UP TO THE LINE ABOVE—————
10) คราวนี้มาทดสอบกันโดยเข้าไปที่ http://foobar.com/member/ คุณจะมีพร้อมสำหรับให้ใส่ค่า ยูสเซอร์เนมและพาสเวิร์ด.

Adding Additional Users and Passwords pairs

1) กลับมาที่ SSH เข้าสู่แอคเคาน์ของคุณ.

2) พิมพ์คำสั่งต่าง ๆ ที่พร้อมและกดปุ่ม Enter

htpasswd /home/foobar/.htpasswd Sulu

สำคัญมาก : อย่าลืมเพิ่ม “require user Sulu” เข้าสู่ไฟล์ .htaccess ของคุณ.

3) คุณอาจจะใช้ user/password ผสมกัน คุณสามารถสร้างใน .htpasswd ของคุณ เพื่ออนุญาติกการเข้าถึงการป้งกันไดเรกทอรีอื่น — จะต้องใช้ชื่อที่ถูกต้องที่อยู่ภายในไฟล์ .htaccess

IMPORTANT: คุณควรที่จะเก็บไฟล์ .htpasswd ในโฮมไดเรกทอรี ดังนั้นมันจะซ่อนจากบุคคลอื่นได้

Asterisk can store CDR records in a MySQL database, as an alternative to CSV text files and other database formats.

How to download cdr_mysql

Due to Mysql client libraries licensing, the Mysql billing application is no longer an integrated part of the Asterisk standard distribution. It is now located in the asterisk-addons CVS directory.

Follow the instructions on http://www.asterisk.org/index.php?menu=download for Subversion download

# cd /usr/src
# svn checkout http://svn.digium.com/svn/asterisk-addons/branches/1.2 asterisk-addons-1.2

You must have mysql and mysql-devel packages installed.

• In an RPM-based Linux, you can check presence of MySQL like this:
  • rpm -qa | grep mysql
• For debian or other dpkg-based systems, check like this:
  • dpkg -l mysql-server libmysqlclient*dev
• In FreeBSD, you’ll find MySQL in the ports library, /usr/ports/databases/mysql4-server

Compile

# cd asterisk-addons-1.2
# make clean
# make
# make install

Check that in make stage that there are no mysql.h errors, which mean you are missing mysql-devel package

If make fails due to complaining about a missing “asterisk.h” file you can either copy this file from your asterisk directory, or create a soft link (”ln -s …”) for /usr/src/asterisk that points to your asterisk source directory.

Another way to resolve the missing “asterisk.h” file is to run the ./configure with option “–with-asterisk=” like
./configure –with-asterisk=MyAsteriskSourceDir

A sample configuration file, can be found on the cdr_mysql.conf page.

Copy the sample configuration file to /etc/asterisk/cdr_mysql.conf and edit it according to your requirements. Then edit your modules.conf to load cdr_addon_mysql.so and finally restart asterisk; before the restart you should, however, check that your cdr table has been created correctly and is accessible to the username and password you specified.

Table definitions for Asterisk cdr_mysql

This is the database definition you use to install in Mysql to support billing.

Create the database

mysql –user=root –password=password [-h dbhost]

Please note that the rights granted in GRANT above is the _least_ the asterisk user will need. To allow the user to do more than just add new data to the table, see the MySQL manual on the topic

for trunk version since 29 Dec 2007 must be:
GRANT INSERT, SELECT …
because cdr_addon_mysql now do DESC ‘cdr’; for check tables fields.

Hint: Copy and paste this SQL command into a text file, save it under an appropriate name, then execute the following command:

mysql –user=username –password=password databasename < nameoftextfile

Voila! The table is created for you. (:biggrin:)

An interesting note: on versions prior to v1.2, the name of the table, ‘cdr’, is hard-coded into the mysql interface, so if the table is created under a different name, the mysql CDR backend will not work. In v1.2 the table name is configurable via the “table=” option in cdr_mysql.conf

Yet another note: It probably isn’t a good idea to use the InnoDB engine. Asterisk keeps autocommit turned on by default; therefore, each INSERT query is its own transaction. This will cause the query to take significantly longer to execute. On my personal computer, the “clock time” penalty is about 4-7 times that of MyISAM on a default Ubuntu install. Please also note that the CDR table does not require the features that the InnoDB engine provides.

Storing the Unique ID

Q: It would appear that the “uniqueid” field is not being populated in the MySQL CDR DB. Is this an obsolete field or is a bug?

A: You need to define MYSQL_LOGUNIQUEID at compile time for it to use that field.

You have two options in /usr/src/asterisk-addons:
1. Either add CFLAGS+=-DMYSQL_LOGUNIQUEID to the Makefile.
Note that recently (around Asterisk 1.4.18 or before) this has changed to ASTCFLAGS+=-DMYSQL_LOGUNIQUEID
2. Or instead add a #define MYSQL_LOGUNIQUEID to the top of cdr_addon_mysql.c.

Finally perform the usual make clean, make, make install. Be sure to check the Makefile for the presence of this flag after having done a CVS update! You will most probably also want to index the uniqueid field in your cdr table to improve performance.

You will also have to add a `uniqueid` column in your mysql database after the `accountcode` column:

ALTER TABLE `cdr` ADD `uniqueid` VARCHAR(32) NOT NULL default ”

after `accountcode`;

What would I need all this for? For example you are running an AGI script and would like to be able to related AGI data with the CDR table. The problem is that the AGI script will lose connection to the call as soon as the caller hangs up, so you’ll need a way to find the correct cdr entry (that’ll also be created only after the call has been completed).

Attention! The uniqueid field is not guaranteed to be unique across the different CDR entries, even though the name suggests exactly that.

If you get the following error while compiling cdr_mysql:

/usr/bin/ld: cannot find -lz

Make sure you have the zlib-devel package installed.

extconfig.conf Setup

Add the following line, swapping your own personal values if you wish:

You can change mysql to odbc if you want to use odbc.
You can change asterisk to be the name of your database.
You can change extensions_table to be the name of the extensions table we will create below.

modules.conf Setup

Don’t forget to load the proper modules or Strange Things (TM) will happen.

or explicitly list them out:

(props to Corydon76 for setting a few of us straight)

extensions.conf Setup

The way RealTime Extensions work is through a switch statement in the dialplan. Here is an example of my context:

This tells Asterisk that any call into the ‘test’ context are to be switched to RealTime using the context “mycontext” and the family name “realtime_ext”.

context is optional: (switch => Realtime/@realtime_ext) and if left off, RealTime will use the current context, in this case “test”.

family is also optional: (switch => Realtime/@) and if left off, RealTime will use the family name “extensions”.

Currently there are no supported options so you can leave it off.

The family name above can be anything you wish. Just be sure it matches the family name you have stored in extconfig.conf: ( realtime_ext => mysql,asterisk,extensions_table )

And YES! You can have multiple switches and multiple family names using this method.

NOTE: It seems like Asterisk 1.6.0 always matches the realtime context with the name of the static context that contains the switch statement. The above example will always seek for the “Test” context in the database and not for the “mycontext”. So you might as well use:

Database Table

Now lets create the table we need:

NOTE: You can use any table name you wish, just make sure the table name matches what you have the above family name bound to.

NOTE: You should REALLY add indices for the context, exten and priority columns, as your asterisk system might slow down considerably otherwise.

When calling a macro from the extensions_conf database you need to pipe delimit your appdata where asterisk doesn’t convert the commas to pipes when it parses the data. So an example of an extensions_conf insert statement for a Macro entry would look like:
Insert into extensions_conf (context,exten,priority,app,appdata) values (’internal’,'1234′,’1′,’Macro’,'ExtensionDial|1234|SIP/1234@gateway’);

In the database it would look like the following:

context exten priority app appdata
internal 1234 1 Macro ExtensionDial|1234|SIP/1234@gateway

Caveats

Although Asterisk can handle pattern extension names in a realtime DB, there is something you ought to know. They can severely slow down Asterisk.

Why?

Because Asterisk will first attempt to do a direct match db lookup first for any lookup by extension. If it succeeds, all is well. But if it does not succeed, then it will sequentially load every extension in the context, and sequentially test to see which best matches the input number. If you have a small number of extensions, this is usually not a big deal. But if you have hundreds or thousands of extensions, finding the best match could take several seconds!

So, as a rule of thumb, do not put extension patterns in your realtime database. In 1.6, there is code added to the core pbx to use a trie-based search that makes the pattern search time almost constant, no matter how many patterns. But this is not possible in a realtime database situation. It must continue to use the old technique which involves testing all patterns in a context to find the ‘best’ match, which grows exponentially slower with the number of patterns. If you absolutely must use patterns, keep them in the in-memory dialplan (extensions.ael, extensions.conf). Let the DB do the exact matches in large datasets that it is built for.

There is a patch which adds an option to the Realtime switch which disables the search for extension pattern in the database: http://bugs.digium.com/view.php?id=13698

One more thing: It is usually wise to separate program from data. Keep the logical control dialplan stuff in extensions.ael and extensions.conf, and reserve realtime lookups for true database lookups. Keep extensions.conf and extensions.ael in some sort of source-control system, like cvs, subversion, or git, so you can track your changes and have some backup. Just because you CAN store your dialplan in a database, doesn’t mean it would be practical, expedient, or right to do so.

Testing

Now place a call into the [test] context. Asterisk should query the associated database/table for the number you dialed.

So if you dial 5745558896, you should Wait(2), then hear Allison saying the numbers “one zero two”.

If you dial 2815551212 you would hear Allison saying “I’m sorry. That’s not a valid extension”

The ‘i’ and ’s’ extensions are currently not supported. The ‘t’ extension is supported.
Edit: Latest CVS will accept ‘i’ and ’s’ / rowitech

Note on using Goto and GotoIf in the extensions table (and DIAL!)

When using a Goto or GotoIf or Dial command you may only use ‘|’ in the app_data field of the command and not ‘,’. For example, the app_data field must take the form of context|s|1 and not context,s,1. Or if using dial, it should be SIP/user|60|Tt, not SIP/user,60,Tt.

Cheers,
Matthew… and now Phil too.