mysql

Cara Reset AUTO_INCREMENT MySQL

Last Updated on 5 June 2017 By tommy Leave a Comment

Cara untuk mereset AUTO_INCREMENT MySQL/MariaDB dengan menjalankan perintah

ALTER TABLE NAMATABEL AUTO_INCREMENT=1

Ganti NAMATABEL dengan tabel yang akan diubah auto increment nya.

Setelah direset auto increment MySQL akan mulai lagi dari 1.

MySQL Error Access denied for user Debian

Last Updated on 25 November 2016 By tommy Leave a Comment

Error yang muncul

● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql)
   Active: active (running) since Fri 2016-11-25 23:37:14 WIB; 2min 6s ago
  Process: 31020 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)
  Process: 31049 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mysql.service
           ├─31077 /bin/sh /usr/bin/mysqld_safe
           └─31437 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pi...
 
Nov 25 23:37:14 load-balancing mysql[31049]: Starting MySQL database server: mysqld ..
Nov 25 23:37:14 load-balancing mysql[31049]: Checking for tables which need an upgrade, are corrupt or were
Nov 25 23:37:14 load-balancing mysql[31049]: not closed cleanly..
Nov 25 23:37:14 load-balancing systemd[1]: Started LSB: Start and stop the mysql database server daemon.
Nov 25 23:37:14 load-balancing /etc/mysql/debian-start[31495]: Upgrading MySQL tables if necessary.
Nov 25 23:37:14 load-balancing /etc/mysql/debian-start[31514]: Checking for insecure root accounts.
Nov 25 23:37:14 load-balancing mysql[31049]: ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

Error tersebut karena user bawaan Debian debian-sys-maint ngga bisa login ke MySQL/MariaDB.

Solusi

Cek password yang saat ini digunakan debian-sys-maint

cat /etc/mysql/debian.cnf
### output
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = kjIsdaf1248FGq5HA
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = kjIsdaf1248FGq5HA
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

lihat di bagian password, selanjutnya login ke MySQL sebagai root

mysql -u root -p

dan berikan akses penuh ke user debian-sys-maint

GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'kjIsdaf1248FGq5HA';

Sampai disini sudah selesai. Untuk melihat log MySQL Debian, cek /var/log/mysql/error.log

Mengatasi Error mysql.service has failed

Last Updated on 15 February 2016 By tommy Leave a Comment

Log errornya dilihat dari journal -xn

-- Unit mysql.service has begun starting up.
Feb 12 16:01:09 kai /etc/init.d/mysql[11135]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf
Feb 12 16:01:09 kai /etc/init.d/mysql[11135]: [61B blob data]
Feb 12 16:01:09 kai /etc/init.d/mysql[11135]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysq
Feb 12 16:01:09 kai /etc/init.d/mysql[11135]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' 
Feb 12 16:01:09 kai /etc/init.d/mysql[11135]: 
Feb 12 16:01:09 kai mysql[10388]: Starting MySQL database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . .
Feb 12 16:01:09 kai systemd[1]: mysql.service: control process exited, code=exited status=1
Feb 12 16:01:09 kai systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has failed.
-- 
-- The result is failed.
Feb 12 16:01:09 kai systemd[1]: Unit mysql.service entered failed state.
Feb 12 16:01:53 kai systemd[1]: Starting LSB: Start and stop the mysql database server daemon...

Solusi

1. Check folder dan file di /var/run/mysqld

$ ls -l  /var/run/mysqld
total 4
-rw-rw---- 1 mysql mysql 6 Feb 12 16:03 mysqld.pid
srwxrwxrwx 1 mysql mysql 0 Feb 12 16:03 mysqld.sock

bila file atau folder diatas ngga ada buat baru

mkdir /var/run/mysqld
touch /var/run/mysqld/mysql.{pid,sock}
chown mysql:mysql -R /var/run/mysqld

2. Cek folder /var/log/mysql

$ ls -l /var/log/mysql/
total 8
-rw-r----- 1 mysql adm 5695 Feb 12 16:03 error.log

bila belum ada buat baru

mkdir /var/log/mysql
chown mysql:adm /var/log/mysql

3. Check port 3306

netstat -tunlp | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      12375/mysqld

matikan service tersebut

kill -9 12375

ganti 12375 dengan PID diatas.

setelah langkah-langkah tersebut dijalankan, jalankan service mysql

service mysql start

service mysql sukses dijalankan

Feb 12 16:03:55 kai systemd[1]: Starting LSB: Start and stop the mysql database server daemon...
-- Subject: Unit mysql.service has begun with start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has begun starting up.
Feb 12 16:03:57 kai mysql[11975]: Starting MySQL database server: mysqld ..
Feb 12 16:03:57 kai mysql[11975]: Checking for tables which need an upgrade, are corrupt or were
Feb 12 16:03:57 kai mysql[11975]: not closed cleanly..
Feb 12 16:03:57 kai systemd[1]: Started LSB: Start and stop the mysql database server daemon.
-- Subject: Unit mysql.service has finished start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has finished starting up.
-- 
-- The start-up result is done.
Feb 12 16:03:57 kai /etc/mysql/debian-start[12431]: Upgrading MySQL tables if necessary.
Feb 12 16:03:57 kai /etc/mysql/debian-start[12434]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Feb 12 16:03:57 kai /etc/mysql/debian-start[12434]: Looking for 'mysql' as: /usr/bin/mysql
Feb 12 16:03:57 kai /etc/mysql/debian-start[12434]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Feb 12 16:03:57 kai /etc/mysql/debian-start[12434]: This installation of MySQL is already upgraded to 5.5.47, use --force if y
Feb 12 16:03:57 kai /etc/mysql/debian-start[12445]: Checking for insecure root accounts.
Feb 12 16:03:57 kai /etc/mysql/debian-start[12450]: Triggering myisam-recover for all MyISAM tables

