Solved Mysql can't change password

C

cesjr

Hi
I have install mysql57-server on my freebsd 10.3 , and I move all of the old /old_db_dir to the new one, all of the privileaged have been changed chown –R mysq:mysql /new_db_dir.

Starting mysql with no error:
/usr/local/etc/rc.d/mysql-server start
Code: 
Starting mysql.

/usr/local/etc/rc.d/mysql-server status
Code: 
mysql is running as pid 4894.

ls -l /tmp/mysql.sock
Code: 
srwxrwxrwx  1 mysql  wheel  0  2月 14 11:52 /tmp/mysql.sock

sockstat -4l | grep mysql
Code: 
mysql    mysqld     4894  15 tcp4   127.0.0.1:3306        *:*

But when I changed mysql root's password , it fails :
mysqladmin -u root password 'mypassword'
Code: 
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

I can still login with old password , but the login prompt looks weired
mysql -u root -p
Code: 
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log Source distribution

Copyright (c) 2000, 2016, 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.

root@localhost [(none)]> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| isep2               |
| #mysql50#isep2.copy |
| mysql               |
| performance_schema  |
| roundcube           |
| test                |
+---------------------+
7 rows in set (0.04 sec)

root@localhost [(none)]>
Is the standard process for changed mysql password changed ?
 
Look at the mysqladmin error: it says that you didn't supply a password.

Nothing changed here: surely you don't expect your MySQL server to allow everyone to change the root password without giving the current password as a means of authentication?

Look into mysqladmin(1), you'll need to specify usage of a password or the current password itself. Same as if you'd run mysql itself.
 
Have you tried doing it as described in the docs ? They recommend writing a SQL statement and feeding to MySQL upon starting it, as far as I can tell.
 
Regardless of how you do it, you have to supply the current password to login in order to be able to change it.
 
With the Linux version, MySQL 5.7 creates a temporary root password when you install it and it is located in the log file. You can find out what the password is by:

grep 'temporary password' /var/log/mysqld.log

This may also be true with the FreeBSD version. MySQL 5.6 does not do this.
 
Thanks all your suggestion .
I using mysqladmin -u root -p 'old_password' password 'new_password' to change it !
Finally , I decided to reinstall a new mysql-server , and dump all databases to a new location.
But ,when I finished the installation , something weired...
Code: 
#MySQL
mysql_dbdir="/backup/JOY/mysql"
mysql_enable="YES"
/usr/local/etc/rc.d/mysql-server start
Code: 
Starting mysql.
/usr/local/etc/rc.d/mysql-server status
Code: 
mysql is not running.

Of course , I can't setup root password
mysqladmin -u root password 'your_password'
Code: 
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!
/var/log/message shows nothing.
Here is error message in my db_dir .
Code: 
2017-02-16T02:35:06.6NZ mysqld_safe Starting mysqld daemon with databases from /backup/JOY/mysql
2017-02-16T02:35:06.485410Z 0 [Note] /usr/local/libexec/mysqld (mysqld 5.7.17-log) starting as process 81879 ...
2017-02-16T02:35:06.529485Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-02-16T02:35:06.529509Z 0 [Note] InnoDB: Uses event mutexes
2017-02-16T02:35:06.529516Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-02-16T02:35:06.529528Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-02-16T02:35:06.529751Z 0 [Note] InnoDB: Number of pools: 1
2017-02-16T02:35:06.529837Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-02-16T02:35:06.530888Z 0 [Note] InnoDB: Initializing buffer pool, total size = 1G, instances = 8, chunk size = 128M
2017-02-16T02:35:06.624089Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-02-16T02:35:06.685380Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-02-16T02:35:06.766994Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2017-02-16T02:35:06.767026Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2017-02-16T02:35:06.767035Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2017-02-16T02:35:06.767043Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/engine_cost.ibd' OS error: 71
2017-02-16T02:35:06.767057Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2017-02-16T02:35:06.767064Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2017-02-16T02:35:06.767070Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2017-02-16T02:35:06.767078Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `mysql/engine_cost`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2017-02-16T02:35:06.767091Z 0 [Warning] InnoDB: Ignoring tablespace `mysql/engine_cost` because it could not be opened.
2017-02-16T02:35:06.767119Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2017-02-16T02:35:06.767127Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2017-02-16T02:35:06.767134Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2017-02-16T02:35:06.767144Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/gtid_executed.ibd' OS error: 71
2017-02-16T02:35:06.767151Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2017-02-16T02:35:06.767157Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2017-02-16T02:35:06.767164Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2017-02-16T02:35:06.767174Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `mysql/gtid_executed`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2017-02-16T02:35:06.767181Z 0 [Warning] InnoDB: Ignoring tablespace `mysql/gtid_executed` because it could not be opened.
....
....
All of that about of the InnoDB error.
Finally the mysql shoutdown ... why?
Code: 
2017-02-16T02:35:08.129311Z 0 [Note] Binlog end
2017-02-16T02:35:08.139429Z 0 [Note] Shutting down plugin 'ngram'
2017-02-16T02:35:08.139439Z 0 [Note] Shutting down plugin 'partition'
2017-02-16T02:35:08.139445Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2017-02-16T02:35:08.139451Z 0 [Note] Shutting down plugin 'ARCHIVE'
2017-02-16T02:35:08.139456Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2017-02-16T02:35:08.139503Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2017-02-16T02:35:08.139510Z 0 [Note] Shutting down plugin 'MyISAM'
2017-02-16T02:35:08.139519Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2017-02-16T02:35:08.139525Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2017-02-16T02:35:08.139531Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2017-02-16T02:35:08.139536Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2017-02-16T02:35:08.139546Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2017-02-16T02:35:08.139551Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2017-02-16T02:35:08.139557Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2017-02-16T02:35:08.139562Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2017-02-16T02:35:08.139567Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2017-02-16T02:35:08.139573Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2017-02-16T02:35:08.139578Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2017-02-16T02:35:08.139587Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2017-02-16T02:35:08.139592Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2017-02-16T02:35:08.139598Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2017-02-16T02:35:08.139603Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2017-02-16T02:35:08.139609Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2017-02-16T02:35:08.139614Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2017-02-16T02:35:08.139624Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2017-02-16T02:35:08.139629Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2017-02-16T02:35:08.139635Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2017-02-16T02:35:08.139640Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2017-02-16T02:35:08.139646Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2017-02-16T02:35:08.139651Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2017-02-16T02:35:08.139660Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2017-02-16T02:35:08.139666Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2017-02-16T02:35:08.139671Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2017-02-16T02:35:08.139677Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2017-02-16T02:35:08.139682Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2017-02-16T02:35:08.139688Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2017-02-16T02:35:08.139693Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2017-02-16T02:35:08.139702Z 0 [Note] Shutting down plugin 'InnoDB'
2017-02-16T02:35:08.139735Z 0 [Note] InnoDB: FTS optimize thread exiting.
2017-02-16T02:35:08.139768Z 0 [Note] InnoDB: Starting shutdown...
2017-02-16T02:35:08.246657Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/db/mysql/ib_buffer_pool
2017-02-16T02:35:08.246754Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 170216 10:35:08
2017-02-16T02:35:09.662449Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2539442
2017-02-16T02:35:09.662760Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-02-16T02:35:09.662780Z 0 [Note] Shutting down plugin 'MEMORY'
2017-02-16T02:35:09.662788Z 0 [Note] Shutting down plugin 'CSV'
2017-02-16T02:35:09.662795Z 0 [Note] Shutting down plugin 'sha256_password'
2017-02-16T02:35:09.662800Z 0 [Note] Shutting down plugin 'mysql_native_password'
2017-02-16T02:35:09.662915Z 0 [Note] Shutting down plugin 'binlog'
2017-02-16T02:35:09.676749Z 0 [Note] /usr/local/libexec/mysqld: Shutdown complete

