Mysql Mariadb Password problem

Source: Internet
Author: User
Tags log log mysql login

MySQL login password forget, in fact, the solution is very simple, just need to add a line "skip authorization Table" parameter selection in MySQL main configuration file my.cnf!

Add the following line in MY.CNF:
[[email protected] ~]# vim/etc/my.cnf//Add in [mysqld] Area
........
Skip-grant-tables//Skip Authorization Form

Then restart the MySQL service, you can login without password
[Email protected] ~]#/etc/init.d/mysqld restart

Reset Password After login
[[email protected] ~]# MySQL
Mysql> select Host,user,password from Mysql.user;
+--------------------+------+-------------------------------------------+
| Host | user | password |
+--------------------+------+-------------------------------------------+
| localhost | Root | *481aca1bd6d1e86221244904e9c0faba33b40b84 |
| host-192-168-1-117 | Root | |
| 127.0.0.1 | Root | |
| :: 1 | Root | |
| localhost | | |
| host-192-168-1-117 | | |
+--------------------+------+-------------------------------------------+
6 rows in Set (0.00 sec)

mysql> Update Mysql.user Set Password=password ("123456") where host= "localhost" and user= "root";
Query OK, 1 row affected (0.02 sec)
Rows matched:1 changed:1 warnings:0

mysql> flush Privileges;
Query OK, 0 rows affected (0.01 sec)

Mysql> select Host,user,password from Mysql.user;
+--------------------+------+-------------------------------------------+
| Host | user | password |
+--------------------+------+-------------------------------------------+
| localhost | Root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
| host-192-168-1-117 | Root | |
| 127.0.0.1 | Root | |
| :: 1 | Root | |
| localhost | | |
| host-192-168-1-117 | | |
+--------------------+------+-------------------------------------------+
6 rows in Set (0.00 sec)

Mysql>

Comment The line that you added in My.cnf again, and then restart MySQL
[Email protected] ~]# VIM/ETC/MY.CNF
........
#skip-grant-tables

[Email protected] ~]#/etc/init.d/mysqld restart

[Email protected] ~]# mysql-p123456
Mysql>

--------------------------------------------------------------------------------------------------------------- --------
Found a pit:
MySQL before the full amount of backup, after the recovery, found to use the previous password login not in!
Using the above method, no password to login and then reset the password, but after resetting the password found still not in.

The final discovery is because the contents of the Mysql.user table are emptied!
Mysql> select Host,user,password from user;
Empty Set (0.00 sec)

Solve:
Insert data, then reset password
mysql> INSERT INTO User (Host,user,password) VALUES ("localhost", "root", "123456");
Query OK, 1 row affected, 3 warnings (0.01 sec)

Mysql> select Host,user,password from user;
+-----------+------+----------+
| Host | user | password |
+-----------+------+----------+
| localhost | Root | 123456 |
+-----------+------+----------+
1 row in Set (0.00 sec)

mysql> Update Mysql.user Set Password=password ("123456") where host= "localhost" and user= "root";
Query OK, 1 row affected (0.01 sec)
Rows matched:1 changed:1 warnings:0

Mysql> select Host,user,password from user;
+-----------+------+-------------------------------------------+
| Host | user | password |
+-----------+------+-------------------------------------------+
| localhost | Root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
+-----------+------+-------------------------------------------+
1 row in Set (0.00 sec)
mysql> INSERT INTO User (Host,user,password) VALUES ("127.0.0.1", "root", "123456");
Query OK, 1 row affected, 3 warnings (0.00 sec)

Mysql> select Host,user,password from user;
+-----------+------+-------------------------------------------+
| Host | user | password |
+-----------+------+-------------------------------------------+
| localhost | Root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
| 127.0.0.1 | Root | 123456 |
+-----------+------+-------------------------------------------+
2 rows in Set (0.00 sec)

mysql> Update Mysql.user Set Password=password ("123456") where user= "root";
Query OK, 1 row Affected (0.00 sec)
Rows matched:2 changed:1 warnings:0

Mysql> select Host,user,password from user;
+-----------+------+-------------------------------------------+
| Host | user | password |
+-----------+------+-------------------------------------------+
| localhost | Root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
| 127.0.0.1 | Root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
+-----------+------+-------------------------------------------+

Then use the reset password will be able to log in normally!

