{"id":5381,"date":"2019-10-11T15:07:15","date_gmt":"2019-10-11T13:07:15","guid":{"rendered":"https:\/\/speefak.spdns.de\/oss_lifestyle\/?p=5381"},"modified":"2024-10-13T15:58:17","modified_gmt":"2024-10-13T13:58:17","slug":"mysql-befehle","status":"publish","type":"post","link":"https:\/\/speefak.spdns.de\/oss_lifestyle\/mysql-befehle\/","title":{"rendered":"MySQL Befehle"},"content":{"rendered":"

Benutzerverwaltung<\/h2>\n\n\n\n\n\n\n\n\n
Benutzer Anzeigen:<\/td>\nsudo mysql -e \"SELECT host, user, password FROM mysql.user\"<\/code><\/td>\n<\/td>\n<\/tr>\n
Benutzer anlegen:<\/td>\nsudo mysql -e \"CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'\"<\/code><\/td>\n<\/td>\n<\/tr>\n
Benutzer l\u00f6schen:<\/td>\nsudo mysql -e \"DROP USER 'newuser'@'localhost'\"<\/code><\/td>\n<\/td>\n<\/tr>\n
Benutzer spezifische Rechte anzeigen:<\/td>\nsudo mysql -e \"SHOW GRANTS FOR 'user'@'localhost'\"<\/code><\/td>\n<\/td>\n<\/tr>\n
Auflistung aller User und deren Berechtigungen:<\/td>\nsudo mysql -e \"select * from information_schema.user_privileges\"<\/code><\/td>\n<\/td>\n<\/tr>\n
Auflistung aller User und Datenbanken<\/td>\n\n
sudo mysql<\/span> -<\/span>e<\/span> \"SELECT db, host, user FROM mysql.db;\"<\/span><\/code><\/span><\/div>\n<\/td>\n
<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n

Datenbankverwaltung<\/h2>\n\n\n\n\n\n\n\n\n\n\n
Datenbanken anzeigen:<\/td>\nsudo mysql -e \"SHOW DATABASES\"<\/code><\/td>\n<\/td>\n<\/tr>\n
Datenbank anlegen:<\/td>\nsudo mysql -e \"CREATE DATABASE newdatabase\"<\/code><\/td>\n<\/td>\n<\/tr>\n
Datenbank l\u00f6schen:<\/td>\nsudo mysql -e \"DROP DATABASE newdatabase\"<\/code><\/td>\n<\/td>\n<\/tr>\n
Userzugriff f\u00fcr alle Datenbanken und Tabellen aktivieren:<\/td>\nsudo mysql -e \"GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost'\"<\/code><\/td>\n<\/td>\n<\/tr>\n
Userzugriff f\u00fcr eine Datenbank und deren Tabellen aktivieren:<\/td>\nsudo mysql -e \"GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost'\"<\/code><\/td>\n<\/td>\n<\/tr>\n
Userzugriff f\u00fcr eine Datenbank und eine Tabellen aktivieren:<\/td>\nsudo mysql -e \"GRANT ALL PRIVILEGES ON database.table TO 'user'@'localhost'\"<\/code><\/td>\n<\/td>\n<\/tr>\n
Datenbank exportieren<\/td>\nmysqldump -u user -p 'database_name' > file.sql<\/code><\/td>\n<\/td>\n<\/tr>\n
Datenbank importieren<\/td>\nsudo mysql -p'password' 'database_name' < file.sql\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # as root
\n<\/code>mysql -u user -p 'password' 'database_name' < file.sql\u00a0\u00a0\u00a0 # as user<\/code><\/td>\n		<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n

Tabellenverwaltung<\/h2>\n\n\n\n
Tabelle l\u00f6schen:<\/td>\nsudo mysql -e \"DROP TABLE <Tabellenname>;\"
\n<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n

Serververwaltung<\/h2>\n\n\n\n\n\n
Serverprozesse anzeigen:<\/td>\nsudo mysql -e \"show processlist\"<\/code><\/td>\n<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Shell Script zum Anlegen eines Users und Datenbank sowie Gew\u00e4hrung s\u00e4mtlicher Rechte f\u00fcr die Datenbank f\u00fcr den entsprechenden User<\/p>\n

# request for values\r\nread -e -p \" Enter MySQL user: \"  \t\tNewUser\r\nread -e -p \" Enter password:  \"\t\t\tNewPass\r\nread -e -p \" Enter database:  \"\t-i \"$NewUser\" \tNewData\r\n\r\n# print entered values\r\necho \"\"\r\necho \" create MySQL entries:\"\r\necho \" user: \t $NewUser\"\r\necho \" pass: \t $NewPass\"\r\necho \" database: $NewData\"\r\necho \"\"\r\n\r\n# check for non empty values\r\nif [[ -z $NewData ]]; then MSG=Database ; fi\r\nif [[ -z $NewPass ]]; then MSG=Password ; fi\r\nif [[ -z $NewUser ]]; then MSG=User ; fi\r\n\r\nif [[ -n $MSG ]]; then\r\n\techo \" Missing value for $MSG\"\r\n\texit\r\nfi\r\n\r\n# check for creating user and database\r\nproceeding_function () {\r\n\tread -n1 -p \" proceed ? [Y]es, [N]o : \" response\r\n\tprintf \"\\n\\n\"\r\n\tcase $response in\r\n\t\t[Yy]* ) ;;\r\n\t\t[Nn]* ) exit 99;;\r\n\t\t    * ) proceeding_function;;\r\n\tesac\r\n}\r\nproceeding_function\r\n\r\n# create user and database and grand access\r\nsudo mysql -e \"CREATE USER '$NewUser'@'localhost' IDENTIFIED BY '$NewPass'\"\r\nsudo mysql -e \"CREATE DATABASE $NewData\"\r\nsudo mysql -e \"GRANT ALL PRIVILEGES ON $NewData.* TO '$NewUser'@'localhost'\"\r\n\r\n# show user and databases on server \r\nsudo mysql -e \"SELECT db, host, user FROM mysql.db;\"\"\"<\/pre>\n
\n
\"CC_BY_NC_SA\"by Speefak| https:\/\/kyup.com<\/a> | https:\/\/sqlbuddy.de<\/a><\/span><\/span>
\n<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
Benutzerverwaltung Benutzer Anzeigen: sudo mysql -e “SELECT host, user, password FROM mysql.user” Benutzer anlegen: sudo mysql -e “CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password'” Benutzer l\u00f6schen: sudo mysql -e “DROP USER ‘newuser’@’localhost'” Benutzer spezifische Rechte anzeigen: sudo mysql -e “SHOW GRANTS FOR ‘user’@’localhost'” Auflistung aller User und deren Berechtigungen: sudo mysql -e “select * from information_schema.user_privileges” […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[92,24,40],"tags":[],"class_list":["post-5381","post","type-post","status-publish","format-standard","hentry","category-datenbanken","category-it-syntax","category-server"],"rttpg_featured_image_url":null,"rttpg_author":{"display_name":"speefak","author_link":"https:\/\/speefak.spdns.de\/oss_lifestyle\/author\/speefak_oss\/"},"rttpg_comment":1,"rttpg_category":"Datenbanken<\/a> IT Syntax<\/a> Server<\/a>","rttpg_excerpt":"Benutzerverwaltung Benutzer Anzeigen: sudo mysql -e \"SELECT host, user, password FROM mysql.user\" Benutzer anlegen: sudo mysql -e \"CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'\" Benutzer l\u00f6schen: sudo mysql -e \"DROP USER 'newuser'@'localhost'\" Benutzer spezifische Rechte anzeigen: sudo mysql -e \"SHOW GRANTS FOR 'user'@'localhost'\" Auflistung aller User und deren Berechtigungen: sudo mysql -e \"select * from information_schema.user_privileges\"…","_links":{"self":[{"href":"https:\/\/speefak.spdns.de\/oss_lifestyle\/wp-json\/wp\/v2\/posts\/5381","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/speefak.spdns.de\/oss_lifestyle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/speefak.spdns.de\/oss_lifestyle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/speefak.spdns.de\/oss_lifestyle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/speefak.spdns.de\/oss_lifestyle\/wp-json\/wp\/v2\/comments?post=5381"}],"version-history":[{"count":0,"href":"https:\/\/speefak.spdns.de\/oss_lifestyle\/wp-json\/wp\/v2\/posts\/5381\/revisions"}],"wp:attachment":[{"href":"https:\/\/speefak.spdns.de\/oss_lifestyle\/wp-json\/wp\/v2\/media?parent=5381"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/speefak.spdns.de\/oss_lifestyle\/wp-json\/wp\/v2\/categories?post=5381"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/speefak.spdns.de\/oss_lifestyle\/wp-json\/wp\/v2\/tags?post=5381"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}