MySQL

creating a dump file

mysqldump -u root -p database-name -h hostname > new-file-name.dmp

dropping (deleting) a database

    DROP DATABASE db-name;

creating a database

    CREATE DATABASE db-name;
    ALTER DATABASE db-name DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    -- The line below creates the user if it does not exist
    GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,CREATE TEMPORARY TABLES, DROP,INDEX,ALTER ON db-name.* TO user-name@localhost IDENTIFIED BY 'password here';

restoring DB from dump file. You need to create a new database first

    mysql --user root -p -D database-name < file-name.dmp

updating the root password:

    use mysql;
    update user set password=PASSWORD(&quot;NEWPASS&quot;) where user = 'root';
    flush privileges;
    exit;

Restoring data from a transaction log file, note that you first have
to restore the data dump that has all the data that existed before the
transaction log

    mysqlbinlog --stop-date=&quot;2009-01-08 13:15:00&quot; binlog.000077 | mysql -u root -D lmsarchive

commands to remember

show databases;
select unix_timestamp(); -- gets the current date/time as a unix timestamp

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: