MySQL / MariaDB Superuser
Simple syntax to create a super user (for editing users and dropping schemas etc) CREATE USER 'superuser'@'%' IDENTIFIED BY '[password]';…
MySQL Relative Time Since
You have some data which many users edit often, and you want to return a field with some sort of…
Mysqldump all databases and gzip
So, quick and simple MySQL backup in a CRON. #!/bin/bash _hour=`date '+%H'`; _user="root"; _pass="thepassword"; _dest="/srv/backups/daily/"; mysqldump -u $_user -p$_pass --all-databases…
Numerical Data Types in MySQL
In most MySQL GUI’s that offer the ability to alter the table definitions of a schema, if you add a…
Convert a MySQL database to UTF8
This is a “script” to convert your latin1 myisam database to utf8 innodb Dump the database mysqldump -u root -p database_to_convert > db.latin1.sql…
MySQL Profiling
MySQL allows you to profile queries, enabling you to see whats being run on your server. They are stored in…
MySQL Administration (Remote Access)
So, once you’re all set up and running, you may want to give users access from the outside world. This…
You can’t specify target table ‘xxx’ for update in FROM clause
I recently needed to update a whole bunch of records using an IN() on the target table: UPDATE table1 SET…
MySQL Get row with greatest parent row
If you have a database of CDs, and artists, etc. Say you want to get a list of artists with…
How to add a new user to MySQL
Don’t leave the root user as ‘the’ user for your web application. That’s just not a good idea. For instance,…