Sql
Jump to navigation
Jump to search
Grundlagen
dump all databases
- http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
- http://stefankonarski.de/content/mysqldump
- http://stefankonarski.de/content/mysqldump-die-option-opt
mysqldump -u root -ppass --opt --all-databases > /root/db_dump.sql
User anlegen
GRANT ALL PRIVILEGES ON DATABASE.* TO 'USER'@'localhost' IDENTIFIED BY 'PASSWD' WITH GRANT OPTION;
Reset root PW
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
#Stop the MySQL Server.
/etc/init.d/mysql stop
#Start the mysqld configuration.
mysqld --skip-grant-tables &
#Replace YOURNEWPASSWORD with your new password!
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
InnoDB Recovery Mode
innodb_force_recovery = 6
Performance Issues
Index Fragmentierung
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
- http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm
- http://www.sql-server-performance.com/2006/detect-fragmentation-sql2000-sql2005/
Reindex
USE pubs
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO