MySQL - Commonly used SQL commands

Most commonly used MySQL specific SQL Commands - Now I don't have to google for them every time I forget the syntax.


GRANT

The GRANT statement enables system administrators to create MySQL user accounts and to grant rights to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. The REVOKE statement is related and enables administrators to remove account privileges. To determine what privileges an account has, use SHOW GRANTS.

GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option [with_option] ...]

object_type:
TABLE
| FUNCTION
| PROCEDURE

priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name

with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count

ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'

REVOKE

SHOW VARIABLES

SHOW [GLOBAL | SESSION] VARIABLES

 [LIKE 'pattern' | WHERE expr]

SHOW VARIABLES shows the values of MySQL system variables. This information also can be obtained using mysqladmin command. The LIKE clause, if present, indicates which variable names to match. The WHERE clause can be given to select rows using more general conditions.

With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL. With SESSION, it displays the values that are in effect for the current connection. If no modifier is present, the default is SESSION. LOCAL is a synonym for SESSION.

SHOW STATUS
SHOW STATUS provides server status information. The LIKE clause, if present, indicates which variable names to match

SHOW [GLOBAL | SESSION] STATUS
    [LIKE 'pattern' | WHERE expr]

With a LIKE clause, the statement displays only rows for those variables with names that match the pattern:

mysql> SHOW STATUS LIKE 'Key%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| Key_blocks_used    | 14955    |
| Key_read_requests  | 96854827 |
| Key_reads          | 162040   |
| Key_write_requests | 7589728  |
| Key_writes         | 3813196  |
+--------------------+----------+

SHOW SLAVE STATUS

This statement provides status information on essential parameters of the slave  threads.
If you issue this statement using  the `mysql` client, you can use a \G  statement terminator
rather than a semicolon to obtain a more readable vertical  layout:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3306
Connect_Retry: 3


SHOW BINARY LOGS / SHOW MASTER LOGS

Lists the binary log files on the server

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000015 |    724935 |
| binlog.000016 |    733481 |
+---------------+-----------+


SHOW [STORAGE] ENGINES

SHOW ENGINES displays status information about the server's storage engines.
This is particularly useful for checking whether a storage engine is
supported, or to see what the default engine is. 
SHOW TABLE TYPES is a deprecated synonym.


mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
Engine: HEAP
Support: YES
Comment: Alias for MEMORY

--

*************************** 4. row ***************************
Engine: InnoDB
Support: YES

SHOW ERRORS

SHOW ERRORS [LIMIT [offset,] row_count]
SHOW COUNT(*) ERRORS

This statement is similar to SHOW WARNINGS, except that instead of displaying errors, warnings, and notes, it displays only errors.

SHOW WARNINGS

SHOW WARNINGS [LIMIT [offset,] row_count]

SHOW COUNT(*) WARNINGS

show warnings shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session. It shows nothing if the last statement used a table and generated no messages. (That is, a statement that uses a table but generates no messages clears the message list.) Statements that do not use tables and do not generate messages have no effect on the message list.

Warnings are generated for DML statements such as INSERT, UPDATE, and LOAD DATA INFILE as well as DDL statements such as CREATE TABLE and ALTER TABLE.

SHOW many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following:

SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW [BDB] LOGS
SHOW MUTEX STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
LIKE 'pattern'
| WHERE expr


ALTER TABLE

ALTER [IGNORE] TABLE tbl_name

    alter_specification [, alter_specification] ...

alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_type]
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_type]
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,...) [index_type]
| ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name]
(index_col_name,...) [index_type]
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE

index_col_name:
col_name [(length)] [ASC | DESC]

index_type:
USING {BTREE | HASH | RTREE}

table_options:
table_option [[,] table_option] ...


To be continued...

MySQL - Sample backup script

Platform - Linux

Sample script to backup MySQL DBs

#!/bin/bash

