backup_dbs_anyprov.sh

#!/bin/sh

#script to take consistent backup of ANYPROV database

date=`/bin/date +"%d-%m-%y-%H"`

umask 066

( /usr/local/bin/mysqldump -uroot –p** --databases ANYPROV --lock-all-tables | gzip) > /var/mysql-backups/anyprov-$date-`hostname -s`.sql.gz 2> /var/mysql-backups/anyprov-$date-`hostname -s`.sql.err.log

B. Take a no-data DDL dump of selected tables from a single database :

backup_dbs_anyprov_nodata.sh

#!/bin/sh

#script to take no-data backup of ANYPROV database selected tables

date=`/bin/date +"%d-%m-%y-%H"`

umask 066

( /usr/local/bin/mysqldump -uroot –p*** --no-data ANYPROV PROVSERVICEVW REPORTGEN | gzip) > /var/mysql-backups/anyprovnd-$date-`hostname -s`.sql.gz 2> …

To make life easier, this profile needs to be created on every MySQL server we log on.

Connect as root and create the following file with correct locations.

cd /

touch /mysql_profile

chmod 600 /mysql_profile

vi /mysql_profile

export ERRORLOG=/local/db/mysql      

export DATADIR=/local/db/mysql      

export MYCNF=/etc

export SCRIPTDIR=

export BACKUPDIR=/var/mysql-backups

# some useful aliases

alias a='tail -500 $ERRORLOG/mk-stxx-1'

alias h='fc -l'

alias j=jobs

alias k='ls -lh'

alias g='egrep -i'

alias l='ls -alhrt'

alias df='df -h'

alias d='ls -lhrt …

SINGLE USER:

To see which grants a user has do

show grants for sdas;

+-----------------------------------------------------------------------------------------------------+

| Grants for sdas@%                                                                                   |

+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'sdas'@'%' IDENTIFIED BY PASSWORD …

/usr/local/bin/mysqldump -uroot --p*** --databases prequal --lock-all-tables | gzip) > /home/backup/rebuild_prequal-`hostname -s`.sql.gz 2> /home/backup/rebuild_prequal-`hostname -s`.sql.err.log

I kept an eye on the database, and it queued all of the inserts until the backup finished, but allowed the selects to go through, an example of processlist is attached. Maybe we should consider this for some of the smaller databases and slaves

Apart from the start/stop commands the below commands show the different commands that can also be used to start and stop mysql daemon especially if you have encountered the following errors when you were using the 'normal' start/stop commands:

ERROR! MySQL manager or server PID file could not be found!

Starting MySQL.................................. ERROR

1: On the server Search for mysqld_safe 

cd /

find . -name mysqld_safe  -print

(ie found : ./usr/local/mysql_5.0.24/mysql-standard-5.0.24a-freebsd6.0-i386/bin/mysqld_safe )

2: To Start mysql daemon:

cd /usr/local/mysql_5.0.24/mysql-standard-5.0.24a-freebsd6.0-i386/bin

./mysqld_safe --user=mysql &

3: To Stop mysql daemon:

cd …

http://www.mysql.com/news-and-events/on-demand-webinars/

090326 12:30:54 [ERROR] Got error 134 when reading table './ANYPROV/ORDERS'

Reference from Mysql Bug Database

http://lists.mysql.org/mysql/51228

perror 134

and get this result:

Unknown error 134

Record was already deleted (or record file crashed)

mysql> check table ORDERS;

+----------------+-------+----------+-------------------------------+

| Table          | Op    | Msg_type | Msg_text                      |

+----------------+-------+----------+-------------------------------+

…

00 08 * * * mysql -uroot -p*** -se 'delete from tacacs.accounting where date < date_sub(now(), interval 90 day);'  > /tmp/delete_accounting.log 2>&1

You can use this script to purge master logs older than the day of the current log.  It checks the slaves are not using the older logs first. 

Some prechecks:

1) create a repl_client user on each slave - i.e

GRANT REPLICATION CLIENT ON *.* TO 'repl_client'@'mk-dbxx-1' IDENTIFIED BY 'r3pl';

2) Put the master and slave user, pwd and hosts in the section below.

3) Set savedays to number of days of logs to keep if more than today+1.

4) Make sure perl DBD and CALC modules are installed.

perl –v , 

instmodsh (and enter l to list modules)

5) The purge statement will not work if the "show master logs;" doesn't match up with the actual log files on disk.  This …

mysql -uroot –p*** -e 'show master logs; purge master logs before date_sub(now(), interval 1 day); show master logs;' 

sample output

+------------------+------------+

| Log_name         | File_size  |

+------------------+------------+

| mysql-bin.000325 | 1073741878 |

| mysql-bin.000326 | 532809491  |

+------------------+------------+

+------------------+------------+

| Log_name         | File_size  |

+------------------+------------+

| mysql-bin.000326 | 532809491  |

+------------------+------------+