MySQL Reference
From Zanecorpwiki
Contents |
System Management
To shut the server down:
./bin/mysqladmin -u root --password=xxx shutdown
To upgrade bugfix/minor versions (i.e., X.Y.Z1 - X.Y.Z2); this assumes it's safe to simply replace binaries.
#download new version and update in ymake/third party repository tar xzf lt;new versiongt; cd lt;new version dirgt; ./configure --prefix=`yquery -W home mysql` make -j 2 make install
Database Management
The following commands assume that you've connected as a user with sufficient privileges (like root) and that you've connected to the DB server without selecting (connecting) to a particular database.
To create a database:
CREATE DATABASE foo;
To drop a database:
DROP DATABASE foo;
To load a dump that does not contain a DB create statement:
./bin/mysql -u root --password=xxx foo foo_dump.sql
User Management
To list users:
SELECT Host, User, Password FROM mysql.user;
To create a user, connect as root:
GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'localhost' IDENTIFIED BY 'xxx';
Where 'database_name' is the database to grant privileges to. For application users, it's a good idea to limit. For general users, one can use '*' to grant all normal privs on all databases.
Help System
- 'SHOW TABLES;' to list tables in the current DB
- 'DESCRIBE table_name;' to describe the table 'table_name' in the current DB
Gotchas
MyISAM, Transactions and Constraints
MySQL uses pluggable 'engines' for tables. To my mind, this a dubious feature that has caused me far more confusion than benefit. It wouldn't be bad, except that the default engine, MyISAM, is non-transactional and doesn't support basic things like foreign keys. This not only obviously makes enforcing data integrity hard, but since it swallows constraint statements without complaining, can be very confusing when data integrity problems eventually start happening.
