Today I searched for information about backing up MySQL databases and found Database Backup Methods which seemed to be a pretty good run down on what the options are.
My backup strategy at the moment uses mysqldump and there is an article about that too: Using mysqldump for Backups.
Today I was looking up the syntax to create a MySQL database from the command-line and found instructions on Snipplr. I haven’t seen snipplr before. I guess I’ll have to spend some time one day browsing the popular snippets.
Found myself looking up the MySQL IF Syntax.
I had a problem with phpMyAdmin not using encrypted connections.
My server was correctly configured for SSL as indicated by:
SHOW VARIABLES LIKE '%ssl%'
Which returned:
| Variable_name | Value |
|---|---|
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/mysql/cacert.pem |
| ssl_capath | |
| ssl_cert | /etc/mysql/server-cert.pem |
| ssl_cipher | |
| ssl_key | /etc/mysql/server-key.pem |
However when I ran:
SHOW STATUS LIKE 'Ssl_cipher'
I got back a null result, indicating that the connection was not encrypted.
Eventually I figured out that the problem was caused by using the ‘mysqli’ provider for my connections in phpMyAdmin. When I switched my connections to use ‘mysql’ instead then encryption started working and an Ssl_cipher was reported.
I’d love to know what the actual problem is, but for now I’m just happy that my connections are actually encrypted. I spent a while hacking on the mysqli dbi interface to try and get it to play nice with SSL but I didn’t make any progress.
Found a list of MySQL Encryption and Compression Functions today. Handy.
Recently after my hosting provider hard-booted one of my machines the MySQL service started to complain “Failed To Read Auto-Increment Value From Storage Engine” when an insert was issued to any table with an auto increment field. I found the solution here, and it basically requires you to reset the auto increment key on the table, like this:
ALTER TABLE `table_name` AUTO_INCREMENT =1
I had to do that on all of my tables that had auto increment keys to resolve the issue.
I found the configuration setting in the documentation that will force phpMyAdmin to automatically track tables during and after creation. It is:
$cfg['Servers'][$i]['tracking_version_auto_create'] boolean
And the default value is ‘false’. I updated:
/var/www/www.progclub.org/pcma/config.inc.php
With the line:
// JE: 2011-09-07: force tracking $cfg['Servers'][$i]['tracking_version_auto_create'] = true;
Had a problem where localhost wasn’t bound because I’d used bind-address to bind the internet IP address. The fix was to remove bind-address altogether, meaning that all addresses get bound.