MySQL/mysqld

Performance Tuning

[mysqld]
delay-key-write-for-all-tables
skip-locking
skip-innodb
skip-networking
set-variable = key_buffer=128M
set-variable = max_allowed_packet=1M
set-variable = table_cache=384
set-variable = sort_buffer=4M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=16M
set-variable = thread_cache=8

####log = /var/log/mysql/mysql.log
####log-long-format
####log-slow-queries = /tmp/mysql.slow.queries.log
####set-variable = long_query_time = 5

###log-bin
###server-id = 1

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.

Stop the MySQL server process.
Start again with no grant tables.
Login to MySQL as root.
Set new password.
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

Siehe auch

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