Webdev

MySQL is the most popular open source database software. I use it in many of the web-based applications.

Important files location

Configuration: /etc/mysql/my.cnf

Log files: /var/log/mysql/

PhpMyAdmin:

Common tasks

Purge records older than x days, y months, or certain date

DELETE FROM `newspaper` WHERE date < DATE_SUB(NOW(), INTERVAL 100 DAY);
DELETE FROM `newspaper` WHERE date < DATE_SUB(NOW(), INTERVAL 3 MONTH);
DELETE FROM `newspaper` WHERE date < '2010-01-01';

Add an existing user to an existing database

mysql -h localhost -u root -p
GRANT CREATE,INSERT,DELETE,UPDATE,SELECT 
ON exampledb.* TO support@localhost;
FLUSH privileges;
exit;

Add a new user to an existing database

mysql -h localhost -u root -p
GRANT CREATE,INSERT,DELETE,UPDATE,SELECT 
ON exampledb.* TO newuser@localhost;
SET password FOR newuser = password('mysecretpassword');
FLUSH privileges;
exit;

Create a new database and add a new user to it

mysql -h localhost -u root -p
CREATE DATABASE exampledb2;
GRANT CREATE,INSERT,DELETE,UPDATE,SELECT 
ON exampledb2.* TO newuser2@localhost;
SET password FOR newuser2@localhost = password('mysecretpassword2');
FLUSH privileges;
exit;

Move some records from one table to another table

INSERT INTO dentist_profile SELECT * FROM `profile` WHERE specialist like '%牙%';
DELETE FROM `profile` WHERE specialist like '%牙%';

Search and Replace:

UPDATE profile SET address_chi = replace(address_chi,"嘉賓大廈","嘉賓商業大廈");

The syntax of REPLACE is REPLACE(field, from_string, to_string)

Import individual table dump file into MySQL

mysql -u root -p db_name < table1-20110505.sql
mysql -u root -p -h localhost db_name < dbname.sql
mysql -u root -p -h localhost --default_character_set utf8 db_name < dbname.sql

Export a database

mysqldump --opt -u root -pYourPassword database01 | gzip -9 > database01_backup$NOW.sql.gz

gunzip database01_backup.sql.gz

mysqldump --opt -u root -p db_name > dbname.sql

mysql -u root -p -h localhost db_name < dbname.sql