mySQL Notes
Scripts
Backup Schemas
more /opt/shared/scripts/backup_mysql.sh
#!/bin/sh
BACKUP_DATE=`date +%Y%m%d`
/usr/bin/mysqldump --password=hdsupply inventory | gzip > /backup/mysql/$BACKUP_
DATE.mysql.gz
Set up for DB Cluster
Mount info:
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
67323516 2688292 61160180 5% /
/dev/cciss/c0d0p1
101086 11730 84137 13% /boot
tmpfs 4154976 0 4154976 0% /dev/shm
nasshare1:/vol/MySQLVol/MySQL2
5242880 21968 5220912 1% /mysql
nasshare2:/vol/NixVol/CDimages
31457280 22257664 9199616 71% /mnt
Mysql Directory contents
[root@localhost os]# cd /mysql/
[root@localhost mysql]# ls -al
total 20568
drwxr-xr-x 7 mysql mysql 4096 Apr 8 09:44 .
drwxr-xr-x 25 root root 4096 Apr 8 09:01 ..
-rw-r—– 1 mysql mysql 10485760 Apr 8 09:44 ibdata1
-rw-r—– 1 mysql mysql 5242880 Apr 8 09:44 ib_logfile0
-rw-r—– 1 mysql mysql 5242880 Apr 7 14:59 ib_logfile1
drwx—— 2 mysql mysql 4096 Apr 7 14:59 mysql
drwx—— 2 mysql mysql 12288 Apr 7 14:59 openser
drwxrwxrwx 10 root root 4096 Apr 14 16:00 .snapshot
drwx—— 2 mysql mysql 4096 Apr 7 14:59 test
drwx—— 2 mysql mysql 4096 Apr 7 14:59 webmeetme
Configuration
[root@localost mysql]# more /etc/my.cnf
[mysqld]
datadir=/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Copy the db files out of /var/lib/mysql to your nfs mount and edit the my.cnf file
DB cluster fails over for this and picks the db up out of nas
ZRM – excellent Open Source backup utility
http://www.zmanda.com/quick-mysql-backup.html
Open Source and the Magic LAMP 