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: Database, MySQL, triggers
Posted by Hans-Henry Jakobsen
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.
Posted by Hans-Henry Jakobsen
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
Posted by Hans-Henry Jakobsen
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;
Posted by Hans-Henry Jakobsen
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: backup, bash, Database, grep, MySQL, mysqldump
Posted by Hans-Henry Jakobsen
