Backup and restore

Backing up tables:

mysqldump --password --compress --quick --quote-names --extended-insert databasename tablename1 tablename2 ... > whatever.sql

Restoring tables:

mysql --password databasename < whatever.sql

Creating a user with their own database

CREATE DATABASE dbname;
CREATE USER 'username'@'%';
GRANT USAGE ON dbname.* TO username@'%';
GRANT ALL ON dbname.* TO username@'%';
SET PASSWORD FOR username@'%' = PASSWORD('blah');
FLUSH PRIVILEGES;

Changing passwords

SET PASSWORD = PASSWORD('blah');

Changing adminstrator password

mysql -u root
mysql> SET PASSWORD FOR root@localhost = PASSWORD('new_password');

Disabling network access

In my.cnf, add to the [mysqld] section:

skip-networking
bind-address=127.0.0.1

Flushing the initial database and users

drop database test;
use mysql;
delete from db;
delete from user where not (host="localhost" and user="root");
flush privileges;

Deleting users

REVOKE ALL PRIVILEGES ON *.* FROM username@'%';
REVOKE GRANT OPTION ON *.* FROM username@'%';
DELETE FROM mysql.user WHERE (User='username' and Host='%');

Notes

• When specifying hosts from which users may login, '%' means any host. These have precedence over 'localhost'

Checking configuration without starting up MySQL

mysqld --defaults-file=/tmp/new.cnf --verbose --help }}}

Source: Jeremy Zawodny's MySQL and Drizzle Tip: Checking configuration file syntax (faking configtest)

Drop databases matching a wildcard

# Drop all databases beginning with "phabricator_"
mysql -e 'show databases' \
| grep phabricator_ \
| xargs -I "@@" mysql -e "DROP database \`@@\`"

Migration

Convert MyISAM tables to Aria tables (MariaDB default)

for i in $(mysql --skip-column-names --silent -e'show databases;'|grep -Ev '(_schema|runtime)'); do mysql --skip-column-names --silent -e"SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$i' AND engine = 'MyISAM'" | xargs -I{} mysql -e"ALTER TABLE {} ENGINE='ARIA' TRANSACTIONAL=1;" $i; done

Switching from latin1 to utf8 encoding

In one extremely dangerous command:

mysqldump -u$USER -p$PASSWORD -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B $DATABASE | \
  sed -e 's/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/' -e 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/' | \
  mysql -u$USER -p$PASSOWRD

Or safer way, execute this SQL state to generate a list of SQL queries:

SELECT CONCAT('ALTER TABLE `', tbl.`TABLE_SCHEMA`, '`.`', tbl.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM `information_schema`.`TABLES` tbl WHERE tbl.`TABLE_SCHEMA` = '$DATABASE'

then execute those queries.

CategoryCheatSheet