Archived Forum

Hi Ian,

Have you verified that MySQL is running?

The command: sudo service mysql status will tell you.

Hi Bill

pi@StudioPiServer ~ $ sudo service mysql status
[info] MySQL is stopped..
pi@StudioPiServer ~ $

So mySQL is stopped

I tried to start it and just got the following.

pi@StudioPiServer ~ $  sudo service mysql start
[FAIL] Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed                !

I have found a mysql log file but it has no entries!

Any help appreciated.

Regards

Ian

What OS are you running?

Do you get any other error text on the screen when you try to start mysql manually?

Hi Bill

Running on a Pi wheezy. No other error messages.

I found a StudioPiServer.err file with these lines:-

151104 11:42:36  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1

​I am guessing I have messed up file permissions some how but I don't know how.

Question is how to restore them. I know the mysql password for emoncms but cannot remember the mysql root password I used. I wrote it down originally but that was 2 years ago!

Regards

Ian

I know it's a case of stable door and all that - I keep all my passwords in a password safe (KeePassX in my case).

How to reset it: http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

Here's the steps to reset the root password:

Since your MySQL server is not running, start with step 2.

1. /etc/init.d/mysql stop
2. mysqld_safe --skip-grant-tables &
3. mysql -u root
4. use mysql;
5. update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
6. flush privileges;
7. quit

Done, reboot.

Hi

Still struggling with this. Password reset did not work. I am certain the issue is related to file ownership. I have been reading about ownership and groups and the fog is beginning to clear. For emoncms who should own the mysql files and which group should they be in?

I have found the emoncms mysql and data files and backed them up onto a PC on the network.

Can you install a new emoncms and then overwrite the default files with the backup files therefore effectively cloning the old setup? The Pi I am using has been a learning tool for me and has had many applications installed over the years. I am thinking a clean install of wheezy plus the latest emoncms version may not be a bad idea.

Robert

KeyPass installed and all critical internet passwords changed. Thanks for the tip.

Regards

Ian

I think mySQL will need to be running before you can reset the password etc, 

Is there enough room for mySQL to function following the updates? The most common cause of any mySQL issues on a Pi is lack of space, on a stock Raspbian that's usually due to the filesystem size being fully used unless running a A or orig 256mb B then it could be RAM.  

Is the filesystem read-only? If so the disc space is less important as disc-swapping isn't used and the in-memory stuff is held in RAM, so the RAM is very easily maxed out and only the 1Gb Pi will definitely handle a full emoncms. mysql, mqtt, apache server build with plenty to spare, a 512mb Pi will also manage it even if some jiggery pokery is needed to free up RAM.

does either "df -h" or "free" reveal anything? 

Is there anything enlightening in the syslog? in particular for mysqld at boot up.

Also if read-only have you tried using "rpi-rw" before manually starting the mysqld service in case there is something it needs to write following the updated and reboot?

Paul

Hi Paul

Hard disk so space should not be a problem.

