|
Size: 1754
Comment:
|
Size: 2562
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 7: | Line 7: |
| {{{#!highlight sh mysqldump -p -c -Q databasename tablename1 tablename2 ... > whatever.sql |
{{{#!highlight sh numbers=off mysqldump --password --compress --quick --quote-names --extended-insert databasename tablename1 tablename2 ... > whatever.sql |
| Line 13: | Line 13: |
| {{{#!highlight sh mysql -p databasename < whatever.sql |
{{{#!highlight sh numbers=off mysql --password databasename < whatever.sql |
| Line 19: | Line 19: |
| {{{#!highlight sql | {{{#!highlight sql numbers=off |
| Line 21: | Line 21: |
| CREATE USER 'username'@'%'; | |
| Line 29: | Line 30: |
| {{{#!highlight sql | {{{#!highlight sql numbers=off |
| Line 35: | Line 36: |
| {{{#!highlight sql | {{{#!highlight sql numbers=off |
| Line 51: | Line 52: |
| {{{#!highlight sql | {{{#!highlight sql numbers=off |
| Line 61: | Line 62: |
| {{{#!highlight sql | {{{#!highlight sql numbers=off |
| Line 73: | Line 74: |
| {{{#!highlight sh | |
| Line 78: | Line 79: |
== Drop databases matching a wildcard == {{{ # Drop all databases beginning with "phabricator_" mysql -e 'show databases' \ | grep phabricator_ \ | xargs -I "@@" mysql -e "DROP database \`@@\`" }}} |
|
| Line 79: | Line 90: |
| CategoryCheetSheet | == 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 }}} CategoryCheatSheet |
Contents
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
