1. Download the correct version.  There are two versions – a tar file and a package.  The package version installs as part of the Solaris OS , but then means we can have no further mysql installs.  So we tend to download the tar version e.g. mysql-advanced-gpl-5.1.47-solaris10-x86_64.tar

1. Put the download into the bin directory eg /ebilldb-master/mysql-bin

1. untar the download file, this creates a directory called mysql-advanced-gpl-5.1.47-solaris10-x86_64

1. change permissions i.e.

chown -R mysql:mysql mysql-advanced-gpl-5.1.47-solaris10-x86_64

1. create a softlink to the untarred directory called mysql 

ln -s mysql-advanced-gpl-5.1.47-solaris10-x86_64 mysql

1. copy a …

ERROR:

091104 11:12:49 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.9339843' (relay_log_pos 233)

091104 11:12:49 [ERROR] Could not find target log during relay log initialization

091104 11:12:49 [ERROR] Failed to initialize the master info structure

Due to the way in which the master and slaves where stopped  the relay logs got out of sync, hence replication would not restart.

Found the solution was to

a) make a note of Master_Log_File and Exec_Master_Log_Pos

mysql> show slave status\G

*************************** 1. row ***************************

             Slave_IO_State:

…

PROVSERVICE table on VMS ANYPROV has a column which is auto increment

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

| Field               | Type         | Null | Key | Default             | Extra          |

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

| PROVSERVICEID       | int(11)      | NO   | PRI | NULL                | auto_increment |

Development were getting an error suggesting an issue with the auto increment column

…

http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html#resetting-permissions-unix

pkg_delete -x mysql

It is not clear what caused this, but this is how it was fixed – eventually!  The main issue that resulted from the failure was that the database would not restart and there were 1000's of relay log files in the $DATADIR

• Killed all mysql processes and mysqld processes – had to use kill -9 for mysqld process

• Removed all relay bin logs from $DATADIR

• Restarted mysql using /usr/local/mysql/bin/mysqld_safe &

• Stopped slave

• Systems team stop mail

• Recreated slave from master (oook)

• Connect to database and issue 'flush logs'

• Set master log file and llog file position

• Restart slave …

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