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.
22,189
questions
542
votes
4
answers
1.0m
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?
- 5,555
302
votes
15
answers
193k
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 ...
- 2,493
297
votes
18
answers
294k
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....
- 3,457
282
votes
10
answers
284k
views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
- 3,049
253
votes
6
answers
445k
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 ...
- 2,934
216
votes
2
answers
551k
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.
- 2,271
216
votes
5
answers
246k
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)
...
subhanshu kumar
207
votes
7
answers
294k
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 ...
- 4,229
169
votes
13
answers
339k
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, ...
- 1,851
164
votes
6
answers
316k
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 ...
- 1,773
147
votes
3
answers
83k
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 ...
- 2,417
146
votes
4
answers
236k
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 ...
user18530
142
votes
5
answers
249k
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/...
- 69.7k
141
votes
11
answers
263k
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....
- 1,747
128
votes
5
answers
208k
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. ...
- 1,383
125
votes
10
answers
356k
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?
- 11.5k
118
votes
3
answers
173k
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, ...
- 1,281
117
votes
5
answers
232k
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:
...
- 23.4k
113
votes
3
answers
391k
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 ...
- 1,133
109
votes
5
answers
32k
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 ...
- 4,883
108
votes
3
answers
166k
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 ...
- 1,298
108
votes
6
answers
178k
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 ...
- 1,215
99
votes
3
answers
246k
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 ...
- 1,101
93
votes
4
answers
171k
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 ...
- 11.5k
80
votes
7
answers
186k
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?
David Collie
79
votes
8
answers
236k
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 ...
- 1,317
72
votes
1
answer
148k
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 ...
- 9,627
70
votes
6
answers
204k
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 ...
- 2,328
69
votes
5
answers
130k
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?
- 835
68
votes
1
answer
33k
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 ...
- 877
68
votes
3
answers
86k
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 ...
- 781
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?
- 1,128
66
votes
3
answers
92k
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 ...
- 3,132
65
votes
3
answers
81k
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 ...
- 3,132
65
votes
3
answers
461k
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 ...
64
votes
4
answers
138k
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?
- 1,999
63
votes
2
answers
199k
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 ...
- 796
62
votes
8
answers
133k
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?
- 1,516
60
votes
3
answers
34k
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 ...
- 2,831
60
votes
6
answers
32k
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 ...
- 4,883
59
votes
6
answers
42k
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 ...
- 693
59
votes
4
answers
149k
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 ...
- 901
59
votes
1
answer
57k
views
What is the meaning of filtered in MySQL explain?
As described here in the MySQL docs:
The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows ...
- 635
58
votes
5
answers
103k
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 ...
- 1,220
58
votes
3
answers
93k
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 ...
- 683
58
votes
11
answers
35k
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 ...
- 997
57
votes
7
answers
150k
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 ...
- 13.3k
56
votes
7
answers
49k
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 ...
- 707
55
votes
6
answers
348k
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 ...
Barbara Dreamer
55
votes
3
answers
72k
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 ...
- 4,491