How To Setup Disk Quotas For MySQL Database

Posted on in Categories Howto, Linux, MySQL, UNIX last updated January 28, 2008

This is a user contributed article.

MySQL server has no support for preventing disk from getting filled up. You need to depend upon external configurations. Let us see how to use standard Linux disk quota to limit user database size.

Suppose you have a user foo on the system as well as on MySQL. Now how do you limit space for foo?

Step #1: Create a separate directory for databases in foo’s home say /home/foo/dbs with ownership mysql:foo and permissions 3755:
# mkdir /home/foo/dbs && chown mysql:foo /home/foo/dbs && chmod 3755 /home/foo/dbs
Step #2: Now move all foo’s databases from /var/lib/mysql to /home/foo/dbs
# mkdir /root/backup
# cp -avr /var/lib/mysql/* root/backup
# mv /var/lib/mysql/database-name /home/foo/dbs/
# chown -Rf mysql:foo /home/foo
# chmod -Rf 3755 /home/foo
# ln -s /var/lib/mysql/database-name /home/foo/dbs/database-name -v

Finally, restart mysqld. If mysqld starts with OK status, then its working else it isn’t:
# /etc/init.d/mysqld restart

NOTE- I AM NOT RESPONSIBLE FOR ANY DAMAGES OCCURRED & TO TEST THIS TRY RESTARTING mysqld. Always backup database before trying something new.

Other tools

  • MySQL Quota-Tool : The MySQL Quota-Tool helps you to set a size limit on MySQL databases. It works by checking the size of each database and revoking the INSERT- and CREATE-priveleges for the databases, which exceed the given size limit. When the size of the database falls below the given limit, the INSERT- and CREATE-priveleges are granted again.
  • MySQL Quota Daemon : MySQL Quota Daemon is a lightweight tool for limiting database sizes.
  • Developing a MySQL Quota Daemon : One of the drawbacks of MySQL is the lack of a real quota system. This article will cover how our ISP was able to solve this problem and keep track of our customers’ databases. With this completely automated solution, many hours of administration work was avoided.
  • Feel free to add your tools and tricks in the comments.

About the author: This article is contributed by Nilesh.

Sun Microsystems to Acquire MySQL Database Server

Posted on in Categories Linux, MySQL, News, Solaris, UNIX last updated January 16, 2008

This is yet another successful story for open source software. Sun has no database software to sell in a $15 billion market that Oracle leads, according to IDC study. Congratulations, Monty, David and the crew.

Now, Sun Microsystems, Inc. today announced it has entered into a definitive agreement to acquire MySQL AB, an open source icon and developer of one of the world’s fastest growing open source databases for approximately $1 billion in total consideration. The acquisition accelerates Sun’s position in enterprise IT to now include the $15 billion database market. Today’s announcement reaffirms Sun’s position as the leading provider of platforms for the Web economy and its role as the largest commercial open source contributor. From the official MySQL blog:

After all the industry speculation about MySQL being a “hot 2008 IPO”, this probably takes most of us by surprise — users, community members, customers, partners, and employees. And for all of these stakeholders, it may take some time to digest what this means. Depending on one’s relationship to MySQL, the immediate reaction upon hearing the news may be a mixture of various feelings, including excitement, pride, disbelief and satisfaction, but also anxiety.

Being part of the group planning this announcement for the last few weeks, I have had the fortune to contemplate the consequences during several partially sleepless nights (I usually sleep like a log). And over the coming days and weeks, I’ll provide a series of blogs with various viewpoints of the deal.

Check out what Jonathan Schwartz has to say about this purchase at official blog:

That in addition to acquiring MySQL, Sun will be unveiling new global support offerings into the MySQL marketplace. We’ll be investing in both the community, and the marketplace – to accelerate the industry’s phase change away from proprietary technology to the new world of open web platforms.

=> Press Release : Sun Microsystems Announces Agreement to Acquire MySQL, Developer of the World’s Most Popular Open Source Database

How to Create Linux Powered MP3 file server using MySQL Amarok and ObsidianMusic

Posted on in Categories Linux, MySQL last updated December 21, 2007

Razvan talk about hooking Amarok to MySQL database server and create an MP3 file server. I this is an excellent hack:

When it comes to playing music in Linux, Amarok is one of the best audio players out there. It offers almost everything you need, from a clean, intuitive interface to a range of useful scripts. You can even put it on a server and give it a Web interface.

ObsidianMusic was previously known as amaroK Web Frontend. It is a collection of scripts that, combined with a MySQL database and the Amarok player, forms an excellent way of sharing MP3 files over the Internet or a small office network. All the music files in your collection can be played, downloaded, or streamed by a regular Web browser. Furthermore, ObsidianMusic offers search capabilities and music sorting, and can be customized using themes.

