Tagged Questions
11
votes
2answers
26k 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 ...
9
votes
3answers
4k views
How to convert mysql to postgresql?
I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be:
Free
Works under Linux
Simple to use and not buggy
You actually tried and ...
8
votes
4answers
8k views
Why using innodb_file_per_table?
There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
7
votes
7answers
6k views
What tools are there for data masking? (MySQL, Linux)
I'm looking for (ideally free, open-source) data masking tools. Do any such exist?
Note: this related question deals with tools for generating test data, but in this question I'm more interested in ...
6
votes
3answers
6k views
Cannot open table mysql/innodb_index_stats
After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well.
The errors I ...
6
votes
5answers
12k views
How to properly kill MySQL?
I have CentOS 64bit with CPanel installed and I use
service mysql stop
However, it keeps doing ..... for minutes and it never stops. It used to be instant. Any idea why it does that and how to fix? ...
5
votes
5answers
6k views
MySQL said “InnoDB: Initializing buffer pool, size = 128.0M”, does it mean it is using 128MB RAM?
I am just using a very few InnoDB tables (e.g. less than 1MB), but during MySQL startup, it said
InnoDB: Initializing buffer pool, size = 128.0M
Does it mean even I am using in such a small size, ...
5
votes
1answer
183 views
Will I need more system resources to run DB2 instead of MySQL?
I need to convert my web application from MySQL to run on DB2. I need to know in advance whether I will need a high spec server to ensure the web application performs at the same speed as it does now ...
4
votes
2answers
2k views
Taking mysql dump from another machine
I have MySql db on Machine A , I have Mysql credentials. And accessing this MySQL from machine B which I have crdential's. But I couldn't take mysqldump from Machine B. Please help me. Both ae Linux ...
4
votes
2answers
1k views
mysql multi threading for a single query
I have big db of size 8GB and having 9 million contacts in this db. When we try to retrieve all contacts, it takes nearly 1 or 2 hour (in some cases result in timeout) to retreive all contacts for the ...
4
votes
3answers
1k views
Should we need to use Barriers on a production database (MySQL/InnoDB)?
Even though we are using a journaling filesystem (EXT3) with barrier enabled, is this still safer and recommended?
e.g
mount -o barrier=1 /dev/sda /mntpnt
Reference:
...
3
votes
2answers
473 views
Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)
DISCLAIMER: I'm very aware this is not supposed to be done, but time consistency between tables aren't a concert here and Im trying whatever pops in my mind right now to have alternatives for ...
3
votes
3answers
3k views
Test MySQL credentials from Linux command line?
How do you test MySQL credentials from the command line on a Linux server?
3
votes
1answer
10k views
MySQL case sensitive table names on Linux
Moving a MySQL database from Windows to Linux I have the problem that on Linux the names of the tables are case sensitive. This is a problem, because the Java application that I am developing can't ...
3
votes
1answer
3k views
I have lots of free memory. How do I use it to increase performance?
I am running Litespeed (with suExec, PHP SAPI), MySQL, DirectAdmin, named on a VPS with a 16-core Xeon CPU and 2GB RAM.
Currently, only 300 MB out of 2048 are used. It might sound a stupid, but how ...
3
votes
2answers
3k views
MySQL Replication Troubleshooting
Here's what I did. On the master, change /etc/my.cnf:
[mysqld]
server-id=1
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1
Save and restart mysql, then log in.
>GRANT ...
3
votes
1answer
230 views
restrict user host settings to socket connection only
Is there a way to restrict a user's host setting so that (s)he can only connect by socket and not TCP/IP? I'd like to have this setting for the root account.
edit: As Abdul Manaf pointed out ...
3
votes
1answer
5k views
innodb_force_recovery when InnoDB corruption
When I start mysqld (in /etc/init.d), it failed with
InnoDB: corruption in the InnoDB tablespace.
What's the best innodb_force_recovery value to force mysqld to start? I have tried 4 and 6, but ...
3
votes
1answer
7k views
Cannot output MySQL data to file
I am trying to output the data from an MySQL table to a file but getting permission errors:
$ pwd
/home/dotancohen
$ mkdir in
$ chmod 777 in/
$ mysql -ugs -p
mysql> USE someDatabase;
mysql> ...
3
votes
1answer
3k views
InnoDB log sequence number is in the future
130711 23:03:40 InnoDB: Error: page 23 log sequence number 3336825872455
InnoDB: is in the future! Current system log sequence number 235089.
InnoDB: Your database may be corrupt or you may have ...
3
votes
2answers
150 views
Choosing between MyISAM & InnoDB - two servers with related tables
I don't want to make a mistake.
I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.
I read here and there that InnoDB should be ...
2
votes
1answer
369 views
Mysql error 28 no space left on the device
I keep running in to the same problem with mysql with error code 28 which means there is not enough space left on the device.
Now the weird part is that there is enough space, here are some tips i ...
2
votes
2answers
3k views
Mysql consumes a lot of memory
Here is my situation.
I have an ubuntu server monitored by nagios.
recently nagios sends alarms regarding the memory consumption on the server. I logged into the server and executed the top command ...
2
votes
1answer
74 views
How identify tables with millions of entries
On a debian server with apache and mysql, how can I find out if any one table gets spammed?
I have lots of different blogs, wordpress, wikis,... on my server of different customers.
It seems like ...
2
votes
1answer
805 views
mysql statement based replication - unsafe statements
I have recently set up statement based replication and my error logs are filling up with this error
121231 21:10:55 [Warning] Unsafe statement written to the binary log
using statement format ...
2
votes
3answers
742 views
Encrypt MySQL database on a Linux VPS?
I'm working on a rails app for our church, which stores some fairly sensitive information about our people in a MySQL database.
We have our own windows server running Hyper-V so the original ...
2
votes
2answers
990 views
MySQL general log
Is there away to have MySQL dump its log to a remote MySQL server?
I would like to audit MySQL connections but I don't want the server to store all the information local on its own hardware.
2
votes
1answer
4k views
How to grant access for multiple tables to a user in MySQL DB?
I have mysql db named "greats" with four tables as below
+---------------------+
| Tables_in_greatstat |
+---------------------+
| log |
| sitedet1 |
| siteindex ...
2
votes
1answer
22 views
Purge mysql and it's configuration completely from MAC.
I, by mistake messed up with mysql's only user and the situation is I can't create new user or give more privileges to existing one. Access denied is the error. Only visible db is info Schema So I ...
2
votes
1answer
154 views
Can I execute an external process via MySQL on table change?
When using triggers, if an update is done to a table then a trigger is executed. This is very convenient. But what I would need is to execute an external script.
Is it possible to configure MySQL ...
2
votes
1answer
411 views
How to write a shell script to check the mysql database backup status.
I have mysql production databases running in main server(Linux) and from this server every night the backup is taken into local server.
Now i want to find a shell script to check if all the databases ...
2
votes
1answer
189 views
Replication with only update and insert
I have set up my replication but I want to delete some outdated data from my master tables. Is it possible that say if I have 10 records at master and I delete 5 of them but I want those 10 records to ...
2
votes
1answer
239 views
MySQL performance on Mac vs Linux
I know that MySQL is tested more on Linux (Oracle has it's own linux distribution and you canot have LAMP wit MySQL (meaning MySQL has always focused more on Linux)
My intuition says that MySQL has a ...
2
votes
0answers
506 views
Index usage for database server
How can i fix the Index usage on one of our database server?
It says from our Nagios control panel that:
Service: DB Index Usage
Host: MySQL Cluster
Address: ( our IP )
State: CRITICAL
Additional ...
2
votes
1answer
695 views
MySQL Tuner OPTIMIZE and Defragmentation
I have just started using mysqltuner to help keep my mysql server fast and healthy. However, I am having a little trouble optimizing my tables. When I execute mysqltuner, it all works fine and returns ...
2
votes
1answer
697 views
My MySQL server time is not the same as my Server
So a month ago I had purchased a server from a hosting company.
The info are;
Linux Centos (latest version).
I installed ZPanel which installs phpMyAdmin, MySQL and all the other good stuff. ...
1
vote
5answers
3k views
Tuning MySQL to consume less memory
I have a VM which has 2GB Ram, (full specs)
And I am setting up a site which has one table in particular with over a million records.
There's little or no usage of this particular database (perhaps ...
1
vote
1answer
4k views
Enabling LOAD DATA LOCAL INFILE in mysql
LOAD DATA LOCAL INFILE is not enabled by default. Normally, it should be enabled by placing local-infile=1 in my.cnf. But it does not work for all installations. In my experience, it worked for Debian ...
1
vote
2answers
162 views
Linux command to kill all MySQL sessions from a specific User?
How can i kill all sessions from a user in mysql using a linux command line command?
I have a user that is using all on my database connections and insted of killing one by one i want to make a ...
1
vote
1answer
138 views
Scaling down DB where filesystem is almost full
Our DBA is on holiday, and since I know 'a bit' of MySQL, they've asked me to fix this. The data slice is at 99%, and I need to know what to get rid of.
[root@servername]# find . -type f -size ...
1
vote
2answers
75 views
Strange issue regarding my.cnf
I'm getting a weird message from phpmyadmin saying:
#2006 - MySQL server has gone away
What I found online is that I should increase the size of max_allowed_packet witch should be in the my.conf ...
1
vote
2answers
243 views
How to avoid lagging when enabling log-slave-updates?
Master:
48GB RAM
16-core
my.cnf: https://clbin.com/hlkUo
Slave:
64GB RAM
24-core
my.cnf: https://clbin.com/viLm0
Both are running 5.5.28.
For the incremental backup purpose, I need to enable ...
1
vote
2answers
141 views
Why Can't I find my databases from Mysql on linux?
I'm new to linux. I cannot get a list of my databases by typing SHOW DATABASES on the MySQL shell. Though this MySQL program is aware of the MySQL datadir because it is listed when I type SHOW ...
1
vote
1answer
98 views
Heartbeat Options with MySQL and Possibilities
I would like to understand if the following scenarios are possible in Heartbeat in Linux.
Setup: Two Database Servers running Mysql in Active/Passive mode in replication mode having Heartbeat setup ...
1
vote
1answer
2k views
mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when using LOCK TABLES
I'm trying to backup a drupal site database but I'm having some issues. When I ran the following command:
mysqldump -uroot -p drupaSite > drupaSite.sql
I get the following error:
mysqldump: Got ...
1
vote
1answer
241 views
MySQL my.cnf won't take any effect
Update 7
As DerekDowney suggested, I tried to use
[mysqld]
init_connect=SET NAMES 'utf8'
But I could not get the desired result. My client side java programme still says both session and global ...
1
vote
1answer
381 views
Is it okay to have different lower_case_table_names value on master and slave?
I have a OSX MySQL master with lower_case_table_names set to 2. I have just setup a Linux MySQL slave with lower_case_table_names=1.
I haven't started replication on the slave, but everything looks ...
1
vote
1answer
519 views
Connection to MySQL Remotely
I want to connect to MySQL Server hosted on a Linux Machine from my Windows Machine using Command Prompt with the following command:
prompt>mysql -u root -p -h remote_hostname
Connections, ...
1
vote
1answer
3k views
How to set a specific directory location for individual MySQL database?
Is it possible to set each database in MySQL to use a separate datadir?
I'm running a userdir development (sandbox) server and would like to put the MySQL data files for the database(s) for that user ...
1
vote
1answer
72 views
Master-Master Replication Duplicate Key
I have set two servers master-master replication mysql , i manage to set successfully but the problem is the duplicate key i got
MASTER 1 Configuration
/etc/my.cnf
[mysqld]
server-id=1
bind-address ...