Cara Memindahkan MySQL ke Server Baru

Last Updated on 21 January 2016 By tommy Leave a Comment

Cara memindahkan MySQL disini sangat mudah dilakukan baik untuk pemula sekalipun. Database MySQL anda tidak akan ada yang hilang dan semua user/password juga masih sama. Cara ini bisa digunakan untuk memindahkan database dari distro linux manapun, yang peru di ingat server baru harus menggunakan MySQL versi yang sama atau lebih baru.

Server Lama

Backup semua data MySQL anda, simpan ke file db.sql

mysqldump -u root -p --all-databases > db.sql

Server Baru

Copy file dari server lama dengan scp atau tool lainnya, contoh copy menggunakan scp

scp -P 22 root@server.lama:/direktori/db.sql /home/db.sql

-P 22 : Port SSH server lama
/direktori/db.sql : lokasi file db.sql
/home/db.sql : tempat anda menyimpan file db.sql di server baru

setelah file tersebut di copy, restore dengan cara

mysql -u root -p < /home/db.sql

setelah selesai, reload priviledge agar MySQL di server baru, menggunakan credential (username/password) yang sama dengan server lama.

mysqladmin flush-privileges

Contoh MySQL Menggabungkan Hasil Query GROUP BY

Last Updated on 20 August 2015 By tommy 1 Comment

Contoh kasus, saya memiliki tabel yang berisi barang dan pembeli. Saya mau menampilkan data dari tabel tersebut dalam bentuk “Customer|Barang Belanjaan|Total Belanja” di kelompokkan per customer/pembeli. Semua jenis barang yang sama hanya ditampilkan 1 kali.

Solusi :

contab saya memiliki cart sebagai berikut

id	customer	barang
1	Andi	Pepsodent
2	Tommy	Mouse Pad
3	Dina	Pepsodent
4	Andi	Mouse Pad
5	Andi	Laptop
6	Tommy	Printer
7	Kacikuang	Laptop
8	Kacibang	Printer
9	Tommy	Printer

Untuk memilih data dikelompokkan berdasarkan customer dan dihitung jumlah totalnya, gunakan group by.

SELECT
   customer,
   barang,
   COUNT(*) AS 'Total Belanja' 
FROM
   `cart` 
GROUP  BY
   customer

customer	Barang	Total Belanja
Andi	Pepsodent	3
Dina	Pepsodent	1
Kacibang	Printer	1
Kacikuang	Laptop	1
Tommy	Mouse Pad	3

masalahnya disini adalah Barang yang ditampilkan hanya 1 sementara ada beberapa customer yang beberapa kali membeli barang, tetapi barang yang ditampilkan hanya 1. solusinya gunakan group_concat

SELECT
   customer,
   GROUP_CONCAT(barang) AS "Barang Belanjaan",
   COUNT(*) AS 'Total Belanja'   
FROM
   `cart`   
GROUP  BY
   customer

hasilnya

customer	Barang Belanjaan	Total Belanja
Andi	Pepsodent,Mouse Pad,Laptop	3
Dina	Pepsodent	1
Kacibang	Printer	1
Kacikuang	Laptop	1
Tommy	Mouse Pad,Printer,Printer	3

pada tabel diatas masih terdapat barang yang sama, yaitu Printer untuk menggabungkan barang yang sama gunakan DISTINCT

SELECT
   customer,
   GROUP_CONCAT(DISTINCT(barang)) AS "Barang Belanjaan",
   COUNT(*) AS 'Total Belanja' 
FROM
   `cart` 
GROUP  BY
   customer

sehingga hasilnya menjadi

customer	Barang Belanjaan	Total Belanja
Andi	Pepsodent,Mouse Pad,Laptop	3
Dina	Pepsodent	1
Kacibang	Printer	1
Kacikuang	Laptop	1
Tommy	Mouse Pad,Printer	3

Mengatasi Error MySQL logger: command not found Debian

Last Updated on 29 December 2016 By tommy 2 Comments

Error yang muncul sewaktu menginstall mysql di Debian 7

Setting up mysql-server-5.5 (5.5.40-0+wheezy1) ...
debconf: unable to initialize frontend: Dialog
debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)
debconf: falling back to frontend: Readline
[ ok ] Stopping MySQL database server: mysqld.
/var/lib/dpkg/info/mysql-server-5.5.postinst: line 146: logger: command not found
ATTENTION: An error has occured. More info is in the syslog!
/var/lib/dpkg/info/mysql-server-5.5.postinst: line 236: logger: command not found
dpkg: error processing mysql-server-5.5 (--configure):
 subprocess installed post-installation script returned error exit status 127
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-server-5.5; however:
  Package mysql-server-5.5 is not configured yet.
 
dpkg: error processing mysql-server (--configure):
 dependency problems - leaving unconfigured
Errors were encountered while processing:
 mysql-server-5.5
 mysql-server
E: Sub-process /usr/bin/dpkg returned an error code (1)

cara mengatasinya, setting locale dengan perintah

dpkg-reconfigure locales

lalu install bsdutils (disini terdapat aplikasi logger tersebut)

apt-get install bsdutils

Bila error diatas masih tetap muncul, kemungkinan besar folder mysql (/var/lib/mysql) korup, hapus folder mysql, tetapi ingat untuk membackup data-datanya terlebih dahulu

#backup
cp -r /var/lib/mysql /tmp/
#hapus
rm -fr /var/lib/mysql

lalu install mysql dengan

apt-get install mysql-server -y

Bila anda memiliki backup dari MySQL tinggal di restore, bila tidak harus di pilah pilah dari folder /tmp/mysql secara manual