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.

Filter by
Sorted by
Tagged with
507 votes
4 answers
932k views

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?
user avatar
  • 5,205
293 votes
15 answers
179k views

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 ...
user avatar
  • 2,393
273 votes
10 answers
276k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
user avatar
  • 2,949
270 votes
18 answers
249k views

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....
user avatar
  • 3,167
231 votes
4 answers
390k views

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 ...
user avatar
  • 2,684
200 votes
2 answers
465k views

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.
user avatar
  • 2,111
200 votes
7 answers
266k views

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 ...
user avatar
  • 4,099
159 votes
13 answers
297k views

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, ...
user avatar
  • 1,751
156 votes
4 answers
128k views

'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) ...
user avatar
142 votes
5 answers
271k views

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 ...
user avatar
  • 1,533
142 votes
5 answers
232k views

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/...
user avatar
  • 68.8k
142 votes
3 answers
74k views

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 ...
user avatar
  • 2,307
132 votes
4 answers
195k views

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 ...
user avatar
125 votes
11 answers
210k views

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....
user avatar
  • 1,587
113 votes
5 answers
214k views

How to safely change MySQL innodb variable 'innodb_log_file_size'?

So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB: ...
user avatar
  • 22.9k
112 votes
10 answers
312k views

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?
user avatar
  • 10.2k
112 votes
5 answers
166k views

MySQL any way to import a huge (32 GB) sql dump faster?

I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. ...
user avatar
  • 1,223
109 votes
3 answers
386k views

ALTER TABLE - Rename a column

This is driving me nuts! I want to rename a column from read-more to read_more in my blog table I tried all this: ALTER TABLE blog RENAME COLUMN read-more to read_more; ALTER TABLE blog CHANGE ...
user avatar
  • 1,093
107 votes
5 answers
30k views

