MariaDB MariaDB Regenerate Password

Hello,

I have been trying to access my MariaDB root account and for some reason cannot access it for the life of me either because I took down the wrong password or something is wrong. Anyways, I used Option 11 > 0 to generate a new root password and it's still not working and gives off the below error:

Code (Text):

Process begins in 10 seconds...

Previous MySQL root password:

[client]
user=root
password=D8I8HzdkAHdwCAbT

mysqladmin -u root -pXXXXXXXX password XXXXXXXX
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

New MySQL root user password: XXXXXXXX

/root/.my.cnf updated

[client]
user=root
password=XXXXXXXX

When attempting to login with the new password, I still have no luck.

Any idea what could be going on?

---

Thanks!

 

I get to the final step of starting up MySQL and then it hangs on:

Code (Text):

[root@nexus ~]# sudo /etc/init.d/mysql stop
Stopping mysql (via systemctl):                            [  OK  ]
[root@nexus ~]# sudo /etc/init.d/mysql start
Starting mysql (via systemctl):

Then comes this error:

Code (Text):

Starting mysql (via systemctl):  Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
                                                           [FAILED]

 

http://pastebin.com/raw/De58GT5c

 

This is what it looks like and still errors out:

Code (Text):

[root@nexus ~]# ps aufx | grep mysql
root      5448  0.0  0.0 193352  2720 pts/0    S    08:49   0:00          \_ sudo mysqld_safe --skip-grant-tables
root      5449  0.0  0.0 113264  1644 pts/0    S    08:49   0:00          |   \_ /bin/sh /bin/mysqld_safe --skip-grant-tables
mysql     5801  0.0  2.6 2095408 210544 pts/0  Sl   08:49   0:00          |       \_ /usr/sbin/mysqd --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mysqld.log --open-files-limit=8192 --pid-file=nexus.bobbaforums.pro.pid --socket=/var/lib/mysql/mysql.sock
root      6327  0.0  0.0 112704   920 pts/0    S+   09:10   0:00          \_ grep --color=auto mysq
[root@nexus ~]# kill -9 5801
[root@nexus ~]# ps aufx | grep mysql
root      5448  0.0  0.0 193352  2720 pts/0    S    08:49   0:00          \_ sudo mysqld_safe --skip-grant-tables
root      5449  0.0  0.0 113264  1660 pts/0    S    08:49   0:00          |   \_ /bin/sh /bin/mysqld_safe --skip-grant-tables
mysql     6342  4.0  2.5 2103304 205212 pts/0  Sl   09:11   0:00          |       \_ /usr/sbin/mysqd --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mysqld.log --open-files-limit=8192 --pid-file=nexus.bobbaforums.pro.pid --socket=/var/lib/mysql/mysql.sock
root      6376  0.0  0.0 112704   924 pts/0    S+   09:11   0:00          \_ grep --color=auto mysq
[root@nexus ~]# sudo /etc/init.d/mysql start

Any time I kill the process and re-run the command, there appears to be a new process for mysql.

 

Yes.

Code (Text):

[root@nexus ~]# mysqlstop
Stopping mysql (via systemctl):                            [  OK  ]
[root@nexus ~]# ps aufx | grep mysql
root      5448  0.0  0.0 193352  2720 pts/0    S    08:49   0:00  |       \_ sudo mysqld_safe --skip-grant-tables
root      5449  0.0  0.0 113264  1660 pts/0    S    08:49   0:00  |           \_ /bin/sh /bin/mysqld_safe --skip-grant-tables
mysql     6925  0.1  2.6 2103304 209312 pts/0  Sl   09:15   0:00  |               \_ /usr/sbin/mysqd --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mysqld.log --open-files-limit=8192 --pid-file=nexus.bobbaforums.pro.pid --socket=/var/lib/mysql/mysql.sock
root      6997  0.0  0.0 112704   924 pts/1    S+   09:18   0:00          \_ grep --color=auto mysq
[root@nexus ~]# kill -9 6925
[root@nexus ~]# ps aufx | grep mysql
root      5448  0.0  0.0 193352  2720 pts/0    S    08:49   0:00  |       \_ sudo mysqld_safe --skip-grant-tables
root      5449  0.0  0.0 113264  1660 pts/0    S    08:49   0:00  |           \_ /bin/sh /bin/mysqld_safe --skip-grant-tables
mysql     7013  9.6  2.5 2103304 205216 pts/0  Sl   09:18   0:00  |               \_ /usr/sbin/mysqd --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mysqld.log --open-files-limit=8192 --pid-file=nexus.bobbaforums.pro.pid --socket=/var/lib/mysql/mysql.sock
root      7045  0.0  0.0 112704   920 pts/1    S+   09:18   0:00          \_ grep --color=auto mysq
[root@nexus ~]# 

 

