Pixelastic

You can clip our wings but we will always remember what it was like to fly.

Posts tagged with "MySQL"

Increasing VirtualBox hard drive size

While trying to import a huge (1.7Go) mysql dump file into my VM I was blocked by mysql telling me that not enough space was left on the device to complete the operation.

As I was running a VM, I thought it would be a simple matter of increasing the virtual hard drive size.

Turns out it wasn't that simple. After much trial and error, here is how I finally did it.

Creating a new hard drive

VirtualBox let you easily add new devices, such as hard drives to your VM. I simply created an empty 120Go hard drive and booted my VM.

Here, under Ubuntu, I cloned my current hard drive to the new one using :

sudo dd if=/dev/sda of=/dev/sdb

Fixing the guest partition

Once finished, I opened Gparted, selected /dev/sdb and saw that I had 112Go unallocated. I couldn't easily add them to the initial partition as a swap partition was in the way.

I finally decided to remove the swap partition and resize the initial one to the (almost) complete size of the hard drive.

I left 1Go free in case I ever needed to create a new swap partition later to fix the one I deleted.

Then, I closed the VM. Get back to VirtualBox panel and remove the original drive, keeping only the new 120Go one.

One reboot later, my Ubuntu was proudly displaying its 120Go.

Creating a readonly mysql user

I wanted to give access to a database to a colleague, so he can connect and extract some useful stats from our database.

As I didn't want to give him nor the root, nor my access nor even an access with writing permission to avoid potential issues, I created a readonly user.

First, connect to the mysql server :

mysql --user=root -p

And type your password when asked

Now that you are in the mysql prompt, create the 'readonly' user and give him SELECT permission on all tables

CREATE USER 'readonly'@'%' IDENTIFIED BY 'your_password';
GRANT SELECT ON *.* TO 'readonly'@'%';

Also note that once connected with root, you can see the list of users by running :

SELECT host,user,pass FROM mysql.user;

Nothing fancy here, everything was found after a few minutes of googling.

Also, if you ever need to delete the user :

DROP USER 'readonly'@'%';

 

Incorrect MySQL date

Several ingame time calculation we made in game are based on Paris time. Some weeks ago, we decided to make a pass on all our servers to always use Paris Time (GMT +1).

Today, I spotted that logs we save in the DB have some date inaccuracy. It appears that our mysql server and instances weren't always updated to the correct date. Some hours later, here is what I learned :

Finding and updating MySQL date

You can tell what timezone mysql should use when you start the service. If you don't specify anything, it will use the system time. Once loaded, you can get its time by running SELECT NOW().

This is the easiest way to spot errors.

To know the defined timezone, run SELECT @@global.time_zone. If not defined, you'll read SYSTEM, which is not very helpful.

Note here that even if you changed the system date AFTER you started mysql, mysql will still use the date that was in effect when you first launched it.

It means that changing your server time will not affet running mysql processes. You'll have to restart mysql to do that : sudo /etc/init.d/mysql restart

Finding and updating the server date

Even after restarting mysql on some servers, the mysql date was still incorrect. After connecting the the sql server, I found that it was the server time that was incorrectly set (I just type date).

To update the current time zone, I had to call sudo dpkg-reconfigure tzdata (I'm using ubuntu) and choose the correct city

Updating mysql running through ndb_mgm

I'm no server expert, so this part was a little trickier. Some of our databases are using ndb cluster for replication. Reloading those configurations was harder.

First, I had to connect to the server running the ndb management and call ndb_mgm. In the later prompt, I typed show and this get me the list of all servers currently managed.

I then shut them down typing shutdown.

The, I reloaded the management and the node running on this server by doing sudo /etc/init.d/mysql-ndb-mgm restart and sudo /etc/init.d/mysql-ndb restart

Finally, I had to connect to all the servers I saw earlier (with the show command) and run sudo /etc/init.d/mysql-ndb restart on each of them

My new backup strategy for 2011

My computer was starting to get slower and slower for the past days. And I realized my automatic backup wasn't backing anything up for the past month.

And I realized I had different versions of the same files on my 2 laptops...

Well, it seems I have to do some cleaning up.

Synchronizing paperwork

I started by cleaning up my Dropbox folder. I removed shared folders with past clients, and created a "Paperwork" folder where I put all my invoices, contracts and general paperwork.

I also added my private KeePass file as well as other info I may need to access anywhere, anytime.

KeePass allow me to store all my login/password credentials in a secure way (protected by a master password). It is really useful to have this file on all my computers (and mobile phone).

Dropbox is excellent for storing simple files, that you need everywhere. Being able to access invoices and contracts even from my mobile phone proved quite valuable when meeting clients.

Hard backup of personal files

I've also changed my scheduled backups of personal files. I bought an Acronis True Image last year, and reconfigured it today.

I have a hard drive whose sole purpose is to save backups. I scheduled for the first of each month to save : my system state, my applications configuration, and my personal files (photos, saved gamed, writings, etc).

I manually started all this backups to have a clean start. I also forced the backup to restart a whole new file every 6 month (opposed to using the incremental backup).

Backing up my music and movies

I did not spent too much time figuring how to save my hundred of Go of music and movies. I rarely watch the same movies twice, so losing them won't affect me too much.

I occasionally re-watch series, though, but as most of my friends have the same tastes as I, I could very easily get them back from them, or download them (again).

Regarding music, well, I have quite a big collection, but most of it is already "backed up" on my portable mp3 player.

Automatic synchronizing with BitBucket

On my day work, I now always version my files using Mercurial. BitBucket offers unlimited storage, and unlimited public repositories. Private repo are limited to 5 users. As I'm mostly alone on projects that should stay private, this seems the best deal I could found.

Mercurial being a versionning system, I got all the benefits of a backup here, being able to revert to previous versions, update it whenever I want and access it from anywhere.

I wrote a custom Hg hook on commit to automatically push my repos to BitBucket at least once a day (I'll post the code in a future post).

MySQL Backup

I used to backup mysql databases on my work computer using a windows app. This was slowing down my computer on every boot as well and backup was thus only effective when I was working and not when I was on vacations.

Today, I wanted something a lot more flexible, so I set a cronjob on my main host coupled with a slightly edited autoMySQLBackup script.

This will automatically run everyday at midnight and make a local save (with daily, weekly and monthly rotate) of all my clients databases. Logs are saved on disk and gzipped, and will also be sent to a special backup@pixelastic.com mail address (stored on GMail).

This way I am sure to have my mysql backups on two different hosts, with daily and automatic saves, that I can access from anywhere if anything goes wrong.

Conclusion

It took me almost two full days to get the right tools, configure them and write my custom scripts but now, it is seamlessly integrated with my daily workflow. This is a weight off my shoulders, I know I can safely work as usual and my files are saved and easily accessible.

Adding some logic in your MySQL

I'm not an expert in SQL queries, I just know how to do simple queries and the more advanced options I've ever used are DISTINCT, HAVING and GROUP BY.

For the project I'm working on, I had to add some logic to my SQL queries, to concatenate various field and compare them.

Here is, as a reminder, how I've managed to I've managed to get the time spent by comparing two dates :

TIMESTAMPDIFF(SECOND, Timestamp.date, EndTimestamp.date) AS timeSpent

And is practice is never as easy as theory, I didn't really had a timestamp but two string fields instead (one date and one time), so here's how I combined them

TIMESTAMPDIFF(SECOND, CONCAT(Timestamp.date, " ", Timestamp.time), CONCAT(Timestamp.date, " ", Timestamp.time)) AS timeSpent

 

MySQL Error 1577 on Dreamhost

Recently, when connecting to my MySQL databases on Dreamhost through Navicat, I was greeted with a "1577 - Cannot proceed because system tables used by Event Scheduler were found damaged at server start" error message.

It didn't stop me from accessing the tables actually, so I didn't bother. But today it prevent me from copying tables from one server to another, so I decided to have a deeper look into it.

It seems that it has to do with a mysql upgrade issue. I contacted Dreamhost about that and they told me that they just upgraded their servers to 5.1, and that they needed to be restarted. Which they do for me.

So if you ever run into the same problem, just contact Dreamhost support and they'll fix it for you.

Mysql auto backup

I'm in the process of automating the backup of all the mysql databases of my clients' website. We never know what can happen, so having a backup of all this data is really really important.

MySQL data contains all the data added by the user, it means that unless you make regular backup of this data, you can't regenerate it.

I have multiple clients, with several websites each and an equivalent number of databases. I wanted to store a backup of all those on my local drive, to prevent any server crash problem.

I wanted a daily backup, and as my working station is on Windows XP I tried to find a simple software to do just that.

There is a little tool called mysqldump on linux that does just that : saving an entire database in a single file. Joined with some cron job and rotating the files it shouldn't be hard to implement.

But finding a software to do that on Windows is just terrible. I've been searching for them for more that 2 hours now and everything I find is horrible.

At first I found AutoBackup for Mysql but the software is written in something that even myself can't call English (and I know that my prose is not exactly Shakespeare). I had to click on some options to guess what it was supposed to do... I guess after some practice and once the tasks are scheduled it doesn't matter anymore but the trial version can't save more that 50 records per table and the price of the full version (~100$) is by far too expensive.

By continuing my quest, I ended up downloading this same software under different names.

Then I found DumpTimer and its brownish interface. It is also a great example of ergonomic errors : window too small forcing to scroll to reveal the options, nonsense message boxes popping, large amount of typo errors, limited choice of options.

But it does the job, in a very basic form, but it does it right. I could save my list of servers, check which databases and tables to save (well, all of them) and choose where to save them. There also was an option to copy them to another database, but I didn't tried that.

One of the main caveats was that it could only save the most recent backup, there was no way to save, say, the last 30 days.