Questions tagged [mysql]
MySQL is an open-source database owned by Oracle. ServerFault topics include how to run the server. For more MySQL specific questions like backup/restore/recovery/configuration, dba.stackexchange.com is probably a better home.
8,753
questions
503
votes
23
answers
364k
views
mysqldump throws: Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
Every time I try to make a mysqldump I get the following error:
$> mysqldump --single-transaction --host host -u user -p db > db.sql
mysqldump: Couldn't execute 'SELECT COLUMN_NAME, ...
- 6,469
313
votes
8
answers
798k
views
How to bind MySQL server to more than one IP address?
Is there a secret way to bind MySQL to more than one IP address?
As far as I can see the bind-address parameter in the my.cnf does not support more than one IP and you can't have it more than once.
- 3,936
230
votes
13
answers
466k
views
How do I load a sql.gz file to my database? (importing)
I'm trying to import a gzipped SQL file into mysql directly. Is this the right way?
mysql -uroot -ppassword mydb > myfile.sql.gz
- 8,641
141
votes
10
answers
180k
views
Is there an equivalent of MySQL's SHOW CREATE TABLE in Postgres?
Is there an equivalent of MySQL's SHOW CREATE TABLE in Postgres? Is this possible? If not what is the next best solution?
I need the statement because I use it to create the table on an remote server ...
- 1,513
127
votes
3
answers
435k
views
How can I show user's privileges in MySQL?
I know that I can set user's privileges in the following simple way:
grant all on [database name].[table name] to [user name]@[host name];
But how can I see existing privileges?
I need to see data ...
- 2,639
119
votes
5
answers
49k
views
Postgres equivalent to MySQL's \G?
Does anyone know if Postgres has a way to display query results "prettily", like how MySQL does when ending a query with \G on the command line? For instance, "select * from sometable\G" as opposed ...
- 1,520
118
votes
2
answers
92k
views
Why does the MySQL command line tool ignore the --port parameter?
This is what I'm doing:
mysql --host=localhost --port=9999 mysql -u root -p --execute="show tables;"
The command works (connecting to port 3306) no matter what I provide in --port argument. I have ...
- 1,856
110
votes
1
answer
158k
views
InnoDB: Error: log file ./ib_logfile0 is of different size
I just added the following lines in /etc/mysql/my.cnf after I converted one database to use InnoDB engine.
innodb_buffer_pool_size = 2560M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
...
- 1,725
102
votes
9
answers
187k
views
mysqldump to a tar.gz
Usually after dumping a MySQL database with mysqldump command I immediately tar/gzip the resultant file. I'm looking for a way to do this in one command:
So from this:
mysqldump dbname -u root -p &...
- 1,201
99
votes
11
answers
153k
views
How can I export the privileges from MySQL and then import to a new server?
I know how to export/import the databases using mysqldump & that's fine but how do I get the privileges into the new server.
For extra points, there are a couple of existing databases on the new ...
- 8,633
95
votes
8
answers
383k
views
How can I show mysql locks?
Is there anyway to show all the locks that are active in a mysql database?
- 32.5k
93
votes
6
answers
281k
views
Changing host permissions for MySQL users
I have the following grants for a user/database
mysql> SHOW GRANTS FOR 'username'@'localhost';
+---------------------------------------------------------------------------+
| Grants for username@...
- 1,233
86
votes
12
answers
136k
views
Where is my mysql log on OS X?
I checked /var/log and /usr/local/mysql and i can't seem to find the log. I am trying to troubleshoot an error establishing a database connection with a php function.
- 4,273
83
votes
10
answers
671k
views
How to check what port mysql is running on
On my windows dev box mysql is running on port 3306
How can I check what port it is running on the unix server that I have to upload the app to.
- 2,379
78
votes
9
answers
120k
views
What is the debian-sys-maint MySQL user (and more)?
I have been bitten several times by the 'debian-sys-maint' user that is installed by default on the mysql-server packages installed from the Ubuntu repositories.
Generally what happens is I pull a ...
- 1,949
72
votes
3
answers
197k
views
How do I change the privileges for MySQL user that is already created?
I understand how to create a new user with privileges, but what is the correct way to change privileges for users that are already created?
We are running a DB audit and some of the users have way ...
- 970
71
votes
7
answers
142k
views
How to force MySQL to connect by TCP instead of a Unix socket?
I would like to analyze mysql traffic. Right now, all mysql requests are sent to the MySQL unix socket:
unix 2 [ ACC ] STREAM LISTENING 3734388 15304/mysqld /var/run/mysqld/...
- 3,583
71
votes
18
answers
131k
views
`mysql_upgrade` is failing with no real reason given
I'm upgrading from MySQL 5.1 to 5.5, running mysql_upgrade and getting this output:
# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed
...
- 1,187
71
votes
9
answers
211k
views
Unknown/unsupported storage engine: InnoDB | MySQL Ubuntu
I recently upgraded from the previous LTS Ubuntu to Precise and now mysql refuses to start. It complains of the following when I attempt to start it:
╰$ sudo service mysql restart
stop: Unknown ...
- 843
66
votes
12
answers
227k
views
MySQL keeps crashing: InnoDB: Unable to lock ./ibdata1, error: 11
I have a simple webserver (Debian 6.0 x86, DirectAdmin with 1 GB of memory and still 10 GB free space, mySQl version 5.5.9), however the mySQL server keeps crashing and I need to kill all mySQL ...
- 1,483
65
votes
6
answers
174k
views
How to rename a MySQL database?
How to rename a MySQL database?
The MySQL online manual has said about the RENAME DATABASE command (this documentation page has been removed by Oracle some time ago):
This statement was added in ...
- 1,107
63
votes
7
answers
110k
views
How to change my MySQL root password back to empty?
When I'm working locally, I don't really need to enter my password to access my database. I changed my root password when I first installed MySQL, but I don't know how to change my password back. What ...
- 3,573
56
votes
4
answers
147k
views
MySQL: creating a user that can connect from multiple hosts
I'm using MySQL and I need to create an account that can connect from either the localhost or from another server, i.e. 10.1.1.1. So I am doing:
CREATE USER 'bob'@'localhost' IDENTIFIED BY '...
- 7,106
53
votes
4
answers
71k
views
What is "freeable memory"?
Amazon RDS has a metric for 'freeable memory'. It appears to go up & down in a sawtooth pattern.
This leads me to believe that it's memory that is being used by MySQL for caching and that when ...
- 1,717
50
votes
5
answers
127k
views
How to export result of a MySQL query using phpMyAdmin 3.4.3?
I've got a 30K row table
When I run a long, 50-line query on that table, a GROUP function reduces the number of rows to 7K
I want to export the grouped 7K rows as a new table, or save them as a
CSV
...
- 501
50
votes
4
answers
192k
views
MySQL warning "IP address could not be resolved"
I've got MySQL Master/Slave setup and I've noticed the following warnings in the mysql log files on both servers:
[Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not ...
- 3,208
49
votes
7
answers
56k
views
What is the best Linux filesystem for MySQL (InnoDB)?
I tried to look for benchmark on the performances of various filesystems with MySQL InnoDB but couldn't find any.
My database workload is the typical web-based OLTP, about 90% read, 10% write. Random ...
- 3,130
48
votes
8
answers
90k
views
Why can't I connect to Amazon RDS after setting it up?
So, I just created the Amazon RDS account.
And I started an instance of database.
The "endpoint" is:
abcw3n-prod.cbmbuiv8aakk.us-east-1.rds.amazonaws.com
Great! Now I try to connect to it from one ...
- 8,641
47
votes
4
answers
125k
views
What is the ibdata1 file in my /var/lib/mysql directory?
Logging in to my Webmin control panel, I noticed that virtually all of my disk space is full. I searched for the ten largest files/ directories on my system and found that a file called ibdata1 is ...
- 643
46
votes
7
answers
57k
views
Forward local port or socket file to remote socket file
Quick question - I run two linux boxes, one my own desktop and the other my VPS. For security reasons on the VPS end I opted for socket connections to MySQL (/var/run/mysqld/mysql.sock). I know I can ...
user32616
46
votes
3
answers
359k
views
How to check my PHP and MySQL version on Ubuntu VPS?
HI I am going to install MyBB but I am not sure whether I have installed the correct version of PHP and MySQL.
PHP version 5.1.0 or above with XML Extension installed
MySQL version 4.0 or ...
- 683
42
votes
4
answers
172k
views
Allow linux root user mysql root access without password
On cPanel when I am logged in as root and type "mysql" without hostname and password it gives me direct access to mysql root user.
I would like to do this for one of my non-cpanel server where the ...
- 481
41
votes
3
answers
117k
views
MySQL lowering wait_timeout value to lower number of open connections
I run a rather busy site, and during peek hours I see over 10.000 open connections to my database server on my webserver when a run a netstat command. 99% of the connections are in the TIME_WAIT state....
- 1,481
41
votes
4
answers
38k
views
What's a secure alternative to using a MySQL password on the command line?
We have a PHP command-line script to version a database.
We run this script whenever a developer has added a new database patch.
The script runs the patch with the MySQL command-line:
system('mysql -...
- 4,607
40
votes
4
answers
25k
views
Why Swap is used when plenty of free memory is left?
I have pretty good web (dedicated) server with good memory resources:
System information
Server load 2.19 (8 CPUs)
Memory Used 29.53% (4,804,144 of 16,267,652)
Swap Used 10.52% (220,...
- 4,522
40
votes
6
answers
273k
views
How to enable MySQL logging?
I am running MySQL 5.0.45 on OS X 10.6.1 and I cannot get MySQL to log anything. I am debugging an application and need to see all queries and errors.
I added to the etc/my.cnf:
[mysqld]
bind-...
- 511
40
votes
6
answers
20k
views
Mysql: Working With 192 Trillion Records... (Yes, 192 Trillion)
Here's the question...
Considering 192 trillion records, what should my considerations be?
My main concern is speed.
Here's the table...
CREATE TABLE `ref` (
`id` INTEGER(13) AUTO_INCREMENT ...
- 403
39
votes
4
answers
316k
views
How do I find out what my IP Address of my MySQL host is?
I have a free domain running at x10hosting (x10.bz), and I want to find out the IP Address of my MySQL host for it, so I can contact the MySQL database from another host. I've already added that host ...
- 739
39
votes
5
answers
113k
views
How can I copy MySQL users table from one server to another? [duplicate]
I'm setting up up a new MySQL server and I'd like to give it the same set of usernames, allowed hosts, and passwords as an existing server (which is going away).
Would it work to just do a dump of ...
- 2,439
39
votes
7
answers
76k
views
How can I speed up a MySQL restore from a dump file?
I am restoring a 30GB database from a mysqldump file to an empty database on a new server. When running the SQL from the dump file, the restore starts very quickly and then starts to get slower and ...
- 3,606
39
votes
1
answer
35k
views
What is the difference between wait_timeout and interactive_timeout?
What is actual difference between wait_timeout and interactive_timeout?
- 401
38
votes
6
answers
267k
views
No mysqld or mysql.server after mariadb-server install
So... likely I'm an idiot, but I'm stuck. I just set up a CentOS 7 on Digial Ocean and I can't seem to get the MariaDB/MySQL server running.
Some output
[root@hostname ~]# yum list installed |grep ...
- 995
38
votes
6
answers
29k
views
MySQL InnoDB - innodb_file_per_table cons?
By default MySQL InnoDB stores all tables of all DBs in one global file. You can change this by setting innodb_file_per_table in the config, which then creates one data file for each table.
I am ...
- 1,638
38
votes
2
answers
74k
views
How bad is setting MySQL's bind-address to 0.0.0.0?
I'm trying to allow a remote server to access a MySQL instance that currently shares a Linux server with a web app. According to the documentation the only way this would be possible (unless I'm not ...
- 507
37
votes
5
answers
96k
views
How to find and fix fragmented MySQL tables
I used MySQLTuner which pointed out some tables were fragmented. I used
mysqlcheck --optimize -A
to optimize all tables. It fixed some tables but MySQLTuner still finds 19 tables fragmented. how ...
- 471
36
votes
4
answers
152k
views
How to know all the users that can access a database (MySQL)?
How to know all the users that can access a database?
- 421
35
votes
1
answer
45k
views
Constantly have to reload PHP-FPM
We have a fairly heavily loaded server running nginx and PHP-FPM. We have 6 websites on this server, running PHP-FPM and nginx. Software is all vBulletin 3.8 and WordPress. Databases are on a separate ...
- 847
35
votes
2
answers
144k
views
How to increase memory usage in MySQL Server to improve speed?
I have a Windows 2008 server with 8GB of RAM running IIS7 and MySQL. I've been tracking the memory, cpu and disk usage on the server and I found out that MySQL is using only 250MB of RAM, keeping the ...
- 889
34
votes
7
answers
128k
views
Creating a docker mysql container with a prepared database scheme
I want to create a docker image on top of the mysql one that already contains the necessary scheme for my app.
I tried adding lines to the Dockerfile that will import my scheme as a sql file. I did ...
- 651
34
votes
4
answers
69k
views
How to escape ! in password?
How might one escape the exclamation point in a password:
$ mysql -umyuser -pone_@&!two
-bash: !two: event not found
Trying the obvious backslash did not help:
$ mysql -umyuser -pone_@&\!...
- 2,650