MySQL
From Apis Networks Wiki
Contents |
Configuration
All MySQL connections made through any application besides Java should use localhost for the hostname. localhost provides a significant speed advantage over 127.0.0.1. The reason between the two is the overhead of the TCP/IP stack when using the IP address 127.0.0.1; there are limitations in the JDBC driver for Java which require 127.0.0.1 to be used as the hostname.
See MySQL Manager for a listing of language-specific connection snippets.
Connecting
"too many connections" error message
By default, all accounts are given an allocation of 10 concurrent connections, scalable up to 20 concurrent connections. Generally, 10 connections is more than adequate to serve your site. In rare circumstances, your site may require more than 10 connections at a given time, in which case MySQL Manager will allow you to raise the number.
Remember that this number reflects the number of clients issuing queries to the server at any given point in time. This figure does not reflect the current traffic at a site or any other metric besides clients who are, at a given millisecond, sending work to the server and waiting on a response. Many MySQL queries take a few milliseconds to complete.
This limit is imposed as a safety measure to prevent any particular site from monopolizing the connection slots open on a MySQL server if a site goes over quota. In other words, the 10 concurrent connection limit is there for a good reason and you are strongly recommended to not change the value except in a last ditch effort.
Resetting your password
MySQL passwords may be reset under MySQL Manager within the control panel. You should change the password for the user on Host localhost. These accounts are typically that ones used by software applications and phpMyAdmin.
Usage
"table x is marked as crashed"
Table corruption occurs when the database has terminated a client in the middle of a write. An abrupt termination may occur if (1) a site goes over its disk quota [likely], (2) MySQl's faulty quota management leaks and locks all connections out [uncommon], (3) MySQL server dies during query [rate], or (4) the server crashes abruptly [very rare]. In any case, the crashed tables may be repaired by issuing a "REPAIR TABLE" query or the myisamchk utility. Bear in mind that "REPAIR TABLE" honors locking, while myisamchk does not. As a consequence, myisamchk may still result in a corrupted table during repair. Table repairs may be done through phpMyAdmin.
Creating additional databases
Additional databases may be created from within MySQL Manager.
Are stored procedures supported?
Stored procedures by underprivileged users require MySQL 5.1, but at this time MySQL/Sun have made no announcements on a GA release of 5.1.
Backup/Recovery
How can I export a database?
Login to phpMyAdmin, select the relevant database from the left frame, then select the Export tab in the right frame. Larger databases may require the mysqldump command within the shell to dump the database contents. To dump a database named mydb and compress it with gzip, the following command will work, provided your username is foo, password is bar, and database is named mydb:
mysqldump -u foo -pbar mydb | gzip -c > ~/mydb.sql.gz