pi@StudioPiServer ~ $ df -h
df: `/squashfs': No such file or directory
Filesystem      Size  Used Avail Use% Mounted on
rootfs          459G  3.6G  432G   1% /
dev             115M     0  115M   0% /dev
/dev/sda1       459G  3.6G  432G   1% /mnt
none            459G  3.6G  432G   1% /
tmpfs            47M  236K   47M   1% /run
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs            93M     0   93M   0% /run/shm
pi@StudioPiServer ~ $

The bit in syslog that makes me think file ownership is the problem is:-

Nov  4 10:31:55 StudioPiServer mysqld: InnoDB: The error means mysqld does not have the access rights to
Nov  4 10:31:55 StudioPiServer mysqld: InnoDB: the directory.
Nov  4 10:31:55 StudioPiServer mysqld: InnoDB: File name ./ibdata1
Nov  4 10:31:55 StudioPiServer mysqld: InnoDB: File operation call: 'open'.
Nov  4 10:31:55 StudioPiServer mysqld: InnoDB: Cannot continue operation.

Regards

Ian

Can you install a new emoncms and then overwrite the default files with the backup files therefore effectively cloning the old setup?

I've just done exactly that on a fresh OS on another Pi. 

The MYSQL is perhaps a little tricky, but all I did was follow the emoncms installation instructions, including creating the emoncms mysql database, and once the new system is installed and everything is running OK, stop emonhub & open up your db in phpmyadmin using your new mysql 'root' password.

In phpmyadmin delete the existing emoncms database and create a new database called 'emoncms' then import your old mysql backup into it.(presumably it is a .sql backup file?)

The data folders are straight forward, just copy them to the default locations /var/lib (deleting the existing ones first) and then set their permissions

sudo chown -R www-data:root /var/lib/{phpfiwa,phpfina,phptimeseries}

(notice the -R in above, as you need to change the permissions of the folder contents too)

It probably reads more complicated than it really is!

Don't forget to check your db settings in settings.php, and also your write API key within emonhub.conf
There will be other things to do, especially if your changing from v8.5 to v9.x, but those are the main steps.

Paul

Hi

Before I did anything else I removed and purged mysql and then reinstalled. I am pleased to say every thing seems to be back to normal. I have also learnt a fair bit more about mysql!

Thanks for all the help and advice.

Regards

Ian

Ian, just reading back through this thread, how are you backing up your mysql database? are you just copying the mysql directories from your SD card?

If so, you may find MYSQL restoration more effective if you export your database as a backup, which you can then easily import if things go wrong.

I use a small script to backup my database (attached).
To use it;

• Create the backup directory $ mkdir ~/mysql_backup
• Rename the attached script to mysql_backup.sh & add your database password
• copy the script to ~/msql_backup/
• Make it executable - $ chmod +x ~/mysql_backup/mysql_backup.sh
• Run the file manually or by using Cron; $ ~/mysql_backup/./mysql_backup_emoncms.sh

..and it will backup your emoncms database to ~/mysql_backup/
You could change the script/backup locations to whatever you wish, maybe a removable memory stick for safe storage.

Paul

Hi Paul

Thanks very much.

I actually use tarsnap for backup but I now realise dumping the file first would be a good idea.

I tried your script and got the following error:-

~/mysql_backup/./mysql_backup.sh
-bash: /home/pi/mysql_backup/./mysql_backup.sh: /bin/sh^M: bad interpreter: No such file or directory
pi@StudioPiServer ~ $

Also tried the command direct and also got an error:-

 mysqldump -u emoncms -pxxxxxxxxxx emoncms >  /home/pi/mysql_backup/emoncms_backup.sql
mysqldump: Got error: 1045: Access denied for user 'emoncms'@'localhost' (using password: YES) when trying to connect
pi@StudioPiServer ~ $

Regards

Ian

Hi Ian,

-bash: /home/pi/mysql_backup/./mysql_backup.sh: /bin/sh^M: bad interpreter: No such file or directory

To fix the first error, change the sh in Paul's script to bash  i.e. change it to #!/bin/bash

1045: Access denied for user 'emoncms'@'localhost' (using password: YES) when trying to

Regarding the second error, be sure you're using the MySQL root user's pasword.

To run the mysqldump command manually, as the MySQL root user, your command line should look like this:

mysqldump -u root -pyour_mysql_root_pw  >  /home/pi/mysql_backup/emoncms_backup.sql

Note: no space between -p and your password. e.g. -pMySQL

Hi Bill

Thanks. Now working. I had already fixed the second problem by using the password for the emoncms database.

Regards

Ian

Sorry Ian, just realised that I'd made a mistake when hastily posting the script above. Before posting it, I added a comment in the script to remind users to add their password to the script. However I preceded the comment with '//' instead of '#' to denote a comment, stopping the script from running!!!

I've just attached the amended script above which should work as is, I've just tried it on another Pi and it now works fine, running through sh and not bash.

It's been running on my Pi for the past 6 months and backs up my database daily at 3am.

Paul