MySQL is an open-source database owned by Oracle.

learn more… | top users | synonyms (7)

13
votes
7answers
13k views

How to set ulimits for a service starting at boot?

I need, for mysql to use large-pages, to set a ulimit - I've done this in limits.conf. However, limits.conf (pam_limits.so), doesn't get read in for init, only for "real" shells. I solved this before ...
13
votes
4answers
4k views

In Puppet, how would I secure a password variable (in this case a MySQL password)?

I am using Puppet to provision MySQL with a parameterised class: class mysql::server( $password ) { package { 'mysql-server': ensure => installed } package { 'mysql': ensure => ...
13
votes
5answers
17k views

Log MySQL login attempts

From time to time there are failed login attempts in our MySQL production server (MySQL dashboard alerts us). Is there a way to log every single success and failed login to the MySQL server without ...
13
votes
3answers
3k views

Building a 1,000M row MySQL table

This question is reposted from Stack Overflow based on a suggestion in the comments, apologies for the duplication. Questions Question 1: as the size of the database table gets larger how can I tune ...
12
votes
8answers
2k views

If I send data to my computer using its public hostname, does it actually go over the internet?

To clarify: I'm using my public hostname to connect to a MySQL database. The hostname resolves to my server's external IP (e.g. 1.2.3.4). Is the data I'm sending/receiving via the MySQL connection ...
12
votes
8answers
2k views

is this a hack attempt?

Looking through my 404 logs I noticed the following two URLs, both of which occurred once: /library.php=../../../../../../../../../../../../../../../../../../../../../../../../proc/self/environ and ...
12
votes
10answers
32k views

Can't start/stop mysql service

Taking over a Debian Etch web server with MySQL running. I usually start, stop and restart msyql using: /etc/init.d/mysql restart For some reason on this set up I get the following: :~# ...
12
votes
5answers
33k views

How can I show mysql locks?

Is there anyway to show all the locks that are active in a mysql database?
12
votes
9answers
83k views

Unable to connect to mysql through JDBC connector through Tomcat or externally

I've installed a stock mysql 5.5 installation, and while I can connect to the mysql service via the mysql command, and the service seems to be running, I cannot connect to it through spring+tomcat or ...
12
votes
2answers
14k views

Best Mysql cache settings for 8gb RAM dedicated mysql server using only Innodb (5gb database)

I'm a pretty big noob when it comes to setting up mysql for performance. And honestly I'm not worried about the fine tuning to squeeze every last bit of performance out of mysql, but I do know that ...
12
votes
6answers
1k views

Downtimeless MySQL backups on a budget

My current MySQL backup scenario is to replicate our db to a second server and run mysqldump on that server to remove any downtime from table or row locking. This is working well but costs $150 per ...
12
votes
3answers
3k views

How fast is MySQL replication?

I'm considering setting up replication of our mysql db to be able to have local slaves in each of our branch offices, while having the master in the main office to improve application performance ...
12
votes
7answers
5k views

How can I force a MySQL table to become corrupted?

I have written a simple Nagios plugin that calls mysqlcheck (which checks for corrupted tables) and will give a warning if any are corrupt. However none of my tables are corrupt now. So I'm not 100% ...
12
votes
3answers
35k views

What does MySQL Error 2013 mean?

I'm running mysqldump on a mysql server in a cron (as backup) and I'm getting warning messages like mysqldump: Got error: 2013: Lost connection to MySQL server during query when doing refresh What ...
12
votes
4answers
12k views

Alter charset and collation in all columns in all tables in MySQL

I need to execute these statements in all tables for all columns. alter table table_name charset=utf8; alter table table_name alter column column_name charset=utf8; Is it possible to automate this ...
12
votes
3answers
3k views

MySQL Replication Performance

I'm having a serious issue with MySQL 5.5 replication performance between two machines, mostly myISAM tables with statement based replication. The binary logs and mysql data directory are both located ...
12
votes
2answers
25k 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 the the MySQL is using only 250MB of RAM, ...
11
votes
5answers
594 views

Is it bad to have a very full hard drive on a high traffic database server?

Running an Ubuntu server with MySQL for a high traffic production database server. Nothing else is running on the machine except the MySQL instance. We store daily database backups on the DB server, ...
11
votes
4answers
3k views

How to reset or recover admin account password for MySQL?

I have a MySQL database that I "inherited" and I was not given the admin credentials. I do however have access to the box that it runs on. Is there a way to either recover the admin credentials or ...
11
votes
5answers
13k views

How to drop all tables in a MySQL database without dropping the database?

I need to drop all the tables in a database without knowing their names beforehand. The typical procedure is to drop and then recreate the database but this is not an option. What is the best way to ...
11
votes
3answers
8k 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 ...
11
votes
4answers
15k views

Unable to install mysql-server in Ubuntu

I am unable to install mysql-server on my ubuntu 9.10 server machine. When using apt-get install mysql-server the output is : # apt-get install mysql-server Reading package lists... Done Building ...
11
votes
3answers
2k views

MySQL replication across geographically separate servers

My organisation has been looking into how to spread our servers around geographically while keeping backups very up to date, and ideally spreading the load. The initial thing I have in mind is Rails ...
11
votes
4answers
7k views

MySQL Master-Master Replication of ALL databases. How?

Background: I have two MySQL 5.1 servers setup in Master-Master row-based-replication (RBR) following this excellent guide. I want all databases replicated, and will be adding new databases ...
11
votes
2answers
10k 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 of from another server, 10.1.1.1. So I am doing: CREATE USER 'bob'@'localhost' IDENTIFIED BY 'password123'; ...
11
votes
3answers
1k views

The A to Z of setting up a Linux box for secure local hosting

I am in the process of reinstalling the OS on a machine that will be used to host a couple of applications for our business. The applications will be local only; access from external clients will be ...
10
votes
6answers
12k views

From a shell script, how can I check whether a MySQL database exists?

mysqladmin -uroot create foo returns an exit status of 1 if foo exists, and 0 otherwise, but of course it will also create the database if it doesn't already exist. Is there some easy way to simply ...
10
votes
6answers
14k 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 ...
10
votes
5answers
12k views

Should I install 32-bit database or 64-bit database?

(I've read a lot about 64-bit versus 32-bit OS/Apps, but this question is specifically in regards to databases.) I'm trying to understand the pros and cons of 32-bit versus 64-bit databases, and ...
10
votes
4answers
3k views

Should I backup and restore the `mysql` database?

In the process of creating an automated solution for backing up and restoring an entire MySQL server, I've come across the mysql database which seems to contain user accounts, permissions, metadata, ...
10
votes
5answers
2k views

How can I backup multi-gigabyte databases in MySQL? Bonus points for speedier restores

I have a 4GB database in MySQL. How can I back it up? Furthermore, is there a way that I can speed up the restoration process in the event that I need to recover? I currently use a cron job every ...
10
votes
6answers
32k views

How to completely remove MySQL from Windows?

(I realize there are similarly-titled questions, but this one is really 'how do I reset the password', and this one doesn't specify an OS and has only gotten Linux-oriented answers.) On Windows XP, I ...
10
votes
3answers
13k views

Can't backup the mysql table with mysqldump. SELECT,LOCK TABL command denied for 'cond_instances'

I am having trouble running mysqldump as the mysql root user. When I try to backup the mysql table I get this error: mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user ...
10
votes
8answers
7k views

How do I backup a mysql database, but at low priority?

I want to backup a database, but during the day when there is load on the server. It's vital that the backup doesn't impact apache and other databases running on the same server. It should be ...
10
votes
5answers
9k views

Using mysqldump in cron job without root password

If I login with the root password on my box I can simply type mysqldump --all-databases and I will get th expected "Dump". I setup a job in cron.daily to run and dump this to a backup drive. The ...
10
votes
3answers
3k views

Secure LAMP server for production use

What is the procedure for securing a Linux, Apache, MySQL, PHP Server (or even Perl) for production use? Other than setting the MySQL password and the root password for Linux what other (maybe not so ...
10
votes
2answers
37k views

Tuning (and understanding) table_cache in mySQL

I ran the excellent MySQL performance tuning script and started to work through the suggestions. One I ran into was TABLE CACHE Current table_cache value = 4096 tables You have a total of 1073 ...
10
votes
3answers
2k views

Mysql Encryption and Key managment

I am developing a local intranet system in PHP/MySQL to manage our client data. It seems that the best practice would be to encrypt the sensitive data on the MYSQL server as it is being entered. I ...
10
votes
3answers
2k views

Optimizing Apache and MySQL on Linux Xen VPS

I have a Xen virtual private server (VPS) running Ubuntu 8.10, with 128M RAM. I've found several "how to optimize Apache and MySQL for low-memory VPS" pages via Google, but they provide contradictory ...
10
votes
1answer
5k views

What is “freeable memory”?

Amazon RDS has a metric for 'freeable memory'. It appears to go down in a sawtooth pattern: https://skitch.com/adamnelson/rnhpu/aws-management-console This leads me to believe that it's memory that ...
10
votes
1answer
536 views

iptables not allowing mysql connections to aliased ips?

I have a fairly simple iptables firewall on a server that provides MySQL services, but iptables seems to be giving me very inconsistent results. The default policy on the script is as follows: ...
10
votes
2answers
4k views

Why are all MySQL InnoDB tables fragmented?

For some reason, all InnoDB tables in my MySQL server are being listed as fragmented when I run mysqltuner. I only installed the server a few hours ago (on OSX Lion), and it has a bunch of fresh data ...
10
votes
2answers
712 views

Is there a MySQL performance benchmark to measure the impact of utf8_unicode_ci versus utf8_general_ci?

I read here and there that using the utf8_unicode_ci collation ensures a better treatment of unicode text (for example, it knowns how to expand characters such as 'œ' into 'oe' for searching and ...
9
votes
9answers
5k views

How to store 3 million records in key value format?

We have to store basic information about 3 million products. Currently the info is one 180 mb CSV which gets updated quarterly. There will be about 30,000 queries per day, but the queries are just a ...
9
votes
6answers
26k views

How to rename a MySQL database?

How to rename a MySQL database? The MySQL online manual says about the RENAME DATABASE command: This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL ...
9
votes
4answers
11k views

MySQL 5.1 or 5.5?

Are there significant differences between versions 5.1 and 5.5? The server in question is used to host a medium-sized vBulletin forum. The main benefit of 5.1 is it's available through apt-get.
9
votes
9answers
29k views

How can I reset the password for MySQL? [duplicate]

I have an old MySQL installation for which I do not have the password. How can I reset the password? My first idea was to completely remove it and reinstall with a new password. I am using Windows ...
9
votes
3answers
8k views

How to automatically kill slow MySQL queries after N seconds?

I am looking for a well tested bash script (or alternative solution) to do so, in order to avoid max_connection to be exhausted. I know that it is fighting the symptoms, but really need such script as ...
9
votes
4answers
2k views

Fix Control-C in mysql command line programme?

In the mysql command line, pressing Control-C will cancel the programme, and bring you back to bash. In psql, the one for postgres, it will kill the current query and will not stop the psql programme. ...
9
votes
3answers
9k views

How does one list warnings from the 'mysqlimport' utility?

To start off, this is not about loading data from within mysql itself, but using the command-line tool "mysqlimport". I am using it to load a CSV directly into a table and need to see the warnings ...