Installing 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;