Wissen

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: Start » mysql
Zuletzt angesehen: • regex • mysql_tuning • flatpak
mysql

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
mysql [2024-10-24 12:12:18] – [eine Tabelle in eine Datei speichern und aus einer Datei wiederherstellen] manfredmysql [2025-09-12 12:37:11] (aktuell) – [auf Platte] manfred
Zeile 228: Zeile 228:
   SELECT CONCAT(@@server_id, UUID());   SELECT CONCAT(@@server_id, UUID());
   SHOW VARIABLES LIKE 'server_id';   SHOW VARIABLES LIKE 'server_id';
-  SHOW VARIABLES WHERE Variable_name IN ('server_id');+  SHOW VARIABLES WHERE Variable_name IN ('hostname'); 
 +  SHOW STATUS WHERE Variable_name IN ('Connections','Created_tmp_files','Innodb_buffer_pool_write_requests','Innodb_data_fsyncs','Innodb_data_writes','Innodb_data_written','Innodb_log_write_requests','Innodb_log_writes','Innodb_pages_written','Queries','Ssl_cipher','Uptime','Uptime_since_flush_status','Innodb_buffer_pool_load_status');
  
  
Zeile 265: Zeile 266:
   ASC   ASC
   LIMIT 10;   LIMIT 10;
 +
 +
 +==== VIEWs zeigen ====
 +
 +  > echo "SHOW STATUS WHERE variable_name LIKE 'Max_used_connections%' OR variable_name LIKE 'Uptime';" | mysql -t
 +  +---------------------------+---------------------+
 +  | Variable_name             | Value               |
 +  +---------------------------+---------------------+
 +  | Max_used_connections      | 386                 |
 +  | Max_used_connections_time | 2025-06-24 10:19:25 |
 +  | Uptime                    | 5931173             |
 +  +---------------------------+---------------------+
  
  
Zeile 507: Zeile 520:
 </code> </code>
  
-<code c test.sql>+<file sql Tabellen_anlegen.sql> 
 +DROP TABLE IF EXISTS `dbname`.`tabname`; 
 + 
 +CREATE TABLE `dbname`.`tabname` ( 
 + `id` int NOT NULL AUTO_INCREMENT, 
 + `pwhash` varchar(768) NOT NULL, 
 + `count` varchar(2) NOT NULL, 
 + PRIMARY KEY (`id`), 
 + KEY `pwhash_INDEX` (`pwhash`) 
 +) ENGINE=InnoDB 
 +/*!50100 PARTITION BY HASH (`id`) 
 +PARTITIONS 128 */ 
 +; 
 + 
 +SHOW CREATE TABLE `dbname`.`tabname`; 
 +</file> 
 + 
 +<code text Beispiel aus Doku>
 SET GLOBAL local_infile=1; SET GLOBAL local_infile=1;
  
Zeile 515: Zeile 545:
 LINES TERMINATED BY '\n' LINES TERMINATED BY '\n'
 IGNORE 1 LINES IGNORE 1 LINES
-(col1, col2, col3, col4, col5...);</code>+(col1, col2, col3, col4, col5...); 
 +</code> 
 + 
 +<code c test.sql> 
 +SET GLOBAL local_infile=1; 
 + 
 +START TRANSACTION; 
 +LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE dbname.tabname 
 +FIELDS TERMINATED BY ':' 
 +LINES TERMINATED BY '\n' 
 +(pwhash, count); 
 +COMMIT; 
 +</code>
  
 <code bash> <code bash>
-root@haveibeenpwned-sftp:~# cat test.sql | mysql --local-infile=1 -usuperuser -p... +cat test.sql | mysql --local-infile=1 -usuperuser -p... 
-root@haveibeenpwned-sftp:~# echo "SELECT * FROM dbname.tabname LIMIT 12;" | mysql -t+echo "SELECT * FROM dbname.tabname LIMIT 12;" | mysql -t
 +----+-------------------------------------+-------+ +----+-------------------------------------+-------+
 | id | pwhash                              | count | | id | pwhash                              | count |
Zeile 1297: Zeile 1339:
  
 ### Summen ### Summen
-echo "  +echo " 
 +SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
 SELECT  SELECT 
         table_schema AS 'Datenbankname',         table_schema AS 'Datenbankname',
Zeile 2080: Zeile 2123:
         data_length / 1024 / 1024 'Daten in MB',         data_length / 1024 / 1024 'Daten in MB',
         index_length / 1024 / 1024 'Index in MB'         index_length / 1024 / 1024 'Index in MB'
-        FROM infor/root/bin/Tabellengröße_einer_Datenbank_anzeigen.sh simsysnetwork -Nmation_schema.tables WHERE table_schema='${1}' AND"+        FROM information_schema.tables WHERE table_schema='${1}' AND"
 fi fi
  
Zeile 2087: Zeile 2130:
         echo "${SQL} table_name='${TAB}';" | mysql ${OPT}         echo "${SQL} table_name='${TAB}';" | mysql ${OPT}
 done done
 +</code>
 +
 +
 +<code bash /root/bin/Datenbankgröße.sh>
 +#!/bin/bash
 +
 +#------------------------------------------------------------------------------#
 +
 +echo "
 +SELECT
 +        table_schema 'Datenbankname',
 +        sum( data_length + index_length ) / 1024 / 1024 'Datenbank in MB',
 +        data_length / 1024 / 1024 'Daten in MB',
 +        index_length / 1024 / 1024 'Index in MB'
 +FROM
 +        information_schema.tables
 +GROUP BY table_schema
 +;" | mysql -t
 +
 +#------------------------------------------------------------------------------#
 +</code>
 +
 +<code bash /root/bin/Tabellengröße.sh>
 +#!/bin/bash
 +
 +#------------------------------------------------------------------------------#
 +echo "
 +SELECT 
 +        table_schema AS 'Datenbankname',
 +        table_name,
 +        ROUND( DATA_LENGTH / 1024 / 1024, 3 ) AS 'Daten (MB)',
 +        ROUND( INDEX_LENGTH / 1024 / 1024, 3 ) AS 'Index (MB)',
 +        ROUND( DATA_FREE / 1024 / 1024, 3 ) AS 'Freier Speicher (MB)'
 +FROM
 +        information_schema.tables
 +WHERE
 +        TABLE_TYPE='BASE TABLE'
 +        AND
 +        ENGINE='InnoDB'
 +        AND
 +        TABLE_SCHEMA<>'mysql'
 +        AND
 +        TABLE_SCHEMA<>'sys'
 +ORDER BY 3
 +;" | mysql -t
 +
 +#------------------------------------------------------------------------------#
 </code> </code>
  
/home/http/wiki/data/attic/mysql.1729771938.txt · Zuletzt geändert: von manfred