# Pls change these
db_host='localhost'
db_user='backupuser'
db_user='secretpassword'
#
# BACKUP_DIR - Need to change as per HOST/DB environment
#
BACKUP_DIR="/backups/`hostname -s`/mysql"

if [ ! -d $BACKUP_DIR ] ; then
mkdir -p $BACKUP_DIR
chmod 700 $BACKUP_DIR
fi

# Pls donot touch below code

DATE=`date +"%Y%m%d"`
MYSQLDUMP="$(which mysqldump)"
LOG_FILE=${BACKUP_DIR}/runlog${DATE}.log
MYSQL="$(which mysql)"

DB_LIST="$($MYSQL -u$db_user -h$db_host -p$db_pass -Bse 'show databases')"

exec 2> ${LOG_FILE}

MYSQLDUMP_OPT="${MYSQLDUMP} -u${db_user} -p${db_pass} -h${db_host} --opt"

for DB_NAME in ${DB_LIST} ; do

if [ ! -d ${BACKUP_DIR}/${DB_NAME} ] ; then
mkdir -p ${BACKUP_DIR}/${DB_NAME}
fi

${MYSQLDUMP_OPT} ${DB_NAME} | gzip -9 > ${BACKUP_DIR}/${DB_NAME}/${DATE}.dmp.gz
done

if [ -s ${LOG_FILE} ]; then

mail -s "Alert- Backup failure `hostname -s` " me@hostname.com < ${LOG_FILE} else #Cleanup process..Delete backup files which are one week old find $BACKUP_DIR -type f -name "*.dmp.gz" -mtime +7 -exec rm {} \; mail -s "Notification - Backup done `hostname -s` " me@hostname.com < ${LOG_FILE}
fi

Step-by-Step guide for Installing MySQL on Windows

You can download the MySQL database from the MySQL website http://www.mysql.com by clicking on the downloads tab. Scroll down to the MySQL database server & standard clients section and select the latest production release of MySQL, 5.1.35 at the time of writing.

Installation of MySQL Server

Unzip the setup file and execute the downloaded MSI file. Follow the instructions below exactly when installing MySQL Server:

Click on the "setup"

Perform a typical installation

<!--[if !vml]--><!--[endif]-->

Check box to configure MySQL Server

If you checked the Configure the MySQL Server now check box on the final dialog of the MySQL
Server installation, then the MySQL Server Instance Configuration Wizard will automatically start.
Follow the instructions below carefully to configure your MySQL Server to run correctly with
EventSentry.

Select Detailed Configuration

I was installing it on my local machine where other applications & tools are running I decided to opt "developer machine" but it is recommended that you use a Dedicated MySQL Server Machine for your MySQL database, if this is not an option then select "Server Machine".

If you selected Dedicated MySQL Server Machine and your MySQL service does not start after the wizard completes, then try to re-run the wizard (or re-install) MySQL, but this time select the Server Machine option.

I have checked "Multifunctional databases" as I wanted MyISAM as default storage engine but if you want you can select "Transactional Database Only", this will make sure that InnoDB is the main storage engine. If you have checked 3rd option then only myISAM engine would be available

Select the drive where the database files will be stored.
Select the drive on the fastest drive(s) on your server







It is recommended that you leave the default port 3306 in place, however
EventSentry will also work with non-standard ports if necessary.







It is highly recommended that you run the MySQL Server as a Windows
service(you can disable this if you want to start it manually whenever required) and include the binary directory in the search path.


Specify a secure root password, you may want to check the box Enable root access
from remote machines if you plan on administering your MySQL server
from your workstation or other servers.

If you are getting an error message after clicking the Next button, then please enable port 3306
in the Windows XP Firewall Settings

Done!!!

