======Applications - Databases - MySQL - Notes - Miscellaneous======


=====mysql-bin bestanden vreten schijfruimte=====
FIXME: translate to English.

Dit kan uitgezet worden door in my.cnf de regel met log-bin te markeren als commentaar. \\
Bron: [[http://systembash.com/content/mysql-binary-log-file-size-huge/|My MySQL Binary Log files are taking up all my disk space!]] Zie ook: [[http://forums.gentoo.org/viewtopic-p-4423769.html|[SOLVED] Gentoo Cleanup]] \\

Andere optie is om het volgende SQL commando uit te voeren:
<code>
PURGE MASTER LOGS TO ‘last-log-file-name.XXX’;
</code>
Bron opmerking bij:[[http://www.zmanda.com/blogs/?p=17|Oops, I deleted my MySQL binary logs]] \\


=====MySQL start niet meer op na met de hand verwijderen alle binary logs=====

FIXME: translate to English.

Vergeet niet om dan ook mysql-bin.index te verwijderen. \\
Bron:[[http://www.zmanda.com/blogs/?p=17|Oops, I deleted my MySQL binary logs]] \\


=====Remote toegang=====

FIXME: 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:
<code>
GRANT ALL ON *.* TO 'root'@'10.1.1.0/255.255.255.0' IDENTIFIED BY 'securesqlpassword';
</code>
Deze account kan later met het volgende commando weer verwijderd worden:
<code>
DROP USER 'root'@'10.1.1.0/255.255.255.0';
</code>


=====Show permissions (grants) for user=====

<code mysql>
show grants for 'userlogin'@'hostname';
</code>

Example:
<code mysql>
show grants for 'root'@'localhost';
</code>

Source: [[https://dev.mysql.com/doc/refman/5.7/en/show-grants.html|MySQL 5.7 Reference Manual - 13.7.5.21 SHOW GRANTS Syntax]] \\


=====Show users=====

<code mysql>
SELECT user,host from mysql.user;
</code>




=====Show database sizes=====

For all databases sorted by database name:
<code mysql>
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 ;
</code>

Sources:
  * [[https://forums.mysql.com/read.php?108,201578,201578|MySQL Developer Zone Forum - GET THE DATABASE SIZE FROM THE MYSQL QUERY BROWSER]]
  * [[https://stackoverflow.com/questions/9731853/what-does-information-schema-tables-data-free-mean-in-mysql|Stack Overflow - What does information_schema.TABLES.DATA_FREE mean in MySQL?]]
  * [[https://stackoverflow.com/questions/19300893/why-is-mysqls-data-free-larger-than-data-and-indexes-combined|Stack Overflow - Why is MySQL's data_free larger than data and indexes combined?]]


=====Show table sizes=====

For all tables in all databases sorted by size:
<code mysql>
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;
</code>
Source: [[https://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database|Stack Overflow - How to get the sizes of the tables of a MySQL database?]]

With number of rows and table engine:
<code mysql>
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;
</code>


=====Show MySQL Engine type per table=====

For all tables in all databases:
<code mysql>
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES;
</code>

For a specific database (change 'dbname' to the name of the database):
<code mysql>
SELECT TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'dbname';
</code>
Source: [[https://stackoverflow.com/questions/213543/how-can-i-check-mysql-engine-type-for-a-specific-table|Stack Overflow - How can I check MySQL engine type for a specific table?]] 


=====ibdata files don't shrink after drop database=====

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:
  * [[https://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql/3456885#3456885|Stack Overflow - How to shrink/purge ibdata1 file in MySQL]]
  * [[http://crazytoon.com/2007/04/03/mysql-ibdata-files-do-not-shrink-on-database-deletion-innodb/|Technology: Learn and Share - MySQL: ibdata files do not shrink on database deletion (innodb)]]
  * [[https://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table|Stack Overflow - MySQL InnoDB not releasing disk space after deleting data rows from table]]
  * [[https://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html|MySQL 5.5 Reference Manual - 14.10.4 InnoDB File-Per-Table Tablespaces]]


=====The MySQL server is running with the --secure-file-priv option=====

// 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:
<code>
 SHOW VARIABLES LIKE "secure_file_priv";
</code>

This should show the directories. Example on Ubuntu 12.04.5 LTS:
<code>
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)
</code>

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:
<code>
secure_file_priv=""
</code>

Source: 
  * [[https://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql|Stack Overflow - How should I tackle --secure-file-priv in MySQL?]]
  * [[https://superuser.com/questions/1088512/how-to-disable-secure-file-priv-mysql-ubuntu|Super User - How to disable secure-file-priv MySQL Ubuntu]]
  * [[https://dev.mysql.com/doc/refman/5.7/en/security-options.html|MySQL 5.7 Reference Manual - Security- 6.1.4 Security-Related mysqld Options and Variables]]


=====Show column types=====

Show the columns in a table with their data type and default values:
<code mysql>
show columns from tablename;
</code>
Source: [[https://dev.mysql.com/doc/refman/5.7/en/show-columns.html|MySQL 5.7 Reference Manual - SHOW COLUMNS Syntax]]


=====Migrate MyISAM tables to InnoDB engine=====

// Tested on a [[https://glpi-project.org/|GLPI]] 9.3 database after upgrade from 9.2.4 running on MariaDB 10.3.9 on CentOS 7.4. //

:!::!: Read and understand [[https://mariadb.com/kb/en/library/converting-tables-from-myisam-to-innodb/|MariaDB Knowledge Base - Converting Tables from MyISAM to InnoDB]] before blindly copying and pasting the below statements! You have been warned! :!::!:

  - Enter a mysql prompt:<code>mysql -u root -p</code>
  - Execute the following to get an alter_table.sql script with an ALTER TABLE line per table:<code mysql>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';</code> // Hint: you may want to change /tmp/alter_tables.sql to a more suitable location (as in: not /tmp/). //
  - Exit the mysql prompt.
  - Execute the alter_tables.sql script:<code>mysql -u root -p database_name < /tmp/alter_tables.sql</code>

Sources:
  * [[https://mariadb.com/kb/en/library/converting-tables-from-myisam-to-innodb/|MariaDB Knowledge Base - Converting Tables from MyISAM to InnoDB]]
  * [[https://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb|Stack Overflow - How to convert all tables from MyISAM into InnoDB?]]