MySQL Server Links

General

MySQL official website
MySQL 5.0 Changes
MySQL local full backup using mysqlhotcopy
Log file maintenance
InnoDB: Forcing recovery
Use force recovery upto mode 4 if encounter similar problem:
InnoDB: Fatal error: cannot read from file. OS error number 17
Enable InnoDB support in MySQL 3.23
For minimal, add to the [mysqld] section the line: innodb_data_file_path=ibdata:10M:autoextend
MySQL InnoDB foreign key must have an associate index else it won’t work (the table won’t create)
Change the auto increment value of an existing table
ALTER TABLE YourTable AUTO_INCREMENT=10000;
MySQL 4 and Unicode problem
Maybe the default binary does not has Unicode support compiled
Compile MySQL 4 in RedHat 8
need these packages or more: gcc-c++, libstdc++-devel, ncurses-devel
Create a case sensitive query
1. if you defined the column binary
2. you cast the field to type binary (SELECT * FROM members where userID = binary “username")
FogBugz – MySQL Setting up Full-Text Search Index

Troubleshooting

Login error after used MySQL 4.1
Password format changed in MySQL 4.1. Add this into /etc/my.cnf, reset the password again.
```
[mysqld]
old-passwords
```
Reference: MySql 4.1 A Few Important Features To Consider
Client does not support authentication protocol
Reset the password with: SET PASSWORD FOR ‘some_user’@’some_host’ = OLD_PASSWORD(‘newpwd’);
A.2.3 Client does not support authentication protocol
050725 15:43:56 Warning: Asked for 196608 thread stack, but got 126976. InnoDB: Error: pthread_create returned 11
You may have SELinux turned on. Check /var/log/messages, try to reconfig your policy. Or simply disable SELinux with: /usr/sbin/setenforce 0 and edit the /etc/sysconfig/selinux

Restore the database without foreign key constraint:

SET FOREIGN_KEY_CHECKS = 0;
SOURCE /path/backupfile.sql;
SET FOREIGN_KEY_CHECKS = 1;

Tools

Documentation

MySQL 5.0 Manual – Functions and Operators
Searchable MySQL Manual with user comments
MySQL HTML Manual
MySQL CREATE INDEX syntax
Fulltext index only available in MyISAM tables, and does not work well with multi-byte characters, can work with utf-8
MySQL InnoDB startup options
InnoDB benchmark results
Add to [mysqld] section of the config file: set-variable=lower_case_table_names=1, to enforce lower case table name, for compatibility between Windows & UNIX version
MySQL User Account Management
- SQL to set the initial password for root@localhost:
  SET PASSWORD FOR ‘root’@’localhost’ = OLD_PASSWORD(‘password’);
- SQL to set the initial password for root@%:
  GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;
- Create a Unicode UTF-8 charset database in MySQL 4.1.10:
  create database ez1 default character set utf8;

Vincent's Webspace