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 [2014-11-07 09:26] becki |
becki:linux:mysql_commands [2017-10-04 12:37] (aktuell) becki |
||
|---|---|---|---|
| Zeile 39: | Zeile 39: | ||
| <code sql> | <code sql> | ||
| - | pager most; | + | pager less -S; |
| use mysql; | use mysql; | ||
| select * from user; # to check | select * from user; # to check | ||
| Zeile 52: | Zeile 52: | ||
| <code sql> | <code sql> | ||
| - | pager most; | + | pager less -S; |
| use mysql; | use mysql; | ||
| select * from user; # to check | select * from user; # to check | ||
| 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 72: | Zeile 74: | ||
| <code sql> | <code sql> | ||
| - | PAGER most; /* or */ PAGER less; # Redirect output to a pager | + | PAGER most; /* or */ PAGER less -S; # Redirect output to a pager |
| - | NOPAGER; # Switch off redirection | + | NOPAGER; # Switch off redirection |
| SHOW DATABASES; # Display the availabel databaese on the server | SHOW DATABASES; # Display the availabel databaese on the server | ||
| 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 141: | Zeile 145: | ||
| <code bash> | <code bash> | ||
| - | mysqldump --opt dbname # Backup a Database | + | # Backup an existing database into an SQL-file |
| - | mysql db_name < backup-file.sql # Restore the Database | + | mysqldump -u root -p$password --opt --default-character-set=utf8 $dbname > $filename |
| - | mysqldump [--opt] --all_databases > file # Backup all Databases | + | |
| + | # Restore the Database from a SQL-file | ||
| + | # Note that the dababase must already exist! | ||
| + | mysql -u root -p$password $db_name < $filename | ||
| + | |||
| + | # Backup all Databases into one SQL-textfile: | ||
| + | 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: | ||
| <note warning> | <note warning> | ||
| In order to have all UTF-8: | In order to have all UTF-8: | ||
| Zeile 152: | 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.1415352372.txt.gz · Zuletzt geändert: 2014-11-07 09:26 von becki
Seiten-Werkzeuge
