6.2.1 Account User Names and Passwords

When installing MySQL, you may noticed that it does not ask for a password. This become troublesome when you want to connect your application to MySQL database. In this article, I will show you on how to find MySQL default password.

Also, in case you have accidently put a password during installation process and can’t recall the password, you need to reset the password.

There is no way to view the password as it’s already written as hashed.

sudo mysql -u root -p

show databases;

use mysql;

select user, authentication_string, plugin, password_lifetime, password_last_changed, password_expired from user;

desc user;

sudo -i mysql

sudo mysqladmin -u root password myPassWhichIWant

mysql_secure_connection

ALTER USER 'root'@'localhost' IDENTIFIED BY 'a';

sudo systemctl stop mysql

sudo mysqld -init-file=~/mysql-password

sudo systemctl start mysql

mysqladmin5 -u root -p ping 

I get a password request.

<<
After installing MySQL 5 on Solaris with pkgadd you will have to perform
the following post-installation tasks:
As root:
# rm -rf /var/lib/mysql/mysql
Then as mysql:
$ cd /opt/mysql/mysql
$ scripts/mysql_install_db
Then as root:
# /etc/init.d/mysql start
Then as mysql:
$ /opt/mysql/mysql/bin/mysqladmin -u root password 'password'
Then as root:
# /opt/mysql/mysql/bin/mysql_secure_installation
>>

The password was not blank as in some versions of MySQL.

Thank you for pointing that out!
The version of my MySQL seems to be

mysql5 --version
mysql5 Ver 14.12 Distrib 5.0.83, for apple-darwin9.7.0 (i386) using readline 6.0

What is the default password of the MySQL?

asked Jul 4, 2009 at 13:35

The default password depends on the distribution, I think. In some Linux distributions you’re asked to enter pass during installation, in other it’s blank for connection over loopback.

Try starting MySQL with the —skip-grant-tables option, then logon with the MySQL command line and change root’s password.

answered Jul 4, 2009 at 13:40

6 gold badges65 silver badges108 bronze badges

mysql -p ping

is very different from

mysql --password=ping

first one means «Connect to database and prompt for password», second one means, «Connect to database with password ‘ping'»

answered Nov 30, 2012 at 16:03

I install mariadb in arch-linux. but I haven’t got access to MySQL.
I try this:

 mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I see very similar question, and test all of answers but I can’t resolve it.

1 gold badge21 silver badges27 bronze badges

asked Dec 24, 2013 at 15:24

Vahid Kharazi

4 gold badges10 silver badges15 bronze badges

You should have been asked for the password during installation.

In case you do not remember your password, you can always reset your password.

answered Dec 24, 2013 at 16:11

The wiki says you should be able to log in with an empty password for root like so:

$ mysql -u root -p

However, you may still get an error when you use an empty password:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

$ sudo mysql -u root -p

This should let you log in as root.

answered Nov 7, 2020 at 17:17

Reset password using this link.
https://bbs.archlinux.org/viewtopic.php?id=51981

sudo rm -r /var/lib/mysql
sudo pacman -Rn mysql
sudo pacman -S mysql
sudo /usr/bin/mysql_install_db
sudo /usr/bin/mysql_secure_installation

you can run all the commands, running last command again worked for me.

answered Oct 13, 2020 at 16:02

first you must to start the service (/etc/init.d/mysqld start). if the installation didn’t ask you the password simply press Enter.

sudo mysql -u root -p

answered Nov 7, 2020 at 17:50

first you must to start the service (/etc/init.d/mysqld start). if the installation didn’t ask you the password simply press Enter.

answered Dec 24, 2013 at 18:09

For a long time, MySQL has supported different authentication plugins, basically programable pieces of code to demonstrate that a mysql accounts is owned by whoever claims so.

