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

Facebook, Google and Others Announce MySQL fork, WebScaleSQL Project

webscalesqlA number of tech companies including Facebook, Google, LinkedIn, and Twitter announced WebScaleSQL on Thursday as a project that adapts MySQL to their massively bigger database needs.

WebScaleSQL is a branch of MySQL designed to run MySQL at scale and gaining maximum performance from the database.

The WebScaleSQL.org project site says, “We aim to create a more integrated system of knowledge-sharing to help companies leverage the great features already found in MySQL 5.6, while building and adding more features that are specific to deployments in large scale environments. In the last few months, engineers from all four companies have contributed code and provided feedback to each other to develop a new, more unified, and more collaborative branch of MySQL.” 

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

PrestoDB Engine Open-sourced by Facebook

Facebook has open-sourced some interesting in-house code in the past like Flashcache for the Linux kernel, the Folly C++ library, and the HipHop Virtual Machine. The latest open-source Linux-compatible software release coming out of Facebook is Presto, their tool for interacting with petabytes of information.

Presto is a distributed SQL query engine developed in-house at Facebook that they use for scouring their 300+ petabytes of data at the social network company. Facebook uses Hadoop clusters but Hive and other existing open-source tools didn’t provide the low-latency results the company wanted, so a team set to develop Presto.  Continue reading PrestoDB Engine Open-sourced by Facebook

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

MongoDB 2.4 Enhanced With Text Searching, Hashed Indexing & Sharding etc

The latest release of MongoDB 2.4 expands the NoSQL database’s capabilities with the addition of text search. Described as “one of the all time most requested features in MongoDB”, text search has been added as a beta feature to MongoDB 2.4. It is built around textindexes, case-insensitive indexes which omit the language-specific stop words and use language-specific stemming for words.

The indexing supports Danish, Dutch, English, Finnish, French, German, Hungarian, Italian, Norwegian, Portuguese, Romanian, Russian, Spanish, Swedish and Turkish. It is also possible to specify none as a language, in which case the stop words will not be dropped and no stemming or tokenising will occur. Users can create new text indexes and then use the text command to search the indexes for a word or phrase using a number of techniques.

Another feature in 2.4 is the hashed index and sharding. Hash-based sharding allows data and CPU load to be spread well between distributed database nodes in a simple to implement way. The developers recommend it for cases of randomly accessed documents or unpredictable access patterns. New Geospatial indexes with support forGeoJSON and spherical geometry allow for 2dsphere indexing; this, in turn, offers better spherical queries and can store points, lines and polygons. Continue reading MongoDB 2.4 Enhanced With Text Searching, Hashed Indexing & Sharding etc

MoSQL, The Hub of MongoDB to Traditional SQL

Faced with a need to have a live backup of their MongoDB clusters in a PostgreSQL instance, the developers at Stripe developed, and have now released as open source, MoSQL. MoSQL is a Ruby application which streams the changes in a MongoDB cluster and reformats them into a stream of SQL which can be sent to a more traditional SQL-based relational database.

Configuring it does require that the administrators create a “collection map” that maps the fields of a JSON document into the columns of the SQL database. MoSQL can optionally be configured to record any fields which it comes across in an _extra_props column for later analysis. The developers recommend running MoSQL for the first time on a secondary node of the MongoDB cluster as it will create a lot of disk activity. Continue reading MoSQL, The Hub of MongoDB to Traditional SQL

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

MongoDB 2.2 adds aggregation

The latest release of MongoDB, version 2.2, has been announced and is making it easier to process documents inside the NoSQL database with the addition of an Aggregation Framework to the JSON document storing server. The NoSQL database developed by10Gen also sees improvements in “Data Center Awareness”, for situations where clusters of the database are being operated over a number of geographically diverse locations, by allowing data to be sharded on the basis of tags.

For better performance, the developers have also eliminated the global lock in its mongod process and moved locking to a per database level. Where systems have a single database in use, concurrency should also be improved because a new subsystem tries to avoid locking on page faults. Continue reading MongoDB 2.2 adds aggregation