How to manage users and login to mysql

How do you login to a remote database with a password?

If your MySQL server isn’t running on your local server, you’ll have to specify the host that the client should try to connect to. You can do that by adding the –host option.

Most of the time, you’ll be authenticating with a password to remote MySQL servers, so the command would look something like this:

Prerequisites

To follow along with this guide, you’ll need an account on a MySQL server with the appropriate privileges.

What is the default root pasword for mysql 5.7

MySQL server 5.7 was already installed by default on my new Linux Mint 19.

But, what’s the MySQL root password? It turns out that:

The default installation uses auth_socket for authentication, in lieu of passwords!

It allows a password-free login, provided that one is logged into the Linux system with the same user name. To login as the MySQL root user, one can use sudo:

sudo mysql --user=root

But how to then change the root password? To illustrate what’s going on, I created a new user “me”, with full privileges, with:

mysql> CREATE USER 'me'@'localhost' IDENTIFIED BY 'my_new_password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'me'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Comparing “me” with “root”:

mysql> SELECT user, plugin, HEX(authentication_string)  FROM mysql.user WHERE user = 'me' or user = 'root';
 ------ ----------------------- ---------------------------------------------------------------------------- 
| user | plugin                | HEX(authentication_string)                                                 |
 ------ ----------------------- ---------------------------------------------------------------------------- 
| root | auth_socket           |                                                                            |
| me   | mysql_native_password | 2A393846353030304545453239394634323734333139354241344642413245373537313... |
 ------ ----------------------- ---------------------------------------------------------------------------- 

Because it’s using auth_socket, the root password cannot be changed: the SET PASSWORD command fails, and mysql_secure_installation desn’t attain anything…

==> To zap this alternate authentication mode and return the MySQL root user to using passwords:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'SOME_NEW_ROOT_PASSWORD';

A good explanation.

More details from the MySQL manual.

Mysql :: mysql 5.6 reference manual :: 2.10.4 securing the initial mysql accounts

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

This section describes how to assign passwords to the initial
accounts created during the MySQL installation procedure, if you
have not already done so.

The mysql.user grant table defines the initial
MySQL user accounts and their access privileges:

To display which accounts exist in the
mysql.user system table and check whether their
passwords are empty, use the following statement:

mysql> SELECT User, Host, Password FROM mysql.user;
 ------ -------------------- ---------- 
| User | Host               | Password |
 ------ -------------------- ---------- 
| root | localhost          |          |
| root | myhost.example.com |          |
| root | 127.0.0.1          |          |
| root | ::1                |          |
|      | localhost          |          |
|      | myhost.example.com |          |
 ------ -------------------- ---------- 

This output indicates that there are several
root and anonymous-user accounts, none of which
have passwords. The output might differ on your system, but the
presence of accounts with empty passwords means that your MySQL
installation is unprotected until you do something about it:

In addition, the mysql.db table contains rows
that permit all accounts to access the test
database and other databases with names that start with
test_. This is true even for accounts that
otherwise have no special privileges such as the default anonymous
accounts. This is convenient for testing but inadvisable on
production servers. Administrators who want database access
restricted only to accounts that have permissions granted
explicitly for that purpose should remove these
mysql.db table rows.

The following instructions describe how to set up passwords for
the initial MySQL accounts, first for the root
accounts, then for the anonymous accounts. The instructions also
cover how to remove anonymous accounts, should you prefer not to
permit anonymous access at all, and describe how to remove
permissive access to test databases. Replace
new_password in the examples with the
password that you want to use. Replace
host_name with the name of the server
host. You can determine this name from the output of the preceding
SELECT statement. For the output
shown, host_name is
myhost.example.com.

You need not remove anonymous entries in the
mysql.proxies_priv table, which are used to
support proxy users. See Section 6.2.12, “Proxy Users”.

You might want to defer setting the passwords until later, to
avoid the need to specify them while you perform additional setup
or testing. However, be sure to set them before using your
installation for production purposes.

A root account password can be set several
ways. The following discussion demonstrates three methods:

To assign passwords using SET
PASSWORD
, connect to the server as
root and issue a SET
PASSWORD
statement for each root
account listed in the mysql.user system
table.

For Windows, do this:

$> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('new_password');

The last statement is unnecessary if the
mysql.user table has no
root account with a host value of
%.

For Unix, do this:

$> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('new_password');

You can also use a single statement that assigns a password to
all root accounts by using
UPDATE to modify the
mysql.user table directly. This method works
on any platform:

$> mysql -u root
mysql> UPDATE mysql.user SET Password = PASSWORD('new_password')
    ->     WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

The FLUSH statement causes the
server to re-read the grant tables. Without it, the password
change remains unnoticed by the server until you restart it.

To assign passwords to the root accounts
using mysqladmin, execute the following
commands:

$> mysqladmin -u root password "new_password"
$> mysqladmin -u root -h host_name password "new_password"

Those commands apply both to Windows and to Unix. The double
quotation marks around the password are not always necessary,
but you should use them if the password contains spaces or other
characters that are special to your command interpreter.

The mysqladmin method of setting the
root account passwords does not work for the
'root'@'127.0.0.1' or
'root'@'::1' account. Use the
SET PASSWORD method shown
earlier.

After the root passwords have been set, you
must supply the appropriate password whenever you connect as
root to the server. For example, to shut down
the server with mysqladmin, use this command:

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

The mysql commands in the following
instructions include a -p option based on the
assumption that you have assigned the root
account passwords using the preceding instructions and must
specify that password when connecting to the server.

Похожее:  Подарочные карты и электронные сертификаты

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *