Mysql

From Wiki.voidnet.us

Jump to: navigation, search

Contents

[edit] reset MySQL root password 

 /etc/rc.d/init.d/mysql stop
 /usr/bin/safe_mysqld --skip-grant-tables &
 mysql -u root

 UPDATE user SET Password=PASSWORD("password") WHERE User="root";
 exit

killall mysql processes 

 /etc/rc.d/init.d/mysql start

[edit] MySQL db user access 

 mysql> GRANT ALL PRIVILEGES ON thedbname.* TO 'monty'@'localhost'
   ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
 mysql> GRANT ALL PRIVILEGES ON thedbname.* TO 'monty'@'%'
   ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
 flush privileges;

[edit] Fix MySIAM db tables

stop db 

 myisamchk -r *.MYI

start db

[edit] Slow logins for MySQL

turn off lookups: 

 [mysqld]
 skip-name-resolve

[edit] MySQL slow queries 

 log-slow-queries = /var/log/mysql-slow.log

--MySQL 3.23-- 

 set-variable=long_query_time=2
 log-long-format
 log-slow-queries = /var/log/slow-queries.log

[edit] MySQL foo for broken mchk 

 [mysql_safe]
 open_files_limit=65536
 [mysqld]
 table_cache=2048

[edit] UDFs don't work

UDFs in MySQL stop working, check strace for malloc() error, reduce buffer size

[edit] Rotate binlogs 

 expire_logs_days = 7
Retrieved from "http://wiki.voidnet.us/index.php/Mysql"
Personal tools