Differences

This shows you the differences between two versions of the page.

--- mysql [2012/10/11 20:57]
94.23.1.28 EHNvAaHEfVFkEd
+++ mysql [2012/10/15 11:58] (current)
zagi old revision restored
@@ Line 1: / Line 1: @@
-You know, the thing about SQL is, that there is virtually noithng that can replace it.Does anyone know if a substitute exists for sql? I mean besides MS SQL and Oracle and all that jazz. Thanks.
+====== MySQL (*nix setup) ======
+   * [[mysql:troubles|problems?]]
+//TODO
+//
+  - shutdown MySQL
+  - start MySQL with `mysqld_safe --skip-grant-tables &`
+  - set password either with :
+      `mysqladmin -u root flush-privileges password "newpwd"`
+    or
+      `mysql -u root mysql`
+      Issue the following commands in the mysql client:
+      mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
+      mysql> FLUSH PRIVILEGES;
+GRANT ALL PRIVILEGES ON *.* TO myuser@localhost IDENTIFIED BY 'password' WITH GRANT OPTION;
+==== MySQL: removing duplicate rows from a table ====
+First create a temporary table containing the cleaned-up data:
+   CREATE TABLE without_duplicates_temp SELECT * FROM original_table GROUP BY columns, that, must, be, unique;
+and then just delete the original and rename the temporary table:
+   DROP TABLE original_table;
+   RENAME TABLE without_duplicates_temp TO original_table;
+===== Convert a db to UTF8 after upgrading to MySQL 4.1 =====
+   mysqldump --user=username --password=password --default-character-set=latin1 --skip-set-charset dbname > dump.sql
+   chgrep latin1 utf8 dump.sql
+   mysql --user=username --password=password --execute="DROP DATABASE dbname; CREATE DATABASE dbname; CHARACTER SET utf8 COLLATE utf8_general_ci;"
+   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            | latin1                                                   | latin1  |
+| character_set_connection        | latin1                                                   | latin1|
+| character_set_database          | latin1                                                   |latin2|
+| character_set_results           | latin1                                                   |latin1|
+| character_set_server            | latin1                                                   |latin2|
+| character_set_system            | utf8                                                     |utf8|
+| collation_connection            | latin1_swedish_ci                                        |latin1_swedish_ci|
+| collation_database              | latin1_swedish_ci                                        |latin2_general_ci|
+| collation_server                | latin1_swedish_ci | latin2_general_ci |
+   # 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 "s/latin1/utf8/g" shema.sql
+   # cat shema| mysql --default-character-set=utf8
+   # cat data| mysql --default-character-set=latin2
+===== Aternative (new) way Converting Character Sets  =====
+   wget http://www.pablowe.net/convert_charset
+It will be added to [[https://code.launchpad.net/perconatools|Percona Tools]] on Launchpad (or perhaps [[http://www.maatkit.org/|maatkit]], if it proves useful enough) once it is feature complete. Outstanding issues include:
+===== 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://rackerhacker.com/2007/10/03/convert-myisam-tables-to-innodb/|by major]]
+If you want to convert a MyISAM table to InnoDB, the process is fairly easy, but you can do something extra to speed things up. Before converting the table, adjust its order so that the primary key column is in order:
+   ALTER TABLE tablename ORDER BY 'primary_key_column';
+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, and they will begin the conversion as well. Keep in mind, however, that this can greatly reduce the performance of your configuration in the process.\\
+\\
+<code bash>
+$ rm $HOME/.mysql_history\\
+$ ln -s /dev/null $HOME/.mysql_history\\
+\\
+</code>
+=== Reducing ibdata mysql file ===
+http://vdachev.net/blog/2007/02/22/mysql-reducing-ibdata1/