Skip to content

Instantly share code, notes, and snippets.

@ShivKumarSaini
Last active May 6, 2020 19:07
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ShivKumarSaini/5535742149aeef15e87bc97dc01ddd63 to your computer and use it in GitHub Desktop.
Save ShivKumarSaini/5535742149aeef15e87bc97dc01ddd63 to your computer and use it in GitHub Desktop.
All privileges to new user in MySQL

Step 0: Remove/Purge:

In case, this is a re-installation, first remove previous version fully.

$ sudo apt-get remove --purge mysql*  
$ sudo apt-get purge mysql*  
$ sudo apt-get autoremove  
$ sudo apt-get autoclean  
$ sudo apt-get remove dbconfig-mysql

After installing mysql from synaptic package manager on Ubuntu Linux, follow below steps:

Step 1: Set up MySQL Security

By default, MySQL lacks many basic and important security features. Luckily, it comes with an installation script that walks you through the configuration.

To install the MySQL security script, enter:

sudo mysql_secure_installation

The system will prompt you for the MySQL root password.

  • Validate Password Plugin

Next, the installer will describe the features of the Validate Password plugin.

This plugin checks to make sure that any new passwords are strong/complex enough. Type “y” to enable, or “n” to disable. This is your choice, but enabling this plugin is more secure.

The Validate Password plugin has three settings for passwords:

  • Low: passwords must be at least 8 characters
  • Medium (default): passwords must have 1 uppercase, 1 lowercase, 1 numeric, and 1 special character
  • Strong: compares the password to a dictionary file to prevent dictionary or brute force attacks
  • Change Root Password

Next, the installer will offer you the chance to change the password for root. Type “y” to change the password, or “n” to keep the same password you set in Step 4. If you do change the password, it will need to follow any requirements you configured in Step 5a.

  • Configure MySQL Security

The system will prompt you for the following security features. It is recommended that you enable (type “y”) all of them, unless you have a reason to keep them disabled.

  • Remove anonymous users?
  • Disallow root login remotely?
  • Remove test database and access to it?
  • Reload privilege tables now?

Step 2: Start, Stop, or Check Status of MySQL Service

In Ubuntu, the MySQL service should be started automatically.

To verify MySQL is running enter the command:

sudo service mysql status

To stop the service:

sudo service mysql stop

To start the service:

sudo service mysql start

Step 3: Launch MySQL to Enter Commands

Many MySQL commands can be entered from the MySQL command shell. This is very similar to a terminal window, but the commands are issued directly to the MySQL service.

Launch MySQL with the command:

sudo mysql –u root –p

The system should prompt for a password, then give an introduction to the MySQL shell. The command prompt will change to look like this:

mysql>

In Terminal:

sudo mysql -u root -p

After that:

mysql > CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
mysql > GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';
mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';     // set blank password for root here.
mysql > FLUSH PRIVILEGES;
mysql > exit;

Then, in terminal:

sudo service mysql restart

Login to server using MySQL Workbench with user Root and go to Users and Privileges tab to modify/grant any user's permissions.

After Change the 'root' user's password to something else using below commands in terminal:

sudo mysql -u root -p // blank password for root is still there. Change it in next step.

mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
mysql > FLUSH PRIVILEGES;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment