Search Results

Search found 48190 results on 1928 pages for 'mysql slow query log'.

Page 27/1928 | < Previous Page | 23 24 25 26 27 28 29 30 31 32 33 34  | Next Page >

• MySQL query optimization JOIN

  - by Pierre

  Hi, I need your help to optimize those mysql query, both are in my slow query logs. SELECT a.nom, c.id_apps, c.id_commentaire, c.id_utilisateur, c.note_commentaire, u.nom_utilisateur FROM comments AS c LEFT JOIN apps AS a ON c.id_apps = a.id_apps LEFT JOIN users AS u ON c.id_utilisateur = u.id_utilisateur ORDER BY c.date_commentaire DESC LIMIT 5; There is a MySQL INDEX on c.id_apps, a.id_apps, c.id_utilisateur, u.id_utilisateur and c.date_commentaire. SELECT a.id_apps, a.id_itunes, a.nom, a.prix, a.resume, c.nom_fr_cat, e.nom_edit FROM apps AS a LEFT JOIN cat AS c ON a.categorie = c.id_cat LEFT JOIN edit AS e ON a.editeur = e.id_edit ORDER BY a.id_apps DESC LIMIT 20; There is a MySQL INDEX on a.categorie, c.id_cat, a.editeur, e.id_edit and a.id_apps Thanks

  Read the article

• MySQL, C++: Retrieving auto-increment ID

  - by Thomas Matthews

  I have a table with an auto-incrementing ID. After inserting a new row, I would like to retrieve the new ID. I found an article that used the MySQL function LAST_INSERT_ID(). The article says to create a new query and submit it. I'm using MySQL Connector C++, Windows XP and Vista, and Visual Studio 9. Here are my questions: Is there an API, for the connector, that will fetch the ID out of the record? Does the result set, after an insert/append, contain the new ID? The LAST_INSERT_ID is MySQL specific. Is there an SQL standard method for obtaining the new ID?

  Read the article

• Best implementation for MySQL replication with Rails 3?

  - by vonconrad

  We're looking at potentially setting up replication for our primary MySQL database, and while setting up the replication seems pretty straight-forward, the application implementation seems a bit murkier. My first idea would be to set up a master-slave configuration and RW-splitting, with all write queries (CREATE, INSERT, UPDATE) going to master, and all read queries (SELECT) going to slave. Having read up on it, it seems that there are essentially two options for how to implement this with our app: Using an independent middleware layer for all MySQL connections, such as MySQL proxy or DBSlayer. However, the former is in Alpha and the latter has limited documentation. Using a Ruby-based gem/plugin, such as Octopus to achieve RW-splitting in the framework. If we wanted to go with a master-slave setup, what you recommend moving forward? The other thought I've had was to use a master-master configuration, but am unsure about the implementation of such a setup. Thoughts?

  Read the article

• Can't delete a mySQL table. (Error 1050)

  - by doublejosh

  I have a pesky table that will not delete and it's holding up my dev environment refresh :( I know this table exists. Example... mysql> select * from uc_order_products_qty_vw limit 10; +-----+-------------+---------+---------+---------+---------+ | nid | order_count | avg_qty | sum_qty | max_qty | min_qty | +-----+-------------+---------+---------+---------+---------+ | 105 | 1 | 1.0000 | 1 | 1 | 1 | | 110 | 5 | 1.0000 | 5 | 1 | 1 | | 111 | 1 | 1.0000 | 1 | 1 | 1 | | 113 | 5 | 1.0000 | 5 | 1 | 1 | | 114 | 1 | 1.0000 | 1 | 1 | 1 | | 115 | 1 | 1.0000 | 1 | 1 | 1 | | 117 | 2 | 1.0000 | 2 | 1 | 1 | | 119 | 3 | 1.3333 | 4 | 2 | 1 | | 190 | 5 | 1.0000 | 5 | 1 | 1 | | 199 | 2 | 1.0000 | 2 | 1 | 1 | +-----+-------------+---------+---------+---------+---------+ 10 rows in set (0.00 sec) However when I try to drop it... mysql> DROP TABLE IF EXISTS uc_order_products_qty_vw; Query OK, 0 rows affected, 1 warning (0.00 sec) It doesn't work, the table is still there, and the warning says this... mysql> show warnings limit 1; +-------+------+------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------+ | Note | 1051 | Unknown table 'uc_order_products_qty_vw' | +-------+------+------------------------------------------+ 1 row in set (0.00 sec) Feeling pretty dumbfounded.

  Read the article

• Best way to handle MySQL date for performance with thousands of users

  - by bitLost

  I am currently part of a team designing a site that will potentially have thousands of users who will be doing a number of date related searches. During the design phase we have been trying to determine which makes more sense for performance optimization. Should we store the datetime field as a mysql datetime. Or should be break it up into a number of fields (year, month, day, hour, minute, ...) The question is with a large data set and a potentially large set of users, would we gain performance wise breaking the datetime into multiple fields and saving on relying on mysql date functions? Or is mysql already optimized for this?

  Read the article

• MySQL, delete and index hint

  - by Manuel Darveau

  I have to delete about 10K rows from a table that has more than 100 million rows based on some criteria. When I execute the query, it takes about 5 minutes. I ran an explain plan (the delete query converted to select * since MySQL does not support explain delete) and found that MySQL uses the wrong index. My question is: is there any way to tell MySQL which index to use during delete? If not, what ca I do? Select to temp table then delete from temp table? Thank you!

  Read the article

