<> == Backup and restore == Backing up tables: {{{#!highlight sh numbers=off mysqldump --password --compress --quick --quote-names --extended-insert databasename tablename1 tablename2 ... > whatever.sql }}} Restoring tables: {{{#!highlight sh numbers=off mysql --password databasename < whatever.sql }}} == Creating a user with their own database == {{{#!highlight sql numbers=off 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 == {{{#!highlight sql numbers=off SET PASSWORD = PASSWORD('blah'); }}} == Changing adminstrator password == {{{#!highlight sql numbers=off 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 == {{{#!highlight sql numbers=off drop database test; use mysql; delete from db; delete from user where not (host="localhost" and user="root"); flush privileges; }}} == Deleting users == {{{#!highlight sql numbers=off 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: [[http://jeremy.zawodny.com/blog/archives/011120.html|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) === {{{#!highlight sh numbers=off 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: {{{#!highlight sh numbers=off 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: {{{#!highlight sql numbers=off 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