Difference between revisions of "MySQL"

Revision as of 16:06, 27 October 2019

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

SQL Syntax:
- /GROUP_CONCAT
- /INSERT
Versions: stuff particular to specific versions
- /version/5.7
Related
- MS Access and MySQL
How-To:
- /porting/data: backing up and restoring
- /notes: disorganized information, mostly how-to
Components:
- MySQL Administrator
- MySQL Query Browser - now replaced with Workbench
- MySQL Workbench
/archive: older stuff that probably is no longer applicable

Links

Reference

official site
- Version 5.1 Manual
- Version 4.1 Manual
Wikipedia
HashMySQL wiki: official wiki of the unofficial-but-sanctioned #mysql IRC channel on Freenode

How To

repair munged tables
- 4.5.3 mysqlcheck — A Table Maintenance Program
data recovery
- Chapter 7. Backup and Recovery
- 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

2016-05-13 In MySQL, never use "utf8". Use "utf8mb4".
2007-06-21 MySQL's architect discusses open source, database in a cloud, other IT issues

@@ Line 1: / Line 1: @@
-==Navigation==
+<hide>
-[[computing]]: [[software]]: [[database software|databases]]: [[database engines|engines]]: [[MySQL]]
+[[page type::article]]
-==Overview==
+[[thing type::software]]
+[[software type::database engine]]
+[[license::libre/open source]]
+</hide>
+==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) 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==
+* '''SQL Syntax''':
+** [[/GROUP_CONCAT]]
+** [[/INSERT]]
+* '''Versions''': stuff particular to specific versions
+** [[/version/5.7]]
+* '''Related'''
+** [[MS Access and MySQL]]
 * '''How-To''':
-** [[using MySQL with MS Access]]
+** [[/porting/data]]: 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''':
 ** [[MySQL Administrator]]
-** [[MySQL Query Browser]]
+** [[MySQL Query Browser]] - now replaced with Workbench
+** [[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==
 ===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/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]]
+===How To===
+* '''repair munged tables'''
+** [https://dev.mysql.com/doc/refman/5.5/en/mysqlcheck.html 4.5.3 mysqlcheck — A Table Maintenance Program]
+* '''data recovery'''
+** [http://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html Chapter 7. Backup and Recovery]
+** [http://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===
+* '''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]