Tag: Database

Create a mysql trigger

On By Hans-Henry JakobsenIn Scripting

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)

Enable query caching in mysql

On By Hans-Henry JakobsenIn Linux, Photo etc

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.

mysql_secure_installation — Improve MySQL Installation Security

On By Hans-Henry JakobsenIn Linux, 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

Backup mysql databases into separate files

On By Hans-Henry JakobsenIn Linux, Scripting

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.