• The unix socket authentication allows login to uses on the local machine with the same unix name than the mysql account. That is commonly used for admin accounts for things like monitoring or other tasks without needing to maintain a password. It has that name because it only works with socket connections (not remotelly)
• A PAM autentication plugin allows to set up, for example, an LDAP backed system and use that to authenticate (nice to integrate it into an existing organization)
• The latest versions of mysql (8.0) use a less trivial authentication method (caching_sha2_password), which in theory is more secure (I am not saying it is or it is not, but certainly the default «native» one was quite bad), but may require updates of client drivers and applications, so you can always revert to the older one for compatibility reasons.

Basically, mysql_native_password is the traditional method to authenticate- it is not very secure (it uses just a hash of the password), but it is compatible with older drivers. If you are going to start a new mysql service, you probably want to use the new plugin from the start (and TLS). If you have special needs, you can use other method— you can even program one if you have certain special needs).

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';

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

Installing and Starting MySQL

• Linux. 
  The easiest way to install MySQL is to use the
  MySQL
  repositories:

  For Linux distributions that do not support the
  MySQL
  repositories or the installation packages mentioned
  above, you can install MySQL using generic binaries:

  Detailed instructions, as well as other methods for
  installation, can be found in
  Installing MySQL on Linux.

• Microsoft Windows. 
  The recommended way to install MySQL on Microsoft Windows is
  to use the MySQL Installer; see
  MySQL Installer Method on how to
  download and run the MySQL Installer. For a detailed
  explanation for each step of the installation wizard, see
  MySQL Installer for Windows.

  If you have chosen to configure MySQL as a Windows service
  during the installation process, which is the default option
  (see Windows Service for details),
  the MySQL server will start automatically after the
  installation process is completed.

  Detailed information regarding Windows installation, including
  alternative installation methods and instructions for
  troubleshooting, can be found in
  Installing MySQL on Microsoft Windows.

• macOS. 
  The recommended way for installing MySQL on macOS is to use
  the macOS installer package. See
  Installing MySQL on macOS Using Native Packages on how to download
  and run the installer package, and how to start the MySQL
  server afterward.

  Detailed information regarding installation on macOS can be
  found in Installing MySQL on macOS.

• Other platforms. 
  For installations on other platforms (for example, FreeBSD
  and Solaris), as well as installation methods not covered
  above, see Installing and Upgrading MySQL.

6.2.1 Account User Names and Passwords

An account may also have authentication credentials such as a
password. The credentials are handled by the account
authentication plugin. MySQL supports multiple authentication
plugins. Some of them use built-in authentication methods, whereas
others enable authentication using external authentication
methods. See Section 6.2.17, “Pluggable Authentication”.

• You should never alter the structure of tables in the
  mysql database in any manner whatsoever
  except by means of the procedure that is described in
  Section 2.10, “Upgrading MySQL”. Attempting to redefine MySQL’s
  system tables in any other fashion results in undefined and
  unsupported behavior. The server is free to ignore rows that
  become malformed as a result of such modifications.

• Standard MySQL client programs support a
  --default-character-set option that causes
  mysql_options() to be called
  as just described. In addition, character set autodetection is
  supported as described in
  Section 10.4, “Connection Character Sets and Collations”. For programs that use a
  connector that is not based on the C API, the connector may
  provide an equivalent to
  mysql_options() that can be
  used instead. Check the connector documentation.

  The preceding notes do not apply for ucs2,
  utf16, and utf32, which
  are not permitted as client character sets.

$> mysql --user=finley --password db_name

If you prefer short options, the command looks like this:

$> mysql -u finley -p db_name

$> mysql --user=finley --password=password db_name$> mysql -u finley -ppassword db_name

2.9.4 Securing the Initial MySQL Account

The MySQL installation process involves initializing the data
directory, including the grant tables in the
mysql system schema that define MySQL accounts.
For details, see Section 2.9.1, “Initializing the Data Directory”.

This section describes how to assign a password to the initial
root account created during the MySQL
installation procedure, if you have not already done so.

Alternative means for performing the process described in this
section:

