These are various snippets for use with MySQL and MariaDB.
Snippets for backing up data.
mysqldump is the most portable export option as it will dump the database(s) into SQL queries. Large backups can be slow to restore though.
To export all data using mysqldump:
TIME=`date +'%Y-%m-%d_%H:%M'`
mysqldump --opt --routines > "mysqldump-${TIME}.sql"
To filter for only specific databases:
TIME=`date +'%Y-%m-%d_%H:%M'`
mysqldump --opt --routines --databases dbname1 dbname2 > "mysqldump-${TIME}.sql"
Some popular fixups that may need to be done for the data:
DEFINER for functions/procedures:sed -i -E 's/DEFINER=[^ *]+/DEFINER=CURRENT_USER/g' "mysqldump-${TIME}.sql"
sed -i 's/) ENGINE=MyISAM/) ENGINE=InnoDB ROW_FORMAT=DYNAMIC /' "mysqldump-${TIME}.sql"
Mariabackup is a forked version of Percona's XtraBackup. It is substantially faster to restore backups than using mysqldump backups for large databases as it is not running the raw SQL queries.
To create the backup:
TIME=`date +'%Y-%m-%d_%H:%M'`
mariadb-backup --backup --target-dir="mariadb-backup-${TIME}"
mariadb-backup --prepare --target-dir="mariadb-backup-${TIME}"
You will then have a copy of the data files which can be restored in the data directory (eg. /var/lib/mysql). As an example of restoring the database:
systemctl stop mariadb
mv "/var/lib/mysql" "/var/lib/mysql_pre-restore"
cp -Rp "mariadb-backup-${TIME}" "/var/lib/mysql"
chown -R mysql:mysql "/var/lib/mysql"
systemctl start mariadb
These are various queries for checking performance and profiling queries.
To explain queries and show what happens during execution:
EXPLAIN SELECT * FROM table WHERE id = 1;
EXPLAIN DELETE FROM table WHERE id = 99;
On newer versions of MySQL the explain format can be changed to TREE to give additional information:
SET @@explain_format=TREE;
EXPLAIN SELECT * FROM table WHERE id = 1;
To get timing information about queries, enable profiling:
SET profiling=1;
You can then run queries as usual. Once done use SHOW profiles; to get the timing information:
MySQL [mydb]> show profiles;
+----------+------------+----------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------+
| 1 | 0.00061075 | SELECT * FROM table; |
+----------+------------+----------------------------------------------------+
Profiling can be disabled again by setting to 0:
SET profiling=0;