Tag Archives

9 Articles
Create A Database and Its Owner on PostgreSQL
PostgreSQL database

Create A Database and Its Owner on PostgreSQL

In MySQL, these are the chants:

sudo mysql --defaults-file=/etc/mysql/debian.cnf
create database mydb;
grant all privileges on mydb.* to myuser identified by 'p@s$W0rd';

On PostgreSQL, the chants are:

sudo -u postgres createuser -DAP myuser
sudo -u postgres createdb -O myuser mydb

Very simple actually and Debian by default listen at localhost. Users can connect to its database via port 5432.

Of course, these chants are useful for developing apps. In Production, you should supply a user that can only Read/Write/Update a database. In fact, several frameworks like Laravel could setup another user just to Read the database.

Galera dengan Galera Arbitrator (GARB)

Galera dengan Galera Arbitrator (GARB)

Galera with Galera Arbitrator for conflict resolution.

Pada implementasi terdahulu, digunakan tiga basisdata agar tercapai kuorum. Kuorum ditujukan agar tidak tercapai konflik penulisan (split brain). Namun, sebenarnya untuk dua basisdata saja sudah cukup. Pasang saja Galera Arbritator (GARB) pada kepala kluster dan matikan saja salah satu node supaya terjadi kuorum.

 The twins looked at each other jubilantly but with some surprise. Although 70-534 answer analysis they 70-534 answer analysis considered themselves Scarlett’s favored suitors, they had never before gained tokens ADM-201 exam dumps of this favor so easily. Usually she made them beg and plead, while she put them off, CISSP exam topics refusing to give a 70-534 answer analysis Yes or No answer, laughing if they sulked, growing cool if they became angry. And here she had practically promised them 70-534 answer analysis the ADM-201 exam dumps whole of tomorrow—seats by her at 70-534 answer analysis the barbecue, all the waltzes (and they’d see to it that the dances were CISSP exam topics all waltzes!) and the supper intermission. This was worth getting expelled from the university.Filled ADM-201 exam dumps with new enthusiasm by their success, they CISSP exam topics lingered on, talking about the barbecue 70-534 answer analysis and the ball and Ashley Wilkes 300-208 study material CISSP exam topics and Melanie Hamilton, CISSP exam topics interrupting each other, making jokes and laughing at them, hinting broadly for invitations 300-208 study material to supper. ADM-201 exam dumps Some time had passed before they realized that Scarlett was having very little to say. The atmosphere had somehow changed. Just how, the twins did not know, but the fine glow had gone out of the afternoon. Scarlett seemed to ADM-201 exam dumps be paying little attention to 70-534 answer analysis what they 300-208 study material 70-534 answer analysis ADM-201 exam dumps said, although she made the correct answers. Sensing something they 300-208 study material could not understand, baffled and annoyed by it, the twins struggled along for a while, and then rose reluctantly, ADM-201 exam dumps looking at their 300-208 study material watches.The sun was low CISSP exam topics across the new-plowed fields and the tall woods across the river ADM-201 exam dumps were looming ADM-201 exam dumps blackly in silhouette. Chimney swallows were darting swiftly across the yard, and chickens, ducks and turkeys were waddling 70-534 answer analysis and strutting and straggling in from the fields.Stuart bellowed: “Jeems!” And after an interval a 70-534 answer analysis tall black boy of their own age ran breathlessly around CISSP exam topics the house and out toward the ADM-201 exam dumps tethered horses. Jeems was their 300-208 study material body servant and, like CISSP exam topics the dogs, accompanied them everywhere. He had been CISSP exam topics their childhood playmate and had been given to the twins for their own ADM-201 exam dumps on their tenth birthday. At the sight of him, CISSP exam topics CISSP exam topics the Tarleton hounds rose up out of the red dust and stood waiting expectantly for their masters. The 300-208 study material boys bowed, shook hands and 300-208 study material told CISSP exam topics Scarlett they’d be over 300-208 study material at the 300-208 study material Wilkeses’ early in the morning, waiting for her. Then CISSP exam topics they were ADM-201 exam dumps off down the 70-534 answer analysis walk CISSP exam topics at a rush, mounted their horses and, followed by ADM-201 exam dumps ADM-201 exam dumps Jeems, went down the avenue of cedars at a gallop, waving their hats and yelling back to her.When they had rounded the curve of the dusty road that hid them from Tara, Brent drew his horse to a ADM-201 exam dumps stop under a clump of dogwood. Stuart halted, 70-534 answer analysis too, and the darky boy pulled up a few paces behind 300-208 study material them. The horses, feeling slack reins, 300-208 study material stretched down their necks to crop the tender spring grass, and the patient hounds lay down again in the 300-208 study material CISSP exam topics soft red dust and looked up longingly at the chimney swallows circling in the gathering dusk. 300-208 study material Brent’s wide ingenuous face was puzzled and mildly indignant.“Look,” he said. “Don’t it look to you like she 70-534 answer analysis would of asked us to stay for supper?”  Nor did 70-534 answer analysis James and Andrew, who took him into their store in Savannah, regret his lack of education. His clear hand, his 300-208 study material accurate CISSP exam topics figures and his shrewd ADM-201 exam dumps ability in bargaining won their respect, where a knowledge 70-534 answer analysis of literature and a fine appreciation of music, had young 300-208 study material Gerald possessed them, would have moved them to snorts of contempt. America, in the early years of the century, had been kind to the Irish. James and Andrew, who had begun by hauling goods in covered wagons 70-534 answer analysis from Savannah to Georgia’s inland towns, had prospered into a store of their own, and Gerald prospered with them.

