MySQL

[partitioning]

raid1/10:log

raid10/50/5:data

transactional database: write back if battery backed controller; otherwise write through

[performance]

Top style view of queries

$ watch -n 1 mysqladmin --user= --password= processlist

[migrate]

Copy a database to another host via SSH

$ mysqldump --add-drop-table --extended-insert --force --log-error=error.log -uUSER -pPASS OLD_DB_NAME | ssh -C user@newhost "mysql -uUSER -pPASS NEW_DB_NAME"

Another approach copying over SSH

$ mysql destdb < <(ssh -C sourceserver.example.com "mysqldump sourcedb")

Using mylvmbackup to take a snapshot of the data volume, tar and copying over NFS to a remote host

# mount remote.host:/data/backup /mnt/backup

# vi /etc/mylvmbackup.conf

# mylvmbackup

[tips&tricks]

Create an SSH tunnel for accessing your remote MySQL database with a local port

$ ssh -CNL 3306:localhost:3306 user@site.com

Change the console prompt

$ vi .my.cnf

>[client]

>user=

>password=

>socket=

>

>[mysql]

>prompt=MASTER MYSQL [\\u@\\h] ON \\d>\\_