MySQL – devops scripts & patterns

MySQL Table Cleanup Procedure

With the following procedure, it is possible to move data from a primary table to a secondary table for archival purposes. This specific example moves data on the basis of a date column in the primary table. The number of days (related to the current date/time) to remain in the primary table is passed to the procedure. Start procedure with ‘CALL pCleanUPTable (35)’.

DELIMITER //

CREATE PROCEDURE pCleanUpTable(IN iDays INT)

  BEGIN
    START TRANSACTION;
    SELECT (now() - interval iDays day) INTO @dtDeleteFrom;
    SAVEPOINT PreCleanUP;
    -- Copy old records to new archive/secundary  table...
    INSERT INTO sectab (primtab_id, value1, value2, value3, datum)
    SELECT id, value1, value2, value3, datum from primtab where datum < @dtDeleteFrom;
    SELECT ROW_COUNT() INTO @iRowsCopy;

    -- Remove records from primary table...
    DELETE FROM primtab WHERE datum < @dtDeleteFrom;
    SELECT ROW_COUNT() INTO @iRowsDelete;

    -- Check if rows copied -eq rows deleted...
    IF (@iRowsCopy = @iRowsDelete) THEN
      COMMIT;
      SELECT 'Success - Committed!' AS Result, @iRowsCopy AS RowsCopy, @iRowsDelete AS RowsDelete;
    ELSE
      ROLLBACK TO PreCleanUp;
      SELECT 'Failure - ROLLBACK!' AS Result, @iRowsCopy AS RowsCopy, @iRowsDelete AS RowsDelete;
    END IF;
  END //

DELIMITER ;

In bovenstaande voorbeeld is gebruik gemaakt van de volgende InnoDB tabellen.

[primtab]
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| value1 | varchar(75)      | YES  |     | NULL    |                |
| value2 | varchar(75)      | YES  |     | NULL    |                |
| value3 | varchar(75)      | YES  |     | NULL    |                |
| datum  | datetime         | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+

[sectab]
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| primtab_id | int(10)          | YES  |     | NULL    |                |
| value1     | varchar(75)      | YES  |     | NULL    |                |
| value2     | varchar(75)      | YES  |     | NULL    |                |
| value3     | varchar(75)      | YES  |     | NULL    |                |
| datum      | datetime         | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

MySQL basic queries en commands

2008-05-15 / MySQL Server / Tim van Kooten Niekerk

Connect to a MySQL instance (commandline)

Connect to A MySQL instance using named pipe sessies.

>mysql -u root -p

MySQL commands

Some examples to show database objects.

SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM naam_van_table;
SHOW STATUS;
SHOW FULL PROCESSLIST;
SHOW PROCEDURE STATUS;

View a stored procedure.

SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES
WHERE SPECIFIC_NAME = '<procedure_name>'
AND ROUTINE_SCHEMA = '<database_name>;

Create, remove and select

CREATE DATABASE naam_van_database;
DROP DATABASE naam_van_database;
USE naam_van_database;

Create and alter tables

CREATE and ALTER table syntax.

CREATE TABLE naam_van_table (column0_id int(10) unsigned NOT NULL primary
key auto_increment,
column1 varchar(75) NOT NULL default '',
column2 varchar(75) NOT NULL default '',
column3 varchar(40) NOT NULL default '',
column4 int(10) unsigned default NULL) engine = InnoDB;

ALTER TABLE naam_van_table ADD COLUMN (column5 bool default 0);
ALTER TABLE naam_van_table ADD COLUMN column6 int unsigned AFTER column2;
ALTER TABLE naam_van_table DROP COLUMN column2;
ALTER TABLE naam_van_table ADD INDEX naam_van_index (naam_van_column);

Queries (Selectie, Insert and Update)

SELECT * FROM naam_van_table WHERE naam_van_column='text';
SELECT column1, column2 FROM naam_van_table WHERE column3='text';
SELECT COUNT(*) FROM naam_van_table;

INSERT INTO naam_van_table VALUES (1, 'Jan', 'Jansen', MD5('zaq123'), 'Docent');
INSERT INTO naam_van_table (voornaam, achternaam, pswd) VALUES
('Jan', 'Jansen', MD5('zaq123')),
('Piet', 'Pieterse', MD5('qaz321'));

UPDATE naam_van_table SET naam_van_column1 = 'X' WHERE naam_van_column2='Y';

Create users and set privileges

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT Select ON databasename.tablename TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'%.domain.tld'; 

ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;

Set file privileges

Add file_priv to account to make it possible to use a bulk import procedure.

USE MYSQL;
UPDATE user SET file_priv = 'Y' WHERE user='username';
FLUSH PRIVILEGES;

MySQL