GARB adalah sebuah layanan khusus untuk memilih (voting) di Galera. Dia tidak ada ada data di dalamnya.

Pasang GARB

Supaya sama, saya asumsikan kita memasang repositori resmi dari MariaDB.

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
sudo apt-get update

Mari memasang GARB:

sudo apt-get install galera-arbitrator-3

Selesai.

Konfigurasi GARB

Ubah berkas /etc/default/garb sebelum memulai GARB.

# Copyright (C) 2012 Codership Oy
# This config file is to be sourced by garb service script.

# A comma-separated list of node addresses (address[:port]) in the cluster
GALERA_NODES="192.168.101.10:4567,192.168.101.11:4567"

# Galera cluster name, should be the same as on the rest of the nodes.
GALERA_GROUP="apakek_cluster"

# Optional Galera internal options string (e.g. SSL settings)
# see http://galeracluster.com/documentation-webpages/galeraparameters.html
# GALERA_OPTIONS=""

# Log file for garbd. Optional, by default logs to syslog
# LOG_FILE=""

Jangan lupa untuk menyertakan port! Ada galat dikonfigurasi GARB bila kita tidak secara spesifik menyebutkan port Galera.

Jalankan GARB

sudo /etc/init.d/garb start

Bila sudah benar, seharusnya saat menguji jumlah node dalam kluster tetap tiga.

$ sudo mysql --defaults-file=/etc/mysql/debian.cnf -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
+--------------+
| cluster size |
+--------------+
| 3            |
+--------------+

Selesai.

KMail Gagal Dijalankan

KMail Gagal Dijalankan

TL;DR

Pada suatu waktu sehabis memperbaharui sistem, KMail saya tidak lagi dapat dijalankan. Alasan kegagalan karena layanan Akonadi tidak berjalan. Hal ini disebabkan oleh karena pembaharuan MySQL ke versi 5.6. Untuk mengatasi itu, paket MySQL diganti dengan MariaDB.

Versi Lengkap

Saya sempat gagal menjalankan KMail. Alasan yang diberikan adalah karena layanan Akonadi tidak berjalan. Dari jurnal Akonadi, saya dapati pesan kesalahan berikut:

stderr: "2015-05-21 07:25:45 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-05-21 07:25:45 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-0ubuntu2) starting as process 13829 ...

Ternyata, Akonadi tidak kompatibel dengan MySQL 5.6. Menurut sumber yang saya temukan dari wawancara dengan Mbah Gugel, saya harus mengganti MySQL dengan MariaDB. Ya, sudah:

sudo apt-get install mariadb-common mariadb-client-core-10.0 mariadb-server-core-10.0

Lalu, saya jalankan ulang KMail dan berhasil. Oh, iya, saya menggunakan Ubuntu Vivid.

MYSQL: Mencari 10 Tabel Dengan Ukuran Terbesar

MYSQL: Mencari 10 Tabel Dengan Ukuran Terbesar

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

 

Bacaan Lebih Lanjut

MySQL Error: Table marked as Crashed

MySQL Error: Table marked as Crashed

Sometimes life’s a bitter chocolate and you found a situation where the error log tells you this:

[ERROR] /usr/sbin/mysqld: Table './DATABASE/TABLE' is marked as crashed and last (automatic?) repair failed

It means a table needs to be repaired. So,

  1. Go to the table directory:
    cd /var/lib/mysql/DATABASE/
  2. Stop the MySQL first:
    invoke-rc.d mysql stop
  3. Fix the table:
    myisamchk -r -v -f TABLE
  4. Start the MySQL again:
    invoke-rc.d mysql start

and hope there is nothing wrong with this. If there is a massive failure, it could make things harder. Your system could runs high load because MySQL server tries to fix it automatically.

Oh, well, c’est la vie.

Mencoba SLiMS Cendana 7

Mencoba SLiMS Cendana 7

