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
1
vote
1answer
98 views

mysql server password change while restoring

I restored all database backup from another server's dumpfile to my machine. At the end my mysql server's password changed to that mysql server's password. Why it got overwritten? What i need to do to ...
66
votes
1answer
118k 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 ...
12
votes
2answers
16k views

Difference between row-based and statement-based replication in MySQL?

What is the actual difference between row-based and statement-based replication. I am actually looking in terms of replication's effect on the slave. If I am using row-based replication then what is ...
2
votes
3answers
205 views

Synchronise two databases (but not really)

I'm trying to design the following system. An arbitrary number of of embedded systems will each record data constantly and indefinitely. It is required that these devices upload their data to a ...
4
votes
3answers
15k views

Does MySQL support replicating all databases?

I’ve been doing some investigation into properly configuring MySQL replication. From what I understand after reading the documentation, you can only replicate specific databases, and each database ...
7
votes
4answers
3k views

What's the equivalent of --safe-updates of mysql in MS SQL Server?

In MySQL, you can use the feature called --safe-updates (--i-am-a-dummy) to limit the number of rows updated per query. http://dev.mysql.com/doc/refman/5.0/en/mysql-tips.html#safe-updates Is there ...
13
votes
1answer
13k views

MySQL Sharding vs MySQL Cluster

Considering performance only, can a MySQL Cluster beat a custom data sharding MySQL solution? sharding = horizontal partitioning When I refer to sharding, I'm considering sharding made in the ...
1
vote
2answers
435 views

MySQL statement in Project Voldemort site

I've just entered Project Voldemort (a NoSQL key-value datastore) site, and saw something interesting: "Unlike MySQL replication, both reads and writes scale horizontally" Well, seems like this ...
2
votes
3answers
3k views

mysql cannot start because of init.d folder?

After I setup mysql 5.5, I changed the data dir to another folder and modify the related config in my.cnf. This is quite simple, but mysql cannot start anymore even if reinstall or not change data dir....
4
votes
1answer
4k views

Master and Slave having same id's

I am working as a remote DBA for some of the production servers. There was replication from Master to Slave. There is something changed in the scenario they have made one of the Slaves as master ...
43
votes
6answers
91k 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?
2
votes
1answer
173 views

How can I best combine “get categories” and “has user access”?

I was wondering if it's possible to create a query for the following problem. I only want to select categories from the (MySQL) database (if the user has access). A user has multiple roles; A ...
1
vote
1answer
4k views

Problem with MySQL Master/Slave reboot when in replication

There is a master and number of slaves connected to it. Due to some patches, we have to Reboot the Master and Slave. Afterwards, we have to make sure replication is running fine. While doing this ...
3
votes
1answer
7k views

Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working

I have several tables being reported by pt-table-checksum with CRC_DIFF between my master and slave servers using the following commands: $ pt-table-checksum h=master,u=user,p=password --empty-...
1
vote
2answers
214 views

Two master databases for two separate apps

We have two different websites one is hosted in Asia datacenter called it asiadata.com and other site is hosted in Europe datacenter called it europedata.com. Currently we have only one master ...
4
votes
1answer
3k views

Does `Seconds_Behind_Master` show exact Slave Lag from Master?

Does Seconds_Behind_Master always show the correct number of seconds the slave is behind the master? I have also Implemented the mk-heartbeat replication monitoring. It was showing the other result ...
6
votes
2answers
18k views

Set up a MySQL slave using mysqldump to get the initial data

I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave ...
6
votes
1answer
11k views

What memory should be given to the variables of my.cnf ?

Installing a MySQL server is quite easy. However, after installing, configuring the server is an important task for a DBA. Having 2GB of RAM and 250GB of disk space, how should I tune MySQL server so ...
2
votes
1answer
997 views

BACKUP and RESTORE MySQL Database

I have very specific question. I have seen the instructions on how to backup and restore mysql database using SSH but they barely explain the process when backing up on one server and restoring on ...
1
vote
1answer
196 views

What is QP layer?

From blogs.innodb.com: InnoDB Memcached with Binlog Capability In our earlier Labs release, we presented a MySQL NoSQL solution through InnoDB Memcached Daemon Plugin (see earlier Calvin’s and my ...
1
vote
1answer
825 views

What is a better way to keep “user online”?

I'm working on software for social network and I plan it to scale a lot. Actually, I'm updating timestamp column every time user clicks, to be sure they're marked as online. When they do not click for ...
4
votes
3answers
641 views

How can I select all online friends?

I'm currently working on a social network software written in PHP & MySQL, and it's all pretty fine, but one thing I'm stuck about is online friends. My tables looks following Users +-----+------...
5
votes
1answer
218 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 ...
3
votes
2answers
791 views

Using all the CPU cores by NDBD and MySQL

We are running MariaDB which is a fork of MySQL with NDB cluster engine. The cluster includes one mysql node and two ndb data nodes. While benchmarking the cluster, we are trying to make a bottleneck ...
3
votes
1answer
277 views

Remote Database Backup [closed]

I am attempting to find a reliable service that does remote MySQL Database Backup. Price is no object, unless it's absurdly expensive. I understand this question doesn't really have an answer, and I ...
14
votes
2answers
25k views

What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?

I am very new to Database Administration. I face a lot of problems while setting up mysql master-slave replication. I also face regular mysql replication troubleshooting issues. Can anybody helps ...
12
votes
1answer
8k views

MySQL Workbench Database Sizes

I'm trying to find the total size on the hard disk that all of my MySQL Workbench databases are using. Does anyone know of an easy way to figure this out? If nothing else, the default location mysql/...
2
votes
2answers
1k views

Logical schema development and migration tool for different physical schema available including triggers and stored procedures?

We are planning to support for our application another rdbms (oracle). At the moment we are design our database schema with the MySQL-Workbench. For every new database version we have schema er-...
1
vote
2answers
299 views

Is it advisable to have MySQL Master/Slave running on the same physical server, on the same mysqld, sharing the same disk?

my Use-Case : The solution I am developing is very data oriented. I run a cron job every 3 hrs to read lots of records from a file, process them and store them in the database. I also need to ...
7
votes
4answers
17k views

Visualization of MySQL/PostgreSQL table structure as a graph/UML

Is there any program, source code or API that will take a MySQL or PostgreSQL schema and spit out a UML or some other graph visualizing of it?
0
votes
1answer
2k views

MySQL Replication question

I have setup a master-slave replication between two MySQL servers using mysql Version 14.12 Distrib 5.0.51a, for debian-linux-gnu. Recently the replication errored out with this in the logs: ...
2
votes
1answer
7k views

mysql db backup excluding specific tables

Are there any mysql scripts for taking a database backup by excluding specified tables? Also, what is the best way to take backup of a running mysql system?
2
votes
2answers
12k views

Is there a max allowance for entries in a table in mysql?

Is there a set maximum (or even a recommended maximum) for how many entries that can/should be stored in a mysql table? How much is really too much?
2
votes
1answer
2k views

mysql replication deadlock slave stopped

I've a master and slave and got the slave stopped with the following error log in slave. I want to fix this and get the two servers on synch and get going again. 111128 8:42:24 [Note] Slave I/O ...
7
votes
3answers
6k views

What is the best approach to run a DELETE query on 92K of 4 million rows?

Options that have occurred to me: One big delete statement. For example: delete from x where (y = 'x' and x = 'y') or (y = 'a' and x = 'b') Same as above, but chunk in smaller pieces, 10k at a time?...
7
votes
2answers
3k views

Why does MyISAM support FULLTEXT Searching and InnoDB does not?

I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess ...
3
votes
1answer
1k views

MySQL 5.5 InnoDB = OFF?

We run a server with MySQL 5.5. 95% of the tables are set to the InnoDB engine. However whenever I look at the my.ini file (using the MySQL Workbench) under mysqld section innodb is unchecked and OFF ...
7
votes
1answer
2k views

Webapp & MySQL: Row Level Security

I am trying to emulate row level security on a webapp I am developing using MySQL. Using this method: Creating a database with the the required tables where the data pertaining to all the users will ...
1
vote
2answers
209 views

MySQL Records Turnover: How to do it?

I have a table of villas ordered by the UID as follow : UID, name, random_order: 4, villaA, 12 8, villaB, 5 9, villaC, 9 11, villaD, 2 I want to show my villas according to the random_order ...
2
votes
2answers
136 views

Database Files-Related Issue

I am using MySQL database and using Ubuntu linux machine. I noticed that my database, db_test, is under the path /var/lib/mysql/db_test There are files with the suffix .frm, .MYD, .MYI like the ...
4
votes
2answers
9k views

MySQL trigger: Compare value in another table

I have the following trigger (generated using PHP via mysqli)... DROP TRIGGER IF EXISTS post_queue_insert; CREATE TRIGGER post_queue_insert AFTER INSERT ON posts FOR EACH ROW BEGIN IF (NEW....
9
votes
3answers
10k views

EXPLAIN output suggests that my index is not being used

I have set up my table with an index only on done_status(done_status = INT): When I use: EXPLAIN SELECT * FROM reminder WHERE done_status=2 I get this back: id select_type table type ...
2
votes
1answer
6k views

ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table ...
2
votes
1answer
4k views

MySQL MyISAM Locking question

It was my understanding that MyISAM uses table locking during inserts and updates. To me that meant that read and write by other queries on that table is disallowed until the first insertion or update ...
10
votes
1answer
8k views

Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

my.cnf (5.5 and 5.1 is the same) : back_log=200 max_connections=512 max_connect_errors=999999 key_buffer=512M max_allowed_packet=8M table_cache=512 sort_buffer=8M read_buffer_size=8M thread_cache=8 ...
6
votes
5answers
2k views

Making a column immutable in MySQL

I want to make a column in a relation unmodifyable for consistency reasons. The backstory is that I have a n:n relationship where the "connecting" relation has additional values. I don't want that ...
6
votes
3answers
9k views

MySQL general query log performance effects

One of our customers who has a somewhat problematic web application would like us to log all the queries for a period of 24 hours. I wouldn't know how much data it will actually write. The amount ...
1
vote
1answer
228 views

Add info to mysql view around an IP address - 10.10.1.100 to https://10.10.1.100

I have built a database in MySQL to replace the use of MANY excel spreadsheets that document our servers, network, IP addresses, etc. In that database I have created a view called DRAC (dell remote ...
4
votes
2answers
10k views

How can I define the datatype of a view in MySQL?

I've written a simple aggregation view over a table, and the view returns different data types that the table. This causes some issues on the application side. Here's a simplified query for the sake ...
16
votes
1answer
102k views

How does MySQL return a result set from a stored procedure?

I'm trying to get my head around stored procedures for use in a web application. It seems to me that whatever the last statement is in a MySQL stored procedure is seems to be what gets treated as that ...

1
362 363
364
365 366
376