MySQL - Xensoft

Change a Tables Default Sort Order in MySQL

When I say "default" what I mean is the order that rows will be returned if you provide no other explicit ordering via your SQL. Usually this order is just by when a row was inserted into the table. If INSERT's and DELETE's have jumbled a nicely ordered

MySQL

Backing up All Databases in MySQL without the Warnings

Backing up MySQL used to be quick, easy and silent using mysqldump, but more recent versions of MySQL produce the following nagging warning: mysqldump -u root -p --all-databases --single-transaction > bak.sql Enter password: -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. If you'd

MySQL

Create a MySQL User for Backups

The following SQL will create a MySQL user with the minimum number of privileges necessary to backup databases: CREATE USER 'backup'@'localhost' IDENTIFIED BY '#PASSWORD#'; GRANT SELECT, RELOAD, FILE, SUPER, LOCK TABLES, SHOW VIEW ON *.* TO 'backup'@'localhost' IDENTIFIED BY '#PASSWORD#' WITH MAX_QUERIES_PER_HOUR

CLI

Split or Restore only Part of a Complete MySQLdump

If you keep your database backed up into a single SQL dump and want to retrieve only the part matching one database, perhaps to transfer to another system, I find the simplest and most re-usable trick is to use sed's "p" function which prints the "pattern space&