Cara Membuat User MySQL Berikut Database dan Privileges

Berikut adalah snippet singkat bagi jenengan yang ingin membuat user MySQL berikut dengan akses database yang sama. Gunakan terminal/konsole untuk melakukan perintah-perintah berikut:


mysql -u namauserroot -p

untuk membuat database: Continue reading Cara Membuat User MySQL Berikut Database dan Privileges

Unable to start MySQL on XAMPP/MAMP OSX Yosemite

aferrini write:

Hi,

I tried to install last bitnami stack for Mac, in Yosemite preview 5, but mysqld doesn’t start and in the error log I find this message:

2014-08-06 17:19:04 5277 mysqld_safe Starting mysqld daemon with databases from /Applications/XAMPP/xamppfiles/var/mysql
dyld: Symbol not found: _sqlite3_intarray_bind
Referenced from: /System/Library/Frameworks/CoreData.framework/Versions/A/CoreData
Expected in: /Applications/XAMPP/xamppfiles/lib/libsqlite3.dylib
in /System/Library/Frameworks/CoreData.framework/Versions/A/CoreData
2014-08-06 17:19:05 5277 mysqld_safe mysqld from pid file /Applications/XAMPP/xamppfiles/var/mysql/MacBook-Pro.local.pid ended
Any suggestion?
Thanks

Continue reading Unable to start MySQL on XAMPP/MAMP OSX Yosemite

How to use MySQL Query Browser on Remote Database on Mandriva

MySQL Query Browser is a part of MySQL database product from MySQL (now it owned by Oracle,Inc.). It is a GUI tool to run MySQL Query and get the result on pretty field-way view. MySQL Query browser mostly used by administrator on it own server, altough it can be managing a remote database query. Here is the how-to:

1. Install the MySQL Query Browser with urpmi on Mandriva 2010.1


urpmi --auto-update

urpmi mysql-query-browser

2. Now, run the terminal/Konsole. Continue reading How to use MySQL Query Browser on Remote Database on Mandriva

Tutorial MySQL Multi Master Replication dengan Galera

Untuk mengikuti tutorial ini setidaknya anda membutuhkan 4 server, 3 server sebagai uji coba kluster server MySQL dan 1 sebagai load balancer. Install dan buat 4 server Linux dengan Virtualbox/VMWare.

Tutorial ini menggunakan skenario 3 server replika untuk menangani seluruh request dari Client dengan ip address sebagai berikut:
mysql1: 192.168.1.138/24
mysql2: 192.168.1.139/24
mysql3: 192.168.1.140/24

Dalam kluster ini, server mysql2 akan terkoneksi ke mysql1 dan server mysql3 ke mysql2. Jika koneksi antara mysql2 dan mysql3 sudah terbangun, komunikasi antara mysql1 ke mysql3 akan dibentuk secara otomatis dengan komunikasi satu arah (termasuk ke semua node pada kluster).

Instalasi MySQL

# apt-get install libaio1 libdbi-perl libdbd-mysql-perl mysql-client rsync

Install MySQL server dengan wsrep patch

Debian/Ubuntu 32 bits:

# wget https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/mysql-server-wsrep-5.5.28-23.7-i386.deb && dpkg -i mysql-server-wsrep-5.5.28-23.7-i386.deb

Debian/Ubuntu 64 bits:

# wget https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/mysql-server-wsrep-5.5.28-23.7-amd64.deb && dpkg -i mysql-server-wsrep-5.5.28-23.7-amd64.deb

Download dan install Galera

– 32 bits:

# wget https://launchpad.net/galera/2.x/23.2.2/+download/galera-23.2.2-i386.deb && dpkg -i galera-23.2.2-i386.deb

– 64 bits:

# wget https://launchpad.net/galera/2.x/23.2.2/+download/galera-23.2.2-amd64.deb && dpkg -i galera-23.2.2-amd64.deb

Konfigurasi Awal MySQL

# /etc/init.d/mysql start
# mysql -u root
mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'P@ssw0rd';
mysql> UPDATE mysql.user SET Password=PASSWORD('P@ssw0rd') WHERE User='root';
mysql> GRANT ALL ON *.* to sst@'%' IDENTIFIED BY 'sstpasswd';

Atur supaya mysqld dijalankan sesaat setelah booting up:

# update-rc.d mysql defaults

Konfigurasi Server MySQL1

# vi /etc/mysql/conf.d/wsrep.cnf
 
# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib/galera/libgalera_smm.so
 
