Hostoza

50 Most Used Mysql Commands

User Management

  1. SELECT user, host FROM mysql.user;
    Fetches a list of all users and their associated hosts.
  2. CREATE USER 'username'@'host' IDENTIFIED BY 'password';
    Creates a new user with a specified host and password.
  3. CREATE USER 'username'@'host' IDENTIFIED WITH 'plugin' BY 'password';
    Creates a user with a specific authentication plugin.
  4. SELECT user, host, plugin, authentication_string FROM mysql.user;
    Displays detailed information about users.
  5. DROP USER 'username'@'host';
    Deletes a user from the MySQL server.
  6. RENAME USER 'olduser'@'oldhost' TO 'newuser'@'newhost';
    Renames an existing user.
  7. UPDATE mysql.user SET host = 'newhost' WHERE user = 'username' AND host = 'oldhost';
    Updates the host for an existing user.
  8. ALTER USER 'username'@'host' IDENTIFIED WITH 'plugin' BY 'password';
    Changes a user’s authentication plugin and password.
  9. ALTER USER 'username'@'host' ACCOUNT LOCK;
    Locks a user account to prevent login.
  10. ALTER USER 'username'@'host' ACCOUNT UNLOCK;
    Unlocks a previously locked user account.

Password Management

  1. ALTER USER 'username'@'host' IDENTIFIED BY 'newpassword';
    Changes the password for a user.
  2. ALTER USER 'username'@'host' PASSWORD EXPIRE;
    Forces the user to change their password on the next login.
  3. SET GLOBAL default_password_lifetime = 90;
    Sets the global password expiration policy to 90 days.
  4. ALTER USER 'username'@'host' IDENTIFIED WITH 'plugin' BY 'password';
    Resets the user’s authentication plugin and password.
  5. ALTER USER 'username'@'host' REQUIRE SSL;
    Enforces SSL for a specific user.
  6. ALTER USER 'username'@'host' IDENTIFIED BY '';
    Removes the password for the specified user.
  7. SHOW VARIABLES LIKE 'validate_password%';
    Displays password validation policies.
  8. SET GLOBAL validate_password_policy = MEDIUM;
    Configures the password strength policy to medium.
  9. SET GLOBAL validate_password_length = 12;
    Sets the minimum password length.
  10. SET GLOBAL validate_password_number_count = 3;
    Prevents password reuse for the last 3 passwords.

Database Management

  1. CREATE DATABASE database_name;
    Creates a new database.
  2. DROP DATABASE database_name;
    Deletes a database.
  3. SHOW DATABASES;
    Lists all databases on the server.
  4. USE database_name;
    Switches to a specific database for queries.
  5. SELECT DATABASE();
    Displays the currently selected database.
  6. RENAME TABLE old_db.table_name TO new_db.table_name;
    Moves a table from one database to another.
  7. mysqldump -u root -p database_name > backup.sql;
    Backs up a database to a .sql file (command-line).
  8. mysql -u root -p database_name < backup.sql;
    Restores a database from a .sql file.
  9. SELECT table_schema AS database_name, SUM(data_length + index_length) / 1024 / 1024 AS size_in_mb FROM information_schema.tables GROUP BY table_schema;
    Displays the size of each database.
  10. SHOW TABLES FROM database_name;
    Lists all tables in a specific database.

Privileges Management

  1. GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
    Grants all privileges on a database to a user.
  2. GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'host';
    Grants specific privileges to a user.
  3. GRANT ALL PRIVILEGES ON *.* TO 'username'@'host' WITH GRANT OPTION;
    Grants all privileges on all databases, including the ability to grant privileges.
  4. REVOKE SELECT, INSERT ON database_name.* FROM 'username'@'host';
    Removes specific privileges from a user.
  5. SHOW GRANTS FOR 'username'@'host';
    Displays the privileges granted to a user.
  6. SELECT * FROM information_schema.user_privileges;
    Shows global privileges for all users.
  7. REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';
    Removes all privileges and the ability to grant privileges from a user.
  8. FLUSH PRIVILEGES;
    Reloads the privilege tables to apply changes.
  9. GRANT SUPER ON *.* TO 'username'@'host';
    Grants superuser privileges to a user.
  10. REVOKE SUPER ON *.* FROM 'username'@'host';
    Removes superuser privileges from a user.

Host Management

  1. CREATE USER 'username'@'%' IDENTIFIED BY 'password';
    Allows a user to connect from any host.
  2. CREATE USER 'username'@'192.168.1.100' IDENTIFIED BY 'password';
    Restricts user access to a specific IP.
  3. SELECT user, host FROM mysql.user;
    Lists all users and their associated hosts.
  4. UPDATE mysql.user SET host = 'newhost' WHERE user = 'username' AND host = 'oldhost';
    Changes the host for an existing user.
  5. CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
    Restricts access to connections from localhost.
  6. DELETE FROM mysql.user WHERE user = 'username' AND host = 'specific_host';
    Removes a user’s access from a specific host.
  7. CREATE USER 'username'@'192.168.1.%' IDENTIFIED BY 'password';
    Allows a user to connect from a subnet.
  8. CREATE USER 'username'@'newhost' IDENTIFIED BY 'password';
    Grants a user access from multiple IPs by creating separate entries for each host.
  9. SHOW GRANTS FOR 'username'@'host';
    Displays privileges for a specific user/host pair.
  10. UPDATE mysql.user SET host = 'localhost' WHERE user = 'username';
    Resets a user’s host to localhost.

superadmin