• MySQL hangs if connection comes from outside the LAN

  - by Subito

  I have a MySQL Server operating just fine if I access him from his local LAN (192.168.100.0/24). If I try to access hin from another LAN (192.168.113.0/24 in this case) it hangs for a really long time before delivering the result. SHOW PROCESSLIST; shows this process in Sleep, State empty. If I strace -p this process I get the following Output (23512 is the PID of the corresponding mysqld process): Process 23512 attached - interrupt to quit restart_syscall(<... resuming interrupted call ...>) = 1 fcntl(10, F_GETFL) = 0x2 (flags O_RDWR) fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK) = 0 accept(10, {sa_family=AF_INET, sin_port=htons(51696), sin_addr=inet_addr("192.168.113.4")}, [16]) = 33 fcntl(10, F_SETFL, O_RDWR) = 0 rt_sigaction(SIGCHLD, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, ) = 0 getpeername(33, {sa_family=AF_INET, sin_port=htons(51696), sin_addr=inet_addr("192.168.113.4")}, [16]) = 0 getsockname(33, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0 open("/etc/hosts.allow", O_RDONLY) = 64 fstat(64, {st_mode=S_IFREG|0644, st_size=580, ...}) = 0 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000 read(64, "# /etc/hosts.allow: list of host"..., 4096) = 580 read(64, "", 4096) = 0 close(64) = 0 munmap(0x7f9ce9839000, 4096) = 0 open("/etc/hosts.deny", O_RDONLY) = 64 fstat(64, {st_mode=S_IFREG|0644, st_size=880, ...}) = 0 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000 read(64, "# /etc/hosts.deny: list of hosts"..., 4096) = 880 read(64, "", 4096) = 0 close(64) = 0 munmap(0x7f9ce9839000, 4096) = 0 getsockname(33, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0 fcntl(33, F_SETFL, O_RDONLY) = 0 fcntl(33, F_GETFL) = 0x2 (flags O_RDWR) setsockopt(33, SOL_SOCKET, SO_RCVTIMEO, "\36\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0 setsockopt(33, SOL_SOCKET, SO_SNDTIMEO, "<\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0 fcntl(33, F_SETFL, O_RDWR|O_NONBLOCK) = 0 setsockopt(33, SOL_IP, IP_TOS, [8], 4) = 0 setsockopt(33, SOL_TCP, TCP_NODELAY, [1], 4) = 0 futex(0x7f9cea5c9564, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x7f9cea5c9560, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1 futex(0x7f9cea5c6fe0, FUTEX_WAKE_PRIVATE, 1) = 1 poll([{fd=10, events=POLLIN}, {fd=12, events=POLLIN}], 2, -1) = 1 ([{fd=10, revents=POLLIN}]) fcntl(10, F_GETFL) = 0x2 (flags O_RDWR) fcntl(10, F_SETFL, O_RDWR|O_NONBLOCK) = 0 accept(10, {sa_family=AF_INET, sin_port=htons(51697), sin_addr=inet_addr("192.168.113.4")}, [16]) = 31 fcntl(10, F_SETFL, O_RDWR) = 0 rt_sigaction(SIGCHLD, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, {SIG_DFL, [CHLD], SA_RESTORER|SA_RESTART, 0x7f9ce7ca34f0}, ) = 0 getpeername(31, {sa_family=AF_INET, sin_port=htons(51697), sin_addr=inet_addr("192.168.113.4")}, [16]) = 0 getsockname(31, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0 open("/etc/hosts.allow", O_RDONLY) = 33 fstat(33, {st_mode=S_IFREG|0644, st_size=580, ...}) = 0 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000 read(33, "# /etc/hosts.allow: list of host"..., 4096) = 580 read(33, "", 4096) = 0 close(33) = 0 munmap(0x7f9ce9839000, 4096) = 0 open("/etc/hosts.deny", O_RDONLY) = 33 fstat(33, {st_mode=S_IFREG|0644, st_size=880, ...}) = 0 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9ce9839000 read(33, "# /etc/hosts.deny: list of hosts"..., 4096) = 880 read(33, "", 4096) = 0 close(33) = 0 munmap(0x7f9ce9839000, 4096) = 0 getsockname(31, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("192.168.100.190")}, [16]) = 0 fcntl(31, F_SETFL, O_RDONLY) = 0 fcntl(31, F_GETFL) = 0x2 (flags O_RDWR) setsockopt(31, SOL_SOCKET, SO_RCVTIMEO, "\36\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0 setsockopt(31, SOL_SOCKET, SO_SNDTIMEO, "<\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0", 16) = 0 fcntl(31, F_SETFL, O_RDWR|O_NONBLOCK) = 0 setsockopt(31, SOL_IP, IP_TOS, [8], 4) = 0 setsockopt(31, SOL_TCP, TCP_NODELAY, [1], 4) = 0 futex(0x7f9cea5c9564, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x7f9cea5c9560, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1 futex(0x7f9cea5c6fe0, FUTEX_WAKE_PRIVATE, 1) = 1 poll([{fd=10, events=POLLIN}, {fd=12, events=POLLIN}], 2, -1^C <unfinished ...> Process 23512 detached This output repeats itself until the answer gets send. It could take up to 15 Minutes until the request gets served. In the local LAN its a matter of Milliseconds. Why is this and how can I debug this further? [Edit] tcpdump shows a ton of this: 14:49:44.103107 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [S.], seq 1434117703, ack 1793610733, win 14600, options [mss 1460,nop,nop,sackOK,nop,wscale 7], length 0 14:49:44.135187 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 106:145, ack 182, win 4345, length 39 14:49:44.135293 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [P.], seq 182:293, ack 145, win 115, length 111 14:49:44.167025 IP 192.168.X.6.64624 > cassandra-test.mysql: Flags [.], ack 444, win 4280, length 0 14:49:44.168933 IP 192.168.X.6.64626 > cassandra-test.mysql: Flags [.], ack 1, win 4390, length 0 14:49:44.169088 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [P.], seq 1:89, ack 1, win 115, length 88 14:49:44.169672 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 145:171, ack 293, win 4317, length 26 14:49:44.169726 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [P.], seq 293:419, ack 171, win 115, length 126 14:49:44.275111 IP 192.168.X.6.64626 > cassandra-test.mysql: Flags [P.], seq 1:74, ack 89, win 4368, length 73 14:49:44.275131 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [.], ack 74, win 115, length 0 14:49:44.275149 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 171:180, ack 419, win 4286, length 9 14:49:44.275189 IP cassandra-test.mysql > 192.168.X.6.64626: Flags [P.], seq 89:100, ack 74, win 115, length 11 14:49:44.275264 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [P.], seq 180:185, ack 419, win 4286, length 5 14:49:44.275281 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [.], ack 185, win 115, length 0 14:49:44.275295 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [F.], seq 419, ack 185, win 115, length 0 14:49:44.275650 IP 192.168.X.6.64625 > cassandra-test.mysql: Flags [F.], seq 185, ack 419, win 4286, length 0 14:49:44.275660 IP cassandra-test.mysql > 192.168.X.6.64625: Flags [.], ack 186, win 115, length 0 14:49:44.275910 IP 192.168.X.6.64627 > cassandra-test.mysql: Flags [S], seq 2336421549, win 8192, options [mss 1351,nop,wscale 2,nop,nop,sackOK], length 0 14:49:44.275921 IP cassandra-test.mysql > 192.168.X.6.64627: Flags [S.], seq 3289359778, ack 2336421550, win 14600, options [mss 1460,nop,nop,sackOK,nop,wscale 7], length 0

  Read the article

• OurSQL: The MySQL Database Community Podcast

  - by bertrand.matthelie(at)oracle.com

  @font-face { font-family: "Arial"; }@font-face { font-family: "Cambria"; }p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: "Times New Roman"; }a:link, span.MsoHyperlink { color: blue; text-decoration: underline; }a:visited, span.MsoHyperlinkFollowed { color: purple; text-decoration: underline; }div.Section1 { page: Section1; } For those of you not aware of it, Sheeri K. Cabral and Sarah Novotny are doing a great job running the "OurSQL" Podcast. A great and convenient way to learn more about various MySQL topics. @font-face { font-family: "Arial"; }@font-face { font-family: "Cambria"; }p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: "Times New Roman"; }div.Section1 { page: Section1; } Episode 33 is about "Looking through the Lenz"...that is, Lenz Grimmer, MySQL Community Manager at Oracle and long time MySQLer.   Lenz talks about snapshot backups in general, MySQL backups with snapshots, and mylvmbackup, a script he wrote and maintains to easily take consistent MySQL snapshot backups. Check it out!   Keep up the good work, Sheeri and Sarah!

  Read the article

• Beginning on MySQL 5.6? Take the New MySQL for Beginners Training

  - by Antoinette O'Sullivan

  The MySQL for Beginners training course is a great way of for you to learn about the world's more popular open source database. During this 4 day course, epxert instructors will teach you how to use MySQL Server 5.6 and the latest tools while helping you develop deeper knowledge of using relational databases. You can take this live-instructor course as a: Live-Virtual event: Take this course from your own desk, choosing from a selection of events on the schedule to suit different time-zones. In-Class Event: Travel to an education center to follow this course. Below is a selection of events already on the schedule.  Location  Date  Delivery Language  Brussels, Belgium  8 September 2013  English  London, England  1 July 2013  English  Berlin, Germany  2 September 2013  German  Stuttgart, Germany  28 October 2013  German  Riga, Latvia  26 August 2013  Latvian Utrecht, Netherlands  9 September 2013  English   Warsaw, Poland  15 July 2013  Polish  Cape Town, South Africa  22 July 2013  English  Petaling Jaya, Malaysia  22 July 2013  English  Sao Paulo, Brazil  7 October 2013  Brazilian Portugese To register for this course or to learn more about the authentic MySQL curriculum, go to http://oracle.com/education/mysql.

  Read the article

• Items Affecting Performance of the MySQL Database

  - by Antoinette O'Sullivan

  To learn about the many factors that can affect the performance of the MySQL Database, take the MySQL Performance Tuning course. You will learn: How your hardware and operating system can affect performance How to set up and logging to improve performance Best practices for backup and recovery And much more You can take this 4-day instructor-led course through the following formats: Training-on-Demand: Start training within 24 hours of registering for training, following lectures at your own pace through streaming video and booking time on a lab environment to suit your schedule. Live-Virtual Event: Attend a live event from your own desk, no travel required. Choose from a selection of events on the schedule to suit different time-zones. In-Class Event: Travel to an education center to attend this course. Below is a selection of events already on the schedule.  Location  Date  Delivery Language  Brussels, Beligum  10 November 2014  English  Sao Paolo, Brazil  25 August 2014  Brazilian Portuguese  London, England  20 October 2014  English  Milan, Italy  20 October 2014  Italian  Rome, Italy  1 December 2014  Italian  Riga, Latvia  29 September 2014  Latvian  Petaling Jaya, Malaysia  22 September 2014  English  Utrecht, Netherlands  10 November 2014  English  Warsaw, Poland  1 September 2014  Polish  Barcelona, Spain  14 October 2014  Spanish To register for an event, request an additional event, or learn more about the authentic MySQL Curriculum, go to http://education.oracle.com/mysql.

  Read the article

• Ubuntu 12.04 MySQL 5.5 MyODBC 5.1 or 3.1 query hangs

  - by jorgearr

  I have been able to install Ubuntu 12.04 with LAMP MySQL version 5.5.x It works fine within linux, it allows me to connect from myodbc windows vista or windows 7 I have configured networking access and have been able to access from windows vista using putty and other tcp connections like mysql query browser. I have also configured or disabled ufw firewall and apparmor. The connection works fine until I query data from the tables. It lets me query small amounts of data like: SELECT name FROM users limit 20 but if I do a SELECT * FROM users, it goes on a never-ending loop. This happens even on tables with very few records like 5 or even less. The problems occur with windows because I tried ssh from linux mint and it worked fine. I need to be able to work using MyODBC either 3.51 or 5.1 since my client program is made in VB6 and connects to mysql server via tcp/ip. The server is an HP PROLIANT ML350G6 with Intel Xeon 64 bits. I tried several ubuntu server version (12.04 64bit, 10.10 64bit, 11.04 32bit) and none has worked I even tried CentOS 6.3 and the same. As a reference, it works fine with onother ubuntu server version 6.x on HP Proliant 150 and mysql 5.0.x that is like 7 years old and never updated. Help Please.

  Read the article

• Learn More About the Scalability, Uptime, and Agility of MySQL Cluster

  - by Antoinette O'Sullivan

  Learn more about the uncompromising scalability, uptime, and agility of MySQL Cluster by taking the authentic MySQL Cluster training course. During this three day class, you will learn how to properly configure and manage the cluster nodes to ensure high availability, how to install the different nodes as well as get a better understanding of the internals of the cluster. Events currently on the schedule for this class include:  Location  Date  Delivery Language  Wein, Austria  4 February 2013  German London, England  12 June 2013   English  Rennes, France 26 February 2013   French  Hamburg, Germany 21 January 2013   German  Munich, Germany  10 June 2013 German   Stuttgart, Germany  26 March 2013  German  Budapest, Hungary  19 June 2013  Hungarian  Milan, Italy  4 February 2013  Italy  Warsaw, Poland  18 March 2013  Polish  Barcelona, Spain  4 March 2013  Spanish  Madrid, Spain 25 February 2013   Spanish Chicago, United States  27 March 2013   English  Reston, United States  6 February 2013  English  Jakarta, Indonesia 21 January 2013  English   Singapore 18 February 2013   English To register for an event or to see further details on this or other courses in the authentic MySQL curriculum, please go to http://oracle.com/education/mysql.

  Read the article

• New MySQL Enterprise Edition Demo

  - by Bertrand Matthelié

  @font-face { font-family: "Arial"; }@font-face { font-family: "Cambria"; }p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: "Times New Roman"; }div.Section1 { page: Section1; } In case you haven’t seen it yet, we released last week a new MySQL Enterprise Edition Flash Demo. @font-face { font-family: "Arial"; }@font-face { font-family: "Cambria"; }p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: "Times New Roman"; }a:link, span.MsoHyperlink { color: blue; text-decoration: underline; }a:visited, span.MsoHyperlinkFollowed { color: purple; text-decoration: underline; }div.Section1 { page: Section1; } This demo helps you understand in only 3 minutes how Oracle’s MySQL Enterprise Edition reduces the risk, cost and time required in developing, deploying and managing business-critical MySQL applications. You can watch it here. After watching the demo, you can easily go ahead and try MySQL Enterprise Edition, and/or get more detailed information in our whitepaper. @font-face { font-family: "Arial"; }@font-face { font-family: "Cambria"; }p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: "Times New Roman"; }a:link, span.MsoHyperlink { color: blue; text-decoration: underline; }a:visited, span.MsoHyperlinkFollowed { color: purple; text-decoration: underline; }div.Section1 { page: Section1; } Enjoy the demo!

  Read the article

• Jumpstart your MySQL Cluster Knowledge

  - by Antoinette O'Sullivan

  Join companies in the web, gaming, telecoms and mobile areas by learning about MySQL Cluster's distributed, shared-nothing, real-time design. The 3 days, MySQL Cluster course teaches you how to configure and manage the cluster nodes to ensure high availability. Learn how to install different nodes and understand cluster internals. Here is a sample of some events on the schedule for this course:  Location  Date  Delivery Language  Wien, Austria  4 February, 2013 German   Prague, Czech Republic  10 December, 2012 Czech   London, England  12 December, 2012 English   Hamburg, Germany  21 January, 2013  German  Stuttgart, Germany  26 March, 2013  German  Budapest, Hungary  4 December, 2012  Hungarian  Warsaw, Poland  10 December, 2012  Polish  Lisbon, Portugal  3 December, 2012 European Portugese   Barcelona, Spain  19 November, 2012 Spanish   Madrid, Spain  25 February, 2013 Spanish   Jakarta, Indonesia  21 January, 2013 English   Singapore  29 October, 2012 English   Chicago, United States  27 March, 2013  English  Reston, United States  6 February, 2013  English For more information on the authentic MySQL curriculum go to http://oracle.com/education/mysql

  Read the article

• heavy load on mysql

  - by payal

  i have dedicated server with very good configuation like 16 gb ram etc but i am facing heavy load from mysql however only one database is running and 5-10 pages are only running. However whm load is always less than one but when i click on whm load it shows 20% of cpu usage by mysql and after some time it starts saying can not connect to mysql . mysql server has gone away 1691 (Trace) (Kill) mysql 0 19.2 2.7 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log- error=/var/lib/mysql/server.xyz.com.err --pid-file=/var/lib/mysql/server.xyz.com.pid i have tested static pages they are coming blezing fast but all dynamic pages which are using mysql is coming damn slow it takes years to open.. ihave checked log error file it says nothing.i have increased maximum connnection also to 1000 but still same problem is there .if i disconnect that one databasejust by changing the name of database i can see withing half hour the load of server and mysql goes down to negliglble .i have tested everything and if there are some query which can cause heavy load to server can you please list which type of query can cause heavy load on server then also for 5-10 pages it will never cause that much heavy load. i have seen server with 500 websites but was working just fine.

  Read the article

• Fresh 12.04 Install - mySQL not starting

  - by Lee Armstrong

  I have a freshly installed Ubuntu 12.04 x64 server and I installed Percona server from their official repositories. Trouble is it will not start! mysql-error.log shows nothing obvious. 121129 12:16:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/ 121129 12:16:54 [Note] Plugin 'FEDERATED' is disabled. 121129 12:16:54 InnoDB: The InnoDB memory heap is disabled 121129 12:16:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins 121129 12:16:54 InnoDB: Compressed tables use zlib 1.2.3 121129 12:16:54 InnoDB: Using Linux native AIO 121129 12:16:54 InnoDB: Initializing buffer pool, size = 12.0G 121129 12:16:54 InnoDB: Completed initialization of buffer pool 121129 12:16:54 InnoDB: highest supported file format is Barracuda. 121129 12:16:55 InnoDB: Waiting for the background threads to start 121129 12:16:56 Percona XtraDB (http://www.percona.com) 1.1.8-rel29.1 started; log sequence number 1598476 121129 12:16:56 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 121129 12:16:56 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 121129 12:16:56 [Note] Server socket created on IP: '0.0.0.0'. 121129 12:16:56 [Note] Event Scheduler: Loaded 0 events 121129 12:16:56 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.28-29.1-log' socket: '/var/run/mysqld/mysql.sock' port: 3306 Percona Server (GPL), Release 29.1 121129 12:16:56 [Note] Event Scheduler: scheduler thread started with id 1 And the syslog shows... Nov 29 12:17:07 V-PF-SQL1 /etc/init.d/mysql[2206]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in Nov 29 12:17:07 V-PF-SQL1 /etc/init.d/mysql[2206]: #007/usr/bin/mysqladmin: connect to server at 'localhost' failed Nov 29 12:17:07 V-PF-SQL1 /etc/init.d/mysql[2206]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Nov 29 12:17:07 V-PF-SQL1 /etc/init.d/mysql[2206]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! Nov 29 12:17:07 V-PF-SQL1 /etc/init.d/mysql[2206]: The socket file is being created and I can access the server NOT using the socket using mysql -h 127.0.0.1 -P 3306 -u root --pPASSWORD

  Read the article

• How to input data into user defined variables into MySql query

  - by user292791

  Simple Shell script echo "Enter 1 for month of March" echo "Enter 2 for month of April" echo "Enter 3 for month of May" read Month case "$Month" in 1) echo "enter establishment name" read a; mysql -u root -p $a < "March.sql";; 2) echo "enter establishment name" read b; mysql -u root -p $b < "April.sql";; 3) echo "enter establishment name" read c; mysql -u root -p $c < "May.sql";; esac done In this i have three other query files March.sql, April.sql, May.sql. i'm linking this in shell script . Example of .sql file: SELECT DISTINCT substr( a.case_no, 3, 2 ), b.case_type, b.type_name, a.case_no into outfile '/tmp/April.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' FROM Civil_t AS a, Case_type_t AS b, disposal_proc AS c WHERE substr( a.case_no, 3, 2 ) = b.case_type AND a.date_of_decision BETWEEN '2014-04-01' AND '2014-04-30' AND a.case_no = c.case_no AND a.court_no =1; I have to alter the .sql script every time. Is there any method to read the variables from shell script and use it in mysql. For example:- echo "enter date" read a #input date Now i have read a "date" and i want to use it in March.sql query in where clause. Is there is any method of using this variable in .sql query.

  Read the article

• Adding one subquery makes query a little slower, adding another makes it way slower

  - by Jason Swett

  This is fast: select ba.name, penamt.value penamt, #address_line4.value address_line4 from account a join customer c on a.customer_id = c.id join branch br on a.branch_id = br.id join bank ba on br.bank_id = ba.id join account_address aa on aa.account_id = a.id join address ad on aa.address_id = ad.id join state s on ad.state_id = s.id join import i on a.import_id = i.id join import_bundle ib on i.import_bundle_id = ib.id join (select * from unused where heading_label = 'PENAMT') penamt ON penamt.account_id = a.id #join (select * from unused where heading_label = 'Address Line 4') address_line4 ON address_line4.account_id = a.id where i.active=1 And this is fast: select ba.name, #penamt.value penamt, address_line4.value address_line4 from account a join customer c on a.customer_id = c.id join branch br on a.branch_id = br.id join bank ba on br.bank_id = ba.id join account_address aa on aa.account_id = a.id join address ad on aa.address_id = ad.id join state s on ad.state_id = s.id join import i on a.import_id = i.id join import_bundle ib on i.import_bundle_id = ib.id #join (select * from unused where heading_label = 'PENAMT') penamt ON penamt.account_id = a.id join (select * from unused where heading_label = 'Address Line 4') address_line4 ON address_line4.account_id = a.id where i.active=1 But this is slow: select ba.name, penamt.value penamt, address_line4.value address_line4 from account a join customer c on a.customer_id = c.id join branch br on a.branch_id = br.id join bank ba on br.bank_id = ba.id join account_address aa on aa.account_id = a.id join address ad on aa.address_id = ad.id join state s on ad.state_id = s.id join import i on a.import_id = i.id join import_bundle ib on i.import_bundle_id = ib.id join (select * from unused where heading_label = 'PENAMT') penamt ON penamt.account_id = a.id join (select * from unused where heading_label = 'Address Line 4') address_line4 ON address_line4.account_id = a.id where i.active=1 Why is it fast when I include just one of the two subqueries but slow when I include both? I would think it should be twice as slow when I include both, but it takes a really long time. On on MySQL.

  Read the article

• Outlook 2007 - slow loading messages, etc

  - by studiohack

  Outlook 2007 is slow to load messages...Why is this, and what can I do to speed it up? Currently have about 850 messages in the Inbox folder, and the preview pane turned off, thus I view messages by double-clicking. This is where it gets slow, when I double click, it brings up a new window with everything but the actual message loaded. Solutions? Thanks! (running Windows 7)

  Read the article

• Outlook 2007 - slow loading messages

  - by studiohack

  Outlook 2007 is slow to load messages...Why is this, and what can I do to speed it up? Currently have about 850 messages in the Inbox folder, and the preview pane turned off, thus I view messages by double-clicking. This is where it gets slow, when I double click, it brings up a new window with everything but the actual message loaded, which then takes several more seconds to appear. Solutions? Thanks! (running Windows 7)

  Read the article

• SQLAuthority News – Book Signing Event – SQLPASS 2011 Event Log

  - by pinaldave

  I have been dreaming of writing book for really long time, and I finally got the chance – in fact, two chances!  I recently wrote two books: SQL Programming Joes 2 Pros: Programming and Development for Microsoft SQL Server 2008 [Amazon] | [Flipkart] | [Kindle] and SQL Wait Stats Joes 2 Pros: SQL Performance Tuning Techniques Using Wait Statistics, Types & Queues [Amazon] | [Flipkart] | [Kindle].  I had a lot of fun writing these two books, even though sometimes I had to sacrifice some family time and time for other personal development to write the books. The good side of writing book is that when the efforts put in writing books are recognize by books readers and kind organizations like expressor studio. Book Signing Event Book writing is a complex process.  Even after you spend months, maybe years, writing the material you still have to go through the editing and fact checking processes.  And, once the book is out there, there is no way to take back all the copies to change mistakes or add something you forgot.  Most of the time it is a one-way street. Book Signing Event Just like every author, I had a dream that after the books were written, they would be loved by people and gain acceptance by an audience. My first book, SQL Programming Joes 2 Pros: Programming and Development for Microsoft SQL Server 2008, is extremely popular because it helps lots of people learn various fundamental topics. My second book covers beginning to learn SQL Server Wait Stats, which is a relatively new subject. This book has had very good acceptance in the community. Book Signing Event Helping my community is my primary focus, so I was happy to see this year’s SQLPASS tag line: ‘This is a Community.‘ At the event, the expressor studio guys came up with a very novel idea. They had previously used my books and they had found them very useful. They got 100 copies of the book and decided to give it away to community folks. They invited me and my co-author Rick Morelan to hold a book signing event. We did a book signing on Thursday between 1 pm and 2 pm. Book Signing Event This event was one of the best events for me. This was my first book signing event outside of India. I reached the book signing location around 20 minutes before the scheduled time and what I saw was a big line for the book signing event. I felt very honored looking at the crowd and all the people around the event location. I felt very humbled when I saw some of my very close friends standing in the line to get my signature. It was really heartwarming to see so many enthusiasts waiting for more than an hour to get my signature. While standing in line I had the chance to have a conversation with every single person who showed up for the signature. I made sure that I repeated every single name and wrote it in every book with my signature. There is saying that if we write a name once we will remember it forever. I want to remember all of you who saw me at the book signing. Your comments were wonderful, your feedback was amazing and you were all very supportive. Book Signing Event I have made a note of every conversation I had with all of you when I was signing the books. Once again, I just want to express my thanks for coming to my book signing event. The whole experience was very humbling. On the top of it, I want to thank the expressor studio people who made it possible, who organized the whole signing event. I am so thankful to them for facilitating the whole experience, which is going to be hard to beat by any future experience. My books Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL PASS, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, SQLAuthority News, T SQL, Technology

  Read the article

• Introducing MySQL for Excel

  - by Javier Treviño

  As part of the new product initiatives of the MySQL on Windows group we released a tool that makes the task of getting data in and out of a MySQL Database very friendly and intuitive, and we paired it with one of the preferred applications for data analysis and manipulation in Windows platforms, MS Excel. Welcome to MySQL for Excel, an add-in that is installed and accessed from within the MS Excel’s Data tab offering a wizard-like interface arranged in an elegant yet simple way to help users browse MySQL Schemas, Tables, Views and Procedures and perform data operations against them using MS Excel as the vehicle to drive the data in and out MySQL Databases. One of the coolest features we had in mind designing MySQL for Excel is simplicity. MS Excel is simple and easy to work with, thus liked by many Windows users because they don’t have to be software gurus to use it.  We applied the same principle by targeting MySQL for Excel to any kind of user, so if you are already familiarized with Excel’s interface you will find yourself working with MySQL data in no time. MySQL for Excel is shipped within the MySQL Installer as one of the tools in the suite; if prerequisites are already installed (.NET Framework 4.0, Visual Studio Tools for Office 4.0 and of course MS Office), installing the add-in involves a very few clicks and no further setup to use it. Being an Excel Add-In there is no executable file involved after the installation, running MS Excel and opening the add-in from its Data tab is all that is required. MySQL for Excel automatically integrates with MySQL Workbench (if installed) to share the same connections to MySQL Server installations, that way connections are defined just once in either product saving time.  Opening the Add-In brings the Welcome Panel at the right side of the Excel main window from which connections to MySQL Servers are shown grouped by Local VS Remote connections; then users can open any of those connections by double-clicking it and entering the password of the used account.  Additionally a user can create a connection by clicking on the New Connection action label or edit connections through MySQL Workbench (if installed) by clicking on the Manage Connections action label. Once a connection is opened, the Schema Selection panel is shown, at the top of it the selected connection (connection name, hostname/IP and username). Just below, a list of schemas is displayed where User Schemas are grouped first followed by System Schemas; users can double-click any selected schema to go to the next panel or select a schema and clicking the Next > button. Users can alternatively click on the < Back button to go back to the Welcome Panel to close the current connection and open a new one; also by clicking the Create New Schema action label they can create an empty new schema. Once a schema is opened the DB Object Selection panel is shown, this is actually the place where the fun stuff happens; from here users are able to perform actions against MySQL Tables, Views and Procedures. ">The actions available here are about importing data from a MySQL Table, View or Procedure to Excel, exporting Excel data to a new MySQL Table, appending Excel data to an existing MySQL Table or editing a MySQL Table’s data by using an Excel Worksheet as a user interface to update data in any row/column, insert new rows or delete existing rows in a very easy and friendly way. More blog posts will follow describing all of these actions, so stay tuned! Remember that your feedback is very important for us, so drop us a message: · MySQL on Windows (this) Blog - https://blogs.oracle.com/MySqlOnWindows/ · Forum - http://forums.mysql.com/list.php?172 · Facebook - http://www.facebook.com/mysql Cheers!

  Read the article

• MySql Connector/NET 6.7.4 GA has been released

  - by fernando

  MySQL Connector/Net 6.7.4, a new version of the all-managed .NET driver for MySQL has been released.  This is the GA, is feature complete. It is recommended for production environments.  It is appropriate for use with MySQL server versions 5.0-5.7.  It is now available in source and binary form from http://dev.mysql.com/downloads/connector/net/#downloads and mirror sites (note that not all mirror sites may be up to date at this point-if you can't find this version on some mirror, please try again later or choose another download site.) The 6.7 version of MySQL Connector/Net brings the following new features: -  WinRT Connector. -  Load Balancing support. -  Entity Framework 5.0 support. -  Memcached support for Innodb Memcached plugin. -  This version also splits the product in two: from now on, starting version 6.7, Connector/NET will include only the former Connector/NET ADO.NET driver, Entity Framework and ASP.NET providers (Core libraries of MySql.Data, MySql.Data.Entity & MySql.Web). While all the former product Visual Studio integration (Design support, Intellisense, Debugger) are available as part of MySql Windows Installer under the name "MySql for Visual Studio".  WinRT Connector  ------------------------------------------- Now you can write MySql data access apps in Windows Runtime (aka Store Apps) using the familiar API of Connector/NET for .NET.  Load Balancing Support  -------------------------------------------  Now you can setup a Replication or Cluster configuration in the backend, and Connector/NET will balance the load of queries among all servers making up the backend topology.  Entity Framework 5.0  -------------------------------------------  Connector/NET is now compatible with EF 5, including special features of EF 5 like spatial types.  Memcached  -------------------------------------------  Just setup Innodb memcached plugin and use Connector/NET new APIs to establish a client to MySql 5.6 server's memcached daemon.  Bug fixes included in this release: - Fix for Entity Framework when inserts data having Identity columns (Oracle bug #16494585). - Fix for Connector/NET cannot read data from a MySql table using UTF-16/UTF-32 (MySql bug #69169, Oracle bug #16776818). - Fix for Malformed query in Entity Framework when eager loading due to multiple projections (MySql bug #67183, Oracle bug #16872852). - Fix for database objects with 'dbo' prefix when using automatic migrations in Entity Framework 5.0 (Oracle bug #16909439). - Fix for bug IIS application pool reset worker process causes website to crash (Oracle bug #16909237, Mysql Bug #67665). - Fix for bug Error in LINQ to Entities query when using Distinct().Count() (MySql Bug #68513, Oracle bug #16950146). - Fix for occasionally return no data when socket connection is slow, interrupted or delayed (MySql bug #69039, Oracle bug #16950212). - Fix for ConstraintException when filling a datatable (MySql bug #65065, Oracle bug #16952323). - Fix for Data Provider is not found after uninstalling Mysql for visual studio (Oracle bug #16973456). - Fix for nested sql generated for LINQ to Entities query with Take and Order by (MySql bug #65723, Oracle bug #16973939). The documentation is available at http://dev.mysql.com/doc/refman/5.7/en/connector-net.html  Enjoy and thanks for the support!  --  Fernando Gonzalez Sanchez | Software Engineer |  Oracle MySQL Windows Experience Team, Connector/NET  Guadalajara | Jalisco | Mexico 

  Read the article

• SQLAuthority News – Meeting SQL Friends – SQLPASS 2011 Event Log

  - by pinaldave

  One of the biggest reason I go to SQLPASS is that my friends are going there too. There are so many friends with whom I often talk on Facebook and Twitter but I rarely get time to meet them as well talk with them. One thing I am usually sure that many fo them will be for sure attend SQLPASS. This is one event which every SQL Server Enthusiast should attend. Just like everybody I had pleasant time to meet many of my SQL friends. There were so many friends that I met and I did not click photo. There were so many friends who clicked photo in their camera and I do not have them. Here are 1% of the photos which I have. If you are not in the photo, it does not mean I have less respect to our friendship. Please post link to our photo together :) I was very fortunate that I was able to snap a quick photograph with Pinal Dave with Dr. David DeWitt. I stood outside of the hall waiting for Dr. to show up and when he was heading down from convention center I requested him if I can have one photo for my memory lane and very politely he agreed to have one. It indeed made my day! Pinal Dave with Dr. David DeWitt Every single time I met Steve, I make sure I have one photo for my memory. Steve is so kind every single time. If you know SQL and do not know Steve Jones, you do not know SQL (IMHO). Following is the photograph with Michael McLean. More details about this photo in future blog post! Pinal Dave, Michael McLean, and Rick Morelan Arnie always shares his wisdom with me. I still remember when I very first time visited USA, I was standing alone in corner and Arnie walked to me and introduced to every single person he know. Talking to Arnie is always pleasure and inspiring. Arnie Rowland and Pinal Dave I am now published author and have written two books so far. I am fortunate to have Rick Morelan as Co-author of both of my books. He is great guy and very easy to become friends with. I am very much impressed by him and his kindness during book co-authoring. Here is very first of our photograph together at SQLPASS. Rick Morelan and Pinal Dave Diego Nogare and I have been talking for long time on twitter and on various social media channels. I finally got chance to meet my friend from Brazil. It was excellent experience to meet a friend whom one wants to meet for long time and had never got chance earlier. Buck Woody – who does not know Buck. He is funny, kind and most important friends of every one. Buck is so kind that he does not hesitate to approach people even though he is famous and most known in community. Every time I meet him I learn something. He is always smiling and approachable. Pinal Dave and Buck Woddy Rushabh Mehta is current SQL PASS president and personal friend. He has always smiling face and tremendous love for SQL community. I often wonder where he gets all the time for all the time and efforts he puts in for community. I never miss a chance to meet and greet him. Even though he is renowned SQL Guru and extremely busy person – every single time I meet him he always asks me – “How is Nupur and Shaivi?” He even remembers my wife and daughters name. I am touched. Rushabh Mehta and Pinal Dave Nigel Sammy has extremely well sense of humor and passion from community. We have excellent synergy while we are together. The attached photo is taken while I was talking to him on Seattle Shoreline about SQL. Pinal Dave and Nigel Sammy Rick Morelan wanted my this trip to be memorable. I am vegetarian and I told him that I do not like Seafood. Well, to prove the point, he took me to fantastic Seafood restaurant in Seattle and treated me with mouth watering vegetarian dishes. I think when I go to Seattle next time, I am going to make him to take me again to the same place. Rick, Rushabh, Pinal and Paras Well, this is a short summary of few of the friends I met at Seattle. What is the life without friends, eh? Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL PASS, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, SQLAuthority News, T SQL, Technology

  Read the article