SLiMS Open Source Library Management System (SLiMS) adalah sebuah manajemen koleksi perpustakaan daring. Selain keanggotaan, kode batang, dan pengaturan koleksi, SLiMS mendukung OPAC dengan pustaka Javascript terbaru. Sayangnya, dia hanya mendukung basisdata MySQL (MariaDB, Percona, dan sejenisnya), tetapi tidak PostgreSQL. Padahal, PostgreSQL lebih bagus dibandingkan dengan MySQL.

Kali ini menjelang pulang kantor saya akan memasang SLiMS dengan menggunakan sistem berbasis NGINX, PHP-FPM, dan MySQL. Ingat, kawan-kawan, ini adalah sebuah keisengan. Jangan lupa untuk riset lebih lanjut. Saya membangun sistem ini dari BlankOn 9 hasil debootstrap.

Memasang Hal-hal Dasar

Mari kita hal-hal dasar sebelum memasang Senayan

Memasang Sistem Dasar

Mari memasang aplikasi-aplikasi yang diperlukan.

$ sudo apt-get install nginx php5-fpm mysql-server php5-mysqlnd php5-gd

NGINX, PHP5-FPM, dan MySQL dipasang. Saya menggunakan MySQL Native Driver (MySQL ND) bukan libmysqlclient. Hal ini karena MySQL ND lebih efisien.(Andrea Usu, 2010) Tak lupa saya juga memasang PHP GD karena SLiMS menggunakannya untuk memroses gambar dan kode batang.

Membuat Basisdata

Lalu mari buat sebuah basisdata Senayan:

$ sudo mysql --defaults-file=/etc/mysql/debian.cnf

Debian menggunakan berkas konfigurasi untuk masuk sebagai administrator MySQL. Berkas konfigurasi ini tidak dapat diakses kecuali oleh root.

mysql> create database senayandb;
mysql> grant all privileges on senayandb.* to senayan@localhost identified by 'senayan123';
mysql> flush privileges;

Setelah itu, keluar dari klien PostgreSQL.

mysql> \q

Anda tentunya tidak akan menggunakan senayan, senayandb, dan “senayan123”, ‘kan? Anda tidak sebodoh itu, ‘kan? Gunakan nama yang lain yang lebih aman!

Memasang Senayan

Saatnya mengunduh Senayan:

$ wget http://slims.web.id/download/slims7-cendana-stable.tar.gz

Lalu memekarkan berkas tersebut:

$ tar xvfz slims7-cendana-stable.tar.gz

Entah mengapa seluruh berkas yang saya ekstraksi memiliki izin 755. Artinya, setiap berkas tersebut rawan untuk bisa dieksekusi. Sebelum diolah lebih lanjut, mari kita betulkan:

$ find slims7_cendana-slims7-cendana/ -type f -exec chmod 644 {} \;

Pindahkan ke /var/www dan buat menjadi milik www-data

$ sudo mv slims7_cendana-slims7-cendana /var/www
$ sudo chown -R 33:33 /var/www

Saatnya mengaktifkan NGINX. Buat konfigurasi situs. Saat ini saya membuat konfigurasi situs berdasarkan /etc/nginx/sites-available/default. Anda dapat mengutak-atik lebih lanjut.

$ cat >> slims < EOF
server {
    listen 80 default_server;
    listen [::]:80 default_server ipv6only=on;

    root /var/www;
    index index.html index.htm index.php;

    # Make site accessible from http://localhost/
    server_name localhost;

    location / {
        # First attempt to serve request as file, then
        # as directory, then fall back to displaying a 404.
        try_files $uri $uri/ =404;
        # Uncomment to enable naxsi on this location
        # include /etc/nginx/naxsi.rules
    }

    # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
    #
    location ~ \.php$ {
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        # NOTE: You should have "cgi.fix_pathinfo = 0;" in php.ini

        # With php5-cgi alone:
        #fastcgi_pass 127.0.0.1:9000;
        # With php5-fpm:
        fastcgi_pass unix:/var/run/php5-fpm.sock;
        fastcgi_index index.php;
        include fastcgi_params;
    }

    # deny access to .htaccess files, if Apache's document root
    # concurs with nginx's one
    #
    location ~ /\.ht {
        deny all;
    }
}
EOF
$ sudo cp slims /etc/nginx/sites-available/slims
$ rm slims

Cara menyunting konfigurasi ini sebenarnya dapat dilakukan dengan mudah menggunakan penyunting favorit Anda (ViM, EMACS, pico, dll.)

$ sudo $EDITOR /etc/nginx/sites-available/slims

Isi dengan konfigurasi yang diwarnai merah. Perhatikan, ganti localhost dengan domain Anda yang benar.

Sekarang, saatnya mengaktifkan SLiMS dan menonaktifkan konfigurasi baku.

