Category Archives: MySql

default value is 8 Hours (28800 Seconds):wait_timeout = 28800interactive_timeout = 28800 1 year : wait_timeout = 31536000interactive_timeout = 31536000

dump only sp trigger vs row data only

SP and Triggers only : time mysqldump -uroot -ppass –routines –no-create-info –no-data –no-create-db –skip-opt dbndm2 > dbndm2-sp-and-trigger-only_2021-09-10.sql Row data only :time mysqldump -uroot -ppass –skip-triggers dbndm2 > dbndm2-data-without-sp-and-trigger_2021-09-10.sql

MySql 8 mysql_secure_installation

sudo mysql_secure_installationpasswordnya : mautauajaRemove anonymous users? (Press y|Y for Yes, any other key for No) : yDisallow root login remotely? (Press y|Y for Yes, any other key for No) : yRemove test database and access to it? (Press y|Y for Yes, any other key for No) : yReload privilege tables now? (Press y|Y for Yes,… Read More »

mysqldump gz from another server

dari computer local ssh ke server database, untuk didump ke local komputer kita : ssh system@ip-server-mysql-yg-mau-di-dump “mysqldump -uroot -ppasswordnya dbname | gzip -9” > file-local-dbname.sql.gz

Duplicate datebase MySQL

create database dbbaru; mysqldump -uusernya -ppasswordnya –events –routines dblama | mysql -uusernya -ppasswordnya dbbaru

Duplicate table MySQL

CREATE TABLE telo_copy LIKE telo_asli; INSERT INTO telo_copy SELECT * FROM telo_asli;

mysql turn off strict mode

tambahkan pada my.cnf sql_mode=

dump gzip only rowdata to another server

#!/bin/bash # loop isi file well_list.txt # kemudian dump | gzip ke server lain # contoh nama table xx_telo_xx while read p; do mysqldump -uuser -ppassowrd –skip-add-drop-table –skip-add-locks –lock-tables=false namadatabase $(mysql -uuser -ppassowrd -D namadatabase -Bse “SHOW TABLES LIKE ‘%$p\_%’”) | gzip -c | ssh user@servertujuan “cat > /home/user/backupdb-rowdata/only-rowdata_$p.sql.gz” done < well_list.txt