Category Archives: MySQL MariaDB

Only on Linux. Windows is way too messy for MySQL.

Automysql Backup deleting old files

I was looking for an update to Automysqlbackup that would allow me to delete older files and keep down the disk space being used. The daily and weekly directories are rotated in version 2.5 that I have used for the last couple of years, but the monthly are retained in perpetuity.

Sourceforge has version 2.5.1-01 as the latest from 2010 which I grabbed to see what has changed.

While Mark over at his MySQL Fanboy site has also made some updates to Automysqlbackup to add some deletion options. The download link in his post is broken, but you can find his updated Automysqlbackup script in his Files area.

Now, just to be sure of what I was working with I ran up BeyondCompare (the excellent comparison tool that I have used for years) to see what Mark had done to Automysqlbackup and what the new version held.

To start Marks version is based from the July 2010 2.5.1-01 Sourceforge release and he has updated the version numbers as he added features, currently 2.5.3 released Jan 2011. Except in the script proper at line 511 where it is still v2.5.2. I think it could be added as a constant in the settings at the top of the file which would ensure consistency.

There are a few other changes he has made including directory chmod’ing of 700 to the directories that it makes. Again I’d prefer to see this, if it exists, not to be hard-coded but driven by a setting that can be modded at the top of the file like:  PERMISSIONS= ‘0700’  or 0770 as I would prefer.

Now the bit I was interested in was the retention of older files.  The new version from Sourceforge has added monthly rotation settings which are hard coded into 2.5.1-01 as 5 months.  Marks 2.5.3 provides two new constants that allow for the selection of periods other than 5 months or weeks.

In the end Marks changes dont really help me, so I am sticking with the 2.5.1-01 version for the moment. However some of the features he has added may be useful in the future so the time I spent today was not wasted.

PHPMYAdmin on SME Server 8.0 beta 5

Adding an ibay for PHPMyAdmin to manage the SQL Db’s on the server.

The install is fine but an error message in the PHPMyadmin screen tells me that mcrypt is required.

So I found the appropriate page in the contribs Wiki for SME Server but I get a dependency error on libmycrypt.so.4

I located a suitable rpm for CentOS 5 and having installed it I could then complete the process as per the Wiki for SME Server.

In the Wiki page for PHP at the bottom is the section for SME 8.0 beta 5 and you need to insert a new step prior to step 4.

yum localinstall libmcrypt-2.5.8-4.el5.centos.i386.rpm

After this all the rest of the process works as it should.

MySQL root password fails or forgotten

I had setup a new Ubuntu server with MySQL and during the install I was sure I knew the password I set for the mysql root user.

So I then setup webmin and went to access mysqladmin and it failed. I then tried at the command line and it failed.

So I am stuck with a running but inaccessible mysql server.

This thread at Trap17 was the one that helped but it was missing a couple of key points.

I’ll repeat it here in case the link goes away in the future. If you are using Ubuntu without a root password you will need to prefix some of the following with the ‘sudo’ command so that they will run ok.

First stop mysql

/etc/init.d/mysql stop

Then run this command that basically says “run mysql without password control”

mysqld_safe –skip-grant-tables &

With my server this resulted in not returning to the command prompt. Press “Enter” to get the command prompt again.
Then open up mysql for a mysql prompt

mysql -u root -p

Which should result in a mysql> command prompt

From the original post this next command appears redundant on Ubuntu so skip this one. mysql client – mysql -u root

The next command is done at the mysql prompt and again I differ from the original post

use mysql;

Note the semi-colon to complete the command. If you do copy and paste the two lines successfully then skipping the semi-colon on the first line is ok, but I like most people will initially try to do it a line at a time so I think it is clearer to execute as a sequence.

The next command sets up your new password and you need to edit this line with your new password and your relevant user account name.

update user set password=PASSWORD(”NEW-ROOT-PASSWORD”) where User=’root’;

Ok, so now you possibly have an error. Depending on the way you held your lips you may or may not have the correct syntax copied to your command line. I am not sure if this is consistent with all browsers and all telnet clients but I use PuTTy and when I copy/paste the above from the original thread I get this

update user set password=PASSWORD(.NEW-ROOT-PASSWORD.) where User=.root.;

Note that the quote marks in the command were converted to a ‘.’ (period) character and when I hit the enter key I got an error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1

So if you check your syntax and make sure you have the quotes correctly placed then it will work fine.

Finish up by closing the mysql command line with ‘exit’ and stop / start mysql to get it to use passwords again.

/etc/init.d/mysql stop

/etc/init.d/mysql start

And that should get your account running again.