Changing the password of the root user of mysql in Ubuntu

Hi all, 

 

In this post we are going to see how to reset the password of root user in mysql

As we all know the root user of mysql is something like the root user of linux with full power to access anything in MySql database server.

Normally during the install, the installation would not ask for password to set for the root user.

This post talks about setting the password in ubuntu for the root user in a precise manner.

This post assumes that you already have a valid MySql Server instance up and running and I am going to use the command line tool i.e the Terminal for connecting to the server and resetting the password.

Let's start it...

Open a terminal and check whether the MySql server is up and running....

> sudo systemctl status mysql

 

If the server is up and running, then you would have response something like given below...

Stop the Server(MySql Server) using the command given below

> sudo systemctl stop mysql

Now we have the server stopped.

 

We have to start the MySql Server differently to have the passord reset for the user root, to do that you must edit mysqld.cnf file located in /etc/mysql/mysql.conf.d folder...

Now open mysqld.cnf file in your favourite editor and try to locate the group [mysqld], you can use the picture below to locate...

At the top of the group or just below the line which contains the content [mysqld]
add the line as said below

skip-grant-tables

Now the configuration file should appear something like the one shown below...

Save the file and exit from the text editor

Now try to start the server using the terminal as given below...

> sudo systemctl start mysql

Check the status of the mysql process

> sudo systemctl status mysql

you should have something like given below...

Connect to mysql server from the command using the command "mysql"

> mysql --user=mysql

Now you should be connected to the server and what you see on the server is the response connected to the server without the password for the user "mysql"

Change the password of the user "root" using the statements given below...

mysql> use mysql;

mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';

mysql> flush privileges;

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

Now stop the server and revert back the changes that we made in mysqld.cnf file and start the server...

Connect to the server now using the password switch..

> mysql --user=root --password

Which would ask you to provide the password, provide the one you updated the user with, now you have successfully changed the password of the user "root"

Hope you enjoyed the post, feel free to post your comments...

Thanks.

Changing the password of the root user of mysql in FEDORA

Hi all, In this post we are going to see how to reset the password of root user in mysql

As we all know the root user of mysql is something like the root user of linux with full power to access anything in MySql database server.

Normally during the install, the installation would ask for password to set for the root user but there are occasions where as administrator or developer would pushed to reset the password.

This post talks about resetting the password in a precise manner.

This post assumes that you already have a valid MySql Server instance up and running and I am going to use the command line tool i.e the Terminal for connecting to the server and resetting the password.

Let's start it...

Open a terminal and check whether the MySql server is up and running....

> sudo systemctl status mysql

In my case I had the following response on the terminal

If the server is up and running, then you would have something like given below...

Stop the Server(MySql Server) using the command given below

> sudo systemctl stop mysql

Now we have the server stopped.

We have to start the MySql Server differently to have the passord reset for the user root, to do that you must edit my.cnf file located in /etc folder...

Now open my.cnf file in your favourite editor and try to locate the group [mysqld], you can use the picture below to locate...

At the top of the group or just below the line which contains the content [mysqld]
add the line as said below

skip-grant-tables

Now the configuration file should appear something like the one shown below...

Save the file and exit from the text editor

Now try to start the server using the terminal as given below...

> sudo systemctl start mysql

Check the status of the mysql process

> sudo systemctl status mysql

you should have something like given below...

Connect to mysql server from the command using the command "mysql"

> mysql --user=root

Now you should be connected to the server and what you see on the server is the response connected to the server without the password for the user "root"

Change the password of the user "root" using the statements given below...

mysql> flush privileges;

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

Now stop the server and revert back the changes that we made in my.cnf file and start the server...

Connect to the server now using the password switch..

> mysql --user=root --password

Which would ask you to provide the password, provide the one you updated the user with, now you have successfully changed the password of the user "root"

Hope you enjoyed the post, feel free to post your comments...

Thanks.

MySQL - ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Hi all,

In this post we are going to discuss the error mentioned in the post heading line....

I was trying to alter the password of an user in mysql server and the password was something like "testing", the mysql server was not allowing me to change the password as metioned rather throwing an error mentioned in the post headline.

this post will help you to overcome this situation if you follow the steps detailed below...

Keep in mind. this should not be performed where you should not be compromising security but for your local database server and other environments where security is not a high priority, then go ahead and try it out.

The environment where I have tried this has fedora as operating system and mysql 8.0.x as the database server. The procedure is more or less similar for other environments as well...

Lets talk business now...

The default password policy in mysql server is defined as given in the image below

the above details can be retrieved using the mysql command given below....

mysql> SHOW VARIABLES LIKE 'validate_password%';

In order to have a simple and plain password for any user, we should execute set of mysql commands as given below...

mysql> SET GLOBAL validate_password.policy=LOW;

mysql> SET GLOBAL validate_password.length=5;

mysql> SET GLOBAL validate_password.mixed_case_count=0;

mysql> SET GLOBAL validate_password.number_count=0;

mysql> SET GLOBAL validate_password.special_char_count=0;

the above commands are self explanatory and the execution of the above commands would change password policy settings for the current session.

After having the settings changed you can confirm the same by firing the command below...

mysql> SHOW VARIABLES LIKE 'validate_password%';

 
Now you can try the following command on the user to whom you want to change the password as something like given below...

mysql > ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'testing';

this would change the password of the user.

The settings that we changed is for the current session only meaning once you have logged out or exited from the mysql server the new settings would be lost and the default settings would be retained

Well this concludes the post and hope you enjoyed it...

thanks and have a great time ahead.

Updating the password of a user in MySQL

Hi All,

In this post I will be giving you the hints to update the password of a user in MySql Database Server...

This is going to be a very short post, hope you like it....

There are many ways to achieve this, but the one I am going to talk about is straight forward SQL statement...

Well before you try to do what is going to be said, have the user credentials of the root user of mysql handy....

Let's start it...

This post assumes that you have the mysql database server installed and running.

Logging in as root user

Log into the database server as root

Open up a command line window and fire the command given below...

> mysql -u root -p

The above command would prompt you to key in the password, provide the one associated with the account.

You should be having the mysql prompt now to proceed...

The general form the SQL command is given below, you can change the statement and execute it accordingly...

UPDATE mysql.user SET authentication_string=PASSWORD('<password>') WHERE USER='<username>';

For Eg...

UPDATE mysql.user SET authentication_string=PASSWORD('welcome') WHERE USER='admin';

Well that is it and now you can query the database to confirm the change using the command below....

> SELECT `User`, authentication_string FROM mysql.`user` where User = '<username>';

This post is concluded here and I hope you had great time.

Thanks.

Running Multiple Instances of MySql in ubuntu 16.04

Hi,

In this post we will discuss about having multiple instances of mysql on a server machine.

I am trying  to establish the objective here using MySql-5.7.25 on Ubuntu, you can do the same with other version of MySql as well

If you do not have one, you can refer an other article of mine to install MySql Server, the link to it is given below

MySql Server Installation in Ubuntu

1. Disabling mysqld in AppArmor 

The first and foremost is disabling mysqld using AppArmor tool, Ubuntu is shipped with this tool by default and if not you can install the same using the following web page as reference...

Installation of AppArmor

Create a link to "usr.sbin.mysqld" in disable folder as follows...

Move to folder "/etc/apparmor.d/disable" and provide the command below to have mysql daemon disabled at the start up

> sudo ln -s ../usr.sbin.mysqld usr.sbin.mysqld

RESTART YOUR MACHINE

2. Creating Directories and Files

The second step is to create required directories for each instances of mysql, the following commands would help to create those directories for you.

I have chosen "/opt" directory as my location to put the directories in but, you can choose to have your convenient location to create directories...

The below commands would ask to provide the password before the execution, provide the one associated with the account.

Creating directories for Instance One

> sudo mkdir -p /opt/mysql/one/data

> sudo mkdir -p /opt/mysql/one/log

> sudo touch /opt/mysql/one/log/mysql.log

> sudo chown -R mysql:mysql /opt/mysql/one

> sudo /usr/sbin/mysqld --initialize --explicit_defaults_for_timestamp --user=mysql --basedir=/opt/mysql/one --datadir=/opt/mysql/one/data

Creating directories for Instance Two

> sudo mkdir -p /opt/mysql/two/data

> sudo mkdir -p /opt/mysql/two/log

> sudo touch /opt/mysql/two/log/mysql.log

> sudo chown -R mysql:mysql /opt/mysql/two

> sudo /usr/sbin/mysqld --initialize --explicit_defaults_for_timestamp --user=mysql --basedir=/opt/mysql/two --datadir=/opt/mysql/two/data

Creating directories for Instance Three

> sudo mkdir -p /opt/mysql/three/data

> sudo mkdir -p /opt/mysql/three/log

> sudo touch /opt/mysql/three/log/mysql.log

