Hello Linux experts.
I post here as i search help from the linux gods.
I (a linux newb) have installed a ubuntu server 18.04 LTS. I installed LAMP-Stack (with this guide) and vsftpd (with this guide). FTP, apache and PHP work perfectly. I have executed mysql_secure_installation But I want to import a .sql file (located in /www/html because I uploaded it via FTP) but I cannot import it because I don’t have the root pw. I wan’t to reset the root pw but when i start the mysql service with # mysqld --skip-grant-tables & or # mysqld_safe --skip-grant-tables & i get the following error:
It appears to be some error with sockets…? Output of # service mysql status:
Is anyone able to help me here? I can provide logs, but please instruct how to get them as I am noob 
.
Greetings,
Ben
It looks like the path doesn’t exist.
Try creating it and restarting the service.
Also, if this is your Ubuntu instance you can do sudo passwd to set the root password after entering your users password.
Thanks for the quick reply. By ‘the path’ do you mean the one cited in the error: /var/run/mysqld? I created the path with # mkdir /var/run/mysqld/ and ran # mysqld_safe --skip-grant-tables & again but it stays at this line:
sudo chown mysql.mysql /var/run/mysqld
If that doesn’t work check the errorlog.
Note he’s talking about the password for the “root” MySQL user, not the OS-level root. That user actually should not have a password unless you deliberately set it.
Now it doens’t go past this line:
That means it’s working, dude. Log-in and change the root password.
Okay, thanks. Now I changed the root pw like this, and now the mysql service doesn’t start.
EDIT: Nevermind it started, but it didn’t change the root password… 
When I try to reproduce the issue, I get this error:
OK, I managed to do it. I simply had to put sudo before logging into mysql and importing the database. I don’t know why I had to do this, as I never before had to, but now it’s working so thanks all very much!!!
1 Like
I dunno, maybe the default Ubuntu Oracle MySQL install uses PAM for OS authentication or something? I use and strongly recommend the Percona MySQL distribution, assuming you’re doing more than playing around.
Thanks for including your solution. It will be helpful to others.
You more than likely had to do this because the OS user that your were logged into does not belong to the mysql group or what ever group was created to manage MySQL. In that case you need root access to the file system where the files are being manipulated. a Simple su to escalate as root before logging into MySql or sudo (as long as as your user is granted permissions) will fix the issue.
I would recommend that you look up and understand Unix permissions and also some best practices on installing, running, and configuring MySQL. Some GNU/Linux distributions do things a little differently than others. Since Oracle’s distro is based on RedHat Enterprise Linux, I would suggest reading up on information from one of those OSes. Canonical is known to do things their own way which may not always jive with the greater GNU/Linux community norms.
Also, here is an obligatory link to the ArchLinux wiki.
https://wiki.archlinux.org/index.php/MySQL
1 Like
He’s running Ubuntu Linux, it’s Oracle’s distro of MySQL.
I get that. The Oracle version may make assumptions that are not true on Ubuntu. That would not be the first time things like this happen. As the OP mentioned that he was a Linux nub, I figured that this would be a great time to point this out as a learning exercise. Especially since he stated that he did not know why he needed to use sudo to log into MySQL to get the files moved over and the root password did not change for the “root” MySQL user.
1 Like
Oh, I see what you mean. I would be very surprised if he wasn’t using the Ubuntu package of Oracle MySQL. Getting a RPM or tarball installed on Ubuntu with systemd integration and everything would be a fair bit of work.
Truer words have never been spoken.
I have been amazed many times by some of my customers that achieved great feats of breakage because they saw a guy’s write-up on the internet. I always cringe when they run unverified scripts as and admin.
I thank you all very much for your helpful responses. I use ubuntu server 18.04 LTS as stated in the beginning of this post. The guide I followed to install all the services are linked there aswell.
I now have another problem. I use this server as a web server and i just imported the website (basic php only, self written) and in the code I of course didn’t use sudo to connect and do commands…
Anyone know how to disable the need to put sudo before every mysql command?
You can setup sudo to work on a timeout period. I use 5 minutes. You can also have it set to only need the password 1 time during the current session.
if you do not want to use sudo, then you will need to change user to root by using su. Note that this will give you god powers and you could break all of the things. If your site is affected by malware, it will have root privileges.
https://wiki.archlinux.org/index.php/Su
https://wiki.archlinux.org/index.php/Sudo
There are a number of ways to directly address the problem, the most direct of which is to alter the special root user to use the mysql_native_password plugin rather than the UNIX socket. But I wouldn’t do any of that. The best answer is to run
GRANT ALL on *.* TO myuser@'%' IDENTIFIED BY 'mypass';
FLUSH PRIVILEGES;
…and then instead of using “mysql” to start it up, type
mysql -umyuser -pmypass
You can alias that to something like mypp in your .profile if you don’t want to type it out every time.
But I personally don’t bother with any of that crap, I just “sudo su - root” when I’m doing admin type stuff and remember to be extra careful.
Hello again and sorry for the late reply, busy weekend.
I tried
But that didn’t do the trick. I still get
Failed to connect to MySQL: (1698) Access denied for user ‘root’@‘localhost’
On my website, because it can’t log into mysql using root because for some reason it requires sudo now… And if somehow possible I would like to avoid making my user su.
My related php code is:
/**
* Diese Funktion versucht sich mit einer Datenbank zu verbinden.
* This function tries to connect with a database.
*
* @param type $host Adresse der Datenbank
* @param type $user Login Benutzer
* @param type $password Login Passwort
* @param type $database Datenbankname
*/
public static function connect($host, $user, $password, $database) {
self::$dbhandle = new mysqli($host, $user, $password, $database);
if (self::$dbhandle->connect_errno) {
die("Failed to connect to MySQL: (" . self::$dbhandle->connect_errno . ") " . self::$dbhandle->connect_error);
}
// Charset UTF-8
if (!mysqli_set_charset(self::$dbhandle, "utf8")) {
printf("Error loading character set utf8: %s\n", mysqli_error(self::$dbhandle));
}
}
Any ideas?
The statement I posted creates a new user. You need to connect as that user, not root.
You also need to put *.* not . after ON. The *.* means your user will have the permission you added ALL on the databases * (meaning all of them) and tables * (meaning again all of the tables from in this case all the databases).
And you forgot the ’ ’ around myuser (witch is the user you´re about to create with this). It´s important to have them around user and your host (% as in any host) You could also put localhost there instead of %, it really probably won´t even make any difference, because by default mysql has a bind-address set to localhost, meaning everyone who is not localhost won´t even get to try to login. So ‘insert_your_desired_user’@‘localhost’ or @’%’.
To check if you did infact create a user you can list users and their hosts with SELECT User,Host FROM mysql.user; in your mysql console and their permissions with SHOW GRANTS FOR 'myuser'@'%';
And if you want to connect from any other machine, other than localhost, you´ll need to put % (or the exact or somewhat exact with only a part of the ip being a wildcard) and also delete the bind-address in your mysql.conf.d or the user won´t even get to ask mysql wether or not his user is valid.
1 Like
