• Home
  • LLMs
  • Docker
  • Kubernetes
  • Java
  • Ubuntu
  • Maven
  • Archived
  • About
Install | MySQL
  1. References
  2. Installation
  3. Allow remote access
  4. Some useful commands
  5. Manage Users
  6. Manage Databases
  7. Manage Tables
  8. Export/backup a database
  9. MySQL "SHOW STATUS" command
  10. MySQL "SHOW PROCESSLIST" command
  11. Set the logs expiration days

  1. References
    See this page for more details on how to install MySQL:
    https://help.ubuntu.com/lts/serverguide/mysql.html
  2. Installation

    • Verify the version of MySQL:

    • Connect to MySQL as "root" (using "sudo" because "root" is configured with "auth_socket" plugin):

      You need to use sudo otherwise you will get this error: "ERROR 1698 (28000): Access denied for user 'root'@'localhost'"

      MySQL in Ubuntu is using "auth_socket" plugin for the "root" user.
      Which means that the "root" user will be authorized by the system user credentials.



    • Set up MySQL Security (root password):

    • MySQL configuration directory:

    • MySQL data directory:

    • MySQL log directory:
  3. Allow remote access
    • Connect to MySQL as "root" (using "sudo" because "root" is configured with "auth_socket" plugin):

    • Locate and change the value of "bind-address" configuration under "[mysql]":

    • Restart the MySQL server:
  4. Some useful commands
    • Connect to MySQL as "root" (using "sudo" because "root" is configured with "auth_socket" plugin):

    • Status:
    • Select configuration variables:
    • Another way to select configuration variables:
    • Show current database:
    • Show current user:
  5. Manage Users
    • Create a user (bind to localhost):
      Create a user (bind to any host):
      You might get this error: "ERROR 1819 (HY000): Your password does not satisfy the current policy requirements"
      To check the Password Validation Plugin configuration:

      You can change any of these settings:
    • Grant permissions to a user (bind to localhost):
      Grant permissions to a user (bind to any host):
    • Drop a user (bind to localhost):
      Drop a user (bind to any host):
    • Select information of users:

    • Connect to MySQL (using a database user configured with "mysql_native_password" plugin):
  6. Manage Databases
    • Create a database:

    • Show all databases:
    • Select information of databases:

    • Select a database:

    • Delete a database:
  7. Manage Tables
    • Create a table:
    • Show all tables:
    • Select data of a table:
    • Drop a table:
  8. Export/backup a database
    • Export/backup a database:
  9. MySQL "SHOW STATUS" command
    • Connect to MySQL:
    • Show MySQL open database connections:
  10. MySQL "SHOW PROCESSLIST" command
    • Connect to MySQL:
    • Show MySQL process list:
    • Kill a process (kill <process-id>):
  11. Set the logs expiration days
    Setup the 'expire_logs_days' system variable to expire binary log files automatically after a given number of days.

    • Show 'expire_logs_days' value:
    • You can setup the 'expire_logs_days' system variable:
    • Or you can set the 'expire_logs_days' system variable in "my.cnf" file ("vmy.ini"):
© 2025  mtitek