Archive for category MySQL

MySQL – limiting the result returned by Show Processlist

Use /P (pager) to limit the result you wish to see.

For example, if you wish to see all processes except the ones sleeping then do the following:


mysql> \P grep -v Sleep
mysql> show processlist;

No Comments

MySQL innodb_flush_log_at_trx_commit parameter

Setting innodb_flush_log_at_trx_commit in my.cnf file can have huge impact to the performance of your MySQL server. The impact of setting this parameter is stated below:

innodb_flush_log_at_trx_commit=X

  • Setting X to 2: causes the log buffer to be written to the log file after each commit, but file writes are flushed to disk approximately once a second. This is somewhat slower than a setting of 0. However the committed changes will not be lost if it is only the MySQL server that crashes and not the operating system or server host. In this scenario the machine continues to run, so the changes written to the log file are in the filesystem cache and eventually will be flushed normally.

  • Setting X to 1: changes are written from log buffer to the log file and the log file is flushed to disk for each commit. This guarantees that the changes will not be lost even in the event of a crash. This is the safest setting, and is also the required setting if you need ACID durability. It is also the slowest setting.

  • Setting X to 0: causes the log file to be written and flushed to disk approximately once a second, but not after each commit. on a busy system, this can reduce log-related disk activity significantly, but in the event of a crash can result in loss of about a second’s worth of commited changes.

No Comments

MySQL – Return table creation time along with other information

Quick query to return the time of creation along with some other info for a table:

select TABLE_NAME, TABLE_TYPE,  ENGINE, TABLE_ROWS, CREATE_TIME from TABLES where TABLE_NAME = 'my_table';

No Comments

MySQL – Backup Script

Great Backup script.

#!/bin/bash

  1. Shell script to backup MySql database
  2. To backup Nysql databases file to /backup dir and later pick up by your
  3. script. You can skip few databases from backup too.
  4. For more info please see (Installation info):
  5. http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html
  6. Last updated: Aug - 2005
  7. --------------------------------------------------------------------
  8. This is a free shell script under GNU GPL version 2.0 or above
  9. Copyright (C) 2004, 2005 nixCraft project
  10. Feedback/comment/suggestions : http://cyberciti.biz/fb/
  11. -------------------------------------------------------------------------
  12. This script is part of nixCraft shell script collection (NSSC)
  13. Visit http://bash.cyberciti.biz/ for more information.
  14. -------------------------------------------------------------------------

MyUSER="SET-MYSQL-USER-NAME" # USERNAME
MyPASS="SET-PASSWORD" # PASSWORD
MyHOST="localhost" # Hostname

  1. Linux bin paths, change this if it can not be autodetected via which command
    MYSQL="$(which mysql)"
    MYSQLDUMP="$(which mysqldump)"
    CHOWN="$(which chown)"
    CHMOD="$(which chmod)"
    GZIP="$(which gzip)"
  1. Backup Dest directory, change this if you have someother location
    DEST="/backup"
  1. Main directory where backup will be stored
    MBD="$DEST/mysql"
  1. Get hostname
    HOST="$(hostname)"
  1. Get data in dd-mm-yyyy format
    NOW="$(date +"%d-%m-%Y")"
  1. File to store current backup file
    FILE=""
  2. Store list of databases
    DBS=""
  1. DO NOT BACKUP these databases
    IGGY="test"

[ ! -d $MBD ] && mkdir -p $MBD || :

  1. Only root can access it!
    $CHOWN 0.0 -R $DEST
    $CHMOD 0600 $DEST
  1. Get all database list first
    DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

for db in $DBS
do

skipdb=-1 if [ "$IGGY" != "" ]; then
for i in $IGGY
do [ "$db" "$i" ] && skipdb=1 || :
done fi if [ "$skipdb" “-1” ] ; then
FILE=”$MBD/$db.$HOST.$NOW.gz”
  1. do all inone job in pipe,
  2. connect to mysql using mysqldump for select mysql database
  3. and pipe it out to gz file in backup dir :)
$MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE fi
done

Cron Job:
@daily /path/to/yourmysql.sh

No Comments

MySQL: Backup and Restore

Backup:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

* [uname] Your database username
* [pass] The password for your database (note there is no space between -p and the password)
* [dbname] The name of your database
* [backupfile.sql] The filename for your database backup
* [--opt] The mysqldump option

To backup all the databases use:
$ mysqldump <del>u root -p --all</del>databases > [alldb_backup.sql]

Restore:
$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

How to Backup and Restore databases:
http://www.webcheatsheet.com/SQL/mysql_backup_restore.php

No Comments

MySQL: Securing a new install

To set the initial root password open a mysql prompt, mysql -u root mysql, and enter the following:

mysql> UPDATE user SET Password=PASSWORD('new_password')
-> WHERE user='root';
mysql> FLUSH PRIVILEGES;

Don’t forget to FLUSH PRIVILEGES; to make the privileges effective.

As well as setting the root password, we should remove anonymous accounts:

mysql> DELETE FROM user WHERE User = '';
mysql> FLUSH PRIVILEGES;

Alternatively set a password for the anonymous accounts:

mysql> UPDATE user SET Password = PASSWORD('new_password')
-> WHERE User = '';
mysql> FLUSH PRIVILEGES;

MySQL official website:
http://dev.mysql.com/doc/refman/5.0/en/server-administration.html

Great link on how to secure a MySQL install:
http://www.ice2o.com/secure_mysql.php

No Comments

MySQL – how to add users?

MySQL’s official website:

http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

No Comments

How to shutdown MySQL server on Linux OS?

Use one of the following commands:

  • mysqladmin -u root -ppassword shutdownPREFERRED

  • /etc/rc.d/init.d/mysqld stop

  • service mysqld stop

No Comments