Archive for MySQL

Separating MySQL Username and Host

// May 6th, 2009 // No Comments » // Database, Development, MySQL

As the MySQL USER() command returns users as ‘user’@’host’, it can be difficult to do straight comparasons. One way to extract both username and host is by using SUBSTRING_INDEX. For example, in a stored procedure you could use:


-- USER
SELECT SUBSTRING_INDEX(USER(), '@', 1) INTO txtUsername;
-- HOST
SELECT SUBSTRING_INDEX(USER(), '@', -1) INTO txtHost;

Voila!

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

Copying One Table Into Another Using MySQL

// February 17th, 2009 // No Comments » // Database, MySQL

I wanted to copy the contents of an existing table I had which only had certain fields populated.


INSERT INTO new-table (field1, field2)
SELECT distinct original-field1, original-field2
FROM existing-table WHERE original-field1 is not null

Random SQL Results

// November 12th, 2008 // No Comments » // Database, MySQL, PHP, SQL

Last year I needed to display 3 random featured items. The solution was very simple using MySQL with this query:

SELECT * FROM `table` WHERE `column` = 'criteria' ORDER BY RAND() LIMIT 3;