Skip to content

Instantly share code, notes, and snippets.

@afriza
Forked from Hikingyo/admin.sql
Last active June 7, 2024 07:35
Show Gist options
  • Save afriza/1730e9eb5cc60ea32a5cedf36b251dc6 to your computer and use it in GitHub Desktop.
Save afriza/1730e9eb5cc60ea32a5cedf36b251dc6 to your computer and use it in GitHub Desktop.
create admin user with all privileges on mysql/mariadb
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
## remote connection - not secure
CREATE USER 'admin'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
# Optional for MySQL 8.0
CREATE USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'some_pass';
ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'some_pass';
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE DATABASE mydatabase CHARACTER SET latin1 COLLATE latin1_swedish_ci;
# Remove user
SELECT User, Host FROM mysql.user;
SHOW GRANTS FOR 'user'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost';
DROP USER 'user'@'localhost';
# OR
DROP USER foo;
# Remove DB
DROP DATABASE mydb;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment