MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.
1
vote
1answer
205 views
MySQL 5.5 - determining correct write[read]_io_threads on high-end system?
Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server.
Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write.
Currently we are running on the default ...
1
vote
1answer
206 views
How to Log IP Address for Inbound Connections in MySQL
Is there a way in MySQL 5.5 (on Redhat 6.x) to log the IP address from where a particular query has been run? And have that appear in the general log or even relay and bin logs? I am trying to track ...
2
votes
1answer
833 views
MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?
When reading online about innodb_log_file_size, it says
The combined size of the log files must be less than 4GB
So what happens if it needs to be larger?
In books and online, it says to set ...
0
votes
1answer
985 views
Mysql 5.5: Optimizing all tables with mysqlcheck - is it useful to run the -check option along with -optimize?
I plan on running 'mysqlcheck --optimize --all-databases' to clear up some fragmentation.
Would it be useful to run -check with this option or does the -optimize already take care of that?
I am only ...
2
votes
1answer
182 views
Innodb mutex locking ?!? Inserts start stacking after 18-24hours, restart required
We have a fairly high end box running mysqld 5.5.20.
The cpu configuration is dual quad core xeons.
It has 32 gb ram.
It has a quad intel ssd configuration running raid 0+1.
On a fairly busy ...
0
votes
1answer
62 views
What would be the first things you do to find out about an MYSQL environment you are taking over?
I am new to DBA role, and wanted to know if there was any commands (or free software) that come with Mysql 5.5 (master/slave/slave) replication to quickly get familiar with
the database, and also ...
3
votes
2answers
557 views
Is it bad to create many mysql temporary tables simultaneously?
I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better ...
1
vote
0answers
145 views
mysql server is not sending result set back after query execution to client and staying in sending data state
When I pass a query from my Java application to mysql server, it stays in Sending Data state and not returning result set back to application.
When I execute the same query in SQLYog directly, it ...
1
vote
2answers
47 views
Contains statement Query
I need to check the string with the cloumn if the cloumn contains that string or not.
example:String 'how to make pizza' and the cloumn if has 'pizza' i need to fetch that..
like
SELECT * FROM ...
3
votes
1answer
135 views
Query not using index when it should
I have this query (below) which keeps hitting the table when it should be using the created index. Is there something simple I'm missing here? How can I get it to use the index instead of the table?
...
0
votes
3answers
536 views
Automatic MySQL Replication Fail-over Strategy
I want to know that if replication is down due to some reason as if master is down then what strategy should be followed so that the replication is up instantly without manually intervening the ...
1
vote
2answers
698 views
Why max_allowed_packet is larger in mysqldump than mysqld in my.cnf?
If you look at the sample config such as /usr/share/mysql/my-large.cnf, you will find..
[mysqld]
max_allowed_packet = 1M
[mysqldump]
max_allowed_packet = 16M
What is the point?
Because when you ...
1
vote
2answers
179 views
Mysql replication - Error No. 1146&1062
short version
In master master replication, How to have one master write data with odd IDs and the other one with even IDs? Is it the way to avoid conflict?
detailed version
I wanted to setup a ...
0
votes
1answer
45 views
Storing user search keywords
I need to store search keywords in a database to analyse user needs.
How efficient should my schema be?
I'm thinking like this:
CREATE TABLE SearchKeyword (
ID INTEGER PRIMARY KEY ...
2
votes
1answer
265 views
Unknown option '--no-beep'
I installed a new version of MySQL server (previously 5.0, now 5.5) and I copied the data folder in to the new one. Now I should perform mysql_upgrade. I opened the command prompt (server is located ...
2
votes
2answers
290 views
How to GROUP_CONCAT between given rows?
I have a table as
id diary
1 breakfast
2 walk
3 start
4 office works
5 office projects
6 end
7 taxi
8 start
9 preparing for meeting
10 doing the meeting
11 end
12 night
I want ...
1
vote
1answer
58 views
Server load discrepency in Slave with Master load being low and constant
We have MySQL Replication
Master/Slave Setup
db01 Master
db02 Slave (ReadOnly)
We are getting load discrepency on the Slave (db02), but the Master's (db01) server load is low. MySQL is the only ...
1
vote
1answer
386 views
Issue with connecting to the mysql database
We have an outstanding issue with VCC in chicago, there is an issue connecting to the mysql database
If an agent does a refresh of the monitor they will be logged out.
This is the error we ...
3
votes
2answers
243 views
How to split a string to and get info from another table in mysql
I have a system to assign score for each sentence stored in a column:
TABLE 1: word_scores
word score
this 3
is 4
a 5
test 1
another 0
sentence 8
TABLE 2: ...
1
vote
0answers
242 views
Trouble with triggers and insert .. on duplicate key update
I have two tables with identical schemas. one is a "main" table that is pruned periodically and kept small. The other is a "reporting" table that is an archive of everything that ever was. For ...
3
votes
3answers
295 views
InnoDB insertion faster
I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
1
vote
1answer
162 views
Mysql 5.0 on Windows 2008
I have a problem with MySQL on Windows Server 2008 x64.
This is the first time I've installed MySQL 5.5 with performance problems. I decided to go back to the older version that worked well with ...
1
vote
1answer
47 views
How are these two user drops different?
Other than the obvious, what is the difference between:
drop user user_01@host01;
and
drop user 'user_01'@'host01';
In a sample test, they both achieved the same result. From Account Names docs ...
1
vote
1answer
76 views
restricting users to rows they created
To educate myself in PHP and mysql, I am making a browser based rpg game. This game will allow any number of users to register an account and, once registered, they can add, edit or delete champions ...
1
vote
1answer
221 views
MySQL Replication Error, can't stop slave
When I run command SHOW SLAVE STATUS\G, I see
Slave_IO_Running : 'Yes'
Slave_SQL_Running : 'No'
What I've usually done to fix this is to run
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
...
3
votes
1answer
88 views
Backup and off-site restore setup
I've been reading a lot of threads about different backup options for MySQL and was hoping for some more insight so I can settle on one.
Our current situation is ~100 GB of InnoDB and MySAM data in ...
1
vote
1answer
218 views
MySQL Slave Relay Logging but not logging Binary Log
I have a master slave setup both mysql 5.5, the setup is fine except that the slave won't Binary log to the standard mysql-binlog only to the relay logs. I fear this may be due to my lack of ...
3
votes
2answers
173 views
How to concatenate a column after x rows?
I have a table with columns
object varchar(255),
frequency tinyint(3) unsigned,
I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as
.. ...
3
votes
1answer
69 views
how to add removed partition?
This is the structure of my table:
-- Table: t_keyword_conversion_ga
-- Create Table:
CREATE TABLE `t_keyword_conversion_ga` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`own_domain_id` int(11) DEFAULT ...
4
votes
3answers
3k views
How to change a MySQL previous slave to be a master and remove slave status information?
I have a master -> slave configuration where the master failed. I've been able to reset the old-slave to be a master and the old-master to slave from it. Fine.
What I can't seem to do is to remove ...
2
votes
1answer
127 views
Does mysqld wait for init-script code to complete before opening the instance?
I'm trying to automate the execution of an application code at MySQL startup.
The init-file server option seems handy and I need to know if mysqld waits for all the code in the init-file to complete ...
0
votes
1answer
172 views
Unable to take backup of mysql database using Xtrabackup
I am trying to take mysql backup without any downtime but unable to take the backup using Xtrabackup .
Actual Database is almost about 40+ GB in size. I am trying to first satisfy myself. Hence, I ...
3
votes
1answer
124 views
What's the most unsafe MySQL configuration?
I have MySQL 5.5.27-29.0-log instance running specifically for Jenkins. Since tests data is always recreated during setup, ACID is unnecessary and I'm hoping to sacrifice it to gain more performance.
...
2
votes
1answer
2k 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 ...
1
vote
0answers
153 views
MySQL cluster stuck in start phase 4, how to proceed?
Over the weekend our cluster we set up for evaluation had one of its data nodes lose network connectivity briefly. This was while another data node was in the middle of a restart (which had been going ...
0
votes
0answers
87 views
Please help configure my Mysql for 12GB 4cores server
I use a dedicated server with 12GB memory and 4 core CPU with Ubuntu 12.04 LTS (x64). This not just a database server. MySQL appears to be restarting frequently, I think I can tune the config to ...
2
votes
3answers
487 views
how to install mysql on mac?
My Mac runs Mac OS X, version 10.7.5., so it's a 64 bit system.
First, I downloaded mysql-5.5.28-osx10.6-x86_64.dmg.
Then I double clicked mysql-5.5.28-osx10.6-x86_64.dmg, it's mounted, and shows
...
0
votes
1answer
34 views
Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL
I am trying to create a new table new_table from t1 by comparing primary key values of another table t2
ie,
1) created table new_table
2) gave the query
insert into new_table select a.* ...
2
votes
1answer
131 views
Restriction on self referencing on insert in MYSQL?
How to restrict insert on adding self referencing rows in a recursive relation table (a table contains foreign key points itself).
mysql> SELECT * FROM Employee;
...
3
votes
1answer
208 views
How to Update same table on deletion in MYSQL?
In my Database I have a table: Employee with recursive association, an employee can be boss of other employee.
Following is query I used to create table:
CREATE TABLE IF NOT EXISTS `Employee` ...
3
votes
3answers
3k views
How to Add Federated engine after installing Mysql
I have Mysql 5.5.18
and upon show engines:
show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine ...
1
vote
0answers
59 views
How to create multi volume file backup using symantec backup.exec
I am using symantec backup .exec for backup and restore in mysql server . Now I am in need to take larger database backup in tape .But it exeeds the tape storage and I am trying to create a multi ...
2
votes
1answer
182 views
mysql emits BINLOG rows even though binlog_format=STATEMENT
We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default)
According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in ...
1
vote
1answer
156 views
mysql session logging tables
I've been building a session logging system for our sites. We get around ~15m hits a year and at the moment we're really only interested in stats about people who logon to our sites.
Our table ...
2
votes
1answer
794 views
MySql - Changing expire_logs_days without restarting the server
I'm using MySql 5.5.
Is it possible to change expire_logs_days and have the changes take effect without restarting the server?
1
vote
1answer
135 views
Query to take partial backup in MYSQL
I tried to take full backup in mysql using enterprise backup by using the command
mysqlbackup -u root -p --backup-dir=g:\full_bck backup
But I could not take backup of a single database , I ...
0
votes
0answers
110 views
how to improve performance of my query it was taking more than one hour in my environment
How can I improve performance of below query? It was running more than one hour to execute in my production environment. Query plan:
mysql> explain
SELECT transaction_detail.trans_id,
...
1
vote
1answer
158 views
How to COUNT number of rows with LIMIT?
I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too?
Currently, I use two separate queries to do so as
SELECT COUNT(*) FROM col WHERE CLAUSE
...
1
vote
1answer
428 views
Retrieve double data type value in non exponential form
Is it possible to retrieve non-exponential value in double data type column? After the width of the column is increased to 15 it shows the values in exponential form.
Now I get the following:
...
1
vote
1answer
120 views
How can I change the timezone in MySQL?
In a MySQL DB, I have two varchar fields. One is for date and one is for time. I need to change the time from CST to GMT. In MySQL how do we convert the combination of these date and time fields from ...
