Restarting Mysql Databases

Home » CentOS » Restarting Mysql Databases
CentOS 16 Comments

I am doing an install of a mysql system, and decided that I needed to change some things like host name. So I have to go back to square one with the mysql setup, but can’t find any instructions for this. I TRIED:

mysql_install_db

And it did SOMETHING, but

mysqladmin -u root password ‘mypass’

failed with:

mysqladmin: connect to server at ‘localhost’ failed error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’

I tried ‘yum reinstall mysql’ and that did not help.

16 thoughts on - Restarting Mysql Databases

  • I don’t know if I need the flush-hosts command or what. The howto I use starts off with:

    mysqladmin -u root password ‘mypass’
    mysqladmin -u root -h foo.bar.com password ‘myhostpass’

    So I am changing foo.bar.com to bar.foo.com. So when I try:

    mysqladmin -u root -h bar.foo.com password ‘myhostpass’

    I get

    mysqladmin: connect to server at ‘bar.foo.com’ failed error: ‘Host ‘bar.foo.com’ is not allowed to connect to this MySQL server’

  • Some apps reinit there data on reinstalls. Some don’t. Don’t know until you try.

    Don’t know what changing the hostname did or does not do. I am digging into various info to figure out what to delete or rebuild to get back to pristine mysql. I can understand why this should not be easy – for someone to remove all of your mysql data, but it should be possible…

  • What I thought. I guess just shooting in the dark.

    ls /var/lib/mysqld/*
    ls: cannot access /var/lib/mysqld/*: No such file or directory

    did you mean /var/lib/mysql/* ?

    But that seems risky as there is /var/lib/mysql/mysql that has lots of interesting looking files. But there is an empty directy
    /var/lib/mysql/foo and /var/lib/mysql/mydb with the file db.opt

    I MIGHT have gotten to this in a couple of days or reading. Thanks for the pointer.

  • Please read the above carefully: using password: NO
    Specifying the password requires the -p or –password option. Just specify -p on the command line and let mysqladmin ask for the password…

  • Did you do the /usr/bin/mysql_secure_install

    Sounds like that is the issue, run mysql_secure_install

    It will setup MySQL securely with a simple set of questions and answers.. Yes is the right answer for most of the questions.

    Here is a link to LAMP Stack for CentOS, the MySQL section is just below the Apache install section..

    https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-on-CentOS-6

    Also good for CentOS 7

    john

  • Looks like the files:

    /var/lib/mysql/mysql/user.MYD
    /var/lib/mysql/mysql/user.MYI
    /var/lib/mysql/mysql/user.frm

    have the goo that has to go.

  • When I try ‘mysqladmin -u root -p’ I get the help dump.

    Going to try the secure install, or delete those three files I found.

  • Not part of my installation. I am running RedSleeve 6, which is the ARM
    buid of CentOS6 and a minimum installation. So sometimes I find I have to go back and add something else…

    And eventually there will be CentOS-arm 7 that I can use.

  • its mysql_secure_installation and its part of the base package…

    # rpm -qf `which mysql_secure_installation`
    mysql-server-5.1.73-3.el6_5.x86_64

  • Oct 02 15:32:20 Installed: mysql-server-5.1.71-1.el6.armv5tel

    I guess I either mistyped it or copied it wrong. I got the prompt this time. So off to the races.

    Using a ‘Howto’ gets things working to some extent faster, but there are things often not ‘right’ and when they go bump, well you see where I am right now.

  • yeah, note its mysql_secure_installation, not _install as the other John P suggested. I typed mysql_secu :)

  • See, that’s what happens when you get a phone call in the middle of trying to help someone. At least we got the ball going in the right direction.

    You might load webmin onto the machine, it is a web interface and can make much of the MySQL control easier. It has a host of modules that may come in handy as you configure your machine.

    http://www.webmin.com your CentOS load should support it. Are you adding Apache, PHP etc..? If so the link I gave you will help with the installations.

    john

  • I am trying to move away from webmin dependency.

    Redsleeve was built for armv5 which did not have selinux support, or some such according to the developers. I am running on armv7 which my Fedora 19 – 21 has selinux support, but with RSEL6 I am running a bit rough (thus I really want CentOS-arm-7). So I am trying to go back to stuff I did long ago (like running named in chroot) and avoid obvious attack routes like webmin.

    arms are fun. Like 2 watts compared to intelboxen at 70w…

  • Why not change hostname back to what it was previously, then change the hostname associated with the root user in the user database?

    I’m thinking this is rather simple and I’m worried I’m overlooking something by suggesting this after having read the entire thread…

    Cheers, ak.

  • What you want to do it seems is change the hostname for access of some users. Although, if it is local access, they should have access to localhost and it doesn’t matter which hostname the machine has. Also, initially, you have to set the root password.

    Almost everyone uses PHPMyAdmin to administer MySQL in this area (apart from the root password). Either by editing the mysql.* databases directly
    (which I wouldn’t recommend if you have no experience) or in the user administration. After that you have to flush the credentials or simply restart MySQL.

    As John and others said, the hostname of the machine doesn’t matter. It’s not necessary to reinstall MySQL as this won’t “fix” it, anyway.

    Kai