Posts Tagged ‘Database’

Users and Permissions in MySQL

// March 24th, 2009 // No Comments » // Database, MySQL

The best way for creating users in MySQL is by using CREATE USER or GRANT statements. Another way is by manipulating MySQLs grant tables, but this is not recommended.

By default the superuser is root. A password must be specified for this. It is best if this is only used for superadmin purposes.

For new accounts you need to create two users with the same name. One is for access via localhost, the other remotely.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

To remove a user, use

DROP USER user;

To Change a password, use:

SET PASSWORD FOR 'user'@'localhost' = PASSWORD('newpassword');
SET PASSWORD FOR 'user'@'%' = PASSWORD('newpassword');

Backing up a MySQL Database

// March 24th, 2009 // No Comments » // Database, MySQL

Using cmd prompt in Windows, navigate to the bin directory where MySQL has been installed. There will be a file called “mysqldump.exe” in that directory. Enter the following command:

mysqldump -u root -ppass --databases your_db > backup.sql

Notice that there is no space between the -p and the password, this is a bug in MySQL.

The backup.sql file will be created in the directory that you are in. You can specify C:\your_db.sql if you want the file to be backed up on the C:\ drive.

If you want to backup the structure only, use -d for the “–no-data-option”. For example:

mysqldump -u root -ppass -d --databases your_db > backup.sql

Spool in Oracle

// September 24th, 2008 // No Comments » // Database, Oracle

Spool prints out the current terminal text to a file

SQL> Spool on
SQL> set heading off <===
SQL> Spool c:\spooltext.txt
SQL> your_Query
SQL> Spool off