> sudo chown -R mysql:mysql /opt/mysql/three

> sudo /usr/sbin/mysqld --initialize --explicit_defaults_for_timestamp --user=mysql --basedir=/opt/mysql/three --datadir=/opt/mysql/three/data

3. Editing my.cnf file (/etc/mysql/my.cnf)

The third step is to update the file called "my.cnf" file which you can locate under the folder "/etc/mysql"

The following are the details to be provided for each instance of MySql in "my.cnf", these entries are called group entries where each entry corresponds to an instance of mysql

[mysqld_multi]

mysqld	=	/usr/bin/mysqld_safe
mysqladmin	=	/usr/bin/mysqladmin
user	=	multi_admin
password	=	password
log	=	/var/log/mysqld_multi.log

[mysqld1]
skip-grant-tables

user	=	mysql
port	=	3306
datadir	=	/opt/mysql/one/data
socket	=	/opt/mysql/one/one-mysql.sock
pid-file	=	/opt/mysql/one/one-mysql.pid
log-error	=	/opt/mysql/one/log/mysql.log

[mysqld2]
skip-grant-tables

user	=	mysql
port	=	4406
datadir	=	/opt/mysql/two/data
socket	=	/opt/mysql/two/two-mysql.sock
pid-file	=	/opt/mysql/two/two-mysql.pid
log-error	=	/opt/mysql/two/log/mysql.log

[mysqld3]
skip-grant-tables

user	=	mysql
port	=	5506
datadir	=	/opt/mysql/three/data
socket	=	/opt/mysql/three/three-mysql.sock
pid-file	=	/opt/mysql/three/three-mysql.pid
log-error	=	/opt/mysql/three/log/mysql.log

Start the Instances

After the configuration update is finished at "my.cnf", the immediate next step is to start the instance to check its function.

The command given below would help you to achieve starting an instance, if you are successful please perform the same actions for other instances as well...

NOTE: do not forget to change the socket file parameter...

> sudo mysqld_multi start

> mysql -u root -S /opt/mysql/default/default-mysql.sock -p

mysql> use mysql

mysql> UPDATE user SET authentication_string=PASSWORD('admin') WHERE User='root';

mysql> FLUSH PRIVILEGES;

4. Granting SHUTDOWN privilege to multi_admin USER

This step discuss granting shutdown privileges to multi_admin user, to provide the same kill the running mysql instances using "ps-aux | grep mysql"

remove "skip-grant-tables" parameter from every group in my.cnf file

now your my.cnf file should look something as below

[mysqld_multi]

mysqld	=	/usr/bin/mysqld_safe
mysqladmin	=	/usr/bin/mysqladmin
user	=	multi_admin
password	=	password
log	=	/var/log/mysqld_multi.log

[mysqld1]

user	=	mysql
port	=	3306
datadir	=	/opt/mysql/one/data
socket	=	/opt/mysql/one/one-mysql.sock
pid-file	=	/opt/mysql/one/one-mysql.pid
log-error	=	/opt/mysql/one/log/mysql.log

[mysqld2]

user	=	mysql
port	=	4406
datadir	=	/opt/mysql/two/data
socket	=	/opt/mysql/two/two-mysql.sock
pid-file	=	/opt/mysql/two/two-mysql.pid
log-error	=	/opt/mysql/two/log/mysql.log

[mysqld3]

user	=	mysql
port	=	5506
datadir	=	/opt/mysql/three/data
socket	=	/opt/mysql/three/three-mysql.sock
pid-file	=	/opt/mysql/three/three-mysql.pid
log-error	=	/opt/mysql/threelog/mysql.log

5. Start an instance

After removing the "skip-grant-tables" parameter from the group in "my.cnf" each instance of MySql will be identified by numbers based  on the position they appear in the configuration file.

To start the first instance, issue the following command

> sudo mysqld_multi start 1

login as root using the following command on instance 1 to check whether the connection establishment is fine.

> mysql -u root -S /opt/mysql/default/default-mysql.sock -p

provide the password of the root user

We can conclude the connection is fine, the the terminal window appears with mysql prompt.

6. Reset the password of root

This is final step and also an optional step to have, If you want to reset the root users password you can perform resetting the password using the following commands.

mysql> SET PASSWORD = PASSWORD('admin');

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY '<password-provided-in-my.cnf-for-multi_admin>';

Reset the password of 'root' user in MySql in Ubuntu-16.0.4

Hi all, In this post we are going to see how to reset the password of root user in mysql

