Applications - Databases - MySQL - Notes - Miscellaneous

: translate to English.

Dit kan uitgezet worden door in my.cnf de regel met log-bin te markeren als commentaar.
Bron: My MySQL Binary Log files are taking up all my disk space! Zie ook: [SOLVED] Gentoo Cleanup

Andere optie is om het volgende SQL commando uit te voeren:

PURGE MASTER LOGS TO ‘last-log-file-name.XXX’;

Bron opmerking bij:Oops, I deleted my MySQL binary logs

: translate to English.

Vergeet niet om dan ook mysql-bin.index te verwijderen.
Bron:Oops, I deleted my MySQL binary logs

: translate to English.

MySQL regelt (externe) toegang tot de server via een username@hostname combinatie. Dit betekent dat je met een 'root'@'localhost' account alleen maar connectie kan maken vanaf de server zelf.

Om een root account aan te maken die alle rechten heeft en inlog hiervoor toe te staan vanaf het 10.1.1.0/24 netwerk, kan het volgende commando gebruikt worden op de mysql> prompt:

GRANT ALL ON *.* TO 'root'@'10.1.1.0/255.255.255.0' IDENTIFIED BY 'securesqlpassword';

Deze account kan later met het volgende commando weer verwijderd worden:

DROP USER 'root'@'10.1.1.0/255.255.255.0';

show grants for 'userlogin'@'hostname';

Example:

show grants for 'root'@'localhost';

Source: MySQL 5.7 Reference Manual - 13.7.5.21 SHOW GRANTS Syntax

SELECT user,host from mysql.user;

For all databases sorted by database name:

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

Sources:

• MySQL Developer Zone Forum - GET THE DATABASE SIZE FROM THE MYSQL QUERY BROWSER
• Stack Overflow - What does information_schema.TABLES.DATA_FREE mean in MySQL?
• Stack Overflow - Why is MySQL's data_free larger than data and indexes combined?

For all tables in all databases sorted by size:

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

Source: Stack Overflow - How to get the sizes of the tables of a MySQL database?

With number of rows and table engine:

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`,
     table_rows AS `Rows`,
     engine AS `Engine`
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

For all tables in all databases:

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES;

For a specific database (change 'dbname' to the name of the database):

SELECT TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'dbname';

Source: Stack Overflow - How can I check MySQL engine type for a specific table?

After dropping a database the storage occupied by that database is not cleared as the ibdata file won't shrink without a full dump, recreate, and restore of the MySQL data storage. Solution/workaround is setting innodb_file_per_table to 1 in the .cnf file to create af file per database table (but this still won't shrink an existing ibdata file). MySQL 5.6 enables innodb_file_per_table by default.

Sources:

• Stack Overflow - How to shrink/purge ibdata1 file in MySQL
• Technology: Learn and Share - MySQL: ibdata files do not shrink on database deletion (innodb)
• Stack Overflow - MySQL InnoDB not releasing disk space after deleting data rows from table
• MySQL 5.5 Reference Manual - 14.10.4 InnoDB File-Per-Table Tablespaces

Encountered/tested on Ubuntu 12.04.5 LTS (precise).

When trying to use mysqldump with the –tab=/path/to/dir/ option to export database table schema and data separately you get the error:

mysqldump: Got error: 1290: The MySQL server is running with the –secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

Check which directories are allowed for data import/export with the following (My)SQL statement:

 SHOW VARIABLES LIKE "secure_file_priv";

This should show the directories. Example on Ubuntu 12.04.5 LTS:

+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

Workaround: use the directory as shown in secure_file_priv value as the basis for the import/export.

Or disable it by adding the following to the [mysql] section of the correct .cnf file:

secure_file_priv=""

Source:

• Stack Overflow - How should I tackle --secure-file-priv in MySQL?
• Super User - How to disable secure-file-priv MySQL Ubuntu
• MySQL 5.7 Reference Manual - Security- 6.1.4 Security-Related mysqld Options and Variables

Show the columns in a table with their data type and default values:

show columns from tablename;

Source: MySQL 5.7 Reference Manual - SHOW COLUMNS Syntax

Tested on a GLPI 9.3 database after upgrade from 9.2.4 running on MariaDB 10.3.9 on CentOS 7.4.

Read and understand MariaDB Knowledge Base - Converting Tables from MyISAM to InnoDB before blindly copying and pasting the below statements! You have been warned!

1. Enter a mysql prompt:

   mysql -u root -p

2. Execute the following to get an alter_table.sql script with an ALTER TABLE line per table:

   USE database_name;
   SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ENGINE=InnoDB;') 
   FROM INFORMATION_SCHEMA.TABLES 
   WHERE TABLE_SCHEMA='databasename' 
   AND ENGINE = 'MyISAM' 
   INTO OUTFILE '/tmp/alter_tables.sql';

   Hint: you may want to change /tmp/alter_tables.sql to a more suitable location (as in: not /tmp/).

3. Exit the mysql prompt.
4. Execute the alter_tables.sql script:

   mysql -u root -p database_name < /tmp/alter_tables.sql

Sources:

• MariaDB Knowledge Base - Converting Tables from MyISAM to InnoDB
• Stack Overflow - How to convert all tables from MyISAM into InnoDB?