Storing vs calculating aggregate values

Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly? For example, suppose I have widgets which users can rate (see schema ...
user avatar
  • 4,763
103 votes
6 answers
163k views

Safest way to perform mysqldump on a live system with active reads and writes?

I'm not sure if this is true but I remember reading if you run the following command in linux mysqldump -u username -p database_name > backup_db.sql while reads and writes are being made to a ...
user avatar
  • 1,165
98 votes
3 answers
141k views

What is the default order of records for a SELECT statement in MySQL?

Suppose you have the following table and data: create table t ( k int, v int, index k(k) ) engine=memory; insert into t (k, v) values (10, 1), (10, 2), (10, 3); When ...
user avatar
  • 1,198
98 votes
3 answers
142k views

What is faster, one big query or many small queries?

I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, ...
user avatar
  • 1,081
91 votes
3 answers
205k views

Difference between On Delete Cascade & On Update Cascade in mysql

I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE ...
user avatar
  • 1,021
86 votes
4 answers
137k views

MySQL: Create index If not exists

Is there a way to create an index in MySQL if it does not exist? MySQL does not support the obvious format: CREATE INDEX IF NOT EXISTS index_name ON table(column) ERROR 1064 (42000): You have an ...
user avatar
  • 10.2k
77 votes
7 answers
166k views

How do I completely disable MySQL replication

I am running dual master MySQL replication and now want to move to a single database with no replication. How do I completely disable replication on both databases?
user avatar
69 votes
1 answer
136k views

What is the best way to reduce the size of ibdata in mysql?

I have some Production servers whose ibdata files increase in size day by day. It has already consumed 290GB of space. The tables in the servers are mostly InnoDB and there are high read and write ...
user avatar
  • 9,269
68 votes
5 answers
12k views

How can a group track database schema changes?

What version control methodologies help teams of people track database schema changes?
user avatar
  • 1,128
68 votes
7 answers
175k views

MySQL Error Reading Communication Packets

In MySQL error logs, I see these quite few a warnings like these: 120611 16:12:30 [Warning] Aborted connection 2619503 to db: 'db_name' user: 'user_name' host: 'webapp_hostname' (Got an error reading ...
user avatar
  • 1,179
66 votes
5 answers
116k views

Is it safe to use innodb_flush_log_at_trx_commit = 2

I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
user avatar
  • 805
66 votes
3 answers
79k views

What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
user avatar
65 votes
3 answers
78k views

Find highest level of a hierarchical field: with vs without CTEs

note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database. I ...
user avatar
65 votes
3 answers
89k views

When to use views in MySQL?

When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table? One reason that I would prefer to use views is that the database schema ...
user avatar
62 votes
1 answer
30k views

How do I swap tables in MySQL?

Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries. That's why I want to compute more recent statistics in foo_new ...
user avatar
  • 817
61 votes
4 answers
132k views

Which is faster, InnoDB or MyISAM?

How can MyISAM be "faster" than InnoDB if MyISAM needs to do disk reads for the data? InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
user avatar
  • 1,968
60 votes
3 answers
375k views

How to grant super privilege to the user?

I have created a user and given privileges to the user1. `grant all privileges on db1.* to user1@'%' with grant option; Am using mysql workbench to import dumps to my database. While importing dumps ...
user avatar
59 votes
3 answers
32k views

Is it possible to have passwords configured per database or per host in .my.cnf

I have the following in my ~/.my.cnf [client] password="somepass" but this is not the password I use for every user@host/database I connect to. Is there some way to specify in the config different ...
user avatar
58 votes
4 answers
132k views

How to check foreign keys related to a table

How to see foreign keys related to a table in MySql? Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this: Error Code: 1217. Cannot delete or ...
user avatar
57 votes
6 answers
143k views

Disable MySQL binary logging with log_bin variable

Default MySQL config file /etc/mysql/my.cnf installed by some debian package using APT often set log_bin variable, so binlog are enabled: log_bin = /var/log/mysql/mysql-bin.log When I want to ...
user avatar
57 votes
8 answers
116k views

Restore mysql database with different name

How can we restore mysql database with different name from mysqldump file. I dont't want to open dump file and edit it. Any other better methods?
user avatar
57 votes
2 answers
163k views

How does ORDER BY FIELD() in MySQL work internally

I understand how ORDER BY clause works and how the FIELD() function works. What i want to understand is how the both of them work together to sort. How are the rows retrieved and how is the sort ...
user avatar
  • 736
57 votes
6 answers
39k views

What problems will I get creating a database per customer?

I remember from the stackoverflow podcasts that Fog Creek use a database per customer for Fogbugz. I assume that means the Fogbugz On Demand servers have 10s of thousands of databases. We are just ...
user avatar
56 votes
3 answers
71k views

Why does InnoDB store all databases in one file?

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
user avatar
  • 4,169
55 votes
11 answers
33k views

Why aren't primary key / foreign key matches used for joins?

As far as I could find out many DBMSs (e.g. mysql, postgres, mssql) use fk and pk combinations only to constrain changes to data, but they are rarely natively used to automatically select columns to ...
user avatar
  • 957
55 votes
5 answers
91k views

Why is DROP DATABASE taking so long? (MySQL)

New CentOS installation. I was running an import of a large DB (2GB sql file) and had a problem. The SSH client seemed to lose the connection and the import seemed to freeze. I used another window to ...
user avatar
55 votes
6 answers
29k views

Performance implications of MySQL VARCHAR sizes

Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure ...
user avatar
  • 4,763
54 votes
6 answers
337k views

How to import a .sql file in MySQL?

I am trying to import a .sql file using MySQL Workbench and I get this error: ERROR 1046 (3D000) at line 28: No database selected I have first created an empty database called with the same name as ...
user avatar
53 votes
2 answers
80k views

Possible INDEX on a VARCHAR field in MySql

I am working in a MySql database, with a table like this: +--------------+ | table_name | +--------------+ | myField | +--------------+ ...and I need to make a lot of queries like this (with ...
user avatar
53 votes
7 answers
45k views

Why should I create an ID column when I can use others as key fields? [duplicate]

Possible Duplicate: Why use an int as a lookup table's primary key? So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not think ...
user avatar
52 votes
4 answers
337k views

How to update one table based on another table's values on the fly?

I have a table in the name of ips as below: CREATE TABLE `ips` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `begin_ip_num` int(11) unsigned DEFAULT NULL, `end_ip_num` int(11) unsigned DEFAULT ...
user avatar
  • 3,546
52 votes
7 answers
122k views

EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other?

Whenever I need to check for the existence of some row in a table, I tend to write always a condition like: SELECT a, b, c FROM a_table WHERE EXISTS (SELECT * -- This is what I normally ...
user avatar
  • 12.4k

1
2 3 4 5
426