knowledge base


mysql howto
MySQL Datenbank Befehle
rename table TABFooBar to foobar
ALTER TABLE TABFooBar RENAME AS foobar;
rename column bar to foo
ALTER TABLE foobar CHANGE bar foo VARCHAR(255) NOT NULL;
modify column definition
ALTER TABLE foobar MODIFY foo BIGINT NOT NULL;
add new column baz after foo
ALTER TABLE foobar ADD baz VARCHAR(60) AFTER foo;
optimize table
OPTIMIZE TABLE foobar;
show tables description, columns etc
DESCRIBE TABLE foobar;
show all available mysql users
SELECT * FROM mysql.user;
delete mysql user
DROP USER username@host;
MySQL Verbindungen + weitere Verbindungsrelevante Infos anzeigen
show status like '%onn%';
Der status Parameter der mysqladmin Binary auf der Kommandozeile
mysqladmin status
Clean MySQL table and reset auto_increment counter
TRUNCATE TABLE tablename;
Clean MySQL table only
DELETE FROM tablename;
MySQL Prozessliste anzeigen
show processlist +----+------+-------------------+--------------+---------+-------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-------------------+--------------+---------+-------+-------+------------------+ | 36 | js | 172.19.73.2:58892 | NULL | Sleep | 28514 | | NULL | | 79 | root | localhost:43350 | timeout_test | Sleep | 4 | | NULL | | 80 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-------------------+--------------+---------+-------+-------+------------------+
MySQL Prozess abbrechen
mysql -e "kill 79";
Timeouts auflisten
SHOW VARIABLES LIKE 'connect_timeout';
Timeouts definieren
SET GLOBAL connect_timeout=60; SET interactive_timeout=200;
show max_used_connections
root@localhost [(none)]> SHOW GLOBAL STATUS LIKE 'max_used_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 141 | +----------------------+-------+ 1 row in set (0.00 sec)
show the value of created threads
SHOW GLOBAL STATUS LIKE 'Threads_created'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | Threads_created | 141 | +-----------------+-------+ 1 row in set (0.00 sec)
show connections value
SHOW GLOBAL STATUS LIKE 'connections'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | Connections | 4675382 | +---------------+---------+ 1 row in set (0.00 sec)
Note that “wait_timeout” would be helpful to clear the sleeping process as “interactive_timeout” does not make any performance improvement since it affect the command line sessions. Obviously increasing the values of connect_timeout, net_read_timeout and net_write_timeout would help to skip the timeout errors when lengthy queries are being executed.
SHOW PLUGINS; +--------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +--------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | . . | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +--------------------------+----------+--------------------+---------+---------+
show Database Sizes
SELECT table_schema “Database Name”, sum( data_length + index_length ) / 1024 / 1024 “Database Size in MB” FROM information_schema.TABLES GROUP BY table_schema;
show variables log%
show variables like "log%";
show engines
show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | . . | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
login to mysql database
mysql -uroot -p'PASSWORD'
show tablespaces
SELECT table_schema “Database Name”, sum( data_length + index_length ) / 1024 / 1024 “Database Size in MB” FROM information_schema.TABLES GROUP BY table_schema;
show tablenames
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = ‘database_name’;
show databases
SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | boi_23642 | . . | session_52763 | | session_59885 | +--------------------+ 18 rows in set (0.00 sec)
SHOW TABLES FROM Database
SHOW TABLES FROM boi_59885; +--------------------------------+ | Tables_in_boi_59885 | +--------------------------------+ | DATABASECHANGELOG | | DATABASECHANGELOGLOCK | . . | updates | | user_status_code | +--------------------------------+ 95 rows in set (0.01 sec)
show table status
SHOW TABLE STATUS FROM boi_59885; +--------------------------------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----------------------------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_incr ement | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------------------------------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----------------------------------------+ | DATABASECHANGELOG | InnoDB | 10 | Compact | 178 | 1012 | 180224 | 0 | 0 | 0 | NULL | 2018-01-10 15:12:07 | NULL | NULL | utf8_unicode_ci | NULL | | | | DATABASECHANGELOGLOCK | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 0 | 0 | NULL | 2018-01-10 15:12:07 | NULL | NULL | utf8_unicode_ci | NULL | | | . . | updates | InnoDB | 10 | Compact | 38 | 431 | 16384 | 0 | 0 | 0 | NULL | 2018-01-10 11:40:53 | NULL | NULL | utf8_unicode_ci | NULL | | Contains the list of stucture updates. | | user_status_code | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 0 | 0 | 3 | 2018-01-09 17:41:51 | NULL | NULL | utf8_unicode_ci | NULL | | | +--------------------------------+--------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----------------------------------------+
show table spaces
SELECT table_name, round(((data_length + index_length) / (1024*1024)),2) as "size in megs" FROM information_ schema.tables WHERE table_schema = "boi_59885"; +--------------------------------+--------------+ | table_name | size in megs | +--------------------------------+--------------+ | DATABASECHANGELOG | 0.17 | | DATABASECHANGELOGLOCK | 0.02 | . . | updates | 0.02 | | user_status_code | 0.02 | +--------------------------------+--------------+
MySQL Datenbank erstellen und Zugriffsberechtigung setzen
mysql -u root -p create database MYDATABASENAME; create user 'MYUSERNAME'@'localhost' identified by 'MYPASSWORD'; grant all privileges on MYDATABASENAME.* to 'MYUSERNAME'@'localhost' with grant option; flush privileges; quit
MySQL root Passwort zurücksetzen
Sollte das Passwort des MySQL root Benutzers einmal verloren gehen, können folgende Befehle verwendet werden, um das
MySQL-Passwort zurückzusetzen. Der MySQL Dienst muss neu gestartet werden, damit keine Anmeldedaten angefordert werden.

Aus Sicherheitsgründen wird deshalb nur die lokale Anmeldung zugelassen!

mysqld --skip-grant-tables --skip-networking
Sobald der Server gestartet ist, kann sich ohne Authentifizierung mit dem MySQL Server verbunden werden:
mysql
Damit die folgenden Befehle funktionieren, muss die Berechtigungstabelle neu eingelesen werden:
FLUSH PRIVILEGES;
Für alle Versionen bis zu MySQL 5.7.5 wird, zum Zurücksetzen des Passwortes es root Benutzers:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');
Bei allen Versionen ab 5.7.6 muss hingegen folgender Befehl verwendet werden:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
Verbindung zum MySQL Server wird wie folgt getrennt:
exit
MySQL Dump vor Backup erstellen
duply bietet weiter die Moeglichkeit vor und nach dem Backup Kommandos auszufuehren. Dafuer muss neben der conf Datei eine oder auch die beiden Dateien pre und/oder post liegen. Bitte Dateiberechtigung (chmod ug+x pre) der Scriptdateien beachten!
#!/bin/bash ## create backup directory if not exist if [ ! -d "/opt/backup" ]; then mkdir /opt/backup; fi ## delete old backup files older than 1 month find /opt/backup -name "*.gz" -type f -mtime +31 -delete ## create dump of all databases mysqldump --opt --routines --add-drop-database --default-character-set=utf8 --create-options --events --all-databases | gzip > /opt/backup/$(date -I)-all-databases.sql.gz
Das MySQL Zugangspasswort wurde in der Datei /root/.my.cnf abgespeichert.
[client] password=PASSWORD
Es befinden sich nun die folgenden drei Dateien unter /etc/duply/HOSTNAME/:
host:/etc/duply/HOSTNAME# ll insgesamt 16 -rw------- 1 root root 4633 1. Sep 17:20 conf -rw-r--r-- 1 root root 168 7. Aug 21:26 exclude -rwxr-x--- 1 root root 415 1. Sep 17:30 pre
cron Eintrag anlegen
Damit jede Nacht das Backup ausgefuehrt und gegebenfalls alte Backupsets geloescht werden, wurde die Datei /etc/cron.daily/duply_HOSTNAME erstellt.
# # nightly duply backup of profile "HOSTNAME" # 5 4 * * * root /usr/bin/duply HOSTNAME backup_verify_purge --force