Category Archives: MySQL

Panduan Lengkap Menggunakan Fungsi Matematika di MySQL

Fungsi matematika di MySQL seperti ABS(), ACOS(), ASIN(), ATAN2(), ATAN(), ATAN(), CEIL(), CEILING(), CONV(), COS(), COT(), CRC32(), DEGREES(), EXP(), LN(), LOG10(), LOG2(), LOG(), MOD(), OCT(), PI(), POW(), POWER(), RADIANS(), RAND(), ROUND(), SIGN(), SIN(), SQRT(), TAN(), TRUNCATE() dan FLOOR() dapat digunakan untuk melakukan operasi matematika pada data yang disimpan di database MySQL.

Semua fungsi matematika di MySQL ini dapat digunakan untuk melakukan operasi matematika pada data yang tersimpan dalam database MySQL.

Berikut adalah penjelasan fungsi-fungsi matematika tersebut beserta contoh codenya: » Read more

Panduan Penggunaan Fungsi Enkripsi dan Dekripsi di MySQL untuk Keamanan Data

Fungsi-fungsi enkripsi dan dekripsi di MySQL seperti AES_ENCRYPT(), AES_DECRYPT(), DES_ENCRYPT(), DES_DECRYPT(), ENCRYPT() dan PASSWORD() digunakan untuk melindungi data sensitif di database.

Contoh penggunaan AES_ENCRYPT() dan AES_DECRYPT() adalah sebagai berikut:

Fungsi DECODE() dan ENCODE() digunakan untuk encoding dan decoding data, contohnya seperti ini:

-- Encoding data
SELECT ENCODE('s3ns1t1f', 'k3y') as encoded_data;

-- Decoding data
SELECT DECODE(ENCODE('s3ns1t1f', 'k3y'), 'k3y') as decoded_data;
-- Enkripsi data
SELECT AES_ENCRYPT('s3ns1t1f', 'k3y') as encrypted_data;

-- Dekripsi data
SELECT AES_DECRYPT(AES_ENCRYPT('s3ns1t1f', 'k3y'), 'k3y') as decrypted_data;

Fungsi COMPRESS() dan UNCOMPRESS() dapat digunakan untuk mengompresi dan men-dekompresi data, contohnya seperti ini: » Read more

Fungsi Dasar MySQL, Penjelasan dan Contoh Penggunaan Query Database MySQL

Penggunaan kode dalam membuat sebuah website memang sangat diperlukan untuk melakukan berbagai macam aksi, salah satunya adalah melakukan manipulasi terhadap database. Salah satu contoh manipulasi tersebut adalah dengan menggunakan query SQL seperti SELECT, UPDATE, dan DELETE. Selain fungsinya yang sangat penting, penggunaan kode-kode tersebut juga dapat mempengaruhi SEO (Search Engine Optimization) sebuah website.

SELECT: Fungsi SELECT digunakan untuk memilih kolom yang akan ditampilkan dalam hasil query. Contoh penggunaannya pada tabel anggota: » Read more

Repair All Databases on MySQL

mysqlcheck --repair --all-databases -u root -p

 

mysqlcheck --repair --yourmysqldb -u yourmysqluser -p

Solusi Can’t create database (errno: 13) pada MySQL

Secara default data mysql tersimpan di /var/lib/mysql , namun karena alasan kemudahan backup dan kapasitas saya mengubahnya di partisi lain yang termount di /mysqldata/. Untuk keperluan tersebut saya menjalankan perintah berikut:

