MySQL/mysqld: Unterschied zwischen den Versionen
Aus SchnallIchNet
< MySQL
Cbs (Diskussion | Beiträge) K (hat „MySQL“ nach „MySQL/mysqld“ verschoben) |
Cbs (Diskussion | Beiträge) |
||
Zeile 1: | Zeile 1: | ||
__FORCETOC__ | __FORCETOC__ | ||
+ | |||
==Performance Tuning== | ==Performance Tuning== | ||
+ | |||
<pre> | <pre> | ||
[mysqld] | [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.160.5 | |
− | + | 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 | ||
</pre> | </pre> | ||
Zeile 44: | Zeile 78: | ||
</pre> | </pre> | ||
− | ==Recover a MySQL root password | + | |
− | Stop the MySQL server process. <br/> | + | == Recover a MySQL root password == |
− | Start again with no grant tables. <br/> | + | |
− | Login to MySQL as root. <br/> | + | # Stop the MySQL server process. <br/> |
− | Set new password. <br/> | + | # Start again with no grant tables. <br/> |
− | Exit MySQL and restart MySQL server. | + | # Login to MySQL as root. <br/> |
+ | # Set new password. <br/> | ||
+ | # Exit MySQL and restart MySQL server. | ||
+ | |||
<pre> | <pre> | ||
# /etc/init.d/mysql stop | # /etc/init.d/mysql stop | ||
Zeile 62: | Zeile 99: | ||
</pre> | </pre> | ||
− | ==hotcopy backups== | + | |
+ | == hotcopy backups == | ||
+ | |||
/usr/bin/mysqlhotcopy -u root -p My2Secure$Password sugarcrm /home/backup/database --allowold --keepold | /usr/bin/mysqlhotcopy -u root -p My2Secure$Password sugarcrm /home/backup/database --allowold --keepold | ||
− | ==Siehe auch== | + | |
+ | == 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: | ||
+ | # you're not editing the my.cnf file and potentially permanently turning on logging | ||
+ | # 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 | ||
+ | # restarting the server leaves you where you started (log is off) | ||
+ | |||
+ | |||
+ | == Siehe auch == | ||
+ | |||
# [[MySQL/Syntax|Syntax]] --> MySQL Syntax beispiele | # [[MySQL/Syntax|Syntax]] --> MySQL Syntax beispiele | ||
# [[MySQL/Programming|Programming]] --> Functions, Trigger & Co. | # [[MySQL/Programming|Programming]] --> Functions, Trigger & Co. |
Version vom 2. November 2011, 12:52 Uhr
Inhaltsverzeichnis
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.160.5 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
- 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
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:
- you're not editing the my.cnf file and potentially permanently turning on logging
- 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
- restarting the server leaves you where you started (log is off)
Siehe auch
- Syntax --> MySQL Syntax beispiele
- Programming --> Functions, Trigger & Co.