Category: MySQL

Changing root password in mysql

Post author By powercram
Post date August 1, 2009
No Comments on Changing root password in mysql

Changing root password in mysql

http://www.cyberciti.biz/faq/mysql-change-root-password/

mysqladmin command to change root password

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows:

$ mysqladmin -u root password NEWPASSWORD

However if you want to change (or update) a root password, then you need to use following command:

$ mysqladmin -u root -p oldpassword newpass

Enter password:
Change MySQL password for other user

To change a normal user password you need to type (let us assume you would like to change password for vivek):

$ mysqladmin -u vivek -p oldpassword newpass

Changing MySQL root user password using mysql sql command

This is another method. MySQL stores username and passwords in user table inside MySQL database. You can directly update password using the following method to update or change password for user vivek:

1) Login to mysql server, type following command at shell prompt:

$ mysql -u root -p

2) Use mysql database (type command at mysql> prompt):

mysql> use mysql;

3) Change password for user vivek:

mysql> update user set password=PASSWORD(“NEWPASSWORD”) where User=’vivek’;

4) Reload privileges:

mysql> flush privileges;
mysql> quit

Also, you can RECOVER mysql password here:

tep # 1 : Stop mysql service

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe –skip-grant-tables &
Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where User=’root’;
mysql> flush privileges;
mysql> quit
Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+ Done mysqld_safe –skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

Linux MySQL Passwords Windows

MySQL Crib Sheet Cheat Sheet

Post author By powercram
Post date July 30, 2009
No Comments on MySQL Crib Sheet Cheat Sheet

To login (from unix shell) use -h only if needed.
[mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.
create database [databasename];

List all databases on the sql server.
show databases;

Switch to a database.
use [db name];

To see all the tables in the db.
show tables;

To see database’s field formats.
describe [table name];

To delete a db.
drop database [database name];

To delete a table.
drop table [table name];

Show all data in a table.
SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.
show columns from [table name];

Show certain selected rows with the value “whatever”.
SELECT * FROM [table name] WHERE [field name] = “whatever”;

Show all records containing the name “Bob” AND the phone number ‘3444444’.
SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ‘3444444’;

Show all records not containing the name “Bob” AND the phone number ‘3444444’ order by the phone_number field.
SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ‘3444444’ order by phone_number;

Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’.
SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444’;

Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.
SELECT * FROM [table name] WHERE rec RLIKE “^a$”;

Show unique records.
SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).
SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.
SELECT COUNT(*) FROM [table name];

Sum column.
SELECT SUM(*) FROM [table name];

Join tables on common columns.
select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Switch to the mysql db. Create a new user.
INSERT INTO [table name] (Host,User,Password) VALUES(‘%’,’user’,PASSWORD(‘password’));

Change a users password.(from unix shell).
[mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password ‘new-password’;

Change a users password.(from MySQL prompt).
SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);

Allow the user “bob” to connect to the server from localhost using the password “passwd”
grant usage on *.* to bob@localhost identified by ‘passwd’;

Give user privilages for a db.
grant all privileges on databasename.* to username@localhost;

To update info already in a table.
UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

Delete a row(s) from a table.
DELETE from [table name] where [field name] = ‘whatever’;

Update database permissions/privilages.
FLUSH PRIVILEGES;

Delete a column.
alter table [table name] drop column [column name];

Add a new column to db.
alter table [table name] add column [new column name] varchar (20);

Change column name.
alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.
alter table [table name] add unique ([column name]);

Make a column bigger.
alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.
alter table [table name] drop index [colmn name];

Load a CSV file into a table.
LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’ (field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db’s.
[mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

Dump one database for backup.
[mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql

Dump a table from a database.
[mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.
[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table Example 1.
CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR (15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table Example 2.
create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastname varchar(50) default ‘bato’);

Here’s a list of the commands to control mysql:
service mysqld restart
service mysqld status
service mysqld stop
service mysqld start

Grep Linux MySQL Passwords Windows Windows 2003

MySQL Backup Shell Script

Post author By powercram
Post date July 27, 2009
No Comments on MySQL Backup Shell Script

***TO CREATE BACKUP SCRIPT ON LOCAL SERVER
mkdir /root/bin
vi /root/bin/mysqlbak.sh (then copy script into this file)
change parameters in script to match database
chmod 755 /root/bin/mysqlback.sh

***TO VERIFY SCRIPT WORKS
1. run this command
/root/bin/mysqlbak.sh
2. when script is done, check subdirs of /var/backup/db/daily for tarred & gzipped backups

***TO SCHEDULE THIS TO RUN DAILY, CREATE SYMBOLIC LINK
ln -s /root/bin/mysqlbak.sh /etc/cron.daily/mysqlbak.sh

***TO RESTORE
mysql -uUSER -pPASSWORD DBNAME_RESTORE < DBNAME_BACKUPFILE.sql

#!/bin/bash
#
# MySQL Backup Script
# VER. 2.5 – http://sourceforge.net/projects/automysqlbackup/
# Copyright (c) 2002-2003 wipe_out@lycos.co.uk
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
#=====================================================================
#=====================================================================
# Set the following variables to your system needs
# (Detailed instructions below variables)
#=====================================================================

# crontab entry that will work for this script
# 57 5 * * 2,3,4,5,6 /root/bin/mysqlbackup.sh

# Username to access the MySQL server e.g. dbuser
USERNAME=

# Username to access the MySQL server e.g. password
PASSWORD=

# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3” – you can put “all” as the value to backup all

DBNAMES=” mysql phpMyAdmin”
# Backup directory location e.g /var/www/backup/db
BACKUPDIR=”/var/backup/db”

# Mail setup
# What would you like to be mailed to you?
# – log : send only log file
# – files : send log file and sql files as attachments (see docs)
# – stdout : will simply output the log to the screen if run manually.
# – quiet : Only send logs if an error occurs to the MAILADDR.
MAILCONTENT=”log”

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
MAXATTSIZE=”4000″

# Email Address to send mail to? (user@domain.com)
MAILADDR=”backups@soundenterprises.com”

# ============================================================
# === ADVANCED OPTIONS ( Read the doc’s below for details )===
#=============================================================

# List of DBBNAMES for Monthly Backups.
MDBNAMES=”mysql $DBNAMES”

# List of DBNAMES to EXCLUDE if DBNAMES are set to all (must be in ” quotes)
DBEXCLUDE=””

# Include CREATE DATABASE in backup?
CREATE_DATABASE=yes

# Separate backup directory and file for each DB? (yes or no)
SEPDIR=yes

# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
DOWEEKLY=7

# Choose Compression type. (gzip or bzip2)
COMP=gzip

# Compress communications between backup server and MySQL server?
COMMCOMP=no

# Additionally keep a copy of the most recent backup in a seperate directory.
LATEST=no

# The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)
MAX_ALLOWED_PACKET=

# For connections to localhost. Sometimes the Unix socket file must be specified.
SOCKET=

# Command to run before backups (uncomment to use)
#PREBACKUP=”/etc/mysql-backup-pre”

# Command run after backups (uncomment to use)
#POSTBACKUP=”/etc/mysql-backup-post”

#=====================================================================
# Options documantation
#=====================================================================
# Set USERNAME and PASSWORD of a user that has at least SELECT permission
# to ALL databases.
#
# Set the DBHOST option to the server you wish to backup, leave the
# default to backup “this server”.(to backup multiple servers make
# copies of this file and set the options for that server)
#
# Put in the list of DBNAMES(Databases)to be backed up. If you would like
# to backup ALL DBs on the server set DBNAMES=”all”.(if set to “all” then
# any new DBs will automatically be backed up without needing to modify
# this backup script when a new DB is created).
#
# If the DB you want to backup has a space in the name replace the space
# with a % e.g. “data base” will become “data%base”
# NOTE: Spaces in DB names may not work correctly when SEPDIR=no.
#
# You can change the backup storage location from /backups to anything
# you like by using the BACKUPDIR setting..
#
# The MAILCONTENT and MAILADDR options and pretty self explanitory, use
# these to have the backup log mailed to you at any email address or multiple
# email addresses in a space seperated list.
# (If you set mail content to “log” you will require access to the “mail” program
# on your server. If you set this to “files” you will have to have mutt installed
# on your server. If you set it to “stdout” it will log to the screen if run from
# the console or to the cron job owner if run through cron. If you set it to “quiet”
# logs will only be mailed if there are errors reported. )
#
# MAXATTSIZE sets the largest allowed email attachments total (all backup files) you
# want the script to send. This is the size before it is encoded to be sent as an email
# so if your mail server will allow a maximum mail size of 5MB I would suggest setting
# MAXATTSIZE to be 25% smaller than that so a setting of 4000 would probably be fine.
#
# Finally copy automysqlbackup.sh to anywhere on your server and make sure
# to set executable permission. You can also copy the script to
# /etc/cron.daily to have it execute automatically every night or simply
# place a symlink in /etc/cron.daily to the file if you wish to keep it
# somwhere else.
# NOTE:On Debian copy the file with no extention for it to be run
# by cron e.g just name the file “automysqlbackup”
#
# Thats it..
#
#
# === Advanced options doc’s ===
#
# The list of MDBNAMES is the DB’s to be backed up only monthly. You should
# always include “mysql” in this list to backup your user/password
# information along with any other DBs that you only feel need to
# be backed up monthly. (if using a hosted server then you should
# probably remove “mysql” as your provider will be backing this up)
# NOTE: If DBNAMES=”all” then MDBNAMES has no effect as all DBs will be backed
# up anyway.
#
# If you set DBNAMES=”all” you can configure the option DBEXCLUDE. Other
# wise this option will not be used.
# This option can be used if you want to backup all dbs, but you want
# exclude some of them. (eg. a db is to big).
#
# Set CREATE_DATABASE to “yes” (the default) if you want your SQL-Dump to create
# a database with the same name as the original database when restoring.
# Saying “no” here will allow your to specify the database name you want to
# restore your dump into, making a copy of the database by using the dump
# created with automysqlbackup.
# NOTE: Not used if SEPDIR=no
#
# The SEPDIR option allows you to choose to have all DBs backed up to
# a single file (fast restore of entire server in case of crash) or to
# seperate directories for each DB (each DB can be restored seperately
# in case of single DB corruption or loss).
#
# To set the day of the week that you would like the weekly backup to happen
# set the DOWEEKLY setting, this can be a value from 1 to 7 where 1 is Monday,
# The default is 6 which means that weekly backups are done on a Saturday.
#
# COMP is used to choose the copmression used, options are gzip or bzip2.
# bzip2 will produce slightly smaller files but is more processor intensive so
# may take longer to complete.
#
# COMMCOMP is used to enable or diable mysql client to server compression, so
# it is useful to save bandwidth when backing up a remote MySQL server over
# the network.
#
# LATEST is to store an additional copy of the latest backup to a standard
# location so it can be downloaded bt thrid party scripts.
#
# If the DB’s being backed up make use of large BLOB fields then you may need
# to increase the MAX_ALLOWED_PACKET setting, for example 16MB..
#
# When connecting to localhost as the DB server (DBHOST=localhost) sometimes
# the system can have issues locating the socket file.. This can now be set
# using the SOCKET parameter.. An example may be SOCKET=/private/tmp/mysql.sock
#
# Use PREBACKUP and POSTBACKUP to specify Per and Post backup commands
# or scripts to perform tasks either before or after the backup process.
#
#
#=====================================================================
# Backup Rotation..
#=====================================================================
#
# Daily Backups are rotated weekly..
# Weekly Backups are run by default on Saturday Morning when
# cron.daily scripts are run…Can be changed with DOWEEKLY setting..
# Weekly Backups are rotated on a 5 week cycle..
# Monthly Backups are run on the 1st of the month..
# Monthly Backups are NOT rotated automatically…
# It may be a good idea to copy Monthly backups offline or to another
# server..
#
#=====================================================================
# Please Note!!
#=====================================================================
#
# I take no resposibility for any data loss or corruption when using
# this script..
# This script will not help in the event of a hard drive crash. If a
# copy of the backup has not be stored offline or on another PC..
# You should copy your backups offline regularly for best protection.
#
# Happy backing up…
#
#=====================================================================
# Restoring
#=====================================================================
# Firstly you will need to uncompress the backup file.
# eg.
# gunzip file.gz (or bunzip2 file.bz2)
#
# Next you will need to use the mysql client to restore the DB from the
# sql file.
# eg.
# mysql –user=username –pass=password –host=dbserver database < /path/file.sql
# or
# mysql –user=username –pass=password –host=dbserver -e “source /path/file.sql” database
#
# NOTE: Make sure you use “” in the above command because
# you are piping the file.sql to mysql and not the other way around.
#
# Lets hope you never have to use this.. 🙂
#

#
#=====================================================================
#=====================================================================
#=====================================================================
#
# Should not need to be modified from here down!!
#
#=====================================================================
#=====================================================================
#=====================================================================
PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin
DATE=`date +%Y-%m-%d_%Hh%Mm` # Datestamp e.g 2002-09-21
DOW=`date +%A` # Day of the week e.g. Monday
DNOW=`date +%u` # Day number of the week 1 to 7 where 1 represents Monday
DOM=`date +%d` # Date of the Month e.g. 27
M=`date +%B` # Month e.g January
W=`date +%V` # Week Number e.g 37
VER=2.5 # Version Number
LOGFILE=$BACKUPDIR/$DBHOST-`date +%N`.log # Logfile Name
LOGERR=$BACKUPDIR/ERRORS_$DBHOST-`date +%N`.log # Logfile Name
BACKUPFILES=””
OPT=”–quote-names –opt” # OPT string for use with mysqldump ( see man mysqldump )

# Add –compress mysqldump option to $OPT
if [ “$COMMCOMP” = “yes” ];
then
OPT=”$OPT –compress”
fi

# Add –compress mysqldump option to $OPT
if [ “$MAX_ALLOWED_PACKET” ];
then
OPT=”$OPT –max_allowed_packet=$MAX_ALLOWED_PACKET”
fi

# Create required directories
if [ ! -e “$BACKUPDIR” ] # Check Backup Directory exists.
then
mkdir -p “$BACKUPDIR”
fi

if [ ! -e “$BACKUPDIR/daily” ] # Check Daily Directory exists.
then
mkdir -p “$BACKUPDIR/daily”
fi

if [ ! -e “$BACKUPDIR/weekly” ] # Check Weekly Directory exists.
then
mkdir -p “$BACKUPDIR/weekly”
fi

if [ ! -e “$BACKUPDIR/monthly” ] # Check Monthly Directory exists.
then
mkdir -p “$BACKUPDIR/monthly”
fi

if [ “$LATEST” = “yes” ]
then
if [ ! -e “$BACKUPDIR/latest” ] # Check Latest Directory exists.
then
mkdir -p “$BACKUPDIR/latest”
fi
eval rm -fv “$BACKUPDIR/latest/*”
fi

# IO redirection for logging.
touch $LOGFILE
exec 6>&1 # Link file descriptor #6 with stdout.
# Saves stdout.
exec > $LOGFILE # stdout replaced with file $LOGFILE.
touch $LOGERR
exec 7>&2 # Link file descriptor #7 with stderr.
# Saves stderr.
exec 2> $LOGERR # stderr replaced with file $LOGERR.

# Functions

# Database dump function
dbdump () {
mysqldump –user=$USERNAME –password=$PASSWORD –host=$DBHOST $OPT $1 > $2
return 0
}

# Compression function plus latest copy
SUFFIX=””
compression () {
if [ “$COMP” = “gzip” ]; then
gzip -f “$1”
echo
echo Backup Information for “$1”
gzip -l “$1.gz”
SUFFIX=”.gz”
elif [ “$COMP” = “bzip2” ]; then
echo Compression information for “$1.bz2”
bzip2 -f -v $1 2>&1
SUFFIX=”.bz2″
else
echo “No compression option set, check advanced settings”
fi
if [ “$LATEST” = “yes” ]; then
cp $1$SUFFIX “$BACKUPDIR/latest/”
fi
return 0
}

# Run command before we begin
if [ “$PREBACKUP” ]
then
echo ======================================================================
echo “Prebackup command output.”
echo
eval $PREBACKUP
echo
echo ======================================================================
echo
fi

if [ “$SEPDIR” = “yes” ]; then # Check if CREATE DATABSE should be included in Dump
if [ “$CREATE_DATABASE” = “no” ]; then
OPT=”$OPT –no-create-db”
else
OPT=”$OPT –databases”
fi
else
OPT=”$OPT –databases”
fi

# Hostname for LOG information
if [ “$DBHOST” = “localhost” ]; then
HOST=`hostname`
if [ “$SOCKET” ]; then
OPT=”$OPT –socket=$SOCKET”
fi
else
HOST=$DBHOST
fi

# If backing up all DBs on the server
if [ “$DBNAMES” = “all” ]; then
DBNAMES=”`mysql –user=$USERNAME –password=$PASSWORD –host=$DBHOST –batch –skip-column-names -e “show databases”| sed ‘s/ /%/g’`”

# If DBs are excluded
for exclude in $DBEXCLUDE
do
DBNAMES=`echo $DBNAMES | sed “s/b$excludeb//g”`
done

MDBNAMES=$DBNAMES
fi

echo ======================================================================
echo AutoMySQLBackup VER $VER
echo http://sourceforge.net/projects/automysqlbackup/
echo
echo Backup of Database Server – $HOST
echo ======================================================================

# Test is seperate DB backups are required
if [ “$SEPDIR” = “yes” ]; then
echo Backup Start Time `date`
echo ======================================================================
# Monthly Full Backup of all Databases
if [ $DOM = “01” ]; then
for MDB in $MDBNAMES
do

# Prepare $DB for using
MDB=”`echo $MDB | sed ‘s/%/ /g’`”

if [ ! -e “$BACKUPDIR/monthly/$MDB” ] # Check Monthly DB Directory exists.
then
mkdir -p “$BACKUPDIR/monthly/$MDB”
fi
echo Monthly Backup of $MDB…
dbdump “$MDB” “$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql”
compression “$BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql”
BACKUPFILES=”$BACKUPFILES $BACKUPDIR/monthly/$MDB/${MDB}_$DATE.$M.$MDB.sql$SUFFIX”
echo ———————————————————————-
done
fi

for DB in $DBNAMES
do
# Prepare $DB for using
DB=”`echo $DB | sed ‘s/%/ /g’`”

# Create Seperate directory for each DB
if [ ! -e “$BACKUPDIR/daily/$DB” ] # Check Daily DB Directory exists.
then
mkdir -p “$BACKUPDIR/daily/$DB”
fi

if [ ! -e “$BACKUPDIR/weekly/$DB” ] # Check Weekly DB Directory exists.
then
mkdir -p “$BACKUPDIR/weekly/$DB”
fi

# Weekly Backup
if [ $DNOW = $DOWEEKLY ]; then
echo Weekly Backup of Database ( $DB )
echo Rotating 5 weeks Backups…
if [ “$W” -le 05 ];then
REMW=`expr 48 + $W`
elif [ “$W” -lt 15 ];then
REMW=0`expr $W – 5`
else
REMW=`expr $W – 5`
fi
eval rm -fv “$BACKUPDIR/weekly/$DB_week.$REMW.*”
echo
dbdump “$DB” “$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql”
compression “$BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql”
BACKUPFILES=”$BACKUPFILES $BACKUPDIR/weekly/$DB/${DB}_week.$W.$DATE.sql$SUFFIX”
echo ———————————————————————-

# Daily Backup
else
echo Daily Backup of Database ( $DB )
echo Rotating last weeks Backup…
eval rm -fv “$BACKUPDIR/daily/$DB/*.$DOW.sql.*”
echo
dbdump “$DB” “$BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql”
compression “$BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql”
BACKUPFILES=”$BACKUPFILES $BACKUPDIR/daily/$DB/${DB}_$DATE.$DOW.sql$SUFFIX”
echo ———————————————————————-
fi
done
echo Backup End `date`
echo ======================================================================

else # One backup file for all DBs
echo Backup Start `date`
echo ======================================================================
# Monthly Full Backup of all Databases
if [ $DOM = “01” ]; then
echo Monthly full Backup of ( $MDBNAMES )…
dbdump “$MDBNAMES” “$BACKUPDIR/monthly/$DATE.$M.all-databases.sql”
compression “$BACKUPDIR/monthly/$DATE.$M.all-databases.sql”
BACKUPFILES=”$BACKUPFILES $BACKUPDIR/monthly/$DATE.$M.all-databases.sql$SUFFIX”
echo ———————————————————————-
fi

# Weekly Backup
if [ $DNOW = $DOWEEKLY ]; then
echo Weekly Backup of Databases ( $DBNAMES )
echo
echo Rotating 5 weeks Backups…
if [ “$W” -le 05 ];then
REMW=`expr 48 + $W`
elif [ “$W” -lt 15 ];then
REMW=0`expr $W – 5`
else
REMW=`expr $W – 5`
fi
eval rm -fv “$BACKUPDIR/weekly/week.$REMW.*”
echo
dbdump “$DBNAMES” “$BACKUPDIR/weekly/week.$W.$DATE.sql”
compression “$BACKUPDIR/weekly/week.$W.$DATE.sql”
BACKUPFILES=”$BACKUPFILES $BACKUPDIR/weekly/week.$W.$DATE.sql$SUFFIX”
echo ———————————————————————-

# Daily Backup
else
echo Daily Backup of Databases ( $DBNAMES )
echo
echo Rotating last weeks Backup…
eval rm -fv “$BACKUPDIR/daily/*.$DOW.sql.*”
echo
dbdump “$DBNAMES” “$BACKUPDIR/daily/$DATE.$DOW.sql”
compression “$BACKUPDIR/daily/$DATE.$DOW.sql”
BACKUPFILES=”$BACKUPFILES $BACKUPDIR/daily/$DATE.$DOW.sql$SUFFIX”
echo ———————————————————————-
fi
echo Backup End Time `date`
echo ======================================================================
fi
echo Total disk space used for backup storage..
echo Size – Location
echo `du -hs “$BACKUPDIR”`
echo
echo ======================================================================
echo If you find AutoMySQLBackup valuable please make a donation at
echo http://sourceforge.net/project/project_donations.php?group_id=101066
echo ======================================================================

# Run command when we’re done
if [ “$POSTBACKUP” ]
then
echo ======================================================================
echo “Postbackup command output.”
echo
eval $POSTBACKUP
echo
echo ======================================================================
fi

#Clean up IO redirection
exec 1>&6 6>&- # Restore stdout and close file descriptor #6.
exec 1>&7 7>&- # Restore stdout and close file descriptor #7.

if [ “$MAILCONTENT” = “files” ]
then
if [ -s “$LOGERR” ]
then
# Include error log if is larger than zero.
BACKUPFILES=”$BACKUPFILES $LOGERR”
ERRORNOTE=”WARNING: Error Reported – “
fi
#Get backup size
ATTSIZE=`du -c $BACKUPFILES | grep “[[:digit:][:space:]]total$” |sed s/s*total//`
if [ $MAXATTSIZE -ge $ATTSIZE ]
then
BACKUPFILES=`echo “$BACKUPFILES” | sed -e “s# # -a #g”` #enable multiple attachments
mutt -s “$ERRORNOTE MySQL Backup Log and SQL Files for $HOST – $DATE” $BACKUPFILES $MAILADDR < $LOGFILE #send via mutt
else
cat “$LOGFILE” | mail -s “WARNING! – MySQL Backup exceeds set maximum attachment size on $HOST – $DATE” $MAILADDR
fi
elif [ “$MAILCONTENT” = “log” ]
then
cat “$LOGFILE” | mail -s “MySQL Backup Log for $HOST – $DATE” $MAILADDR
if [ -s “$LOGERR” ]
then
cat “$LOGERR” | mail -s “ERRORS REPORTED: MySQL Backup error Log for $HOST – $DATE” $MAILADDR
fi
elif [ “$MAILCONTENT” = “quiet” ]
then
if [ -s “$LOGERR” ]
then
cat “$LOGERR” | mail -s “ERRORS REPORTED: MySQL Backup error Log for $HOST – $DATE” $MAILADDR
cat “$LOGFILE” | mail -s “MySQL Backup Log for $HOST – $DATE” $MAILADDR
fi
else
if [ -s “$LOGERR” ]
then
cat “$LOGFILE”
echo
echo “###### WARNING ######”
echo “Errors reported during AutoMySQLBackup execution.. Backup failed”
echo “Error log below..”
cat “$LOGERR”
else
cat “$LOGFILE”
fi
fi

if [ -s “$LOGERR” ]
then
STATUS=1
else
STATUS=0
fi

# Clean up Logfile
eval rm -f “$LOGFILE”
eval rm -f “$LOGERR”

exit $STATUS

Linux MySQL SSH WPA

Change MySQL root Password

Post author By powercram
Post date July 20, 2009
No Comments on Change MySQL root Password

How do I change MySQL root password under Linux, FreeBSD, OpenBSD and UNIX like operating system over ssh / telnet session?

Setting up mysql password is one of the essential tasks. By default root user is MySQL admin account. Please note that the Linux / UNIX login root account for your operating system and MySQL root are different. They are separate and nothing to do with each other (indeed some admin removes root account and setup admin as mysql super user).

mysqladmin command to change root password

$ mysqladmin -u root password NEWPASSWORD

However, if you want to change (or update) a root password, then you need to use following command

$ mysqladmin -u root -p'oldpassword' password newpass

For example, If old password is abc, and set new password to 123456, enter:

$ mysqladmin -u root -p'abc' password '123456'

Change MySQL password for other user

To change a normal user password you need to type (let us assume you would like to change password for sysadmin):

$ mysqladmin -u sysadmin -p oldpassword password newpass

Changing MySQL root user password using MySQL sql command

$ mysql -u root -p

2) Use mysql database (type command at mysql> prompt):

mysql> use mysql;

3) Change password for user sysadmin:

mysql> update user set password=PASSWORD("NEWPASSWORD") where User='sysadmin';

4) Reload privileges:

mysql> flush privileges;
mysql> quit

This method is to be use while using PHP or Perl scripting.