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.
Belajar Mengajar
Last Updated on 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.
Last Updated on 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.
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
Last Updated on 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...
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
Last Updated on 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.
Backup semua data MySQL anda, simpan ke file db.sql
mysqldump -u root -p --all-databases > db.sql
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 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.
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 |
Last Updated on 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