Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Last revision Both sides next revision | ||
mysql [2008/09/03 18:54] a |
mysql [2012/10/11 20:57] 94.23.1.28 EHNvAaHEfVFkEd |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== MySQL (*nix setup) ====== | + | You know, the thing about SQL is, that there is virtually noithng |
- | + | ||
- | * [[mysql: | + | |
- | + | ||
- | //TODO | + | |
- | // | + | |
- | + | ||
- | + | ||
- | - shutdown MySQL | + | |
- | - start MySQL with `mysqld_safe --skip-grant-tables &` | + | |
- | - set password either with : | + | |
- | `mysqladmin -u root flush-privileges password " | + | |
- | or | + | |
- | `mysql -u root mysql` | + | |
- | + | ||
- | Issue the following commands in the mysql client: | + | |
- | mysql> UPDATE mysql.user SET Password=PASSWORD(' | + | |
- | mysql> FLUSH PRIVILEGES; | + | |
- | + | ||
- | + | ||
- | GRANT ALL PRIVILEGES ON *.* TO myuser@localhost IDENTIFIED BY ' | + | |
- | + | ||
- | + | ||
- | ===== Convert a db to UTF8 after upgrading to MySQL 4.1 ===== | + | |
- | + | ||
- | | + | |
- | | + | |
- | mysql --user=username --password=password --execute=" | + | |
- | mysql --user=username --password=password --default-character-set=utf8 dbname < dump.sql | + | |
- | + | ||
- | + | ||
- | + | ||
- | + | ||
- | ===== From MySQL 4.x (latin1) to MySQL 5.x (latin2) and UTF8 data :) ===== | + | |
- | + | ||
- | **variables: | + | |
- | + | ||
- | | ^ MySQL 4.1 ^ MySQL 5.x ^ | + | |
- | | character_set_client | + | |
- | | character_set_connection | + | |
- | | character_set_database | + | |
- | | character_set_results | + | |
- | | character_set_server | + | |
- | | character_set_system | + | |
- | | collation_connection | + | |
- | | collation_database | + | |
- | | collation_server | + | |
- | + | ||
- | # mysqldump --default-character-set=latin1 -skip-set-charset --create-options -n -c --opt > shema | + | |
- | # mysqldump --default-character-set=latin1 --skip-set-charset --create-options -n -c --extended-insert -t > data | + | |
- | # sed -i " | + | |
- | # cat shema| mysql --default-character-set=utf8 | + | |
- | # cat data| mysql --default-character-set=latin2 | + | |
- | + | ||
- | ===== InnoDB ===== | + | |
- | [mysqld] | + | |
- | default-storage-engine=innodb | + | |
- | innodb_file_per_table=1 | + | |
- | innodb_flush_log_at_trx_commit=0 | + | |
- | innodb_thread_concurrency=2 | + | |
- | + | ||
- | + | ||
- | ==== Convert MyISAM tables to InnoDB ==== | + | |
- | [[http:// | + | |
- | + | ||
- | If you want to convert a MyISAM table to InnoDB, the process | + | |
- | + | ||
- | ALTER TABLE tablename ORDER BY ' | + | |
- | + | ||
- | This will pre-arrange the table so that it can be converted quickly without a lot of re-arranging required in MySQL. Then, simply change the table engine: | + | |
- | + | ||
- | ALTER TABLE tablename ENGINE = INNODB; | + | |
- | + | ||
- | If your table is large, then it may take a while to convert it over. There will probably be a fair amount of CPU usage and disk I/O in the process. | + | |
- | + | ||
- | These statements are also safe in replicated environments. When you issue this statement to the master, it will begin the conversion process. Once it is complete on the master, the statement will roll down to the slaves, | + |