sed -i 's/datadir=\/var\/lib\/mysql/datadir=\/ripanel\/mysqldata/g' /etc/my.cnf
mkdir -p /mysqldata
/etc/init.d/mysqld stop
cp -rap /var/lib/mysql/* /mysqldata
chown mysql.mysql /mysqldata 
/etc/init.d/mysqld start

» Read more

Cara Install MySQL 5 di Debian

Kali ini saya install MySQL 5

Caranya sangat mudah menggunakan apt-get dari terminal

sudo apt-get install mysql-server

Tampilan prosesnya seperti ini:

rahmatriyanto@debian6:~$ su
Password: 
root@debian6:/home/rahmatriyanto# sudo apt-get install mysql-server
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following extra packages will be installed:
  libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient16
  libnet-daemon-perl libplrpc-perl mysql-client-5.1 mysql-common
  mysql-server-5.1 mysql-server-core-5.1
Suggested packages:
  libipc-sharedcache-perl libterm-readkey-perl tinyca
The following NEW packages will be installed:
  libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient16
  libnet-daemon-perl libplrpc-perl mysql-client-5.1 mysql-common mysql-server
  mysql-server-5.1 mysql-server-core-5.1
0 upgraded, 11 newly installed, 0 to remove and 228 not upgraded.
Need to get 22.2 MB/23.3 MB of archives.
After this operation, 54.7 MB of additional disk space will be used.
Do you want to continue [Y/n]? y
Get:1 https://security.debian.org/ squeeze/updates/main mysql-common all 5.1.73-1 [71.2 kB]
Get:2 https://security.debian.org/ squeeze/updates/main libmysqlclient16 i386 5.1.73-1 [1,927 kB]
Get:3 https://security.debian.org/ squeeze/updates/main mysql-client-5.1 i386 5.1.73-1 [9,615 kB]
Media change: please insert the disc labeled                                   
 'Debian GNU/Linux 6.0.3 _Squeeze_ - Official i386 DVD Binary-1 20111008-13:01'
in the drive '/media/cdrom/' and press enter

Media change: please insert the disc labeled                                   
 'Debian GNU/Linux 6.0.3 _Squeeze_ - Official i386 DVD Binary-1 20111008-13:01'
in the drive '/media/cdrom/' and press enter

Media change: please insert the disc labeled                                   
 'Debian GNU/Linux 6.0.3 _Squeeze_ - Official i386 DVD Binary-1 20111008-13:01'
in the drive '/media/cdrom/' and press enter

Get:4 https://security.debian.org/ squeeze/updates/main mysql-server-core-5.1 i386 5.1.73-1 [3,955 kB]
Get:5 https://security.debian.org/ squeeze/updates/main mysql-server-5.1 i386 5.1.73-1 [6,543 kB]
Get:6 https://security.debian.org/ squeeze/updates/main mysql-server all 5.1.73-1 [65.7 kB]
Fetched 22.2 MB in 4min 1s (91.8 kB/s)                                         
Preconfiguring packages ...
Selecting previously deselected package mysql-common.
(Reading database ... 127614 files and directories currently installed.)
Unpacking mysql-common (from .../mysql-common_5.1.73-1_all.deb) ...
Selecting previously deselected package libnet-daemon-perl.
Unpacking libnet-daemon-perl (from .../libnet-daemon-perl_0.43-1_all.deb) ...
Selecting previously deselected package libplrpc-perl.
Unpacking libplrpc-perl (from .../libplrpc-perl_0.2020-2_all.deb) ...
Selecting previously deselected package libdbi-perl.
Unpacking libdbi-perl (from .../libdbi-perl_1.612-1_i386.deb) ...
Selecting previously deselected package libmysqlclient16.
Unpacking libmysqlclient16 (from .../libmysqlclient16_5.1.73-1_i386.deb) ...
Selecting previously deselected package libdbd-mysql-perl.
Unpacking libdbd-mysql-perl (from .../libdbd-mysql-perl_4.016-1_i386.deb) ...
Selecting previously deselected package mysql-client-5.1.
Unpacking mysql-client-5.1 (from .../mysql-client-5.1_5.1.73-1_i386.deb) ...
Selecting previously deselected package mysql-server-core-5.1.
Unpacking mysql-server-core-5.1 (from .../mysql-server-core-5.1_5.1.73-1_i386.deb) ...
Processing triggers for man-db ...
Setting up mysql-common (5.1.73-1) ...
Selecting previously deselected package mysql-server-5.1.
(Reading database ... 127990 files and directories currently installed.)
Unpacking mysql-server-5.1 (from .../mysql-server-5.1_5.1.73-1_i386.deb) ...
Selecting previously deselected package libhtml-template-perl.
Unpacking libhtml-template-perl (from .../libhtml-template-perl_2.9-2_all.deb) ...
Selecting previously deselected package mysql-server.
Unpacking mysql-server (from .../mysql-server_5.1.73-1_all.deb) ...
Processing triggers for man-db ...
Setting up libnet-daemon-perl (0.43-1) ...
Setting up libplrpc-perl (0.2020-2) ...
Setting up libdbi-perl (1.612-1) ...
Setting up libmysqlclient16 (5.1.73-1) ...
Setting up libdbd-mysql-perl (4.016-1) ...
Setting up mysql-client-5.1 (5.1.73-1) ...
Setting up mysql-server-core-5.1 (5.1.73-1) ...
Setting up mysql-server-5.1 (5.1.73-1) ...
Stopping MySQL database server: mysqld.
141225  5:39:06 [Note] Plugin 'FEDERATED' is disabled.
141225  5:39:06  InnoDB: Initializing buffer pool, size = 8.0M
141225  5:39:06  InnoDB: Completed initialization of buffer pool
141225  5:39:06  InnoDB: Started; log sequence number 0 44233
141225  5:39:06  InnoDB: Starting shutdown...
141225  5:39:12  InnoDB: Shutdown completed; log sequence number 0 44233
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
Setting up libhtml-template-perl (2.9-2) ...
Setting up mysql-server (5.1.73-1) ...
root@debian6:/home/rahmatriyanto#

Setelah proses install selesai untuk mejalankan MySQL gunakancara berikut:

root@debian6:/home/rahmatriyanto# /etc/init.d/mysql start
 Starting MySQL database server: mysqld.
 Checking for corrupt, not cleanly closed and upgrade needing tables..
 root@debian6:/home/rahmatriyanto# /etc/init.d/mysql restart
 Stopping MySQL database server: mysqld.
 Starting MySQL database server: mysqld.
 Checking for corrupt, not cleanly closed and upgrade needing tables..
 root@debian6:/home/rahmatriyanto# /etc/init.d/mysql reload
 Reloading MySQL database server: mysqld.
 root@debian6:/home/rahmatriyanto# /etc/init.d/mysql force-reload
 Reloading MySQL database server: mysqld.
 root@debian6:/home/rahmatriyanto# /etc/init.d/mysql status
 /usr/bin/mysqladmin Ver 8.42 Distrib 5.1.73, for debian-linux-gnu on i486
 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.

Server version 5.1.73-1
 Protocol version 10
 Connection Localhost via UNIX socket
 UNIX socket /var/run/mysqld/mysqld.sock
 Uptime: 31 sec

Threads: 1 Questions: 104 Slow queries: 0 Opens: 99 Flush tables: 1 Open tables: 23 Queries per second avg: 3.354.
 root@debian6:/home/rahmatriyanto# /etc/init.d/mysql stop
 Stopping MySQL database server: mysqld.
 root@debian6:/home/rahmatriyanto#