But if you are installing MySQL on a Windows XP workstation, or any other computer that has a firewall enabled, and the wizard fails with an error message similar to the one shown below (Can't connect to MySQL server on 'localhost'), then you will have to exclude the MySQL daemon from your firewall configuration

On Windows XP, you can exclude MySQL from the firewall by following the steps below:
1. Navigate to Start -> Settings -> Control Panel -> Windows Firewall

2. In the resulting dialog, enter the information as shown in the screenshot

After clicking OK twice, return to the MySQL error message and select Retry. MySQL should now be able to create the instance correctly.

<!--[if !vml]--><!--[endif]-->

MySQL - Syncing slave with the master

Manual procedure of syncing slave with he master

Below commands to be run on Slave

1. Check the slave status and Stop the slave its is running

mysql> show slave status\G

mysql> stop slave;

2. Get the dump of master server

shell> time mysqldump --opt -master_server_ip --master-data=1 --triggers --routines --databases DB1 DB2 DB3 DBn | gzip > /path/to/dumpfile.gz

3. Once the above dump is over, you can start importing

shell> time gunzip –c /path/to/dumpfile.gz | mysql

Once the above import is done, start slave on slave server<!--[endif]-->

mysql> show slave status\G

mysql> start slave;

In production environment you can do this kind of sync activity by taking a maitenance window/down time during the dump process(mysqldump command)

MySQL - Restoring a single database from a backup file contaning multiple databases

Sometime (rather I would say most of the time) you may need to restore a single/specific database from the nightly backup but unfortunately your nightly backup contains all the other databases and restoring all the databases is not the solution as we may end up losing some of the data which might be added to the databases after the nightly backup. Fortunately there is one option "--one-database" available in the 'mysql' command line utility which comes into our rescue.

In order to restore a single database from the nightly backup you can use the --one-database or -o option when restoring

shell>mysql --one-database required_db < /path/to/backup.sql

mysqlhotcopy

mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It is the fastest way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix and NetWare.


The options can be viewed by executing the following command:
shell>mysqlhotcopy --help


Backup one/many database at once

shell>mysqlhotcopy [options] db_name1...db_nameN /path/to/backup_directory

Using mysqlhotcopy to backup only those tables within a given database that match a regular expression:

shell>mysqlhotcopy [options] db_name./regex/

The regular expression for the table name can be negated by prefixing it with a tilde (“~”):


shell>mysqlhotcopy [options] db_name./~regex/

For complete info on mysqlhotcopy
http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html

MySQL - Renaming database

You would hear a big "NO" if you ask some some one about the command to RENAME the DATABASE.

Sometime ago MySQL tried to include the SQL command(This statement was added in MySQL 5.1.7) to rename the database "RENAME {DATABASE | SCHEMA} db_name TO new_db_name;"

but was found to be dangerous and was removed in MySQL 5.1.23. It was intended to enable upgrading pre-5.1 databases to use the encoding implemented in 5.1 for mapping database names to database directory names . However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present.

Then is there any workaround for this? yes there couple of ways to do this.. below suggestion are given by some of MySQL experts on online forums which I'm including here

A) use mysqldump to dump the database, create the new
database, reload the dump file into the new database, then drop the old
database.  That is, in effect, a database rename, although (I suspect)
it can cause problems if you have foreign key relationships that refer
to the table names in the original database.

B) Another approach is to create the new database, and then, for each
table in the original database, use RENAME TABLE orig_db.t TO new_db.t
to move the table from one database to the other.  Then drop the old
database.

C) Some ppl suggest this way ( NOT A SAFE-WAY though if you have mixed engines)

i) Bring down MySQL if it is running
ii) rename database directory using OS commands
iii) Bring up MySQL

MySQL - lower,upper char count

In one of my task I wanted to get the count of upper/lower char from a string but noticed that the string function provided in MySQL couldn't get me that.. and also at the moment it seems that the regex function in MySQL can match only and the matched stats cannot be captured nor returned.

I know 2 liner code in any scripting language can perform this tasks very well.


DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`uGetLowerUpperCharCount`$$

