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
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
# 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
This program enables you to improve the security of your MySQL installation in the following ways:
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
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
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