A password may already be assigned to the initial account under
these circumstances:

• On Windows, installations performed using MySQL Installer give you the
  option of assigning a password.

• Installation using RPM packages generates an initial random
  password, which is written to the server error log.

• Installations using Debian packages give you the option of
  assigning a password.

• For data directory initialization performed manually using
  ,
  generates an initial random
  password, marks it expired, and writes it to the server error
  log. See Section 2.9.1, “Initializing the Data Directory”.

Start the server if it is not running. For instructions, see
Section 2.9.2, “Starting the Server”.

• If the root account exists with an initial
  random password that has been expired, connect to the server
  as root using that password, then choose a
  new password. This is the case if the data directory was
  initialized using ,
  either manually or using an installer that does not give you
  the option of specifying a password during the install
  operation. Because the password exists, you must use it to
  connect to the server. But because the password is expired,
  you cannot use the account for any purpose other than to
  choose a new password, until you do choose one.

  1. If you do not know the initial random password, look in
     the server error log.

  2. Connect to the server as root using the
     password:

     $> mysql -u root -p
     Enter password: (enter the random root password here)

  3. Choose a new password to replace the random password:

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

• If the root account exists but has no
  password, connect to the server as root
  using no password, then assign a password. This is the case if
  you initialized the data directory using mysqld
  —initialize-insecure.

  1. Connect to the server as root using no
     password:

     $> mysql -u root --skip-password

  2. Assign a password:

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

After assigning the root account a password,
you must supply that password whenever you connect to the server
using the account. For example, to connect to the server using the
client, use this command:

$> mysql -u root -p
Enter password: (enter root password here)

$> mysqladmin -u root -p shutdown
Enter password: (enter root password here)

Legal Notices

This software and related documentation are provided under a license
agreement containing restrictions on use and disclosure and are
protected by intellectual property laws. Except as expressly
permitted in your license agreement or allowed by law, you may not
use, copy, reproduce, translate, broadcast, modify, license,
transmit, distribute, exhibit, perform, publish, or display any
part, in any form, or by any means. Reverse engineering,
disassembly, or decompilation of this software, unless required by
law for interoperability, is prohibited.

The information contained herein is subject to change without notice
and is not warranted to be error-free. If you find any errors,
please report them to us in writing.

This software or hardware is developed for general use in a variety
of information management applications. It is not developed or
intended for use in any inherently dangerous applications, including
applications that may create a risk of personal injury. If you use
this software or hardware in dangerous applications, then you shall
be responsible to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure its safe use. Oracle
Corporation and its affiliates disclaim any liability for any
damages caused by use of this software or hardware in dangerous
applications.

Oracle, Java, and MySQL are registered trademarks of Oracle and/or its
affiliates. Other names may be trademarks of their respective
owners.

Intel and Intel Inside are trademarks or registered trademarks of
Intel Corporation. All SPARC trademarks are used under license and
are trademarks or registered trademarks of SPARC International, Inc.
AMD, Epyc, and the AMD logo are trademarks or registered trademarks
of Advanced Micro Devices. UNIX is a registered trademark of The
Open Group.

This software or hardware and documentation may provide access to or
information about content, products, and services from third
parties. Oracle Corporation and its affiliates are not responsible
for and expressly disclaim all warranties of any kind with respect
to third-party content, products, and services unless otherwise set
forth in an applicable agreement between you and Oracle. Oracle
Corporation and its affiliates will not be responsible for any loss,
costs, or damages incurred due to your access to or use of
third-party content, products, or services, except as set forth in
an applicable agreement between you and Oracle.

You may create a printed copy of this documentation solely for your
own personal use. Conversion to other formats is allowed as long as
the actual content is not altered or edited in any way. You shall
not publish or distribute this documentation in any form or on any
media, except if you distribute the documentation in a manner
similar to how Oracle disseminates it (that is, electronically for
download on a Web site with the software) or on a CD-ROM or similar
medium, provided however that the documentation is disseminated
together with the software on the same medium. Any other use, such
as any dissemination of printed copies or use of this documentation,
in whole or in part, in another publication, requires the prior
written consent from an authorized representative of Oracle. Oracle
and/or its affiliates reserve any and all rights to this
documentation not expressly granted above.

