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
(→‎Links: official doc on recovery (and backup))
(tidied up by moving stuff to subpages)
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>
 +
==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==
 
* '''Related'''
 
* '''Related'''
Line 15: Line 18:
 
** [[MySQL Administrator]]
 
** [[MySQL Administrator]]
 
** [[MySQL Query Browser]]
 
** [[MySQL Query Browser]]
−
 
+
* [[/notes]]: disorganized information
−
==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===

Revision as of 11:12, 16 November 2016

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

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