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 [2011-05-13 14:44] becki |
becki:linux:mysql_commands [2017-10-04 12:37] (aktuell) becki |
||
|---|---|---|---|
| Zeile 1: | Zeile 1: | ||
| ====== MySql Tips and Commands == | ====== MySql Tips and Commands == | ||
| - | --- slack 11.0 2007-06-26 , slack 12.0 2007-10-18 | + | Done with slack 11.0 2007-06-26, slack 12.0 2007-10-18, slack 14.1 2014-11-07 |
| ===== Setup == | ===== Setup == | ||
| Zeile 15: | Zeile 15: | ||
| </code> | </code> | ||
| - | ==== Set password for root user == | + | ==== Secure Mysql == |
| + | |||
| + | Run ''mysql_secure_installation'' as root and answer every question with yes! | ||
| + | |||
| + | After that, you may continue with creating databases. See [[mysql_commands#Managing_Databases]] below. | ||
| + | |||
| + | DELETEME The rest of this section is obsolete since --- 2014-11-07 (Slack 14.1) | ||
| Get your values for <yourHostName> and <yourDomainName> with ''hostname -f'' | Get your values for <yourHostName> and <yourDomainName> with ''hostname -f'' | ||
| Zeile 22: | Zeile 28: | ||
| <code bash> | <code bash> | ||
| su - # become user 'root' | su - # become user 'root' | ||
| - | mysqladmin -u root password <yourPassword> | + | mysqladmin -u root password |
| - | #mysqladmin -u root -h <yourHostName> password <yourPassword> | + | #mysqladmin -u root -h <yourHostName> password |
| </code> | </code> | ||
| Zeile 33: | 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 46: | 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 59: | 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 66: | 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 84: | Zeile 92: | ||
| CREATE DATABASE myDb; | CREATE DATABASE myDb; | ||
| - | /* Create a user for the new datase with all possible access privileges. | + | /* Recommended metohd to create a user for the new datase with all possible access privileges. |
| Affects mysql.user and mysql.db | Affects mysql.user and mysql.db | ||
| ' is important for password! */ | ' is important for password! */ | ||
| Zeile 90: | Zeile 98: | ||
| /* Alternative to create a user: */ | /* Alternative to create a user: */ | ||
| - | CREATE USER td_ps_user@localhost IDENTIFIED BY 'myPassword'; | + | CREATE USER myUser@localhost IDENTIFIED BY 'myPassword'; |
| - | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON td_ps.* TO td_ps_user@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 116: | 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 124: | 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 135: | 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 146: | 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.1305297844.txt.gz · Zuletzt geändert: 2011-05-13 14:44 von becki
Seiten-Werkzeuge
