msgbartop
A chronological documentation test project, nothing serious, really!
msgbarbottom

01 Oct 2008 Create a mysql trigger

This is a short description on how you can create an easy Trigger in mysql. My trigger will insert todays date when I insert a new database record.

A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.

First you have to create a table to work with

CREATE TABLE IF NOT EXISTS `Links` (
  `Id` int(11) NOT NULL auto_increment, 
  `DateInserted` date NOT NULL,
  PRIMARY KEY  (`Id`)
)

Then we create the trigger that will insert todays date, curdate(), when you insert a new row

CREATE TRIGGER Links_TriggerDate BEFORE INSERT ON Links
FOR EACH
ROW
SET NEW.DateInserted = curdate( ) 

Triggers is a functionality that was introduced in mysql version 5.0.2
To view all your triggers i mysql

mysql> show triggers;
+--------------------+--------+--------+----------------------------------+--------+---------+----------+----------------+
| Trigger            | Event  | Table  | Statement                        | Timing | Created | sql_mode | Definer        |
+--------------------+--------+--------+----------------------------------+--------+---------+----------+----------------+
| Links_TriggerDate | INSERT | Links | SET NEW.DateInserted = curdate( ) | BEFORE | NULL    |          | root@localhost |
+--------------------+--------+--------+----------------------------------+--------+---------+----------+----------------+
1 row in set (0.00 sec)

Tags: , ,

Posted by

13 Sep 2008 Enable query caching in mysql

Query caching is a way to increase the performance of mysql by caching database queries.
It’s quite easy to do and only requires to edit one file, in Debian it’s called /etc/mysql/my.cnf

Add the following lines in the mysqld section

[mysqld]
query-cache-type = 1
query-cache-size = 10M

restart the mysql daemon

# /etc/init.d/mysql restart

Verify that query caching is enabled

# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33499
Server version: 5.0.32-Debian_7etch6-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 10485760 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

If you see something like the out above, caching is enabled.

Tags: ,

Posted by

19 Feb 2008 mysql_secure_installation — Improve MySQL Installation Security

This program enables you to improve the security of your MySQL installation in the following ways:

  • You can set a password for root accounts.
  • You can remove root accounts that are accessible from outside the local host.
  • You can remove anonymous-user accounts.
  • You can remove the test database, which by default can be accessed by anonymous users.

Invoke mysql_secure_installation without arguments:

shell> mysql_secure_installation

The script will prompt you to determine which actions to perform.

Source: http://dev.mysql.com

Tags: ,

Posted by

27 Jan 2008 mysql create utf8 database

This is the way to create a mysql database with UTF8 characterset

CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

Tags: , ,

Posted by

18 Nov 2007 Backup mysql databases into separate files

This bach script makes separate backup files of all the databases in mysql and saves the result in the mysql_backup folder.

#!/bin/bash -v

USERNAME='yourusername'
PASSWORD='yourpassword'
HOSTNAME='yourhostname'
BackupFolder='/backup'

for i in $(echo 'SHOW DATABASES;' | mysql --user $USERNAME -p$PASSWORD -h $HOSTNAME | grep -v '^Database$' ); do
        mysqldump --user $USERNAME -p$PASSWORD -h $HOSTNAME --opt $i > $BackupFolder/$i.sql;
done;

Remember to change the -h, -p and -h switch according to your needs and avoid space between -p and the password variable.

Tags: , , , , ,

Posted by