MySQL
From AdminWiki
m (→Database formats) |
|||
| (4 intermediate revisions not shown) | |||
| Line 6: | Line 6: | ||
== Database formats == | == Database formats == | ||
| - | MySQL currently offers two database formats. MyISAM is the non-transactional in-place updating table. Think of it more like a relational textfile than a real database. InnoDB is the relational table-format, which MySQL promotes as being ACID-conform. It's less mature than MyISAM and it's behaviour with semi-corrupted tables (these things happen in the real | + | MySQL currently offers two database formats. MyISAM is the non-transactional in-place updating table. Think of it more like a relational textfile than a real database. InnoDB is the relational table-format, which MySQL promotes as being ACID-conform. It's less mature than MyISAM and it's behaviour with semi-corrupted tables (these things happen in the real world ™) have yet to be discovered by us *knocks on wood*. |
| + | |||
| + | If you've got any data that is of value to you, you should strongly opt for a transactional database along with an application which utilizes those features. | ||
= Migration woes = | = Migration woes = | ||
| Line 13: | Line 15: | ||
== Password format == | == Password format == | ||
| + | |||
| + | If you're using "old" user tables and/or "old" clients to connect to your database make sure to put | ||
| + | |||
| + | old_passwords = 1 | ||
| + | |||
| + | in your my.cnf. | ||
== Auth table changes == | == Auth table changes == | ||
| + | |||
| + | = Replication = | ||
| + | |||
| + | TODO | ||
| + | |||
| + | Unfuck replication: | ||
| + | |||
| + | * stop master/lock all tables | ||
| + | |||
| + | * show master status, note position and file. alternatively, check filesize and name of binlog | ||
| + | |||
| + | * stop slave | ||
| + | |||
| + | * slave# rsync -av -P --delete rsync://master/mysql /var/lib/mysql | ||
| + | |||
| + | * start slave with slavethread disabled or stopped master | ||
| + | |||
| + | * CHANGE MASTER TO MASTER_HOST='<ip>', MASTER_USER='<user>', MASTER_PASSWORD='<pass>', MASTER_LOG_FILE='<file>', MASTER_LOG_POS=<pos>; | ||
| + | |||
| + | * start slave thread or start master | ||
| + | |||
| + | =Timezones= | ||
| + | |||
| + | * store values you expect to consider the mysql time_zone setting as timestamp | ||
| + | |||
| + | = Exporting = | ||
| + | |||
| + | To export a MySQL Database, data-only (as schema will never be compatible across DBs), in a somewhat compatible mode, probably suitable for import into [[PostgreSQL]], you could try using this: | ||
| + | |||
| + | mysqldump --extended-insert=off --compatible=ansi --lock-tables=off --data -n -t --add-locks=off [DBNAME] | ||
Latest revision as of 15:37, 24 June 2009
Contents |
Introduction
MySQL is the defacto standard database in the opensource world, but it's reign isn't undisputed. See the link list on the Databases page.
Database formats
MySQL currently offers two database formats. MyISAM is the non-transactional in-place updating table. Think of it more like a relational textfile than a real database. InnoDB is the relational table-format, which MySQL promotes as being ACID-conform. It's less mature than MyISAM and it's behaviour with semi-corrupted tables (these things happen in the real world ™) have yet to be discovered by us *knocks on wood*.
If you've got any data that is of value to you, you should strongly opt for a transactional database along with an application which utilizes those features.
Migration woes
Since 3.23 a few things have changed. Depending on the version jump one or more of those points may apply to you.
Password format
If you're using "old" user tables and/or "old" clients to connect to your database make sure to put
old_passwords = 1
in your my.cnf.
Auth table changes
Replication
TODO
Unfuck replication:
- stop master/lock all tables
- show master status, note position and file. alternatively, check filesize and name of binlog
- stop slave
- slave# rsync -av -P --delete rsync://master/mysql /var/lib/mysql
- start slave with slavethread disabled or stopped master
- CHANGE MASTER TO MASTER_HOST='<ip>', MASTER_USER='<user>', MASTER_PASSWORD='<pass>', MASTER_LOG_FILE='<file>', MASTER_LOG_POS=<pos>;
- start slave thread or start master
Timezones
- store values you expect to consider the mysql time_zone setting as timestamp
Exporting
To export a MySQL Database, data-only (as schema will never be compatible across DBs), in a somewhat compatible mode, probably suitable for import into PostgreSQL, you could try using this:
mysqldump --extended-insert=off --compatible=ansi --lock-tables=off --data -n -t --add-locks=off [DBNAME]