As we all know the root user of mysql is something like the root user of linux with full power to access anything in MySql database server.

Normally during the install, the installation would ask for password to set for the root user but there are occasions where as administrator or developer would pushed to reset the password.

This post talks about resetting the password in a precise manner.

This post assumes that you already have a valid MySql Server instance up and running and I am going to use the command line tool i.e the Terminal for connecting to the server and resetting the password.

Let's start it...

Open a terminal and check whether the MySql server is up and running....

> sudo systemctl status mysql

In my case I had the following response on the terminal

If the server is up and running, then you would have something like given below...

Stop the Server(MySql Server) using the command given below

> sudo systemctl stop mysql

Now we have the server stopped.

We have to start the MySql Server differently to have the passord reset for the user root, to do that you must edit mysqld.cnf file located in /etc/mysql/mysql.conf.d folder...

Now open mysqld.cnf file in your favourite editor and try to locate the group [mysqld], you can use the picture below to locate...

At the top of the group or just below the line which contains the content [mysqld]
add the line as said below

skip-grant-tables

Now the configuration file should appear something like the one shown below...

Save the file and exit from the text editor

Now try to start the server using the terminal as given below...

> sudo systemctl start mysql

Check the status of the mysql process

> sudo systemctl status mysql

you should have something like given below...

Connect to mysql server from the command using the command "mysql"

> mysql --user=root

Now you should be connected to the server and what you see on the server is the response connected to the server without the password for the user "root"

Change the password of the user "root" using the statement like the one given below...

Now stop the server and revert back the changes that we made in mysqld.cnf file and start the server...

Connect to the server now using the password switch..

> mysql --user=root --password

Which would ask you to provide the password, provide the one you updated the user with, now you have successfully changed the password of the user "root"

Hope you enjoyed the post, feel free to post your comments...

Thanks.

How to Restore MySQL Database Dump in MySQL Server in Linux Box

In this article I would like to discuss how to Restore database dump into MySQL Server...

For our discussion, let me have the database dump of database "mysql" from MySQL Server, which I have already taken in a file named "serverdump-of-database-mysql.sql"

To know how to dump a database from MySQL server, you can checkout my article given below...
How to take MySQL dump using the command utility "mysqldump"

This article assumes you have the Username and Password handy to connect to database server.

I am going to use the root user's credentials to access the server instance and to have the database dump.

To try out I am going to assume the following properties

DATABASE : mysql_dump
USERNAME : root
PASSWORD : admin

Open up a terminal and connect to MySQL Sever as dictated below.

> mysql --user=root --password

The above command would prompt to provide the password, please provide the one you used to connect to server, I am going to use the password "admin" as mentioned above.

Soon after you provide the password, you would have mysql prompt as given in the image below...

Now create a database as given below...

mysql> create database mysql_dump;

The above statement creates a database named "mysql_dump", to check that use the statement below...

mysql> show databases;

The above statement would list down the databases like the one given below along with the one just created...

As you can see we have the database "mysql_dump" created with no tables in it... this can be justified by firing the following statement at the prompt

mysql> use mysql_dump;
mysql> show tables;

the above command results something like the image given below...

Now exit from MySQL Server by typing "exit" at the prompt.

mysql> exit

So far we had been working on the set up, now is the time to restore the database we had backed up.

To achieve that... type the following command at the prompt

> mysql --user=root --password mysql_dump < <location-of-the-dump-file>

eg

> mysql --user=root --password mysql_dump < /home/ravaneswaran/serverdump-of-database-mysql.sql

provide the password to restore the database.

To check the restoration process successful... connect to database server and check the database "mysql_dump", which can be done as follows...

> mysql --user=root  mysql_dump --password

provide the password...

mysql> show tables;

The response of the above statement would be as follows...

Well this concludes the restoration of backed up database in MySQL Server.

Hope you enjoyed reading.

MySQL Server Installation in Ubuntu 18.10

Hi, this post discuss the installation of MySQL Server in Ubuntu-18.10.

You can install the same in many different ways you like, but this post talks about installing MySQL Server using "Advanced Package Tool" simply apt

This Advanced Package Tool comes default with Debian, Ubuntu and related Linux Distributions.

This procedure is not just for this particular distribution of Ubuntu but for other distributions too....

To install MySQL Server, you need to have Administrator Rights... so make sure you logged into the system with an account(Linux Login Account) with administrator rights

Open a terminal and issue the following command at the prompt..

 > which mysql

The above command gives the location of mysql executable.

