MySQL

http://dev.mysql.com/doc/refman/5.1/en/index.html

http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-isolation.html

Warnings

* The {{{mysqldump}}} utility offers to give the password on the command line (for scheduling purposes etc.). DO NOT USE that as every user on your system will be able to read your password (using top or ps etc.). Password security. Example .my.cnf file:

[client]
password=your_pass

Checking privileges

mysql mysql
select * from user;
select * from db;

Creating users

 user darf alles:

 mysql> GRANT ALL ON *.* TO marco@localhost IDENTIFIED BY "" WITH GRANT OPTION;

 user hat nur Berechtigung für sample_db und nur von localhost aus:

 mysql> GRANT ALL ON sampel_db.* TO marco@localhost IDENTIFIED BY "";

 user hat nur Berechtigung für sample_db, egal von wo:

 mysql> GRANT ALL ON sample_db.* TO marco@% IDENTIFIED BY "";

Setting Passwords

shell> mysql -u root
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd') WHERE User = '';
mysql> FLUSH PRIVILEGES;

Transactions

* set default storage engine to innodb: add the line "default-storge-engine = innodb" to the mysqld section of /etc/mysql/my.cnf.
* Use "SELECT @@identity" to get the last auto-incremented identity value after an INSERT.

"Tricks"

Managing Cron Jobs

mysql> select * from cronjob where lastrun < ( now() - INTERVAL period MINUTE);
+------------+---------+-----------+---------------------+--------+---------+
| cronjob_id | name    | script_id | lastrun             | period | enabled |
+------------+---------+-----------+---------------------+--------+---------+
|          1 | 2211222 |         5 | 2007-04-26 00:43:31 |      5 |       1 |
+------------+---------+-----------+---------------------+--------+---------+
1 row in set (0.00 sec)

InnoDB

If you store InnoDB tables into one single storage file (usually ibdata1, and maybe ibdata2, ...), you wont be able to reclaim space on disk (shrink that file) without dumping and restoring *all* of your InnoDB (and maybe all other) databases. A *much* more convenient way would be to add the following line to your mysqld config section:

[mysqld]
innodb_file_per_table           = 1
transaction-isolation           = SERIALIZABLE
# other useful parameters
character-set-server            = utf8
default-character-set           = utf8
default-storage-engine          = innodb
expire_logs_days                = 3

To reclaim disk space, run "optimize table <tablename>;", or, probably more conveniently, just

mysqloptimize --all-databases

on the command line. That way you only lock one table at a time, and you don't have to take down *all* of your databases during the entire duration a complete dump and restore needs to run (in other words, that increases your availability by an average factor of n^2 where n is the number of tables or databases).

Look up storage engine of your tables

show table status;

Binary Logs

Configuration

In the [mysqld] section of /etc/mysql/my.conf, add:

log-bin          = mysqld-bin
expire_logs_days = 3

Puring

 # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1402
Server version: 5.0.54-log Gentoo Linux mysql-5.0.54

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW BINARY LOGS;
+-------------------+------------+
| Log_name          | File_size  |
+-------------------+------------+
| mysqld-bin.000042 |  734553614 |
| mysqld-bin.000043 |    9579182 |
| mysqld-bin.000044 |        117 |
| mysqld-bin.000045 | 1073741881 |
| mysqld-bin.000046 |  818839244 |
+-------------------+------------+
5 rows in set (0.00 sec)

mysql> purge master logs before now();
Query OK, 0 rows affected (5.13 sec)

mysql> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| mysqld-bin.000046 | 818839244 |
+-------------------+-----------+
1 row in set (0.00 sec)

mysql>

http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

mySQL

Contents