All About MYSQL

Cara Backup dan Restore Database MySQL

Cara Backup :

>mysql

>show database

>exit




-> mysqldump -u root -p db_safety_survey > dump_db_safety_survey_15092019.sql

kalau ditanya password, enter aja




Output :

Cara Restore :

-> mysql -u root -p imte2 < dump_imte_04062020.sql

atau jika ingin menyimpan log ketika restore :

-> mysql -u root -p adpart_test < adpart.sql > /root/mysqldump.log 2>&1

Cara Create Database & User MySQL via CLI

Create database :

sql> create database db_misalnya;

Create user All Access :

sql> create user 'usr-misalnya';

sql> GRANT ALL PRIVILEGES ON db_misalnya.* TO 'usr-misalnya'@'%' identified by 'p@ssw0rd';

sql> GRANT ALL PRIVILEGES ON db_misalnya.* TO 'usr-misalnya'@'_gateway' identified by 'p@ssw0rd';

sql> flush privileges; 

atau 

> grant all on 'db_misalnya'.* to 'usr_misalnya'@'_gateway' identified by 'p@ssw0rd';  

> grant all on 'db_misalnya'.* to 'usr_misalnya'@'%' identified by 'p@ssw0rd'; 

> flush privileges; 

> exit 

Create user Read Only :

> create user 'usr-misalnya';

> grant select on database_name.* to 'read-only_user_name'@'%' identified by 'password';

> flush privilages;

Cara Cek Username Mysql

Show All MySQL Users

MySQL stores information about the users in a table named user in the mysql database.

To get a list of all MySQL user accounts, use the SELECT statement to retrieve all rows from the mysql.users table:

mysql> SELECT User, Host FROM mysql.user;

Cara Cek Password Mysql

> select * from mysql.user;