Mysql commands

• Cara masuk ke terminal mysql

mysql -u root -p

• Cara create database

create database db_name;

• Cara show database

show databases;

• Buat user database

#to remote from localhost
CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';

#to remote from any host client
CREATE USER 'user_name'@'%' IDENTIFIED BY 'password';

• Grant Access database ke user

# grant access semua table didalam database ke user any host
GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'%';

# grant access hanya table tertentu didalam database ke user localhost
GRANT ALL PRIVILEGES ON db_name.table_name TO 'user_name'@'localhost';

#disesuaikan saja

• Masuk ke database

use db_name;

• Hapus database

drop database db_name;

• Backup database di localhost

#backup dari user selain root harus memasukkan password usernya
mysqldump -u user_name -p db_name > backup-db_name.sql

#backup database local dengan user root tanpa password
mysqldump -u root db_name > backup-db_name.sql

#backup database dari server lain & masukkan password usernya
mysqldump --host ip_address -P 3306 -u user_name -p db_name > backup-db_name.sql

• Restore database

#restore menggunakan simbol buka kurung siku
#restore menggunakan user root tanpa password
mysql -u root db_name < backup-db_name.sql

#restore menggunakan user_name dengan memasukkan password
mysql -u user_name -p db_name < backup-db_name.sql

semoga bermanfaat ~