/usr/bin/mysql

If nothing appears conveys us it is yet to be installed.

to install MySQL Server issue the following command at the prompt

 > sudo apt-get install mysql-server

the above command would prompt you to provide the password, provide the one associated with the account

Now the package manager i.e Advanced Package Tool would install MySQL Server

Be patience till the process is completed.

After the installation is completed, at the terminal issue the same command which we had used to check mysql service is installed. i.e "which mysql"

The response would give a valid location of mysql which is nothing but the client to connect to the database server.

The acutal server executable is mysqld whose location can be located the using the following

 > which mysqld

The above command would print out the location of the mysqld executable, usually you will be locating mysqld in the following folder

/usr/sbin/mysqld

With this we can confirm that MySQL Server is installed successfully but still we have not checked it is up and running or not

To have that check issue the following command at the prompt...

 > sudo service mysql status

If the server is not up and running

you can start the server as follows..

 > sudo service mysql start 

Now you should have mysql server instance for service.

to verify, execute the command "sudo service mysql status", which would result in response something like the one shown in the image below...

With this we have concluded the installtion of MySQL Server in Ubuntu-18.10

Thanks...

How to take MySQL dump using the command utility "mysqldump"

This post discusses about taking backup of your database using the utility "mysqldump"

This post assumes you have MySQL Server installed, up and running

Alright, lets talk the business..

There are two ways you can have the MySQL Dump one is with data and another is without it

this post provides options for both, depending on you requirement you can pick the appropriate

Taking MySQL Server Database Dump with data

This form takes the format as mentioned below

mysqldump --routines --add-drop-table --disable-keys --extended-insert --host=<SERVERHOST> --port=<PORT> --user=<USERNAME> --verbose <DATABASENAME> --password > <FILENAME>.<EXTENSION>

let us assume certain parameters to this utility as follows

--host=localhost
--port=3306
--user=root
<DATABASENAME>=mysql
<FILENAME>=serverdump-of-database-mysql
<EXTENSION>=sql

for the above mentioned parameters, the command appears to be as follows...

mysqldump --routines --add-drop-table --disable-keys --extended-insert --host=localhost --port=3306 --user=root --verbose mysql --password > serverdump-of-database-mysql.sql

Now open a termial window and fire the command at the prompt as follows

> mysqldump --routines --add-drop-table --disable-keys --extended-insert --host=localhost --port=3306 --user=root --verbose mysql --password > serverdump-of-database-mysql.sql

at the end of the process you should be having the file "serverdump-of-database-mysql.sql" at the present-working-directory.

that is it, you have backed up your required database successfully...

Taking MySQL Server Database Dump without data

This form takes the format as the one we discussed above but with a little difference...

mysqldump --routines --add-drop-table --disable-keys --extended-insert --host=<SERVERHOST> --port=<PORT> --user=<USERNAME> --verbose <DATABASENAME> --no-data --password > <FILENAME>.<EXTENSION>

having the same parameters as discussed above the command appears to be...

mysqldump --routines --add-drop-table --disable-keys --extended-insert --host=localhost --port=3306 --user=root --verbose --no-data mysql --password > serverdump-of-database-mysql.sql

now fire the command at the prompt as given below

> mysqldump --routines --add-drop-table --disable-keys --extended-insert --host=localhost --port=3306 --user=root --verbose --no-data mysql --password > serverdump-of-database-mysql.sql

well that is it you have taken the structure or template of the requierd database.

How to remove MySQL completely in Ubuntu-16.04 ?

In this post we will discuss how to delete mysql server.

If you want to backup the database before removing MySQL service, you can refer the post

How to take MySQL dump using the command utility "mysqldump"

This post assumes you have running instance of mysql server which you want to remove or the corrupted one which you want to get rid of...

In the former case let us confirm first that the server is not running...

use the following command to check the server is running or not

> service mysql status or systemctl status mysql

the response would let you know whether the server is running or not

if the server is running then shut it down first as follows...

> sudo service mysql stop

use "service mysql status" to check the server has come to a stop.

Now we are going to perform the action intented... which is removing the mysql service

execute the following commands in order as they are mentioned at the terminal

> 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

Deleteing the mysql directory in /etc and /var/lib

> sudo rm -rf /etc/mysql

> sudo rm -rf /var/lib/mysql

If you want to install MySQL Server again, you can check my post

MySQL Server Installation in Ubuntu - 18.10

Well that is it, please post your comments if you encounter any issue while removing the instance thanks.