mysql

Good MySQL Created/Updated/Deleted defaults

Snippet

Current MySQL versions (>= 5.6.5) have valid DATETIME defaults for tracking when a record was created, updated, and deleted.

Yes, you hear my saying, "FINALLY!"

For a field that has the time a record was created, use DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, it will not update when the record is changed.

For a field that should automatically update to NOW() when the record is changed, use DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

For a field that you want to manually adjust, use DATETIME, allowing it to be NULL when it wasn't modified. In this case, add WHERE deleted_at IS NULL to find records that are live, and WHERE deleted_at IS NOT NULL to find records we mark as deleted (though, clearly, they still exist in the table).

An example:

CREATE TABLE IF NOT EXISTS `collections` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `uid` INT(11) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME,
  PRIMARY KEY (`id`),
  KEY (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='collections';

Mysql default databases on install

Snippet

As near as I can tell, these are the databases that are created by default on mysql installation.

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| mysql               |
| performance_schema  |
| test                |
+---------------------+
8 rows in set (0.00 sec)

Dump all mysql DBs

Snippet

Two ways. First way:

$ mysqldump -u root -p --all-databases > alldb.sql

Import this way:

$ mysql -u root -p < alldb.sql

Or, second way, this bash script, with edits:

#!/bin/bash
 
USER="root"
PASSWORD="THISISMYROOTPASSWORD"
#OUTPUT="/Users/kitt/mysql-dumps"
 
#rm "$OUTPUT/*gz" > /dev/null 2>&1
 
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
 
for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
       # gzip $OUTPUT/`date +%Y%m%d`.$db.sql
    fi
done

MySQL config for Rails app

Snippet
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000
 
test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000
 
production:
  adapter: mysql1\2
  encoding: utf8
  database: your_db
  username: your_user
  password: your_pass
  socket: /tmp/mysql.sock
  host: your_db_ip     #defaults to 127.0.0.1
  port: 3306          
 
# mongodb
host: <%= ENV['MONGOID_HOST'] %>
port: <%= ENV['MONGOID_PORT'] %>
username: <%= ENV['MONGOID_USERNAME'] %>
password: <%= ENV['MONGOID_PASSWORD'] %>
database: <%= ENV['MONGOID_DATABASE'] %>
# slaves:
#   - host: slave1.local
#     port: 27018
#   - host: slave2.local
#     port: 27019

Postfix check_recipient_access with MySQL

Blog

Having a list of rejecting email addresses in a DB table makes adding them easy (add via command line insert, add via script, add via email trigger, add via desktop SQL app like Sequel Pro, add via web app), removing the need for root access to edit configuration files and restart the postfix process that may happen when hashed files are used.

This is what I did, without a list of why I did it, or how I ended up with this particular solution:

In my /etc/postfix/main.cf file, I have this entry in my list of smtpd_recipient_restrictions values:

  check_recipient_access mysql:/etc/postfix/mysql-virtual-recipient-access.cf

This says, check the recipient address by executing the SQL query specified in /etc/postfix/mysql-virtual-recipient-access.cf using the mysql credentials in the same file.

My whole smtpd_recipient_restrictions section looks like, this, but your mileage will vary:

smtpd_recipient_restrictions =
  permit_sasl_authenticated,
  permit_mynetworks,
  reject_unauth_destination,
  reject_unknown_sender_domain
  reject_invalid_hostname,
  reject_non_fqdn_hostname,
  reject_unknown_recipient_domain,
  reject_unknown_helo_hostname,
  check_recipient_access mysql:/etc/postfix/mysql-virtual-recipient-access.cf

For the mysql query to work, I needed my table. I created my mysql table with this command:

CREATE TABLE `virtual_sender_access` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source` varchar(256) NOT NULL DEFAULT '',
  `access` varchar(128) NOT NULL DEFAULT '',
  `created_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Where the fields are id to track unique entries, source is the email address (using the conventions from other tables that are defined in a whole bunch of tutorials for the virtual domains, aliases, and users), access is what I'm going to do with the email address: deliver it, reject it, drop it, etc. as allowed in the access(5) values on http://www.postfix.org/access.5.html.

I nearly always use created_on and last_modified in my tables, as a way to track changes. You can totally leave those out if you don't care about them or have a different logging mechanism.

I have the table, I have the configuration, now the query.

In my mysql-virtual-recipient-access.cf, I have this:

user = lookupuser
password = *********************
hosts = 127.0.0.1
dbname = postfix
query = SELECT access FROM virtual_sender_access WHERE source='%s'

Now, all of this you likely could have gotten from the other tutorials around the web. The problem I had when setting this thing up with my mail server was *what do I put IN the database table*?

Right.

Put in this to reject an address:

INSERT INTO virtual_sender_access (source, access, created_on) VALUES ('reject@example.io', 'REJECT', NOW());

Put in this to accept an address and deliver it:

INSERT INTO virtual_sender_access (source, access, created_on) VALUES ('accept@example.io', 'OK', NOW());

Follow along with the other tutorials for the rest of the configuration, restart your postfix server with something like "service postfix restart" and check your queries and tables are all setup correctly. I tested by sending email to an address I wanted rejected, as listed in my virtual_sender_access with a REJECT code, and watching it be rejected. I then sent an email to an address NOT listed in the table, and it was delivered. I considered this a success. I would not recommend that as the correct way to test if a system is working.

rename mysql table

Snippet

Rename a table in mysql

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2];
 
--- Done left to right, so to swap two tables, use a tmp table:
 
RENAME TABLE old_table TO tmp_table,
             new_table TO old_table,
             tmp_table TO new_table;
 
 
--- Actual case: adjust drupal table names for shared tables.
 
rename table users to shared_users;
rename table sessions to shared_sessions;
rename table role to shared_role;
rename table authmap to shared_authmap;

Add auto_increment after table created

Snippet
ALTER TABLE purchase_links ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT first, add primary key (id);

Importing MySQL data into Access

Snippet

Sometimes, a linked database needs to be exported into Access for someone to use with Excel. Don't ask me, it happens. How to do that, even with linked database tables.

20.1.5.4.2. Importing MySQL Data to Access
 
To import a table or tables from MySQL to Access, follow these instructions:
 
1. Open a database, or switch to the Database window for the open database.
 
2. To import tables, on the File menu, point to Get External Data, and then 
click Import.
 
3. In the Import dialog box, in the Files Of Type box, select ODBC Databases (). 
The Select Data Source dialog box lists the defined data sources The Select Data 
Source dialog box is displayed; it lists the defined data source names.
 
4. If the ODBC data source that you selected requires you to log on, enter your
login ID and password (additional information might also be required), and then 
click OK.
 
5. Microsoft Access connects to the MySQL server through ODBC data source and 
displays the list of tables that you can import.
 
6. Click each table that you want to import, and then click OK.

Pause a MySQL query with sleep

Snippet

For MySQL > 5.0.12, execution of a series of queries can be delayed / paused by using SELECT SLEEP(), which is useful when a previous query needs to be paused, say, when a slave is reconnecting to a master.

-- delay a query by one second
SELECT SLEEP(1);
 
-- delay a series of queries by a 30 seconds
SELECT SLEEP(30);
 
-- stop the slave, skip the error SQL, start the slave, pause a second, then check the slave status
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; SELECT SLEEP(1); SHOW SLAVE STATUS;

Skip duplicate entries for replication queries

Snippet

If a conflicting query occurs on a slaved database, and it's okay to skip that query, use the SQL_SLAVE_SKIP_COUNTER setting.

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N;
START SLAVE;

Pages