2017-02-16T02:35:09.6NZ mysqld_safe mysqld from pid file /backup/JOY/mysql/mydomain.com.tw.pid ended
 
Yeah, the error is pretty self-explanatory:
Code: 
2017-02-16T02:35:06.6NZ mysqld_safe Starting mysqld daemon with databases from /backup/JOY/mysql
{snip}
2017-02-16T02:35:06.766994Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2017-02-16T02:35:06.767026Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.

The /backup/JOY/mysql/ directory either doesn't exist or the mysql account doesn't have access to it.
 
Yes ,the path /backup/JOY/mysql exist . I using chown -R mysql:mysql/backup/JOY/mysql to give it is accessible.
I found the problem , because I copy my.cnf(version 5.6) from old machine to /var/db/mysql/ , and may have conflict with /usr/local/etc/mysql/my.cnf .
I did not know the new version will be automatically create , in version 5.6 , there is no my.cnf in /usr/local/etc/ , so I copy a sample to it .

Below is solution of my problem:
1. rm -rf /backup/JOY/mysql
2. mkdir -p /backup/JOY/mysql && chown -R mysql:mysql /backup/JOY/mysql
3. reinstall mysql57-server
4. vi /etc/rc.conf
Code: 
#MySQL
mysql_dbdir="/backup/JOY/mysql"
mysql_enable="YES"
5. /usr/local/etc/rc.d/mysql-server start
It will be start , and create my.cnf automatically.
But it will never success with mysqladmin -u root password 'yourpassword' .
It will say :
Code: 
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
Actually, there is a default random passwod in /root/.mysql_secret .
I found it after execute mysql_secure_installation ,it will tell you where the password file .
Using mysqladmin -u root password -p
Code: 
Enter Password : 'random password'
Enter New Password : 'your passwod'
Congratulation ! , now you can enjoy your new mysql-server.
 
If you look at the MySQL rc(8) script:
Code: 
: ${mysql_dbdir="/var/db/mysql"}
: ${mysql_confdir="/usr/local/etc"}
if [ -f "${mysql_dbdir}/my.cnf" ]; then
: ${mysql_optfile="${mysql_dbdir}/my.cnf"}
else
: ${mysql_optfile="${mysql_confdir}/my.cnf"}
fi

By default it will look for /var/db/mysql/my.cnf first, then try /usr/local/etc/my.cnf. But you've changed dbdir so in your case it looks for /backup/JOY/mysql/my.cnf first.

I did not know the new version will be automatically create , in version 5.6 , there is no my.cnf in /usr/local/etc/ , so I copy a sample to it .
Click to expand...
You don't require a my.cnf. Don't just copy a sample file, use the default settings (so no my.cnf), leave it running for a couple of days then tweak certain settings like caches and memory usage according to its usage. It's extremely easy to just use a bunch settings taken from the internet but chances are those settings will not apply to you and will actually cause performance to decrease instead of increase.
 
SirDice said:
The /backup/JOY/mysql/ directory either doesn't exist or the mysql account doesn't have access to it.
Click to expand...
Sorry too late to see your reply ,this is a good idea to solve the problem.

SirDice said:
But you've changed dbdir so in your case it looks for /backup/JOY/mysql/my.cnf first.
Click to expand...
Yeah , I have changed .. but I put it in /var/db/mysql/ .. a big mistake.

Ok, thanks your suggestion , I will use the default settings , and leave it running for a couple of days.
 
You must log in or register to reply here.
Back
Top