How To Reset MySQL Default Password

Windows OS

2. On the windows search box, search for services.msc and click to open.

3. Scroll down to all services with its status. Find MySQL services, right-click on it and click stop services.

4. Create a text file which contains the SQL statement in a single line as below:

Change MyNewPass to your new desired password.

5. Save it to a text file. For example, save it as C:\new-password.txt.

7. Start the MySQL with init_file system variable set to text file name created as below:

C:\> cd “C:\Program Files\MySQL\MySQL Server 5.7\bin”
C:\> mysqld –init-file=C:\\mysql-init.txt

You may replace your MySQL installation location after cd command.

Linux

1. Open terminal.

2. Stop MySQL server.

sudo service mysql stop

sudo /usr/local/mysql/support-files/mysql.server stop

3. Start MySQL in safe mode.

sudo mysqld_safe –skip-grant-tables

4. Open another terminal and login as root by run below command.

mysql -u root

3. Once MySQL is logged in via terminal, run below queries.

which be looks like:

If you are using MySQL 5.7 and above you need to run command as below:

4. Now, you can exit MySQL safe mode.

If you received error ‘access denied’ you can run below command with the new password:

5. Start MySQL service again by run below command.

sudo service mysql start

Other Helpful Resources

What If Still Failed To Reset MySQL Default Password?

Thanks for reading this article. I hope you find it helpful.

Some Basic Operations with MySQL

Here are some basic operations with the MySQL server.
SQL Statements explains in detail the rich
syntax and functionality of the SQL statements that are
illustrated below.

Showing existing databases. 
Use a SHOW DATABASES
statement:

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

Creating a new database. 
Use a CREATE DATABASE
statement:

mysql> CREATE DATABASE pets;
Query OK, 1 row affected (0.01 sec)

Check if the database has been created:

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| pets |
| sys |
+--------------------+
5 rows in set (0.00 sec)

Creating a table inside a database. 
First, pick the database in which you want to create the table
with a USE statement:

mysql> USE pets
Database changed

The USE statement tells MySQL to use
pets as the default database for subsequent
statements. Next, create a table with a
CREATE TABLE statement:

CREATE TABLE cats
( id INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record name VARCHAR(150) NOT NULL, # Name of the cat owner VARCHAR(150) NOT NULL, # Owner of the cat birth DATE NOT NULL, # Birthday of the cat PRIMARY KEY (id) # Make the id the primary key
);

Check if the table has been created with a
SHOW TABLES statement:

mysql> SHOW TABLES;
+----------------+
| Tables_in_pets |
+----------------+
| cats |
+----------------+
1 row in set (0.00 sec)

DESCRIBE shows information on all
columns of a table:

mysql> DESCRIBE cats;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(150) | NO | | NULL | |
| owner | varchar(150) | NO | | NULL | |
| birth | date | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

INSERT INTO cats ( name, owner, birth) VALUES ( 'Sandy', 'Lennon', '2015-01-03' ), ( 'Cookie', 'Casey', '2013-11-13' ), ( 'Charlie', 'River', '2016-05-21' );

See Literal Values for how to write string, date, and
other kinds of literals in MySQL.

Retrieving records from a table. 
Use a SELECT statement, and
to match all columns:

mysql> SELECT * FROM cats;
+----+---------+--------+------------+
| id | name | owner | birth |
+----+---------+--------+------------+
| 1 | Sandy | Lennon | 2015-01-03 |
| 2 | Cookie | Casey | 2013-11-13 |
| 3 | Charlie | River | 2016-05-21 |
+----+---------+--------+------------+
3 rows in set (0.00 sec)

To select specific columns and rows by a certain condition using
the WHERE clause:

mysql> SELECT name FROM cats WHERE owner = 'Casey';
+--------+
| name |
+--------+
| Cookie |
+--------+
1 row in set (0.00 sec)

Deleting a record from a table. 
Use a DELETE statement to delete a
record from a table, specifying the criterion for deletion with
the WHERE clause:

mysql> DELETE FROM cats WHERE name='Cookie';
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM cats;
+----+---------+--------+------------+
| id | name | owner | birth |
+----+---------+--------+------------+
| 1 | Sandy | Lennon | 2015-01-03 |
| 3 | Charlie | River | 2016-05-21 |
+----+---------+--------+------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE cats ADD gender CHAR(1) AFTER name;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0

Use DESCRIBE to check the result:

mysql> DESCRIBE cats;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(150) | NO | | NULL | |
| gender | char(1) | YES | | NULL | |
| owner | varchar(150) | NO | | NULL | |
| birth | date | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

SHOW CREATE TABLE shows a
CREATE TABLE statement, which
provides even more details on the table:

mysql> SHOW CREATE TABLE cats\G
*************************** 1. row *************************** Table: cats
Create Table: CREATE TABLE `cats` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(150) NOT NULL, `gender` char(1) DEFAULT NULL, `owner` varchar(150) NOT NULL, `birth` date NOT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE cats DROP gender;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE cats;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(150) | NO | | NULL | |
| owner | varchar(150) | NO | | NULL | |
| birth | date | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Connecting to the MySQL Server with the mysql Client

• $> mysql -u root -p

• C:\> mysql -u root -p

You are then asked for the root password, which
was assigned in different manners according to the way you
installed MySQL. The installation and initialization instructions
given above already explain the root password,
but here is a quick summary:

• $> sudo grep 'temporary password' /var/log/mysqld.log

• For installations using the MySQL APT repository or Debian
  packages directly downloaded from Oracle, you should have
  already assigned the root password
  yourself; if you have not done that for some reason, see the
  «Important» note
  here
  or How to Reset the Root Password.

• [Warning] A temporary password is generated for root@localhost:
  iTag*AfrH5ej

  Depending on the configuration you used to initialize the
  MySQL server, the error output might have been directed to
  the MySQL error log; go
  there and check for the password if you do not see the
  above message on your screen. The error log is a file with
  a .err extension, usually found under
  the server’s data directory (the location of which depends
  on the server’s configuration, but is likely to be the
  data folder under the base directory
  of your MySQL installation, or the
  /var/lib/mysql folder).

  If you have initialized the data directory with
  mysqld --initialize-insecure instead, the
  root password is empty.

• For installations on Windows using the MySQL Installer and OS
  X using the installer package, you should have assigned a
  root password yourself.

If you have forgotten the root password you
have chosen or have problems finding the temporary
root password generated for you, see
How to Reset the Root Password.

Once you are connected to the MySQL server, a welcome message is
displayed and the mysql> prompt appears, which
looks like this:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.32 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates.
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>

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

Here are a few useful things to remember when using the
client:

• Client commands (for example, help,
  quit, and clear) and
  keywords in SQL statements (for example,
  SELECT,
  CREATE TABLE, and
  INSERT) are not case-sensitive.

• Column names are case-sensitive. Table names are
  case-sensitive on most Unix-like platforms, but not
  case-sensitive on Windows platforms. Case-sensitivity during
  string comparison depends on the character collation you use.
  In general, it is a good idea to treat all identifiers
  (database names, table names, column names, etc.) and strings
  as case-sensitive. See
  Identifier Case Sensitivity and
  Case Sensitivity in String Searches for details.

To disconnect from the MySQL server, type QUIT
or \q at the client:

mysql> QUIT

Other Important Tasks to Perform

Configure MySQL to be managed with systemd. 
If you have installed MySQL on a systemd platform using generic
binaries and want it to be managed with systemd, see
Managing MySQL Server with systemd.