MediaWiki/Sql: Difference between revisions
No edit summary |
syntax / typo |
||
| Line 1: | Line 1: | ||
== Server System Variables == | ==Server System Variables== | ||
It's important with MySQL (and MariaDB) to use <code>innodb_file_per_table ON</code> so that you do not end up with an extremely large and ever-growing ibdata1 file.<ref>https://bugs.mysql.com/bug.php?id=1341 <br /> | It's important with MySQL (and MariaDB) to use <code>innodb_file_per_table ON</code> so that you do not end up with an extremely large and ever-growing ibdata1 file.<ref>https://bugs.mysql.com/bug.php?id=1341 <br /> | ||
'''21 Oct 2015 17:57 James Day'''<br /> | '''21 Oct 2015 17:57 James Day'''<br /> | ||
| Line 8: | Line 8: | ||
''Even so, the process of moving tables and doing a new install to get rid of the disk space allocated to the shared tablespace is painful for installations that do have lots of data or redo there.'' (emphasis added) | ''Even so, the process of moving tables and doing a new install to get rid of the disk space allocated to the shared tablespace is painful for installations that do have lots of data or redo there.'' (emphasis added) | ||
<br /><br /> | <br /><br /> | ||
James Day, MySQL | James Day, MySQL Senior Principal Support Engineer, Oracle | ||
</ref> You can't change this setting on an existing database server. You have to set it up initially, and migrate dumps from an old server. If your server is online, you can use replication to a properly configured slave to minimize downtime. Meza and QualityBox use innodb_file_per_table. | </ref> You can't change this setting on an existing database server. You have to set it up initially, and migrate dumps from an old server. If your server is online, you can use replication to a properly configured slave to minimize downtime. Meza and QualityBox use innodb_file_per_table. | ||
== Debugging with the General Query Log == | ==Debugging with the General Query Log== | ||
In the mysql console, you can use a global variable to turn logging on | In the mysql console, you can use a global variable to turn logging on | ||
<source lang="mysql"> | <source lang="mysql"> | ||
| Line 26: | Line 26: | ||
</source> | </source> | ||
== Query Examples == | ==Query Examples== | ||
Some database queries that you might want to use and bookmark in your database administration tool such as [https://dev.mysql.com/downloads/workbench/ MySQL Workbench] or SQLyog http://www.webyog.com/en/index.php | Some database queries that you might want to use and bookmark in your database administration tool such as [https://dev.mysql.com/downloads/workbench/ MySQL Workbench] or SQLyog http://www.webyog.com/en/index.php | ||
| Line 37: | Line 37: | ||
-- How about a table? | -- How about a table? | ||
show create table wiki_demo.l10n_cache; | show create table wiki_demo.l10n_cache; | ||
-- What character collations are specified for each table and column (that supersede any global declaration or default.) | |||
SELECT | SELECT | ||
TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME | TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME | ||
| Line 61: | Line 61: | ||
text | text | ||
WHERE | WHERE | ||
-- old_text LIKE '%#icon%' | |||
old_text REGEXP('#icon') # regex is easier to deal with | old_text REGEXP('#icon') # regex is easier to deal with | ||
) | ) | ||
| Line 92: | Line 92: | ||
</source> | </source> | ||
== See Also == | ==See Also== | ||
* [[Collation]] | |||
* [[mw:Manual:Database_access]] | *[[Collation]] | ||
*[[mw:Manual:Database_access]] | |||
{{References}} | {{References}} | ||