# Group communication system handle
wsrep_cluster_address="gcomm://"
 
# State Snapshot Transfer method
wsrep_sst_method=rsync
 
# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:
wsrep_sst_auth=sst:sstpasswd
# /etc/init.d/mysql restart

NB. Untuk sementara, alamat node masih gcomm://, selanjutnya akan dikonfigurasi pada server 3.

Konfigurasi Server MySQL2

# vi /etc/mysql/conf.d/wsrep.cnf
 
# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib/galera/libgalera_smm.so
 
# Group communication system handle
wsrep_cluster_address="gcomm://192.168.1.138:4567"
 
# State Snapshot Transfer method
wsrep_sst_method=rsync
 
# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:
wsrep_sst_auth=sst:sstpasswd
# /etc/init.d/mysql restart

Konfigurasi Server MySQL3

# vi /etc/mysql/conf.d/wsrep.cnf
 
# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib/galera/libgalera_smm.so
 
# Group communication system handle
wsrep_cluster_address="gcomm://192.168.1.139:4567"
 
# State Snapshot Transfer method
wsrep_sst_method=rsync
 
# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:
wsrep_sst_auth=sst:sstpasswd
# /etc/init.d/mysql restart

Konfigurasi Ulang Server MySQL1

# vi /etc/mysql/conf.d/wsrep.cnf
 
wsrep_cluster_address="gcomm://192.168.1.140:4567"
# mysql -u root -p
mysql> set global wsrep_cluster_address='gcomm://192.168.1.140:4567';

Checking wsrep variables

mysql> show status like 'wsrep%';
+----------------------------+----------------------------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------------------------+
| wsrep_local_state_uuid | 17048124-4c6e-11e2-0800-5c8217cefd3f |
| wsrep_protocol_version | 4 |
| wsrep_last_committed | 1 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_received | 10 |
| wsrep_received_bytes | 1039 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_incoming_addresses | 192.168.1.138:3306,192.168.1.140:3306,192.168.1.139:3306 |
| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 17048124-4c6e-11e2-0800-5c8217cefd3f |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 23.2.2(r137) |
| wsrep_ready | ON |
+----------------------------+----------------------------------------------------------+
40 rows in set (0.00 sec)

The most important variables are wsrep_ready, if the value is ON it means that the cluster is working, and wsrep_cluster_size that is equals to the number of nodes that is composed the cluster.

SkySQL Announce Merging with MariaDB

SkySQL today announced that it has signed a merger agreement with Monty Program Ab, creators of the fastest growing open source database technology: MariaDB.  This merger reunites key members of the original MySQL AB developer and services teams. Their aim is to develop MariaDB into a truly interoperable, NewSQL open source database in collaboration with its customers, partners and the community. The company also pledges to further evolve its offering for users of the MySQL database.

MariaDB is truly open source and is compatible with the MySQL database while providing new extensions and features. Customers and users move to MariaDB for its performance. It is uniquely capable of interfacing with NoSQL database systems like Cassandra and LevelDB as well as to access data in other formats thanks to pluggable storage engines. This allows customers to leverage the capabilities of the latest database technologies as well as access data in legacy systems without expensive and risky database migrations. This merger brings cloud and big data capabilities together, which will enable the new company to become an interface for fellow open source technology providers. Continue reading SkySQL Announce Merging with MariaDB

Belajar SQL: Select Data Di Tabel A Yang tidak Ada di Tabel B

Tips singkat berikut berisi query yang mungkin berguna bagi anda yang mencari query SQL yang digunakan untuk mencari data di tabel A yang tidak ada di tabel B. Contoh kasus, jika A adalah tabel master dan tabel B adalah tabel data isian, maka anda diminta untuk mencari data siapa yang belum mengisi. Sehingga logika yang dibangun adalah, select data ditabel A yang tidak ada di tabel B.

Sintaks yang dipakai adalah dengan sintaks NOT EXISTS. Contoh:

SELECT * FROM tabel_a WHERE NOT EXISTS (SELECT * FROM tabel_b)

Continue reading Belajar SQL: Select Data Di Tabel A Yang tidak Ada di Tabel B

How to Compressing MySQLdump output file

The result of mysqldump is a flat text file containing the sql commands used to restore the mysql databases/tables that were dumped. Normally the result of mysqldump is compressed (in regular backups) because the size of the resulted file is normally big and good compression rates are achieved on such text files.
To do this manually using gzip we just run:

gzip -v outputfile.sql

