Search
 
SCRIPT & CODE EXAMPLE
 
CODE EXAMPLE FOR SHELL

mySQL root password config

1. Confirm MySQL version
Firstly, you must confirm which version of MySQL on Ubuntu you are running as commands will be different.

mysql -V

If on MySQL version 8, you will see something like:

mysql Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

If you are on MySQL version 5, you will see something similar to:

mysql  Ver 14.14 Distrib 5.7.36, for Linux (x86_64) using  EditLine wrapper

2. Restart MySQL with skip-grant-table
In order to skip the grant tables and reset the root password, we must first stop the MySQL service. Enter your Linux password if prompted.

sudo /etc/init.d/mysql stop

Ensure the directory /var/run/mysqld exists and correct owner set.

sudo mkdir /var/run/mysqld

sudo chown mysql /var/run/mysqld

Now start MySQL with the --skip-grant-tables option. The & is required here.

sudo mysqld_safe --skip-grant-tables&

You should see something similar:

[1] 1283
user@server:~$ 2019-02-12T11:15:59.872516Z mysqld_safe Logging to syslog.
2019-02-12T11:15:59.879527Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2019-02-12T11:15:59.922502Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Now press ENTER to return to the Linux BASH prompt.

3. Change MySQL Root Password
You can now log in to the MySQL root account without a password.

sudo mysql --user=root mysql
 Save

Once logged in, you will see the mysql> prompt.

MySQL 8 – Reset Root Password
For MySQL 8 on Ubuntu, run following commands.

UPDATE mysql.user SET authentication_string=null WHERE User='root';

flush privileges;

Replace your_password_here with your own. (Generate a strong password here)
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';

Flush privileges again.

flush privileges;

Exit MySQL.

exit


4. Test New Root Password
Make sure all MySQL processes are stopped before starting the service again.

sudo killall -u mysql

If you see a message similar to below, press ENTER to continue.

2020-05-30T07:23:38.547616Z mysqld_safe mysqld from pid file /var/lib/mysql/ubuntu.pid ended

Start MySQL again.

sudo /etc/init.d/mysql start

Log in to MySQL again and you should now be prompted for a password.

sudo mysql -p -u root

Enter your MySQL root password. If correct, you should see something like:

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>
Source by www.shervinrazmara.com #
 
PREVIOUS NEXT
Tagged: #mySQL #root #password #config
ADD COMMENT
Topic
Name
4+3 =