Fixed the issue but now have when entering the new password:

Code (Text):

ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'root'@'localhost' has a password in the old format; please change the password to the new format

 

Code (Text):

mysqlstop

sudo mysqld_safe --skip-grant-tables &

mysql -uroot

use mysql;

update user set password=PASSWORD("mynewpassword") where User='root';

 flush privileges;

 quit

mysqlstart

Code (Text):

[client]
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 64M

[mysqld]
[client]
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 64M

[mysqld]
ignore_db_dirs=cmsetiofiotest
local-infile=0
ignore_db_dirs=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# vbtechsupport.com

tmpdir=/home/mysqltmp

innodb=ON
#skip-federated
##skip-pbxt
##skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 512
max_connections = 500
key_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 256K
table_definition_cache = 8192
table_open_cache = 4096
thread_cache_size = 256
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 64M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
# query_cache boost for MariaDB >10.1.2+
# https://community.centminmod.com/posts/30811/
query_cache_limit = 1024K
query_cache_size = 80M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
log-error=/var/log/mysqld.log

# innodb settings
innodb_large_prefix=1
innodb_purge_threads = 4
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 512M

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
innodb_buffer_pool_instances=1

innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
#innodb_thread_concurrency = 20
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 800
innodb_io_capacity_max = 1600
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_flush_neighbors = 0

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 768M
aria_log_purge_type = immediate 
aria_pagecache_buffer_size = 768M
aria_sort_buffer_size = 192M

[mariadb-5.5]
innodb_file_format = Barracuda
innodb_file_per_table = 1

#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe] 
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 64M

[myisamchk] 
tmpdir=/home/mysqltmp
key_buffer = 256M 
sort_buffer = 32M
read_buffer = 32M
write_buffer = 32M

[mysqlhotcopy]
interactive-timeout

[mariadb-10.0]
innodb_file_format = Barracuda
innodb_file_per_table = 1

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
#innodb_buffer_pool_dump_at_shutdown=1
#innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mariadb-10.1]
innodb_file_format = Barracuda
innodb_file_per_table = 1

## wsrep specific
# wsrep_on=OFF
# wsrep_provider
# wsrep_cluster_address
# binlog_format=ROW
# default_storage_engine=InnoDB
# innodb_autoinc_lock_mode=2
# innodb_doublewrite=1
# query_cache_size=0

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
#innodb_buffer_pool_dump_at_shutdown=1
#innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

# Defragmenting unused space on InnoDB tablespace
innodb_defragment=1
innodb_defragment_n_pages=7
innodb_defragment_stats_accuracy=0
innodb_defragment_fill_factor_n_recs=20
innodb_defragment_fill_factor=0.9
innodb_defragment_frequency=40

 

Still no luck. It doesn't want to start when using mysqlstart but will restart just fine.

 

They work perfectly fine as standalone commands but the minute I finalize the process from Recovering a lost mysql root password | Centmin Mod Community to start the MySQL server using systemctl start mariadb, it hangs.

 

But my question is, when I go through those steps, am I killing the processes before I do the final step of starting the MySQL server or what? I think that's where I may be confused.