CREATE FUNCTION `uGetLowerUpperCharCount`(prm_string varchar(250)) RETURNS varchar(250) CHARSET latin1
BEGIN
DECLARE strPos INT default 1;
DECLARE strUpperLen INT default 0;
DECLARE strLowerLen INT default 0;
DECLARE strNonAlphaLen INT default 0;
WHILE strPos <= LENGTH(prm_string) DO IF ASCII(SUBSTRING(prm_string,strPos ,1)) >= 65 AND ASCII(SUBSTRING(prm_string,strPos ,1)) <=90 THEN SET strUpperLen = strUpperLen+1; ELSEIF ASCII(SUBSTRING(prm_string,strPos ,1)) >= 97 AND ASCII(SUBSTRING(prm_string,strPos ,1)) <=122 THEN SET strLowerLen = strLowerLen+1; ELSE SET strNonAlphaLen = strNonAlphaLen+1; END IF; SET strPos = strPos+1; END WHILE; RETURN CONCAT('String ',prm_string,' has ', IF(strUpperLen>0,CONCAT(strUpperLen, ' - Upper Chars '),''),IF(strLowerLen>0,CONCAT(',',strLowerLen,' - Lower Chars '),''),IF(strNonAlphaLen>0,CONCAT(' and ',strNonAlphaLen,' - Non-Alpha '),''));
END$$

DELIMITER ;

Usage:

select uGetLowerUpperCharCount('Umesh Kumar Shastry');

String Umesh Kumar Shastry has 3 - Upper Chars ,14 - Lower Chars and 2 - Non-Alpha

MySQL - initcap function

Sometime back I was looking for a built-in initcap/ucfirst function in MySQL but unfortunately couldn't find such string functions so decided to write my own.. thanks to the MySQL community member who corrected the bug in my function & posted it back.


DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`initcap`$$

CREATE FUNCTION `initcap`(x char(30)) RETURNS char(30) CHARSET utf8
BEGIN
SET @str='';
SET @l_str='';
WHILE x REGEXP ' ' DO
SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
END WHILE;
RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END$$

DELIMITER ;


Usage:

select initcap('umesh kumar shastry');

Umesh Kumar Shastry

select initcap('ashutosh s');

Ashutosh S

select initcap('rahul giri');

Rahul Giri

select initcap('alam seraj');

Alam Seraj

select initcap('atul kaushik');

Atul Kaushik

MySQL - Copying records from remote server DB table to local server DB table

Sometime you may need to populate(sync) local testing table with the production table which is hosted on a remote server.


# This command should be run on local server(SINGLE COMMAND)

mysqldump -t -h [remote_host_ip] -u[remote_User] -p[remote_Password] [remote_DbName] [remote_TableName] | mysql -h localhost -u [local_User] -p[local_Password] [local_DbName]


What if you want to copy last month data only?

mysqldump -t -h [remote_host_ip] -u[remote_User] -p[remote_Password] [remote_DbName] [remote_TableName] -w "column_date_time > NOW() - INTERVAL 1 MONTH"| mysql -h localhost -u [local_User] -p[local_Password] [local_DbName]

MySQL - Thread stack overrun

Sometime you may come across a situation wherein a stored procedure(in mycase it was uGetBussinessDays which was working perfectly until this morning is now throwing a Thread stack overrun error) when called throws Thread stack overrun error.

Typically the error message would look like:

Error Code : 1436
Thread stack overrun: 6444 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack.


According to the MySQL manual "The default (192KB) is large enough for normal operation. If the thread stack size is too small, it limits the complexity of the SQL statements that the server can handle, the recursion depth of stored procedures, and other memory-consuming actions" .

To resolve this issue you need to increase the default value of parameter thread_stack (128 in my my-small.cnf) to something 258K in your MySQL configuration file.

MySQL - Get bussiness/Off days

MySQL Version >5.0