$ sudo rm -f /etc/nginx/sites-enabled/default
$ sudo ln -sf /etc/nginx/sites-available/slims /etc/nginx/sites-enabled/slims

Setelah itu, saatnya menyalakan ulang NGINX.

$ sudo invoke-rc.d nginx restart

Selesai. Selanjutnya tinggal mengarahkan peramban kepada situs kita.

Mengonfigurasi Situs

Kalau Anda telah selesai mengonfigurasi dengan benar, maka tampilan pertama dari SLiMS di peramban adalah laman pemasangan.

A Welcome screen for SLiMS setup.

01. A welcoming start for SLiMS setup.

Pilih "Let's Start The Installation" untuk lanjut. Maka pada laman berikutnya akan ditampilkan isian untuk mengonfigurasi.

Setup configuration for SLiMS

02. Setup configuration for SLiMS

Halaman kedua intinya untuk mengonfigurasi basisdata dan admin SLiMS. Contoh yang saya isi untuk isian tersebut:

Database Host localhost
Database Name senayandb
Database Username senayan
Database Password **********
Generate Sample Data Yes
Username pustakawan
Password *************
Retype Password *************

Saya memilih untuk memasang data contoh agar mendapatkan gambaran mengenai apa itu SLiMS. Nanti kalau sudah digunakan secara penuh, tentunya data contoh ini tak perlu dipasang. Bahkan, dapat dihapus begitu saja. Setelah mengisi itu semua, pilih "Continue" untuk lanjut ke halaman terakhir.

Setup completed

03. Setup completed

Halaman terakhir memberikan rekomendasi untuk menghapus direktori "install/". Namun sebelumnya, mari memulai SLiMS dengan memilih "OK, start the SLiMS". Maka, Anda akan dibawa ke laman depan SLiMS.

SLiMS frontpage

SLiMS frontpage

Seperti rekomendasi pemasang SLiMS, mari hapus direktori pemasangan dan juga membetulkan perizinan berkas yang ada.

$ sudo rm -rf /var/www/install
$ sudo chmod 644 /var/www/sysconfig.local.inc.php

Sampai sini proses konfigurasi awal berhasil. Anda sudah dapat berpuas diri dan berpesta ria. Selanjutnya adalah proses yang saya hendak lakukan di dalam SLiMS agar lebih bagus. Tapi, itu, 'kan, tergantung selera masing-masing.

Bacaan Lebih Lanjut

Daftar Pustaka

Andrea Usu (2010, August 3). Benchmark: libmysql vs mysqlnd. Retrieved Jan 16, 2014 from https://usu.li/benchmark-libmysql-vs-mysqlnd/.^
Dumping MD5 Password From MySQL to LDAP

Dumping MD5 Password From MySQL to LDAP

I got this knowledge from Koben. Credit goes to him.

Someone migrating from MySQL to LDAP as their profile backend. The problem is both of them using different conversion. MySQL using hexdump of the actual MD5sum while LDAP using BASE64 to store actual MD5sum.

Fortunately, GNU/Linux have this utility:

xxd

, a tool that convert hexdump to hex and vice versa. With MySQL, you would get this:

{md5}5fb1a056793ca0e9acd77cad95420afe==

Cut the “{md5}” prefix and the “==” suffix. And you get “5fb1a056793ca0e9acd77cad95420afe”. Then, using

xxd

, convert the hexdump into binary. Let’s assume the value “5fb…afe” was stored in

$REAL_VALUE

.

$ echo $REAL_VALUE | xxd -r -p

It would produce non-printable characters. That’s the real hex value. Convert it into BASE64:

$ echo $REAL_VALUE | xxd -r -p | base64

There, you would have:

X7GgVnk8oOms13ytlUIK/g==

Add “{MD5}” prefix so it would be:

{MD5}X7GgVnk8oOms13ytlUIK/g==

Now, that’s what you would like to have in the LDAP. But, if you want to put it in an LDIFF file, it has to be rebase with BASE64. So, the value that in the LDIFF file is:

$ echo "{MD5}X7GgVnk8oOms13ytlUIK/g==" | base64
e01ENX1YN0dnVm5rOG9PbXMxM3l0bFVJSy9nPT0K

So, the value in LDIFF is “

e01ENX1YN0dnVm5rOG9PbXMxM3l0bFVJSy9nPT0K

“.

To sum all, the steps to convert MySQL to LDAP MD5 hash are:

  1. Remove “{md5}” prefix and “==” suffix.
  2. Convert hexdump to hex with “xxd -r -p”.
  3. Convert hex to BASE64 with “base64”.
  4. Append “{MD5}” at the beginning.
  5. Do BASE64 again if you are trying to insert it via LDIFF file.
  6. DONE.

Don’t forget, sed awk or ViM is your friend.