MySQL

Latest Compile with pdo drivers for mysql along with mod_security.

NOTE:
Remove the MySQL-shared rpm else openssl will not work.

Recently, I forgot the root password for MySQL and went about resetting it as below:

1. Stop mysqld and restart it with:
   

   # service mysqld stop
   # mysqld_safe --skip-grant-tables --user=root
   

2. Connect to the mysqld server with this command:

   sql> mysql -u root
   

3. Issue the following statements in the mysql client:

   mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
   mysql> FLUSH PRIVILEGES;
   

   Replace “newpassword

Here's a quick tip at sorting VARCHAR type data in mysql database with values in a column.

With the default sort, it would look something like below:

mysql> SELECT column FROM table_name ORDER BY column; 

column
======
100
1000
10000
200
2000
20000
...

Now with "... ORDER BY column+0", I get it sorted right:

mysql> SELECT column FROM table_name ORDER BY column+0; 

column
======
100
200
1000
2000
10000
20000
...

This is a quick fix instead of sorting to CAST operator.

Had to do some fine tuning of MySQL 4.1.9 and here is what my.cnf file looks like for a 2GHz machine with 1GB of memory.

[mysqld]
socket=/path/to/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb
# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
# max_connections=500
# Reduced to 200 as memory will not be enough for 500 connections.
# memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections

# Create User
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'];

# Create Database
$ mysqladmin -u <username> -p create <nameOfDatabase>

# Drop/Delete Database
$ mysqladmin -u <username> -p drop <nameOfDatabase>

# Check Process List
$ mysqladmin -u root -p proc

# Check Status at 5 seconds interval
$ mysqladmin -u root -p -i 5 status

# Dump Database
$ mysqldump --opt -u <username> -h <hostname> <nameOfDatabase> -p > /path/to/file    

$ mysqldump --opt -u <username> -h <hostname> --all-databases -p > /path/to/file 

# Import Database
$ mysql -h <host> -u <username> <nameOfDatabase> -p < /path/to/file

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON <dbname>.* TO <dbuser@localhost> [IDENTIFIED BY '<password>'];

REVOKE ALL ON <dbname> FROM <dbuser@localhost>;

CREATE DATABASE <dbname>;

DROP DATABASE <dbname>;

DROP TABLE <tablename1[, table2, table3...]>;

# To activate new permissions
FLUSH PRIVILEGES; 

USE <nameOfDatabase>;

SHOW DATABASES;

# show tables begining with the prefix
SHOW TABLES LIKE 'prefix%'; 

SELECT * FROM <nameOfTable>;

DESCRIBE <nameOfTable>;

INSERT INTO <table> <username, password, name1, name2, ...> VALUES ('user', password('pass'), 'value1', 'value2' ...);

CREATE TABLE <newtable> AS SELECT DISTINCT <field> FROM <oldtable>;

INSERT INTO <database.table> SELECT * FROM <database.table> WHERE <field> = <value>;

ALTER TABLE <tableOldName> RENAME <tableNewName>;

UPDATE <tableName> SET <field1> = <newValue> [WHERE <field2> = <currentValue>];