Database backup like a boss

We often need to create and restore database backups to migrate data between servers or run backups. The mysqldump command creates a series of mysql insert commands that is good for readability but not so good for file size. It’s recommended to compress before transferring across a network but that means another command, and then further commands to cleanup afterwards. Compressing can often reduce file sizes to a tenth of the original. Sometimes however the uncompressed file simply won’t fit in the file system, likely for large data stored when the system is running low on disk space.

In the situations it’s best to pipe the mysqldump straight into a compression utility so there is no intermediate file. On the other end likewise the compressed file can be uncompressed and the output piped to mysql.

It’s simple to backup like a boss:

and then

Connect SequelPro to a remote mysql server

First alter /etc/mysql/my.cnf and comment out the line:

This enables remote connections. Now we need to create a user to receive the remote connection. In the mysql shell:

Job done!

Configuring Snow Leopard as a SLAMP box (Snow Leopard Apache MySQL PHP)

#install xdebug

http://aspn.activestate.com/ASPN/Downloads/Komodo/RemoteDebugging

Enter the “5.3” directory and find the file named “xdebug.so”. You need to move this file to the/usr/lib/php/extensions/no-debug-non-zts-20090626 directory. First, open a new Finder window and press Cmd + Shift + G. Enter /usr/lib/php/extensions/no-debug-non-zts-20090626/ into the prompt that appears and press “Go”. This will open the destination directory in the Finder window. Drag xdebug.so into the destination directory. You may be prompted for your administrator password. If the /usr/lib/php/extensions/no-debug-non-zts-20090626/ directory does not exist, locate the /usr/lib/php/extensions/ directory instead. This directory will contain a directory named something similar to no-debug-non-zts-20090626. Place xdebug.so into that directory instead.
edit php.ini:
1.[xdebug]
2.zend_extension=/usr/lib/php/extensions/no-debug-non-zts-20090626/xdebug.so

Understanding Joolma! ACL tables

I noticed strange behavior with my Joomla site regarding user groups in the backend user manager. The usergroup (author, publisher, etc) was listed incorrectly. When clicking on the user to get the full details it was listed correctly. Checking with phpMyAdmin revealed the usertype and gid fields were set correctly so what’s going on? More poking around the database revealed some strange tables prefixed with _core_acl. Access control list? Ahhh….

A quick note of the more interesing fields of these tables: 

*Start from the users table*

jos_users.id

jos_users.usertype = Registered/Author/Administrator/Public Frontend/etc

jos_users.gid

jos_users.name (Full name, not username)

*List of access request objects, ie users!*

jos_core_acl_aro.value = jos_users.id (user id)

jos_core_acl_aro.name = jos_users.name

jos_core_acl_aro.id = 25 for Andy

jos_core_acl_aro.section_value = ‘users’ for all

*List of user access types familiar to Joomla*

jos_core_acl_aro_groups.id = jos_users.gid

jos_core_acl_aro_groups.parent_id = id of object with next less permission

jos_core_acl_aro_groups.name = jos_users.usertype

jos_core_acl_aro_groups.lft = ???

jos_core_acl_aro_groups.rgt = ???

jos_core_acl_aro_groups.value = jos_users.usertype = jos_core_acl_aro_groups.name

*Currently empty*

jos_core_acl_aro_map

*ARO sections, currently only users defined*

jos_core_acl_aro_sections.id = 10

jos_core_acl_aro_sections.value = users

jos_core_acl_aro_sections.name = Users

*groups to aro map – is this where the magic happens?*

jos_core_acl_groups_aro_map.group_id = jos_users.gid

jos_core_acl_groups_aro_map.aro_id = jos_core_acl_aro.id

jos_core_acl_groups_aro_map.section_value = currently empty

From looking at the data in these tables this is how I think (I’m using educated guesswork here!) it all works:

Link users to ARO objects, using user id:-

jos_users.id = jos_core_acl_aro.value

Map aro objects to access groups, using the magic map:-

jos_core_acl_aro.id = jos_core_acl_groups_aro_map.aro_id

Link access groups to access group description using group id:-

jos_core_acl_groups_aro_map.group_id = jos_core_acl_aro_groups.id

In a perfect world after this link 

jos_core_acl_aro_groups.id will equal jos_users.gid and

jos_core_acl_aro_groups.name will equal jos_users.usertype

 

Lets try a SQL query to test this, which will list the jos_users version of the user type alongside the same obtained from the acl tables:

SELECT jos_users.name, jos_users.gid, jos_users.usertype, 

 jos_core_acl_aro_groups.id, jos_core_acl_aro_groups.name

 FROM jos_users, jos_core_acl_aro, jos_core_acl_aro_groups, jos_core_acl_groups_aro_map

 WHERE jos_users.id = jos_core_acl_aro.value

 AND jos_core_acl_aro.id = jos_core_acl_groups_aro_map.aro_id

 AND jos_core_acl_groups_aro_map.group_id = jos_core_acl_aro_groups.id;

 

What has caused my problems is I’ve mistakenly altered the gid and usertype fields in jos_users directly so it doesn’t correlate with the correct ACL values. To correct I need to alter the magic map table:

UPDATE jos_core_acl_groups_aro_map, jos_users, jos_core_acl_aro, jos_core_acl_aro_groups

 SET jos_core_acl_groups_aro_map.group_id = jos_users.gid

 WHERE jos_users.id = jos_core_acl_aro.value

 AND jos_core_acl_aro.id = jos_core_acl_groups_aro_map.aro_id

 AND jos_core_acl_groups_aro_map.group_id = jos_core_acl_aro_groups.id;

 

This seems to now work with the backend user list now showing the correct user type.

 

Result!