This quick tip shows how you can run the same thing on the fly with only one command directly compressing the resulted file. Continue reading How to Compressing MySQLdump output file

Backup/Restore Database MySQL dengan PHP Berkemampuan Multithreaded

Backup dan restore database MySQL adalah kegiatan rutin harian seorang administrator. Berbagai tool dan script mungkin digunakan dan dijalankan secara otomatis oleh cron setiap harinya. Semakin besar database yang dicadangkan, semakin lama waktu yang dibutuhkan. Kebanyakan tool backup hanya bekerja tunggal, belum bisa multithreading. Nah, bagaimana jika anda ingin melakukan backup dan restore MySQL sampai pada tataran tabel per tabel? sehingga hanya tabel-tabel transaksi penting yang akan dibackup harian? dan bisa berjalan multithread?

Solusinya ada di ASHDumper, sebuah script PHP buatan om Aziz Saleh Hussain (dari Amerika) yang baru saja memenangkan PHPClasses Inovation Award of The Month (bulan September 2012). ASHDumper sebagai tool backup akan membackup tabel per tabel MySQL dengan 1 thread (1 service) tiap tabelnya, sehingga proses lebih berjalan cepat karena multithreading. Untuk dapat menggunakan ASHDumper, PHP/MySQL anda harus memenuhi syarat berikut:
– proc_open enabled (lihat php.ini)
– akses ke mysql atau mysqldump
– direktori output yang writeable Continue reading Backup/Restore Database MySQL dengan PHP Berkemampuan Multithreaded

MariaDB is Release their own Clustering Version of MySQL

onty Program’s MariaDB team has announced the first availability of MariaDB Galera, a clustering solution that is the result of a partnership between the MariaDB team and Codership. The alpha release, which should not be used for production systems, includes a merge of MariaDB 5.5.25 with Galera Cluster from Codership in a combination that is said to offer a read/write scalable system, synchronous multi-master replication and guaranteed data consistency.

“We’ve received a significant amount of requests for a MariaDB based Galera cluster and today we’re proud to deliver that,” said Michael “Monty” Widenius, adding that Monty Program and partners are preparing to roll out the GA product and, via SkySQL, professional services, in what is planned to be a rapid release cycle, Codership’s Galera Cluster software was originally developed for MySQL. Continue reading MariaDB is Release their own Clustering Version of MySQL

Konfigurasi MySQL untuk Trafik Tinggi

Secara default MySQL hadir dengan konfigurasi yang sudah cukup mumpuni melayani request database puluhan orang, apalagi kalau cuma sekedar melayani testing environment oleh sang Developer saja. Namun jika pengguna aplikasi anda ini ratusan atau bahkan ribuan, anda harus melakukan tuning ulang untuk server bertrafik tinggi.

Untuk melakukan konfigurasi tersebut, silakan buka file my.cnf pada direktori /etc/mysql/my.cnf (jika anda menggunakan linux) atau C:\xampp\mysql\bin\my.ini.

Silakan pakai dan ubah konfigurasi yang saya rekomendasikan berikut: Continue reading Konfigurasi MySQL untuk Trafik Tinggi

Replikasi Master to Master MySQL

Persiapan

Untuk dapat mencoba melakukan replikasi master to master mysql ini, silakan persiapkan (minimal) 2 server mysql siap pakai, contoh dengan IP 10.2.9.1 dan 10.2.9.2.

Langkah Replikasi Master to Master MySQL

1. Sesuaikan konfigurasi masing-masing server MySQL sebagai master. Ubah pada tiap file /etc/my.cnf.

Untuk master server 1 misalnya: Continue reading Replikasi Master to Master MySQL

Menyimpan sebuah array ke suatu kolom tabel di database MySQL dg PHP

Snippet kecil PHP-MySQL ini sengaja saya sekadar sebagai referensi bagi yang sedang mencari-cari bagaimana cara menyimpan array ke dalam suatu field didatabase MySQL. Kalau anda pernah coba-coba menyimpan array tanpa melakukan pemrosesan terlebih dulu, maka di field anda hanya terisi dengan kata ‘Array’ dan otomatis tidak bisa dimanfaatkan.

Untuk menyimpan sebuah array ke suatu kolom tabel didatabase MySQL dengan PHP dapat anda lakukan dengan melakukan serialisasi array. Serialisasi array ini dapat anda lakukan dengan mudah di PHP karena telah ada fungsi serialize() dan unserialize(). Continue reading Menyimpan sebuah array ke suatu kolom tabel di database MySQL dg PHP