=> Create an MP3 file server using Amarok and ObsidianMusic

Back up and print WordPress blog with OpenOffice.org Base

Posted on in Categories Download of the day, Howto, Linux, Linux desktop, MySQL last updated November 1, 2007

This sounds like a good idea to me. Never thought one can use OpenOffice to import MySQL database and do all sort of crazy things. I can even build PDF file of all top posts and provide it as download option for a small fee. From the article:

If you are running a blog (or any Web publishing system, for that matter) that relies on a database back end, you will sooner or later face the problem of backing up the content stored in the database. One way to go about it is to build a backup tool using OpenOffice.org Base. Since Base can pull data from a MySQL or any ODBC-compliant data source, you can create a simple database that connects to the blog’s back end and extracts content from it, which you can then export in different formats.

Read more

Quickly Backup / dump MySql / Postgres database to another remote server securely

Posted on in Categories Backup, FreeBSD, Howto, Linux, MySQL, Postgresql, RedHat/Fedora Linux, Sys admin, Tips, UNIX last updated October 1, 2007

Using UNIX pipe concept one can dump database to another server securely using ssh protocol. All you need remote execution rights for the ‘dd’ command, over SSH. This allows you to run database dumps across an encrypted channel.

Dump Postgres Database using ssh

Use pg_dump command command:
pg_dump -U USERNAME YOUR-DATABASE-NAME | ssh [email protected] "dd of=/pgsql/$(date +'%d-%m-%y')"

Dump MySQL Database using ssh

Type the following command:
mysqldump -u USERnAME -p'PASSWORD' YOUR-DATABASE-NAME | ssh [email protected] "dd of=/mysql/$(date +'%d-%m-%y')"

Howto: Make consistent MySQL database backups using Solaris ZFS snapshots

Posted on in Categories Howto, MySQL, Solaris, UNIX last updated August 20, 2007

I’ve already written about MySQL backup using a shell script and consistent backup with Linux Logical Volume Manager (LVM) snapshots:

A snapshot volume is a special type of volume that presents all the data that was in the volume at the time the snapshot was created. This means you can back up that volume without having to worry about data being changed while the backup is going on, and you don’t have to take the database volume offline while the backup is taking place.

Niclas has posted a nice howto about consistent MySQL backups using Solaris UNIX ZFS snapshots:

In this article I will show you how to install MySQL on a ZFS file system and supply you with a script to make consistent snapshots of the databases. This script may not be 100% fit for busy sites but for most smaller places I think it is perfect.

MySQL Proxy Load balancing and Failover Tutorial

Posted on in Categories CentOS, Debian Linux, Download of the day, High performance computing, Howto, Linux, Monitoring, MySQL, RedHat/Fedora Linux, Suse Linux, Tuning, Ubuntu Linux, UNIX last updated August 10, 2007

MySQL Proxy is a simple and new program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for a wide variety of use cases, including:
a) Load balancing
b) Failover
c) Query analysis
d) Query filtering and modification
e) and many more…

MySQL Proxy tutorial

Oreilly has published a nice tutorial using MySQL proxy application:

MySQL Proxy is a lightweight binary application standing between one or more MySQL clients and a server. The clients connect to the Proxy with the usual credentials, instead of connecting to the server. The Proxy acts as man-in-the-middle between client and server.

In its basic form, the Proxy is just a redirector. It gets an empty bucket from the client (a query), takes it to the server, fills the bucket with data, and passes it back to the client.

If that were all, the Proxy would just be useless overhead. There is a little more I haven’t told you yet. The Proxy ships with an embedded Lua interpreter. Using Lua, you can define what to do with a query or a result set before the Proxy passes them along.

MySQL Proxy Load balancing and Failover Tutorial - Logo

Download MySQL proxy

You can download MySQL proxy here

Install and configure MySQL on Windows XP or Vista or 2003 server

Posted on in Categories GNU/Open source, MySQL, Windows, Windows server last updated June 25, 2007

There is a nice small article about using MySQL on Windows 2003 or Vista desktop system.

I honestly have to say that, now after years of using MySQL on Windows, I find it to be my favorite database/platform combination of choice. In my opinion, it’s easier to use than Microsoft’s own database on their platform, the performance and feature set will meet 90+% of the needs that Windows/Web applications have, it consumes less resources than SQL Server, and the reliability is stellar.

This article shows you why you should believe this to be the case and why, if you haven’t started using MySQL on Windows yet, you really should think about doing so.

MySQL on Windows? Absolutely! [mysql.com]