Post

Install MySql on Ubuntu

Installing and securing MySQL on Ubuntu, including user setup and password recovery.

Install MySql on Ubuntu

for version 8.0.27

  • First install my-sql database:

    sudo apt install mysql-server # start the utility prompt to set the root user password sudo mysql_secure_installation utility
  • Allow remote access

    sudo ufw enable sudo ufw allow mysql
  • Start the sql service

    sudo systemctl start mysql # to allow the service to start at startup sudo systemctl enable mysql
  • Start mysql shell

    /usr/bin/mysql -u root -p
  • View users

    • SELECT User, Host, authentication_string FROM mysql.user;
  • Add a new user

    • CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';

    • localhost for local user

  • gGive permission to the user to access some database

    • GRANT ALL ON database_name.* TO 'user_name'@'localhost';

    • do FLUSH PRIVILEGES; to reload the assigned permissions

Troubleshooting

  • If you face any permission issues while launching the mysql

    • use chown -R mysql:mysql filename
  • Start mysql with skip-grant-tables

    • mysqld --skip-grant-permissions --user=root

Resetting the root user password

  • If your data directory /var/lib/mysql contains the old version mysql format data, or something is wrong with it, regenerate it

    # Move or remove the original dir mv /var/lib/mysql /tmp/mysql # Create a new dir mkdir /var/lib/mysql # Change owner to mysql (user and group) chown -R mysql:mysql /var/lib/mysql # Create MySQL initial data mysqld --initialize
  • Now start the mysqld with skip grant tables

    # starting the mysqld daemon sudo mysqld --skip-grant-tables --user=root # or if you can't open another terminal sudo mysqld --skip-grant-tables --user=root & # open mysql mysql -u root
  • Fix the user permission table

    UPDATE mysql.user SET authentication_string=null WHERE User='root'; FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd'; FLUSH PRIVILEGES; EXIT;
  • Now you can login with the new password.

References

This post is licensed under CC BY 4.0 by the author.