Notes‎ > ‎

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>\\_
Comments