Mysql: Unterschied zwischen den Versionen

Aus Info-Theke
Zur Navigation springen Zur Suche springen
 
(16 dazwischenliegende Versionen von 2 Benutzern werden nicht angezeigt)
Zeile 1: Zeile 1:
[[Kategorie:ServerApplikation]]
[[Kategorie:ServerApplikation]]
[[Kategorie:DB]]
[[Kategorie:DB]]
= Links =
* [[Mariadb-Connector]]


== Debian Anpassungen ==
== Debian Anpassungen ==
Zeile 7: Zeile 9:
<pre>
<pre>
mysql -u root -p mysql
mysql -u root -p mysql
grant all on *.* to 'debian-sys-maint'@'localhost' identified by "6QLw8BSKFwzDnoUT";
grant all on *.* to 'debian-sys-maint'@'localhost' identified by "6QLw8BSKFwzDnoUT" with grant option;
grant all on *.* to 'dbadmin'@'192.168.2.%' identified by '6QLw8BSKFwzDnoUT';
grant all on mydb.* to 'dbadmin'@'192.168.2.%' identified by '6QLw8BSKFwzDnoUT';
grant SELECT on *.* to 'backup'@'localhost' identified by 'SAVE:alls!4711';
grant SELECT,SHOW VIEW,TRIGGER,PROCESS,LOCK TABLES on *.* to 'backup'@'localhost' identified by 'SAVE:alls!4711';
flush privileges;
flush privileges;
</pre>
</pre>
MariaDB: Es ist für root ein Passwort vergeben. Man kann sich aber als OS-User root ohne Passwort anmelden.
MariaDB: Es ist für root ein Passwort vergeben. Man kann sich aber als OS-User root ohne Passwort anmelden.
<syntaxhighlight lang="bash">
# Admin
echo "grant all on *.* to 'dba'@'localhost' identified by 'BlaBla' with grant option; flush privileges;" | sudo mysql mysql
# einfacher Benutzer
echo "grant all on lrvtaskx.* to 'taskx'@'localhost' identified by 'BlaBla'; flush privileges;" | sudo mysql mysql
</syntaxhighlight>


