mysql-5 : Version of MySQL Starting with MySQL 5.0
43
votes
7answers
23k 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 ...
3
votes
2answers
2k views
Query runs a long time in some newer MySQL versions
I have created a database on MySQL 5.0.15. I have a query and when I run this query on this MySQL version, I get 0.9 s run time. When I import this database to another MySQL server with same hardware ...
1
vote
1answer
1k 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 ...
1
vote
1answer
273 views
decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
i recently testing the move of our db from one server to another.
as part of this process i copied all the data to the new db and started testing it.
as a comparison i run a store procedure whose ...
5
votes
3answers
1k 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 values(10, 1), (10, 2), (10, 3);
When issuing select * from t where k = 10 with ...
5
votes
3answers
1k views
Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB
I need to design database to store information contained in millions of log files generated by devices on trial on network.
I have designed 5 different tables to store log file information. But, as ...
1
vote
2answers
469 views
MySQL Community Edition: Can I replicate my entire server with another server in one go?
I need to migrate all the databases from one server to another. There are about 100 databases, and all databases are used daily and updated/inserted too and read through their respective websites.
...
11
votes
5answers
3k views
In MySQL, does the order of the columns in a WHERE clause affect query performance?
I am having performance issues on certain database queries that have large possible result sets.
The query in question, I have three ANDs in the WHERE clause
Does the order of the clauses matter?
...
3
votes
1answer
648 views
Determining max database and table size supported and present size
One of my MySQL databases has been running on a single PC from the last four years.
Here is my setup:
MySQL 5.5.14
Windows XP on NTFS partition
All of my tables are using InnoDB engine
Here are ...
3
votes
3answers
2k views
Changed max_allowed_packet and still receiving 'Packet Too Large' error
I use mysqldump to create a flat file for backup purposes. I have used this file to recreate the database on an alternate server. I ran the import process through ssh on the command line and I ...
2
votes
2answers
578 views
Can we assign mysql Key_buffer_size value for particular table?
In some documents I found that we can assign key_buffer_size to myisam table. This also includes the following steps:
SET GLOBAL myisam_table.key_buffer_size = 4194304;
CACHE INDEX test.myisam_table ...
8
votes
3answers
813 views
Speeding up conversion of MyISAM to InnoDB
I've got a mysql 5.1 server with a database of approximately 450 tables, taking up 4GB . The vast majority of these tables (all but 2) are MyIsam. This has been fine for the most part (don't need ...
4
votes
1answer
346 views
will replication from 5.5.20 to 5.0.XX server work?
I gave two machines which run on two entire different versions of MySQL server. I want to do the replication from 5.5.20 to 5.0 server. Will that be possible?
Because I remember when I transferred ...
1
vote
1answer
789 views
mysql db backup excluding specific tables
Are there any mysql scripts for taking a database backup, while excluding specified tables?
Also, what is the best way to take backup of a running mysql system?
Regards,
Praveen
5
votes
2answers
6k 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 ...
8
votes
3answers
2k views
Why does importing a 12 GB .sql file take more than 36 hours?
I've been waiting now for 36 hours for a 12 GB .sql file to be imported with a simple type site.sql | mysql command. I can see the ibdata1 is growing still, currently nearly 40 GB.
Considering the ...
7
votes
4answers
468 views
Is splitting a 'users' table for authentication purposes a good idea?
Suppose I have a user table in my site in which there are around 2-3 Million users (records) in the table.
For speeding up my login process, is it a good approach to split my user table, one for ...
3
votes
1answer
424 views
Keeping MySQL staging database on same server updated from production
I just migrated my MySQL 5.5.20 to Amazon RDS from MySQL on EC2. The one thing I'm having difficulty with is how to keep my staging database updated from production. Before, I just ran a nightly ...
4
votes
3answers
6k views
Resolving issue with mysql.proc after upgrading MySQL from 5.0.* to 5.1.*
Like many others, we upgraded our instance of MySQL from 5.0.* to 5.1.49. Since then, we've been running into an error that repeats itself when we perform certain actions:
Attempting to view or run ...
3
votes
2answers
1k views
Performance settings for MyISAM tables - keep everything in memory
I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data).
Since the databases size is several times smaller than the available memory, how can I make full usage ...
3
votes
1answer
1k views
Deleting bin logs in replication environment
I have a question about deleting binary logs in the replication environment:
We have an environment with 1 master and 2 slaves (running mysql 5.5). Sometimes, we run into a space issues during heavy ...
3
votes
2answers
701 views
MySQL replication to slave having VPN connection to Master
I am setting up an environment in which the Master and Slave are on different networks at different physical locations (both run on MySQL 5.5.20). I have set up a VPN connection from slave's network ...
3
votes
3answers
4k views
Restore mysql database with different name
Hello Experts,
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.?
regards,
Praveen
3
votes
2answers
1k views
How do you copy a table from MySqlServer_A to MySqlServer_B?
I have a daily task that collects data from 3 MySql Servers and then pushes that aggregate data back out to each of the servers deleting the previous days data. All servers are MySql. I would like to ...
2
votes
3answers
135 views
How to get all non deleted messages from one user
I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like:
CREATE TABLE IF NOT EXISTS ...
1
vote
2answers
38 views
Where condition execution sequence in MySQL
I am using MySQL 5.0.88. I have a query like:
select DISTINCT party.id
FROM party
LEFT join party_identifier AS pi
ON pi.party_id = party.id
WHERE (pi.source_note=?
AND ...
1
vote
1answer
537 views
Error in login in with root user in new mysql server
I've installed a MySQL server in CentOS 6.2 machine
+-------------------------+---------------------+
| Variable_name | Value |
...
1
vote
1answer
666 views
Duplicate entry in mysql
I have a mysql master-slave replication setup. when I checked "show slave status", I got the result as "Slave_Sql_running:No" and Last error as "Duplicate entry 'x' on query."
But when I tried to ...