--------------------------------------------------------------------------------------------------------------- ---
MySQL Login error 1:
[Email protected] ~]# mysql-p123456
ERROR 2002 (HY000): Can ' t connect to local MySQL server through socket '/var/lib/mysql/mysql.sock ' (111)

[[email protected] ~]# ps-ef|grep MySQL
Root 28279 1 0 12:55? 00:00:00/bin/sh/usr/local/mysql//bin/mysqld_safe--datadir=/data/mysql/data--pid-file=/data/mysql/data/ Mysql.pid
MySQL 29059 28279 0 12:55? 00:00:01/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql/--datadir=/data/mysql/data--plugin-dir=/usr/ Local/mysql//lib/plugin--user=mysql--log-error=/data/mysql/data/mysql-error.log--pid-file=/data/mysql/data/ Mysql.pid--socket=/usr/local/mysql/var/mysql.sock--port=3306
Root 30726 11268 0 12:58 pts/2 00:00:00 grep mysql

The current Mysql.sock file path is/usr/local/mysql/var/mysql.sock,

Workaround: Do a soft link
[Email protected] ~]# Ll/usr/local/mysql/var/mysql.sock
Rwxrwxrwx. 1 mysql mysql 0 Nov 12:55/usr/local/mysql/var/mysql.sock
[Email protected] ~]# rm-f/var/lib/mysql/mysql.sock
[Email protected] ~]# ln-s/usr/local/mysql/var/mysql.sock/var/lib/mysql/mysql.sock

That would be fine.
[Email protected] ~]# mysql-p123456
Mysql>

----------------------------------------------------------------------------------------------------
Error when starting MySQL:
Starting MySQL .... error! The server quit without updating PID file (/data/mysql/data/mysql.pid).

Try the solution:
(1) Permissions issues
May be the Mysql.pid file does not have permission to write, the MySQL installation directory and data directory permissions are set to the MySQL start user rights. Change to Mysql:mysql permissions, for example.
(2) The MySQL process may already exist in the process
Ps-ef|grep MySQL Find out if a MySQL process exists, kill it, and then try restarting MySQL
(3) MySQL may be installed on the machine multiple times, with residual data affecting the startup of the service.
Go to MySQL data directory to see, if there is mysql-bin.index, immediately delete it, it is the culprit!
(4) MySQL will use the/ETC/MY.CNF profile when it is not specified at startup, please open this file to see if there is a specified data directory under the [Mysqld] section.
Add settings under [Mysqld], such as DataDir =/data/mysql/data
(5) skip-federated field problem
Check to see if there are any skip-federated fields in the my.cnf file that are not commented out, and comment them out if any.
(6) Error log directory does not exist
Go to my.cnf file whether there is a log log configuration path, if any, to see if the log directory exists, log directory permissions to ensure that the MySQL start user rights.
(7) The disaster of SELinux, if it is a CentOS system, the default will open SELinux
Close it, open the/etc/selinux/config, change the selinux=enforcing to selinux=disabled after save to restart the machine to try.
(8) Re-initialization of MySQL data test
Switch to MySQL's installation directory
./scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/data/mysql/data--user=mysql

-----------------------------------------------------------
Log in to MySQL using MySQL server-side licensing information, with the following error:
ERROR 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.1.14 ' (111)

The possible causes are:
1) Possible network connection problem, remote ping 192.168.1.14, can ping pass, exclude this condition;
2) MySQL server 192.168.1.14 iptables to make a whitelist limit of 3306 port connections;
3) bind_address address bindings are configured in the my.cnf file of the MySQL server 192.168.1.14, and the native connection is not allowed;
4) MySQL server 192.168.1.14 my.cnf file configured skip_networking, which use MySQL only through the native socket connection (socket connection is also the default way of local connection), to abandon the TCP/IP monitoring;
5) Troubleshoot the DNS resolution problem, check the MySQL server 192.168.1.14 my.cnf file is set skip_name_resolve. After this parameter is added, the host name connection is not supported.
6) Troubleshooting The--port problem, it is possible that MySQL server 192.168.1.14 MySQL port is not the default 3306, such as 3307 port, so that when the remote connection to add--port=3307
7) Troubleshoot user and password issues, in fact, the user and password errors, will not appear 111, so the user password issues
ERROR 1045 (28000): Access denied for user ' root ' @ ' XXXX ' (using Password:yes)

Mysql Mariadb Password problem

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.