== Nachträglich Root-Passwort setzen ==
== Nachträglich Root-Passwort setzen ==
Wenn bei der Installation das Passwort nicht gesetzt wurde, kann das nachträglich passieren:
Wenn bei der Installation das Passwort nicht gesetzt wurde, kann das nachträglich passieren:
<pre>update user set
<pre># Set root password:
authentication_string=password('ge:heimesp_w1'),
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd';
plugin='mysql_native_password'
where user='root';
</pre>
</pre>
Zugriff ohne Root-Passwort (als Benutzer root):
Zugriff ohne Root-Passwort (als Benutzer root):
Zeile 30: Zeile 36:
== Datentypen ==
== Datentypen ==
<pre>create table example(
<pre>create table example(
   id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
   id int(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
   ts TIMESTAMP NULL default NULL,
   ts TIMESTAMP NULL default NULL,
   dt DATETIME,
   dt DATETIME,
Zeile 37: Zeile 43:
</pre>
</pre>
* TIMESTAMP ist normalerweise ohne NULL, Default-Wert ist NOW()
* TIMESTAMP ist normalerweise ohne NULL, Default-Wert ist NOW()
* Datentyp ändern:
<pre>alter table configurations modify configuration_id int(10) unsigned;
</pre>
* Spaltennamen ändern:
<pre>alter table sample change sampleid sample_id int(10) unsigned;
</pre>
== Fremdschlüssel ==
<pre>alter table rolestarters add constraint fk_roles2rolestarter foreign key (role_id)
      references roles (role_id) on delete restrict on update restrict;
alter table rolestarters drop constraint fk_roles2rolestarter;
</pre>
* Fremdschlüssel und Primärschlüssel müssen gleichen Datentyp haben.
== Index erstellen und abfragen ==
<pre>create index geotestdate on testresult (`geo_bundesland`,`test_immunitaet`,`sample_date`);
# und abfragen:
show index from testresult;
</pre>
== SQL-Tipps ==
<pre>show create table geo;
rename table A to B;
alter table mytable change columnA columnB varchar(20);
</pre>
== DB-Export ==
<pre>mysqldump -u $USR $PWPHRASE $DB >$OUT
# oder nur Tabelle users  mit Bedingung:
mysqldump "--where=name like 'a%'" -u $USR $PWPHRASE $DB users logs >$OUT
</pre>
== CSV-Import ==
* Wichtig: CSV-Datei muss wie Tabelle heißen, also geo.csv
<pre>mysqlimport --ignore-lines=1 --fields-terminated-by=, --local \
  --columns=geo_id,geo_staat,geo_land,geo_bezirk,geo_kreis,geo_gemeindeags,geo_gemeinde,geo_plz \
  -u $USR $PWPHRASE $DB  geo.csv
</pre>
<pre>LOAD DATA
LOCAL INFILE '/tmp/data.csv'
INTO TABLE geo
COLUMNS TERMINATED BY '\t'
IGNORE 1 LINES
(geo_id,geo_staat,...)
</pre>
== Export der Benutzer ==
<syntaxhighlight lang="bash">
FN=users.txt
FN_SQL=users.sql
mysql -B -N -uroot -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql > $FN
while read line; do mysql -B -N -uroot  -e "SHOW GRANTS FOR $line"; done < $FN > $FN_SQL
</syntaxhighlight>
== CSV-Export ==
<pre>SELECT * FROM users
INTO OUTFILE '/tmp/users.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
</pre>
= Funktionen =
<pre>hex(sample_id)
cast(sample_id as CHAR)
NOW() - INTERVAL 1 HOUR
</pre>

Aktuelle Version vom 28. März 2024, 20:54 Uhr

Links[Bearbeiten]

Debian Anpassungen[Bearbeiten]

Debian prüft die Tabellen beim Booten. Damit das funktioniert, muss ein passender User eingerichtet sein:

  • Siehe /etc/mysql/debian.cnf
mysql -u root -p mysql
grant all on *.* to 'debian-sys-maint'@'localhost' identified by "6QLw8BSKFwzDnoUT" with grant option;
grant all on mydb.* to 'dbadmin'@'192.168.2.%' identified by '6QLw8BSKFwzDnoUT';
grant SELECT,SHOW VIEW,TRIGGER,PROCESS,LOCK TABLES on *.* to 'backup'@'localhost' identified by 'SAVE:alls!4711';
flush privileges;

MariaDB: Es ist für root ein Passwort vergeben. Man kann sich aber als OS-User root ohne Passwort anmelden.

# Admin
echo "grant all on *.* to 'dba'@'localhost' identified by 'BlaBla' with grant option; flush privileges;" | sudo mysql mysql
# einfacher Benutzer
echo "grant all on lrvtaskx.* to 'taskx'@'localhost' identified by 'BlaBla'; flush privileges;" | sudo mysql mysql

Nachträglich Root-Passwort setzen[Bearbeiten]

Wenn bei der Installation das Passwort nicht gesetzt wurde, kann das nachträglich passieren:

# Set root password:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd';

Zugriff ohne Root-Passwort (als Benutzer root):

update user set 
authentication_string='',
plugin='auth_socket'
where user='root';

Datentypen[Bearbeiten]

create table example(
   id int(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
   ts TIMESTAMP NULL default NULL,
   dt DATETIME,
   name VARCHAR(100) UNIQUE NOT NULL
);
  • TIMESTAMP ist normalerweise ohne NULL, Default-Wert ist NOW()
  • Datentyp ändern:
alter table configurations modify configuration_id int(10) unsigned;
  • Spaltennamen ändern:
alter table sample change sampleid sample_id int(10) unsigned;

Fremdschlüssel[Bearbeiten]

alter table rolestarters add constraint fk_roles2rolestarter foreign key (role_id)
      references roles (role_id) on delete restrict on update restrict;
alter table rolestarters drop constraint fk_roles2rolestarter;
  • Fremdschlüssel und Primärschlüssel müssen gleichen Datentyp haben.

Index erstellen und abfragen[Bearbeiten]

create index geotestdate on testresult (`geo_bundesland`,`test_immunitaet`,`sample_date`);
# und abfragen:
show index from testresult;

SQL-Tipps[Bearbeiten]

show create table geo;
rename table A to B;
alter table mytable change columnA columnB varchar(20);

DB-Export[Bearbeiten]

mysqldump -u $USR $PWPHRASE $DB >$OUT
# oder nur Tabelle users  mit Bedingung:
mysqldump "--where=name like 'a%'" -u $USR $PWPHRASE $DB users logs >$OUT

CSV-Import[Bearbeiten]

  • Wichtig: CSV-Datei muss wie Tabelle heißen, also geo.csv
mysqlimport --ignore-lines=1 --fields-terminated-by=, --local \
  --columns=geo_id,geo_staat,geo_land,geo_bezirk,geo_kreis,geo_gemeindeags,geo_gemeinde,geo_plz \
  -u $USR $PWPHRASE $DB  geo.csv
LOAD DATA
 LOCAL INFILE '/tmp/data.csv'
 INTO TABLE geo
 COLUMNS TERMINATED BY '\t'
 IGNORE 1 LINES
 (geo_id,geo_staat,...)

Export der Benutzer[Bearbeiten]

FN=users.txt
FN_SQL=users.sql
mysql -B -N -uroot -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql > $FN
while read line; do mysql -B -N -uroot  -e "SHOW GRANTS FOR $line"; done < $FN > $FN_SQL

CSV-Export[Bearbeiten]

SELECT * FROM users
INTO OUTFILE '/tmp/users.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

Funktionen[Bearbeiten]

hex(sample_id)
cast(sample_id as CHAR)
NOW() - INTERVAL 1 HOUR