Ever wondered how would you get a list of business dates/Off dates from a specific date range in MySQL? here is a simple way to do so...

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`uGetBussinessDays`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `uGetBussinessDays`(in_sDate DATE, in_eDate DATE)
READS SQL DATA
BEGIN
DECLARE l_sDate DATE;
SET l_sDate = in_sDate;
DROP TABLE IF EXISTS _tblBussinessDays;
DROP TABLE IF EXISTS _tblHolidaysDays;
CREATE TEMPORARY TABLE _tblBussinessDays(BussinessDays date);
CREATE TEMPORARY TABLE _tblHolidaysDays(Offdays date);
IF ( in_sDate > in_eDate ) THEN
SELECT "Invalid dates supplied";
END IF;
WHILE l_sDate <= in_eDate DO IF (DAYNAME(l_sDate) = 'Sunday' ) THEN INSERT INTO _tblHolidaysDays VALUES(l_sDate); ELSEIF ( DAYNAME(l_sDate) = 'Saturday' ) THEN INSERT INTO _tblHolidaysDays VALUES(l_sDate); ELSE INSERT INTO _tblBussinessDays VALUES(l_sDate); END IF; SET l_sDate = DATE_ADD(l_sDate,INTERVAL 1 DAY); END WHILE; SELECT BussinessDays FROM _tblBussinessDays; SELECT Offdays FROM _tblHolidaysDays; END$$ DELIMITER ;


## Now call above procedure to get a list of Bussiness days/Off days

call uGetBussinessDays('2009-06-01','2009-06-11');

BussinessDays
==========
2009-06-01
2009-06-02
2009-06-03
2009-06-04
2009-06-05
2009-06-08
2009-06-09
2009-06-10
2009-06-11

Offdays
======
2009-06-06
2009-06-07

MySQL - Exporting data as CSV or TSV

The example below shows how to export the entire data from a table into a CSV/TSV file.

# Generate CSV file

use database_name;

SELECT *
FROM table_name
INTO OUTFILE ‘/path/to/dumpfile.csv’
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

# Generate TSV file

use database_name;

SELECT *
FROM table_name
INTO OUTFILE ‘/path/to/dumpfile.tsv’
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

If you need customized/filtered data (filtering data with where condition) then the SQL command is as follows


# Generate CSV file

use database_name;

SELECT *
FROM table_name
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

INTO OUTFILE ‘/path/to/dumpfile.csv’
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

# Generate TSV file

use database_name;

SELECT *
FROM table_name
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
INTO OUTFILE ‘/path/to/dumpfile.tsv’
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

MySQL - How to remove Slow query log, Error log , General query log and InnoDB Log files

Platform: Linux/Unix

MySQL Server can create a number of different log files that make it easy to see what is going on. However, you must clean up these files regularly to ensure that the logs do not take up too much disk space.

To remove the 'error log', 'general query log' or 'slow query log', You can just remove the related files manually and then re-start the logs in MySQL by executing the following command at the command-line

shell>mysqladmin flush-logs

Normally no one removes InnoDB log files but sometimes if you need to re-size the log files then the approach is quite different then as shown above for other logs.

Purge Binary Logs

If you have enabled binary logging for the point-in-time recovery (or using replication in your environment) option and forgot to purge it then it may eat up memory in no time, so to “purge” binary logs from production server follow the steps(depending upon your environment)

Simple Environment (No replication etc)

(1) List the current binary logs using “show master logs”/ “show binary logs” SQL command

mysql> SHOW BINARY LOGS;
+---------------+-----------+
Log_name File_size
+---------------+-----------+
xyz-bin.000015 724935
xyz-bin.000016 733481


xyz-bin.000020 733481
+---------------+-----------+

(2) Run PURGE BINARY LOGS statement deletes all the binary log files up to listed in the log index file prior to the specified log file name xyz-bin.000019 (Can also be done for date)

mysql>PURGE BINARY LOGS TO ‘xyz-bin.000019’;

(3) Confirm whether or not files have been deleted

