MySQL/mysqld: Unterschied zwischen den Versionen
Aus SchnallIchNet
< MySQL
Cbs (Diskussion | Beiträge) |
Cbs (Diskussion | Beiträge) |
||
(3 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt) | |||
Zeile 15: | Zeile 15: | ||
skip-external-locking | skip-external-locking | ||
− | bind-address = 192.168. | + | bind-address = 192.168.XXX.ZZZ |
key_buffer = 256M | key_buffer = 256M | ||
max_allowed_packet = 16M | max_allowed_packet = 16M | ||
Zeile 136: | Zeile 136: | ||
# 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 | # 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) | # 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; | ||
Zeile 142: | Zeile 156: | ||
# [[MySQL/Syntax|Syntax]] --> MySQL Syntax beispiele | # [[MySQL/Syntax|Syntax]] --> MySQL Syntax beispiele | ||
# [[MySQL/Programming|Programming]] --> Functions, Trigger & Co. | # [[MySQL/Programming|Programming]] --> Functions, Trigger & Co. | ||
+ | |||
+ | |||
+ | === external docu === | ||
+ | |||
+ | # [http://mysql.rjweb.org/doc.php/memory Memory Tuning] | ||
+ | # [http://mysql.rjweb.org/doc.php/myisam2innodb Indexes aso.] |
Aktuelle Version vom 31. März 2014, 14:31 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.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
- 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)
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
- Syntax --> MySQL Syntax beispiele
- Programming --> Functions, Trigger & Co.