Differences between revisions 3 and 5 (spanning 2 versions)
Revision 3 as of 2009-06-27 20:22:59
Size: 1754
Editor: SamatJain
Comment:
Revision 5 as of 2011-04-13 23:07:16
Size: 1893
Editor: SamatJain
Comment: Expand options
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 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 29: Line 29:
{{{#!highlight sql {{{#!highlight sql numbers=off
Line 35: Line 35:
{{{#!highlight sql {{{#!highlight sql numbers=off
Line 51: Line 51:
{{{#!highlight sql {{{#!highlight sql numbers=off
Line 61: Line 61:
{{{#!highlight sql {{{#!highlight sql numbers=off
Line 73: Line 73:
{{{#!highlight sh {{{#!highlight sh numbers=off

Backup and restore

Backing up tables:

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

Restoring tables:

mysql --password databasename < whatever.sql

Creating a user with their own database

CREATE DATABASE dbname;
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)


CategoryCheatSheet

SamatsWiki: CheatSheet/MySQL (last edited 2018-07-18 10:24:19 by SamatJain)