Questions tagged [mysql]

All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

MySQL is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. MySQL is officially pronounced /maɪˌɛskjuːˈɛl/ ("My S-Q-L"), but is often pronounced /maɪˈsiːkwəl/ ("My Sequel"). It is named for My, the daughter of Michael Widenius (the original developer of MySQL)

The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL was originally owned and sponsored by a single for-profit firm, the Swedish company MySQL AB.

MySQL has changed ownership a few times over the years. Sun purchased MySQL in January 2008 for $1B. Some 15 months later, before the ink could fully dry, Oracle buys Sun. This made MySQL a subsidiary of Oracle Corporation.

Ever since Oracle has stepped into the picture, they have made good on their promises to make steady strides in MySQL development, which have come to fruition heretofore by means of better performance and configurability. However, older bugs still exist in MySQL. Oracle has not taken up the cause to fix these older bugs fast enough.

In light of this and in keeping with the spirit of open source liberty and freedom, forks of MySQL have propelled in the OpenSource DB World as viable alternatives:

These two are very active and highly regarded.

The two forks/trees/branches below must now be considered defunct.

MySQL can run on multiple platforms (32-bit and 64-bit)

  • Linux (Redhat Enterprise, Oracle Enterprise, Generic, Linux6 as for 5.5.17)
  • Sun Solaris
  • macOS
  • Free BSD
  • Microsoft Windows

MySQL features the use of several storage engines

Each Storage Engine has Distinct Properties that make efficient usage of data depending on

  1. Read Performance
  2. Write Performance
  3. Storage Requirements
  4. Memory Utilization
  5. Tuning the Engine Settings for
  • Internal Use
  • Multiple CPUs
  • OS Usage

For example, InnoDB has undergone a facelift which now allows it to take advantage of multiple CPU architectures. It was first introduced in MySQL 5.1.38 InnoDB Plugin. Those changes have now been fully incorporated in MySQL 5.5's InnoDB (Note: Percona Server already had these enhancements in 5.0 and 5.1. Oracle is nicely catching up). The necessary options have default settings that must be tuned to engage multicore activity.

Other third party storage engines have been used in MySQL including:

22059 questions
4 answers

How do you mysqldump specific table(s)?

How can I dump a specific table or set of tables without including the rest of the db tables?
  • 5,375
  • 3
  • 15
  • 6
15 answers

Can MySQL reasonably perform queries on billions of rows?

I am planning on storing scans from a mass spectrometer in a MySQL database and would like to know whether storing and analyzing this amount of data is remotely feasible. I know performance varies wildly depending on the environment, but I'm looking…
  • 2,463
  • 2
  • 13
  • 7
18 answers

How can I monitor the progress of an import of a large .sql file?

I am importing a 7 GB foobar.sql to restore a table in a local database. $ mysql -h localhost -u root 'my_data' < foobar.sql $ mysql --version /usr/local/mysql/bin/mysql Ver 14.12 Distrib 5.0.96, for apple-darwin9.8.0 (i386) using readline…
  • 3,327
  • 9
  • 21
  • 21
10 answers

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
  • 3,009
  • 5
  • 19
  • 13
5 answers

How large should be mysql innodb_buffer_pool_size?

I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and grind the server to halt. The average query per second…
  • 2,844
  • 6
  • 19
  • 18
2 answers

Create a MySQL database with charset UTF-8

I'm new to MySQL and I would like to know: How can I create a database with charset utf-8 like I did in navicat? create mydatabase; ...seems to be using some kind of default charset.
  • 2,221
  • 2
  • 11
  • 4
7 answers

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to backup the database nightly. Due to my comcast…
  • 4,179
  • 7
  • 27
  • 27
4 answers

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

I am trying to backup mysql using the command mysqldump -u root -p database_name > backup.sql , but it is throwing an error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump…
subhanshu kumar
13 answers

How can I move a database from one server to another?

How can I move MySQL tables from one physical server to another? Such as this exact scenario: I have a MySQL server that uses innodb table and is about 20GB size. I want to move it to a new server, what's the most efficient way to do this?
  • 1,791
  • 2
  • 13
  • 10
6 answers

Good explanation of cascade (ON DELETE/UPDATE) behavior

I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is which. For example, if I have two tables -…
  • 1,663
  • 2
  • 11
  • 7
3 answers

Advantages and Disadvantages to using ENUM vs Integer types?

Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled. Is it better for this column's data type to be an int/bool (1 or zero) or to use ENUM with the values being enabled and…
Jake Wilson
  • 2,377
  • 8
  • 21
  • 23
5 answers

Possible to make MySQL use more than one core?

I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help Setup The servers are quite hefty with an OLAP/DataWarehouse (DW) type load: Primary: 96GB RAM, 8…
  • 69,343
  • 8
  • 160
  • 240
4 answers

Is it safe to delete mysql-bin files?

I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those files like mysql-bin.000123, mysql-bin.000223 etc.…
11 answers

How to easily convert utf8 tables to utf8mb4 in MySQL 5.5

I have a database which now needs to support 4 byte characters (Chinese). Luckily I already have MySQL 5.5 in production. So I would just like to make all collations which are utf8_bin to utf8mb4_bin. I believe there is no performance loss/gain with…
  • 1,677
  • 2
  • 12
  • 11
10 answers

mysql: Show GRANTs for all users

MySQL's SHOW GRANTS shows the permissions of the current user. Is there a way to log in as root and show the permissions of all users?
Adam Matan
  • 11,019
  • 29
  • 79
  • 94
2 3
99 100