Category Archives: MySQL

Cara mengubah semua tabel InnoDB ke MyISAM dan Mematikan InnoDB storage engine di MySQL / MariaDB

InnoDB adalah storage engine yang mendukung proses transaksi antar tabel databases dan banyak keunggulan lain, namun untuk kasus saya pribadi terlalu berlebihan jika menggunakan innodb, myisam masih menjadi andalan saya.

Untuk mengubah tipe table InnoDB ke MyIsam, langkahnya sangat mudah,

Langkah 1

buka PHPMyAdmin kemudian paste code berikut

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=MyISAM;') FROM information_schema.tables WHERE engine = 'InnoDB';


Sebagai contoh disini saya menggunakan tabel bawaan wordpress, setelah code diatas dijalankan akan tampil seperti dibawah ini

 ALTER TABLE wp_commentmeta ENGINE = MyISAM;
ALTER TABLE wp_comments ENGINE = MyISAM;
ALTER TABLE wp_links ENGINE = MyISAM;
ALTER TABLE wp_options ENGINE = MyISAM;
ALTER TABLE wp_postmeta ENGINE = MyISAM;
ALTER TABLE wp_posts ENGINE = MyISAM;
ALTER TABLE wp_terms ENGINE = MyISAM;
ALTER TABLE wp_term_relationships ENGINE = MyISAM;
ALTER TABLE wp_term_taxonomy ENGINE = MyISAM;
ALTER TABLE wp_usermeta ENGINE = MyISAM;
ALTER TABLE wp_users ENGINE = MyISAM;

Lalu copy code diatas dan paste ke SQL maka semua tabel InnoDb akan berubah menjadi MyISAM.

Jika konversi hanya untuk beberapa tabel bisa menggunakan format berikut

ALTER TABLE namatabel ENGINE = MyISAM;

Cara diatas hanya berfungsi untuk mengubah tabel innodb ke tabel myisam

Langkah 2

Jika ingin mematika innodb dan menggantinya dengan myisam gunakan perintah ini, login sebagai root buka file /etc/my.cnf dan tambahkan code ini dibawah

[mysqld]
skip-innodb
default-storage-engine=MyISAM

MySQL 5.6 keatas gunakan ini

default-storage-engine=MyISAM
default-tmp-storage-engine=MyISAM
innodb=OFF

Terakhir restart server mysql

Jangan sampai terbalik urutannya, langkah 1 kemudian langkah 2.

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#