MySQL/mysqld

Performance Tuning

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /dev/shm/mysql
language        = /usr/share/mysql/english

skip-external-locking
bind-address            = 192.168.XXX.ZZZ
key_buffer              = 256M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
max_connections         = 256
table_cache             = 4M
wait_timeout            = 14400
tmp_table_size          = 256M
max_heap_table_size     = 256M
thread_concurrency      = 8
query_cache_limit       = 8M
query_cache_size        = 32M
log_slow_queries        = /var/log/mysql/mysql-slow.log     # <---- development only!! do not use in production mode
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M

innodb_autoextend_increment = 10
innodb_log_file_size        = 50M
innodb_buffer_pool_size     = 256M
innodb_file_per_table

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 20M
sort_buffer_size        = 20M
read_buffer             = 2M
write_buffer            = 2M

[myisamchk]
key_buffer              = 20M
sort_buffer_size        = 20M
read_buffer                     = 2M
write_buffer            = 2M

und alle halbe stunde ein cronJob wegen der Stabilitaet:

/usr/bin/mysql -u mysqlAdminUser --password=geheim --exec='FLUSH TABLES;'

Und dann einmal die woche wegen der Dicken Datenbank folgende 'hardlife' optimierung:

#!/bin/sh

/etc/init.d/mysql stop

cd /var/lib/mysql/$1
myisamchk --verbose --force --recover *.MYI
myisamchk --verbose --sort-index *.MYI
myisamchk --verbose --sort-records=1 *.MYI
myisamchk --verbose --force --recover *.MYI
myisamchk --verbose *.MYI

/etc/init.d/mysql start

Recover a MySQL root password

1. Stop the MySQL server process.
   
2. Start again with no grant tables.
   
3. Login to MySQL as root.
   
4. Set new password.
   
5. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

hotcopy backups

/usr/bin/mysqlhotcopy -u root -p My2Secure$Password sugarcrm /home/backup/database --allowold --keepold

Logging

logging is version dependent...

Version < 5.1.12

add something like that to 'my.cnf':

log = /var/log/mysql/mysql.log

reload the server

Version >= 5.1.12

Additionally, for those blessed with MySQL >= 5.1.12:

SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

take a look at the table mysql.general_log

SELECT * from mysql.general_log;

I prefer this method because:

1. you're not editing the my.cnf file and potentially permanently turning on logging
2. you're not fishing around the filesystem looking for the query log - or even worse, distracted by the need for the perfect destination. /var/log /var/data/log /opt /home/mysql_savior/var
3. restarting the server leaves you where you started (log is off)

Replication

Errors on Slave

if a query was aborted on master and you get an error on the slave like

Query partially completed on the master (error on master: 1317) and was aborted. There is a chance that your master is inconsistent at this point.

try:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Siehe auch

1. Syntax --> MySQL Syntax beispiele
2. Programming --> Functions, Trigger & Co.

external docu

1. Memory Tuning
2. Indexes aso.