Difference between revisions of "MySQL"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
m (→‎Navigation: section transclusion)
 
(16 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
==Navigation==
+
<hide>
−
<section begin=navbar />{{#lst:software|navbar}}: [[database software|databases]]: [[database engines|engines]]: [[MySQL]]<section end=navbar />
+
[[page type::article]]
−
 
+
[[thing type::software]]
−
==Overview==
+
[[software type::database engine]]
 +
[[license::libre/open source]]
 +
</hide>
 +
[[category:needs update]]
 +
==About==
 
[[MySQL]] is a [[FOSS]] database engine and server. It fills roughly the same niche as [[Microsoft SQL Server]].
 
[[MySQL]] is a [[FOSS]] database engine and server. It fills roughly the same niche as [[Microsoft SQL Server]].
  
−
The MySQL package (at least in Linux) comes with a number of helper utilities, including [[MySQL Administrator]] and [[MySQL Query Browser]]. The Administrator utility is useful for making backups, and the Query Browser serves as a front-end for interacting with databases.
+
The MySQL package (at least in Linux) has a number of helper utilities available, including [[MySQL Workbench]] (a combination of the former [[MySQL Administrator]] and [[MySQL Query Browser]] applications).
 
==Articles==
 
==Articles==
 +
* '''SQL Syntax''':
 +
** [[/GROUP_CONCAT]]
 +
** [[/INSERT]]
 +
* '''Versions''': stuff particular to specific versions
 +
** [[/version/5.7]]
 +
* '''Related'''
 +
** [[MS Access and MySQL]]
 
* '''How-To''':
 
* '''How-To''':
−
** [[using MySQL with MS Access]]
+
** [[/migration]]: backing up and restoring
−
** [[backing up MySQL data]]
+
** [[/notes]]: disorganized information, mostly how-to
−
** [[MySQL notes]]: any loose notes not ready to be an article yet
 
 
* '''Components''':
 
* '''Components''':
−
** [[MySQL Administrator]]
+
** [[mysqlcheck]]
−
** [[MySQL Query Browser]]
+
** [[mysqldump]]
−
 
+
** [[MySQL Workbench]]
−
==Criticisms==
+
* [[/archive]]: older stuff that probably is no longer applicable
−
MySQL has been criticized by at least one user of lacking production-quality features, with version 5.0 only just starting to add them, where as other [[FOSS]] alternatives such as [[PostgreSQL]] had such features in their very earliest versions. MySQL has also been criticized for bad performance, e.g. locking entire tables when it only should have locked a row, as well as general incompatibility (Editor's note: with what?).
 
−
 
 
−
* '''Wikipedia''': [[Wikipedia:MySQL#Criticisms of MySQL|Criticisms of MySQL]]
 
−
* '''2007-05-26''' [http://developers.slashdot.org/comments.pl?sid=236249&cid=19280725 Re:MySQL the db for people who don't understand db]
 
−
 
 
−
==Files & Folders==
 
−
The [http://dev.mysql.com/doc/refman/5.1/en/installation-layouts.html Installation Layouts] manual page gives a listing of folders created by a mysql installation, but does not list all files (some of them added to existing folders) upon which a working installation is dependent.
 
−
 
 
−
===RPM distributions===
 
−
(The Debian/Ubuntu setup is similar; folders marked with * indicate those found in Ubuntu and not given in the documentation.)
 
−
{|
 
−
|-
 
−
! Directory || Contents of Directory
 
−
|-
 
−
| */etc/init.d/mysql || daemon start/stop/restart script
 
−
|-
 
−
| */etc/mysql || [[my.cnf]]: configuration file; also other configuration files
 
−
/etc/mysql/conf.d/
 
−
|-
 
−
| /usr/bin || Client programs and scripts
 
−
|-
 
−
| /usr/include/mysql || Include (header) files
 
−
|-
 
−
| /usr/lib/mysql || Libraries
 
−
|-
 
−
| /usr/sbin || The mysqld server
 
−
|-
 
−
| /usr/share/info || Manual in Info format
 
−
|-
 
−
| /usr/share/man || Unix manual pages
 
−
|-
 
−
| /usr/share/mysql || Error message and character set files; *also scripts
 
−
|-
 
−
| /usr/share/sql-bench || Benchmarks
 
−
|-
 
−
| /var/lib/mysql || Log files, databases
 
−
|}
 
−
 
 
−
==Working Notes==
 
−
===SQL error messages===
 
−
* 1271 - Illegal mix of collations for operation 'concat'
 
−
** http://www.issociate.de/board/post/274187/CONCAT_with_IF.html
 
−
** When the problem expression was a CONCAT(...) function, doing CAST(CONCAT(...) AS BINARY) fixed it.
 
−
===munged MySQL installation===
 
−
If you can't reinstall ''or'' remove MySQL because the "{{faint|/etc/init.d/}}mysql start" command fails during (un)installation, check for rogue "mysql" and "mysql-safe" processes which aren't getting stopped by "{{faint|/etc/init.d/}}mysql stop".
 
−
===debian-start===
 
−
The /etc/mysql/debian-start file appears to contain a minor error, at least on the MySQL install I just reinstalled. It sets the variable MYCHECK, and then tries to invoke the command to which it is set by saying just "mycheck;", resulting in the error message "/etc/mysql/debian-start: line 17: mycheck: command not found" although the mysql daemon is apparently restarted successfully. To fix the error, change "mycheck;" to "$mycheck;". (This also has the benefit of actually running whatever command MYCHECK is set to, which is typically some kind of integrity check to help keep your databases from becoming corrupted.)
 
−
===external connections===
 
−
If mysql refuses to respond to connection attempts from anywhere but the local machine, you need to edit /etc/mysql/my.conf and comment out the following line:
 
−
bind-address  = 127.0.0.1
 
−
: and then restart mysqld.
 
−
===miscellaneous===
 
−
To restart the mysql [[daemon]] (mysqld) when you don't have a user with SHUTDOWN privileges but you do have system root:
 
−
sudo /etc/init.d/mysql restart
 
−
===Command-line Client Example===
 
−
'''editing a blank table''': To work around the Query Browser bug (fixed in the latest version) which won't let you edit a blank table, create the first entry using MySQL's command line interface:
 
−
 
 
−
woozle@camilla:~ $ mysql -h<u>yourhost</u> -u<u>yourusername</u> -p<u>yourpassword</u>
 
−
Welcome to the MySQL monitor.  Commands end with ; or \g.
 
−
Your MySQL connection id is 12531702 to server version: 4.1.13-standard
 
−
 
−
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
−
 
−
mysql> use <u>yourdatabasename</u>
 
−
Reading table information for completion of table and column names
 
−
You can turn off this feature to get a quicker startup with -A
 
−
 
−
Database changed
 
−
mysql> INSERT INTO <u>tablename</u> () VALUES();
 
−
Query OK, 1 row affected (0.05 sec)
 
−
 
−
mysql>
 
−
 
 
−
...where <u>yourhost</u> is the name of your MySQL server, and can be anything that can resolve to an IP address, e.g. localhost, yourmachinename, yourserverdomain.com, or an actual IP address (I think).
 
−
===SQL Examples===
 
−
'''creating a table''': If the Query Browser crashes when you try to create or edit a table, use the command line interface to create the table:
 
−
'''create table''' <u>table_name</u> (<u>field_name</u> <u>field_type</u>, <u>field_name</u> <u>field_type</u>{{optargs|, ...}});
 
−
Example:
 
−
<sql>create table example (key varchar(255));</sql>
 
−
* More examples: [[importing data into ZenCart]]
 
−
* Documentation: [http://dev.mysql.com/doc/refman/5.1/en/create-table.html CREATE TABLE Syntax]
 
−
 
 
−
'''making a field autonumbered''':
 
−
'''ALTER TABLE''' <u>tablename</u> '''MODIFY''' <u>fieldname</u> <u>fieldtype</u> '''AUTO_INCREMENT''';
 
−
Example:
 
−
<sql>ALTER TABLE Tracks_Queued MODIFY id INTEGER AUTO_INCREMENT;</sql>
 
−
 
 
−
'''changing the name of a field (column)''':
 
−
'''ALTER TABLE''' <u>tablename</u> '''CHANGE''' <u>oldname</u> <u>newname</u> <u>new_fieldtype</u>;
 
−
This also changes the field's definition, if new_fieldtype is not the same as the field's prior definition.
 
−
 
 
−
Example:
 
−
<sql>ALTER TABLE cat_items CHANGE isInStock qtyInStock INT COMMENT 'number of pieces currently in stock (calculated from stock table)';</sql>
 
−
* Documentation: [http://dev.mysql.com/doc/refman/5.1/en/alter-table.html ALTER TABLE Syntax]
 
  
 
==Links==
 
==Links==
 
===Reference===
 
===Reference===
−
* [http://mysql.com/ MySQL] Home Page
+
* [[URL::http://mysql.com/|official site]]
 
** [http://dev.mysql.com/doc/refman/5.1/en/ Version 5.1 Manual]
 
** [http://dev.mysql.com/doc/refman/5.1/en/ Version 5.1 Manual]
 
** [http://dev.mysql.com/doc/refman/4.1/en/ Version 4.1 Manual]
 
** [http://dev.mysql.com/doc/refman/4.1/en/ Version 4.1 Manual]
−
* {{wikipedia|MySQL}}
+
* {{wikipedia}}
−
* [http://hashmysql.org/ HashMySQL wiki]: official wiki of the unofficial-but-[http://dev.mysql.com/doc/refman/5.1/en/irc.html sanctioned] #mysql [[IRC]] channel on [[Freenode]]
+
* [http://hashmysql.org/ HashMySQL wiki]: official wiki of the unofficial-but-[http://dev.mysql.com/doc/refman/5.1/en/irc.html sanctioned] #mysql [[IRC]] channel on [[freenode]]
 +
 
 +
===How To===
 +
* '''repair munged tables''': see [[mysqlcheck]]
 +
* '''data recovery'''
 +
** [http://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html Chapter 7. Backup and Recovery]
 +
** [https://web.archive.org/web/20200712010317/https://egil.biz/how-to-recover-mysql-data-from-innodb/ How to recover MySQL data from Innodb?] - if you have a copy of /var/www/lib/mysql but MySQL can't see the data
 +
*** The important thing to remember is that not all of the data is in the *.fr? files.
 +
 
 
===News===
 
===News===
 +
* '''2016-05-13''' [https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434 In MySQL, never use "utf8". Use "utf8mb4".]
 
* '''2007-06-21''' [http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9025427&pageNumber=1 MySQL's architect discusses open source, database in a cloud, other IT issues]
 
* '''2007-06-21''' [http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9025427&pageNumber=1 MySQL's architect discusses open source, database in a cloud, other IT issues]
 +
==Archive==
 +
These two apps have been merged into [[MySQL Workbench]]:
 +
* [[MySQL Admin]]istrator
 +
* [[MySQL Query Browser]]

Latest revision as of 14:55, 6 November 2024

About

MySQL is a FOSS database engine and server. It fills roughly the same niche as Microsoft SQL Server.

The MySQL package (at least in Linux) has a number of helper utilities available, including MySQL Workbench (a combination of the former MySQL Administrator and MySQL Query Browser applications).

Articles

Links

Reference

How To

News

Archive

These two apps have been merged into MySQL Workbench:

Retrieved from "https://htyp.org/mw/index.php?title=MySQL&oldid=28985"