• New in MySQL Enterprise Edition: Policy-based Auditing!

  - by Rob Young

  Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} For those with an interest in MySQL, this weekend's MySQL Connect conference in San Francisco has gotten off to a great start. On Saturday Tomas announced the feature complete MySQL 5.6 Release Candidate that is now available for Community adoption and testing. This announcement marks the sprint to GA that should be ready for release within the next 90 days. You can get a quick summary of the key 5.6 features here or better yet download the 5.6 RC (under “Development Releases”), review what's new and try it out for yourself! There were also product related announcements around MySQL Cluster 7.3 and MySQL Enterprise Edition . This latter announcement is of particular interest if you are faced with internal and regulatory compliance requirements as it addresses and solves a pain point that is shared by most developers and DBAs; new, out of the box compliance for MySQL applications via policy-based audit logging of user and query level activity. Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} One of the most common requests we get for the MySQL roadmap is for quick and easy logging of audit events. This is mainly due to how web-based applications have evolved from nice-to-have enablers to mission-critical revenue generation and the important role MySQL plays in the new dynamic. In today’s virtual marketplace, PCI compliance guidelines ensure credit card data is secure within e-commerce apps; from a corporate standpoint, Sarbanes-Oxely, HIPAA and other regulations guard the medical, financial, public sector and other personal data centric industries. For supporting applications audit policies and controls that monitor the eyes and hands that have viewed and acted upon the most sensitive of data is most commonly implemented on the back-end database. Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} With this in mind, MySQL 5.5 introduced an open audit plugin API that enables all MySQL users to write their own auditing plugins based on application specific requirements. While the supporting docs are very complete and provide working code samples, writing an audit plugin requires time and low-level expertise to develop, test, implement and maintain. To help those who don't have the time and/or expertise to develop such a plugin, Oracle now ships MySQL 5.5.28 and higher with an easy to use, out-of-the-box auditing solution; MySQL Enterprise Audit. MySQL Enterprise Audit The premise behind MySQL Enterprise Audit is simple; we wanted to provide an easy to use, policy-based auditing solution that enables you to quickly and seamlessly add compliance to their MySQL applications. MySQL Enterprise Audit meets this requirement by enabling you to: 1. Easily install the needed components. Installation requires an upgrade to MySQL 5.5.28 (Enterprise edition), which can be downloaded from the My Oracle Support portal or the Oracle Software Delivery Cloud. After installation, you simply add the following to your my.cnf file to register and enable the audit plugin: [mysqld] plugin-load=audit_log.so (keep in mind the audit_log suffix is platform dependent, so .dll on Windows, etc.) or alternatively you can load the plugin at runtime: mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so'; 2. Dynamically enable and disable the audit stream for a specific MySQL server. A new global variable called audit_log_policy allows you to dynamically enable and disable audit stream logging for a specific MySQL server. The variable parameters are described below. 3. Define audit policy based on what needs to be logged (everything, logins, queries, or nothing), by server. The new audit_log_policy variable uses the following valid, descriptively named values to enable, disable audit stream logging and to filter the audit events that are logged to the audit stream: "ALL" - enable audit stream and log all events "LOGINS" - enable audit stream and log only login events "QUERIES" - enable audit stream and log only querie events "NONE" - disable audit stream 4. Manage audit log files using basic MySQL log rotation features. A new global variable, audit_log_rotate_on_size, allows you to automate the rotation and archival of audit stream log files based on size with archived log files renamed and appended with datetime stamp when a new file is opened for logging. 5. Integrate the MySQL audit stream with MySQL, Oracle tools and other third-party solutions. The MySQL audit stream is written as XML, using UFT-8 and can be easily formatted for viewing using a standard XML parser. This enables you to leverage tools from MySQL and others to view the contents. The audit stream was also developed to meet the Oracle database audit stream specification so combined Oracle/MySQL shops can import and manage MySQL audit images using the same Oracle tools they use for their Oracle databases. So assuming a successful MySQL 5.5.28 upgrade or installation, a common set up and use case scenario might look something like this: Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} It should be noted that MySQL Enterprise Audit was designed to be transparent at the application layer by allowing you to control the mix of log output buffering and asynchronous or synchronous disk writes to minimize the associated overhead that comes when the audit stream is enabled. The net result is that, depending on the chosen audit stream log stream options, most application users will see little to no difference in response times when the audit stream is enabled. So what are your next steps? Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} Get all of the grainy details on MySQL Enterprise Audit, including all of the additional configuration options from the MySQL documentation. MySQL Enterprise Edition customers can download MySQL 5.5.28 with the Audit extension for production use from the My Oracle Support portal. Everyone can download MySQL 5.5.28 with the Audit extension for evaluation from the Oracle Software Delivery Cloud. Learn more about MySQL Enterprise Edition. As always, thanks for your continued support of MySQL!

  Read the article

< Previous Page | 23 24 25 26 27 28 29 30 31 32 33 34  | Next Page >