Benutzer-Werkzeuge
becki:linux:mysql_commands
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
| Beide Seiten der vorigen Revision Vorhergehende Überarbeitung Nächste Überarbeitung | Vorhergehende Überarbeitung | ||
|
becki:linux:mysql_commands [2015-06-18 13:05] becki [Backup and Restore] |
becki:linux:mysql_commands [2017-10-04 12:37] (aktuell) becki |
||
|---|---|---|---|
| Zeile 65: | Zeile 65: | ||
| ===== MySql Commands == | ===== MySql Commands == | ||
| + | |||
| + | <note important>In order to get UTF-8 content, you must log in and mysqldump with ''----default-character-set=latin1''. FIXME Dont know why!!</note> | ||
| ==== Misc == | ==== Misc == | ||
| Zeile 97: | Zeile 99: | ||
| /* Alternative to create a user: */ | /* Alternative to create a user: */ | ||
| CREATE USER myUser@localhost IDENTIFIED BY 'myPassword'; | CREATE USER myUser@localhost IDENTIFIED BY 'myPassword'; | ||
| - | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, LOCK ON myDb.* TO myUser@localhost; | + | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, LOCK TABLES ON myDb.* TO myUser@localhost; |
| /* Delete the database with all tables. */ | /* Delete the database with all tables. */ | ||
| DROP DATABASE myDb; | DROP DATABASE myDb; | ||
| </code> | </code> | ||
| + | |||
| + | FIXME [[http://dev.mysql.com/doc/refman/5.7/en/grant.html|Manual]] says: Use of GRANT to create accounts ... is deprecated as of MySQL 5.7.6. Instead, perform these tasks using CREATE USER or ALTER USER. | ||
| ==== Managing Tables == | ==== Managing Tables == | ||
| Zeile 122: | Zeile 126: | ||
| <code sql> | <code sql> | ||
| - | SELECT * FROM myTable; # Show content of a table | + | SELECT * FROM myTable; -- Show content of a table |
| /* Query 2 tables at the same time (see 3.6.6. Using Foreign Keys) */ | /* Query 2 tables at the same time (see 3.6.6. Using Foreign Keys) */ | ||
| Zeile 130: | Zeile 134: | ||
| SELECT * FROM myTable WHERE myColumn IS NULL; | SELECT * FROM myTable WHERE myColumn IS NULL; | ||
| - | SELECT COUNT(*) FROM myTable; # Shows the number of lines of a table | + | SELECT COUNT(*) FROM myTable; -- Shows the number of lines of a table |
| - | TRUNCATE myTable; # Delete all rows of a table, like DELETE, but faster | + | TRUNCATE myTable; -- Delete all rows of a table, like DELETE, but faster |
| /* Delete rows with conditions */ | /* Delete rows with conditions */ | ||
| Zeile 151: | Zeile 155: | ||
| mysqldump [--opt] --all_databases > file | mysqldump [--opt] --all_databases > file | ||
| </code> | </code> | ||
| + | |||
| + | To avoid root user and passwords in mysqldump, add a special user vor backups in mysql: | ||
| + | |||
| + | <code sql> | ||
| + | GRANT SELECT, LOCK TABLES ON *.* TO backupuser@localhost IDENTIFIED BY 'your_pass'; | ||
| + | </code> | ||
| + | |||
| + | Create a file ''.my.cnf'' in the Linux home dir of the user: | ||
| + | [client] | ||
| + | password=your_pass | ||
| FIXME Is this warning still relevant: | FIXME Is this warning still relevant: | ||
| Zeile 159: | Zeile 173: | ||
| * Remove all appearences of ''collate latin1_german2_ci'' | * Remove all appearences of ''collate latin1_german2_ci'' | ||
| * ''mysqldump'' inserts funny comments which seem to mess with the character set when imported. => Remove all comments in ''backup-file.sql'' | * ''mysqldump'' inserts funny comments which seem to mess with the character set when imported. => Remove all comments in ''backup-file.sql'' | ||
| - | </note> | ||
| + | </note> | ||
| ===== Old, German & unsorted == | ===== Old, German & unsorted == | ||
becki/linux/mysql_commands.1434632756.txt.gz · Zuletzt geändert: 2015-06-18 13:05 von becki
Seiten-Werkzeuge
