MySQL mysqld

• Performance Tuning
• Recover a MySQL root password
• hotcopy backups
• Logging
  • Version < 5.1.12
  • Version >= 5.1.12
• Replication
  • Errors on Slave
• Siehe auch
  • external docu

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

On really big databases do cron job every 30 minutes

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

And do a ‘hardlife’ optimization once a week

#!/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 – no password required!
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 the following to ‘my.cnf’ and reload the server

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

Version >= 5.1.12

Additionally, for those blessed with MySQL >= 5.1.12:

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

now 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

• Indexes aso.
• Memory Tuning