• Create User

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

di mysql8

CREATE USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password_nya';

grant ke database tertentu

GRANT ALL PRIVILEGES ON db_name.* TO 'ojs'@'localhost';

biar usernya bisa grant maka jadinya

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost' WITH GRANT OPTION;

• mysql backup

CREATE USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password_nya';
GRANT SELECT, LOCK TABLES, SHOW VIEW,PROCESS, EVENT ON ojs. * TO 'username'@'host';

• MySQL backup wis mysqldump command

#standar command
mysqldump -u username -p   your_database_name >  your_database_name.sql

#backup multiple databases:
mysqldump -u username -p --databases db1 db2 > backup.sql

#all the databases in one shot:
mysqldump -u username -p --all-databases > backup.sql

#compress with gzip:
mysqldump -u username -p your_database_name | gzip >  your_database_name.sql

#dump with timestamp database name
mysqldump -u username -p database_name  > "database_name-$(date +%Y-%m-%d-%H.%M.%S).sql"

• mindahin data /var/lib/mysql di centos

chcon -Rt mysqld_db_t /path_to_mysql/db
chcon -Ru system_u /path_to_mysql/db
chown -R mysql:mysql /path_to_mysql/db

• Command sql

To optimise all databases:

mysqlcheck -hlocalhost -uuser -ppassword --optimize --all-databases
To repair all databases if errors:

mysqlcheck -hlocalhost -uuser -ppassword --auto-repair --check --all-databases
To backup a database and save it into *.SQL files (plain text)

mysqldump --opt -hlocalhost -uuser -ppassword database > database.sql
To compress the files into gzip format, using:

mysqldump --opt -hlocalhost -uuser -ppassword database | gzip > database.sql.gz

• reset mysql password

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

• charset compatibility on mysql8, add this line to mysql config (my.cnf) file

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8