Install MySQL and MySQL Workbench in Ubuntu

#sql #workbench #database

Urvi soni May 10 2021 · 2 min read
Share this

Step 1:-  Updates the list of available packages and their versions using this command

sudo apt update

Step 2:- Install 'mysql-server'

sudo apt install mysql-server

Step 3:- Check if 'mysql' is installed or not by checking it's version

mysql --version

Step 4:- Now, make a 'secure_installation' of 'mysql'.

sudo mysql_secure_installation

It will ask some questions, follow these steps for that questions:-

  • Would you Like to set up VALIDATE PASSWORD plugin? -> Press 'y'
  • Then it will ask you for the level of password from '0 = Low, 1=Medium, 2=Strong' press any of them to create a new password for MySQL.
  • Make a new password and re-enter it.
  • Do you wish to continue with the password provided? -> Press 'y'
  • Remove anonymous users? -> Press 'y'
  • Disallow root login remotely? -> Press 'n'
  • Remove the test database and access it? -> Press 'n'
  • Reload privilege table now? -> Press 'y'
  • Step 5:- Now run this command:-

    sudo mysql

    Step 6:- To see the user, authentication, and plugin:-

    select user, authentication_string, plugin from mysql.user;

    To see the user, authentication, and plugin

    You can see that the 'root' user has the 'auth_socket' plugin. So, we need to change it to 'mysql_native_password'.

    Step 7:- To change user with 'mysql_native_password' Plugin:-

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by '<type_password_you_want_and_remember_it>';

    For example:-

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'abcde1234';

    Here 'abcde1234' is the password for the 'root' user

    Now, the Password is saved and if you run this command:-

    select user, authentication_string, plugin from mysql.user;

    Check  the user, authentication, and plugin

    You can see user 'root' has 'authenticcation_string' as well as a plugin as 'mysql_native_password'

    Step 8:-  To change this change effective immediately:-

    FLUSH PRIVILEGES;

    and exit from MySQL

    exit

    Now, try to login MySQL again:-

    sudo mysql

    You can see the Error like this:-

    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

    Now you can't run MySQL directly. 

    Step 9:- To run MySQL run the below command:-

    sudo mysql -u root -p

    It will ask for the password:- Type the password 'abcde1234' 

    You can type the password which you set it for. This is just for information.

    Now, you can opt for it.

    Step 10:- To see databases:-

    show databases;

    You can see these databases which are available in MySQL

    Step 11:- Connect this 'mysql' database with 'mysql-workbench'

    Go To Ubuntu Software -> MySQL WOrkbench -> Install

    Install 'MYSQL Workbench'  don't install 'MYSQL Workbench Community'

    Open 'MYSQL WOrkbench'.

    Step 12:- Make a new connection.

    Click on '+' icon to make new connection
    Give the connection name whatever you want

    Then in 'Password:' click on the 'Store in Keychain..' button and type the password which you made for the 'root' user here we make 'abcde1234' so, we can type it.

    Then click on the "OK" Button 

    Now, click on the "Test Connection" button.

    You are successfully made the MySQL connection

    Then click on the "OK" button.

    You can open your "TEST" connection whenever you want to work in it.

    NOTE:- For anyone having issues getting the password to store in the keychain. Sometimes you may need to go into MySQL in the software center > permissions > read, add, change, or remove saved passwords.

    Reference:- 

    Comments
    Read next