mysql> SHOW BINARY LOGS;
+---------------+-----------+
Log_name File_size
+---------------+-----------+
xyz-bin.000019 724935
xyz-bin.000020 733481
+---------------+-----------+


Complex Environment (Active/dormant Slaves)

If you have an active slave that currently is reading one of the logs you are trying to delete, PURGE BINARY LOGS statement does nothing and fails with an error. However, if a slave is dormant and you happen to purge one of the logs it has yet to read, the slave will be unable to replicate after it comes up.

To safely purge logs, follow this procedure:

1. Suppose we have only one slave, use SHOW SLAVE STATUS to check which log it is reading.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3306
Connect_Retry: 3
Master_Log_File: xyz-bin.000020
Read_Master_Log_Pos: 79
Relay_Log_File: abc-relay-bin.009
Relay_Log_Pos: 548
Relay_Master_Log_File: xyz-bin.000020

2. Obtain a listing of the binary logs on the master server with SHOW BINARY LOGS

mysql> SHOW BINARY LOGS;
+---------------+-----------+
Log_name File_size
+---------------+-----------+
xyz-bin.000015 724935
xyz-bin.000016 733481

xyz-bin.000020 733481
+---------------+-----------+

3. Determine the earliest log among all the slaves. This is the target log xyz-bin.000019. If all the slaves are up to date, this is the last log on the list.
4. Make a backup of all the logs you are about to delete. (This step is optional, but always advisable.)
5. Purge all logs up to but not including the target log.
Run PURGE BINARY LOGS statement deletes all the binary log files up to listed in the log index file prior to the specified log file name xyz-bin.000019

mysql>PURGE BINARY LOGS TO ‘xyz-bin.000019’;

Automatically purge binary logs:

You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days. If you are using replication, you should set the variable no lower than the maximum number of days your slaves might lag behind the master.

MySQL - Shell command to list installed MySQL packages

To know which MySQL packages have been installed on linux environment then the command is

shell>rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" grep -i mysql

MySQL - Unfolding System error codes using perror

If MySQL encounters any system errors the system error code in one of the following styles:

message ... (errno: #)
message ... (Errcode: #)

You can find out what the error code means by examining the documentation for your system or by using the perror utility.
perror prints a description for a system error code or for a storage engine (table handler) error code.
Invoke perror like this:

shell> perror [options] errorcode ...
Example:
C:\ushastry\MySQL_Ent\bin>perror 2
OS error code 2: No such file or directory
Win32 error code 2: The system cannot find the file specified.

C:\ushastry\MySQL_Ent\bin>perror 3
OS error code 3: No such process
Win32 error code 3: The system cannot find the path specified.

MySQL's Point-in-Time Recovery option

If your MySQL server is started with the --log-bin option to enable binary logging, you can use the mysqlbinlog utility to recover data from the binary log files, starting from a specified point in time (for example, since your last backup) until the present or another specified point in time.

To restore data from a binary log, you must know the location and name of the current binary log file. By default, the server creates binary log files in the data directory, but a path name can be specified with the --log-bin option to place the files in a different location. Typically the option is given in an option file (that is, my.cnf or my.ini, depending on your system). It can also be given on the command line when the server is started. To determine the name of the current binary log file, issue the following statement:

You can check the possible options and complete steps in the online manual

http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html

Relocating MySQL DATADIR under Unix -

To relocate a database directory under Unix, use the following procedure:

a) Bring down MySQL server if it is running
b) Create the directories that will be new datadir
c) chown the directory to the mysql:mysql user
d) copy the files from the old datadir to the new location. (If you are using InnoDB then make sure that the files named ib_log* etc. are not copied to the newer location).
e) Make sure that the files and directories are owned by mysql user
f) Make changes in the my.cnf to point to the new datadir.
g) Bring up MySQL server You can verify this by creating a new database and verify that the files for this database are getting created in the new datadir

NOTE: The same procedure applies when you are relocating DATADIR under Windows.