mysql

This step-by-step tutorial walks you through the installation of Django 1.1 with Apache and MySQL. on CentOS 5. The example VPS used during the walk-through is the popular SliceHost from RackSpace ™ The steps involved should be quite similar for any properly installed CentOS 5.x (or RedHat EL 5.x).

We use the latest stable version of Python: 2.6 and mod_wsgi (far superior alternative to mod_python), in this tutorial. Django is not, yet, compatible with Python 3.x branch (few things are).

If you are working on a system with somewhat large number of tables, you probably have wanted to delete a number of tables matching a pattern (dropping all tables is a specific case of this one). You can do that, by creating a very simple stored procedure:

mysql> delimiter $$
create procedure drop_tables_like(pattern varchar(255))
begin

SELECT 
@drop_sql:=concat('DROP TABLE IF EXISTS ', group_concat(table_name)) 
drop_statement
FROM information_schema.tables
WHERE table_schema=database() and table_name like pattern;

IF (@drop_sql IS NOT NULL) THEN
  PREPARE stmt from @drop_sql;
  EXECUTE stmt;
  DROP PREPARE stmt;
END IF;

end$$
delimiter ;

then you can use this procedure to mass-delete tables like:

mysql> call drop_tables_like ('ika%');

If, for whatever reason, you are running MySQL from your LAMPP installation and try to use Python's MySQLdb API to connect to MySQL server you will get the following nasty error into your face:

ImportError: libmysqlclient_r.so.15: cannot open shared object file

The problem is that lampp, in its attemt to not disturb the rest of the OS space, does not expose some crucial shared libraries (like: mysql client, ssl etc.) to Linux at large and Python is unable to find them even though they are installed under LAMPP.

The solution is quite easy

Task: allow unrestricted access to a virtual host from a range of IPs, require MySQL-based authentication from any other IP.

Solution:

A transaction in the database modifies data. Due to its impotance, any extra care and security you can apply to a transaction, can pay back big time in a critical situation. There is one measure which is relatively easy to implement in MySQL and can save you a lot of headache if something goes south.

There are transactions in program logic that developer knows should only ever affect one row. It can be a Delete or Update statement and we can predict that it never spans across multiple rows. If that is a case, it is a good practice to add "Limit 1" at the end of the statement. That way, even if for some reason our delete or update statement's "where" clause gets messed up, we will only damage one row at max per transaction.

Now, let's assume you are building your "where" clause on-the-fly (disclaimer: generally, a security risk in itself). Let's imagine that, for whatever reasons, under some conditions you end up with a generated statement like:

delete from users where user_id is not null

now, compare this to

delete from users where user_id is not null Limit 1

better?

XAMPP is one of the greatest (i.e most useful) bundled open-source packages that gives an easy, rich LAMP installation in seconds.

If you have tried it, you know that installing many different PHP extensions is neither easy nor fun on Linux. Well, except, maybe on Debian/Ubuntu where you can easily add components with apt-get. Alas, a lot of corporate production systems run RedHat and there XAMPP comes as real life-saver.

One thing about XAMPP though - it installs a whole bunch of stuff like Apache, PHP, MySQL, Proftpd, Webmin etc. I never use or enable FTP because I think it is insecure, legacy protocol that should never be used. Use SSH instead. As for MySQL, I like to install it myself from official MySQL binary distribution. It\'s a database server, hence - tricky and needs more attention than a vanilla installation. At least - that is my taste.

So, how do we disable FTP, MySQL and WebMin in XAMPP installation and use it only for Apache/PHP/Perl (we could install Python add-on, too) ?