]]>
]]>

You are here

Feed aggregator

Failed to connect to MySQL server: DBI connect

Lastest Forum Posts - February 2, 2015 - 7:14am
After upgrading to CentOS 6.6, our extrabackup backup has stopped working. When I attempt to run the backup off the command line, it fails with: "Access denied for user 'mysql'@'localhost' (using password: NO) at /usr/bin/innobackupex line 2978".

The complete command line and traceback is at: http://pastebin.com/vEu7jMpR

Here are a few notes about my setup:
# mysql --version
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
# uname -a
Linux hostname.domain.tld 2.6.32-504.8.1.el6.x86_64 #1 SMP Wed Jan 28 21:11:36 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
# cat /etc/redhat-release
CentOS release 6.6 (Final)
# yum list perl-DBD-MySQL
Loaded plugins: versionlock
Installed Packages
perl-DBD-MySQL.x86_64 4.013-3.el6 @base

Thanks very much for your help.
-bsquared


Cluster not starting when mysqld_multi is used

Lastest Forum Posts - February 1, 2015 - 11:11pm
I have a Percona cluster.
For a new application I needed to run a non-clustered instance of mysqld on one node.
So I created a separate configuration in my.cnf, and everything worked great.

However, I decided to check if it runs after reboot, and found out that it does not.
Mysqld starts, but it doesn't try to access the cluster, despite all the setting regarding the clustered instance remained the same.

Here is the startup log:

150202 10:47:48 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150202 10:47:48 mysqld_safe Skipping wsrep-recover for 646956ec-a7c6-11e4-a49a-0eafe484ab9f:35 pair
150202 10:47:48 mysqld_safe Assigning 646956ec-a7c6-11e4-a49a-0eafe484ab9f:35 to wsrep_start_position
150202 10:47:48 [Note] WSREP: wsrep_start_position var submitted: '646956ec-a7c6-11e4-a49a-0eafe484ab9f:35'
150202 10:47:48 [Note] Plugin 'FEDERATED' is disabled.
150202 10:47:48 InnoDB: The InnoDB memory heap is disabled
150202 10:47:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150202 10:47:48 InnoDB: Compressed tables use zlib 1.2.3
150202 10:47:48 InnoDB: Using Linux native AIO
150202 10:47:48 InnoDB: Initializing buffer pool, size = 128.0M
150202 10:47:49 InnoDB: Completed initialization of buffer pool
150202 10:47:49 InnoDB: highest supported file format is Barracuda.
150202 10:47:49 InnoDB: Waiting for the background threads to start
150202 10:47:50 Percona XtraDB (http://www.percona.com) 5.5.39-36.0 started; log sequence number 1600827
150202 10:47:50 [Note] Event Scheduler: Loaded 0 events
150202 10:47:50 [Note] WSREP: Initial position: 646956ec-a7c6-11e4-a49a-0eafe484ab9f:35
150202 10:47:50 [Note] WSREP: wsrep_load(): loading provider library 'none'
150202 10:47:50 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.39-36.0-55' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona XtraDB Cluster (GPL), Release rel36.0, Revision 824, WSREP version 25.11, wsrep_25.11.r4023

If I return back to the single-instance my.cnf then this node joins cluster again.
Does myslqd_multi support clustered instances at all? And if it does, which option should I add?

One node crashing

Lastest Forum Posts - February 1, 2015 - 10:07pm
I have a 4 node cluster, where 3 of the nodes are VPSes in the same data centre, and the fourth node is in the office, which is connected to the internet by fibre, and to the rest of the cluster via OpenVPN.

The VPSes are quad E5620 CPU, 32GB mem, 2TB disk. The dedicated office server is 8 thread i7-4820K CPU, 48GB mem, and runs 3 KVM VPSes as well. All have of the order of 10GB memory unused.

All are running Ubuntu 14.04 LTS, and Percona 5.6.21 ( the latest GHOST vulnerability has been patched on all servers ).

MySQL on the dedicated node has crashed twice over the weekend with the message below.

Could anyone provide any pointers on where to look while I'm working through the manual, or in the meantime indicate how to reverse the message

'150201 06:11:25 mysqld_safe WSREP: not restarting wsrep node automatically' so that it does restart?

Cheers,


Steve


7:11:24 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona XtraDB Cluster better by reporting any
bugs at https://bugs.launchpad.net/percona-xtradb-cluster

key_buffer_size=134217728
read_buffer_size=2097152
max_used_connections=2
max_threads=153
thread_count=3
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 603396 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x49f4130
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fe1d403ee20 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x923b1c]
/usr/sbin/mysqld(handle_fatal_signal+0x352)[0x67cac2]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7fe209627340]
/usr/sbin/mysqld[0xb42ab0]
/usr/sbin/mysqld(_ZN11MDL_context12release_lockEP10MDL_ticke t+0x4e)[0x66e29e]
/usr/sbin/mysqld(_ZN18Global_backup_lock7releaseEP3THD+0x1c)[0x66127c]
/usr/sbin/mysqld(_ZN3THD7cleanupEv+0xee)[0x6ccb5e]
/usr/sbin/mysqld(_ZN3THD17release_resourcesEv+0x298)[0x6cf988]
/usr/sbin/mysqld(_Z29one_thread_per_connection_endP3THDb+0x2 a)[0x5920fa]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xf0)[0x6d7840]
/usr/sbin/mysqld(handle_one_connection+0x39)[0x6d7ad9]
/usr/sbin/mysqld(pfs_spawn_thread+0x140)[0xb41570]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7fe20961f182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fe208b2c00d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 196
Status: KILL_CONNECTION

You may download the Percona XtraDB Cluster operations manual by visiting
http://www.percona.com/software/percona-xtradb-cluster/. You may find information
in the manual which will help you identify the cause of the crash.




Moving data directory to another location

Lastest Forum Posts - January 31, 2015 - 4:45pm
Recently i installed fresh copy of Percona MySql 5.6 on server. I created my.cnf with following parameters but when i start "/etc/init.d/mysql start" i got "Starting MySQL (Percona Server). ERROR! The server quit without updating PID file (/home/databases/server.pid)." error.

Note: It works properly when i move /etc/my.cnf to another location.

[mysqld]
datadir=/home/databases
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

thread-cache-size=50
join-buffer-size=4M
max_allowed_packet=64M
table-cache=1024
tmp-table-size=64M
max-heap-table-size=64M
key-buffer-size=256M

innodb_buffer_pool_size=512M
innodb_log_buffer_size=8M
#innodb_log_file_size=64M
innodb_log_file_size = 5242880
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_file_per_table
innodb_file_format=barracuda
innodb_lazy_drop_table=1

[mysqld_safe]
log-error=/home/databases/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


Whats wrong with this configuration file.


Thanks & Regards

I am getting segmentation fault while running xtrabackup with MySQL 5.6 version

Lastest Forum Posts - January 30, 2015 - 9:37pm
here are the details of RPM's and percona xtrabackup version i am using in my server:

[mysql@trprtelgldb3 db01]$ innobackupex --user=backup --password=******** --use-memory=500M /db01/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

150130 22:57:50 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'backup' (using password: YES).
150130 22:57:50 innobackupex: Connected to MySQL server
150130 22:57:50 innobackupex: Executing a version check against the server...
Segmentation fault
[mysql@trprtelgldb3 db01]$ rpm -qa | grep -i mysql
MySQL-server-5.6.19-1.rhel5
MySQL-shared-5.6.19-1.rhel5
perl-DBD-MySQL-3.0007-2.el5
MySQL-shared-compat-5.6.19-1.rhel5
MySQL-client-5.6.19-1.rhel5
[mysql@trprtelgldb3 db01]$ uname -a
Linux trprtelgldb3.intra.searshc.com 2.6.18-274.18.1.el5xen #1 SMP Thu Feb 9 13:27:02 EST 2012 x86_64 x86_64 x86_64 GNU/Linux
[mysql@trprtelgldb3 db01]$ rpm -qa | grep -i percona
percona-xtrabackup-2.2.8-5059.el5




************************************************** ************************************************** **********************

[mysql@trprtelgldb3 db01]$ rpm -qa | grep -i mysql
MySQL-server-5.6.19-1.rhel5
MySQL-shared-5.6.19-1.rhel5
perl-DBD-MySQL-3.0007-2.el5
MySQL-shared-compat-5.6.19-1.rhel5
MySQL-client-5.6.19-1.rhel5
[mysql@trprtelgldb3 db01]$ uname -a
Linux trprtelgldb3.intra.searshc.com 2.6.18-274.18.1.el5xen #1 SMP Thu Feb 9 13:27:02 EST 2012 x86_64 x86_64 x86_64 GNU/Linux
[mysql@trprtelgldb3 db01]$ rpm -qa | grep -i percona
percona-xtrabackup-2.1.7-721.rhel5




[mysql@trprtelgldb3 ~]$ innobackupex --user=backup --password=******** --use-memory=500M /db01/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

150130 23:01:23 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'backup' (using password: YES).
150130 23:01:23 innobackupex: Connected to MySQL server
150130 23:01:23 innobackupex: Executing a version check against the server...
Segmentation fault

Please let me know in fixing this issue.

pt-archiver writing to STDOUT

Lastest Forum Posts - January 30, 2015 - 4:03pm
For the next version of pt-archiver, I would like to suggest adding the ability to stream directly to STDOUT. This way, we can more easily pump mysql data into our favorite object store. What's the best way to suggest this to the tool developers?

Thanks,
Jeffrey

Problem PREPARING incremental backup

Lastest Forum Posts - January 30, 2015 - 1:00pm
I'm testing out Percona XtraBackup 2.2 and ran into a problem the first time I tried to restore an incremental backup. I assume I've done something wrong; hopefully someone can tell me what that is, I've included all the details below.

Executive Summary:

The PREPARE of the first incremental backup fails with the following error:
2015-01-30 14:33:35 b735b700 InnoDB: Error: space id and page n stored in the page
InnoDB: read in are 0:524293, should be 0:5!

Operating System = Ubuntu 14.04.01 (Bitnami Tomcat Stack)
Database = MySQL 5.5.38

Backup Steps
============
1) Start up a virtual machine (V1) from image (V) and log in
2) Create a full backup (B) of the database:
/usr/bin/innobackupex --defaults-file=/opt/bitnami/mysql/my.cnf --user=root --password=xxxx /mnt/mySQLDumps/BackupTesting
==> Successful, creates directory 2015-01-30_10-44-37

3) Add a few records to the database
4) Create an incremental backup (b1) of the database
/usr/bin/innobackupex --defaults-file=/opt/bitnami/mysql/my.cnf --incremental --user=root --password=xxxx /mnt/mySQLDumps/BackupTesting --incremental-basedir=/mnt/mySQLDumps/BackupTesting/2015-01-30_10-44-37
==> Successful, creates directory 2015-01-30_10-59-30

5) Add a few more records to the database
6) Create an incremental backup (b2) of the database
/usr/bin/innobackupex --defaults-file=/opt/bitnami/mysql/my.cnf --incremental --user=root --password=xxxx /mnt/mySQLDumps/BackupTesting --incremental-basedir=/mnt/mySQLDumps/BackupTesting/2015-01-30_10-59-30
==> Successful, creates directory 2015-01-30_11-06-54

7) Disconnect the disk (D) with these backups on it from V1.


Restore Steps
=============
My first testcase was to do a point in time restore the database on a new virtual machine V2 to the state after the first incremental backup (b1)

8) Start up another virtual machine (V2) from image (V) and log in
9) Connect the disk (D) to (V2).
10) Prepare the base backup (B)
/usr/bin/innobackupex --defaults-file=/opt/bitnami/mysql/my.cnf --user=root --password=xxxx --apply-log --redo-only /mnt/mySQLDumps/BackupTesting/2015-01-30_10-44-37
==> Works OK

11) Prepare the incremental backup (b1)
/usr/bin/innobackupex --defaults-file=/opt/bitnami/mysql/my.cnf --user=root --password=xxxx --apply-log /mnt/mySQLDumps/BackupTesting/2015-01-30_10-44-37 --incremental-dir=/mnt/mySQLDumps/BackupTesting/2015-01-30_10-59-30
==> At this point I get an error:

xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (i686) (revision id: )
incremental backup from 19492498688 is enabled.
xtrabackup: cd to /mnt/mySQLDumps/BackupTesting/2015-01-30_10-44-37
xtrabackup: This target seems to be already prepared.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(19492581719)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /mnt/mySQLDumps/BackupTesting/2015-01-30_10-59-30
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Generating a list of tablespaces
xtrabackup: page size for /mnt/mySQLDumps/BackupTesting/2015-01-30_10-59-30/ibdata1.delta is 16384 bytes
Applying /mnt/mySQLDumps/BackupTesting/2015-01-30_10-59-30/ibdata1.delta to ./ibdata1...
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /mnt/mySQLDumps/BackupTesting/2015-01-30_10-59-30
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.8
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
2015-01-30 14:33:35 b735b700 InnoDB: Error: space id and page n stored in the page
InnoDB: read in are 0:524293, should be 0:5!
2015-01-30 14:33:35 b735b700 InnoDB: Assertion failure in thread 3073750784 in file srv0start.cc line 1373
InnoDB: Failing assertion: prev_space_id + 1 == undo_tablespace_ids[i]
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/...-recovery.html
InnoDB: about forcing recovery.
19:33:35 UTC - xtrabackup got signal 6 ;
This could be because you hit a bug or data is corrupted.
This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Thread pointer: 0xab7ff60
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x10000
xtrabackup(my_print_stacktrace+0x2d) [0x85a011d]
xtrabackup(handle_fatal_signal+0x270) [0x8439be0]
[0xb7760400]
[0xb7760424]
/lib/i386-linux-gnu/libc.so.6(gsignal+0x47) [0xb738b827]
/lib/i386-linux-gnu/libc.so.6(abort+0x143) [0xb738ec53]
xtrabackup(srv_undo_tablespaces_init(unsigned long, unsigned long, unsigned long, unsigned long*)+0x6a8) [0x8207ff8]
xtrabackup(innobase_start_or_create_for_mysql()+0x d86) [0x8208eb6]
xtrabackup() [0x81adde7]
xtrabackup(main+0x173f) [0x8197f8f]
/lib/i386-linux-gnu/libc.so.6(__libc_start_main+0xf3) [0xb7376a83]
xtrabackup() [0x81ab151]

Please report a bug at https://bugs.launchpad.net/percona-xtrabackup
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 2633.
main::apply_log() called at /usr/bin/innobackupex line 1561
innobackupex: Error:
innobackupex: ibbackup failed at /usr/bin/innobackupex line 2633.

No metrics after master slave switch

Lastest Forum Posts - January 30, 2015 - 5:59am
I had percona-agent running and reporting on a set of master and slave DBs. Everything was working fine, until I promoted the slaves to masters and then rejoined the old masters as a slaves. Once the switch was finished, my new masters were reporting correctly, but the slaves were not. The agents are connected, but I'm no longer getting any metrics. On one slave, the log is clean. On the other slave, I keep getting this error: write unix /var/run/mysqld/mysqld.sock: broken pipe. Why would switching roles break percona-agent? Any help is appreciated.

No metrics after master slave switch

Lastest Forum Posts - January 30, 2015 - 5:47am
I had percona-agent running and reporting on a set of master and slave DBs. Everything was working fine, until I promoted the slaves to masters and then rejoined the old masters as a slaves. Once the switch was finished, my new masters were reporting correctly, but the slaves were not. The agents are connected, but I'm no longer getting any metrics. On one slave, the log is clean. On the other slave, I keep getting this error: write unix /var/run/mysqld/mysqld.sock: broken pipe. Why would switching roles break percona-agent? Any help is appreciated.

OpenStack Live 2015: FAQs on the who, what, where, when, why & how

Latest MySQL Performance Blog posts - January 30, 2015 - 5:00am

This April 13-14 Percona is introducing an annual conference called OpenStack Live. I’ve seen a few questions about the new event so decided to help clarify what this show is about and who should attend.

Unlike OpenStack Summits, held twice a year and dedicated to primarily to developers, OpenStack Live is an opportunity for OpenStack evaluators and users of all levels to learn from experts on topics such as how to deploy, optimize, and manage OpenStack and the role of MySQL as a crucial technology in this free and open-source cloud computing software platform. A full day of hands-on tutorials will also focus on making OpenStack users more productive and confident in this emerging technology.

Still confused about OpenStack Live 2015? Fear not! Here are the answers to commonly asked questions.

Q: Who should attend?
A: You should attend…

  • if you are currently using OpenStack and want to improve your skills and knowledge
  • if you are evaluating or considering using it.
  • if you are a solutions provider – this is your opportunity to show the world your contributions and services

Q: Percona Live has a conference committee. Does OpenStack Live have one, too?
A: Yes and it’s a completely different committee comprised of:

  • Mark Atwood, Director of Open Source Engagement at HP (Conference Chairman)
  • Rich Bowen, OpenStack Community Liaison at Red Hat
  • Jason Rouault, Senior Director OpenStack Cloud at Time Warner Cable
  • Peter Boros, Principal Architect at Percona

Q: Are the tutorials really “hands-on”?
A: Yes and most are at least 3-hours long. So you’ll need your laptop and power cord. Here’s a look at all of the OpenStack tutorials.

Q: How meaty are the sessions?
A: Very meaty indeed! Here’s a sample:

Q: I am going to attend the Percona Live MySQL Conference and Expo. Will my pass also include OpenStack Live 2015?
A: Yes, your Percona Live pass will be honored at the OpenStack Live conference. OpenStack Live attendees will also have access to the Percona Live/OpenStack Live Exhibit hall, keynotes, receptions and FUN activities April 13 and 16, allowing them to dive deeper into MySQL topics such as high availability, security, performance optimization, and much more. However, the OpenStack Live pass does not allow access to Percona Live breakout sessions or tutorials.

Q: Where can I register?
A: You can register here and take advantage of Early Bird discounts but those end Feb. 1 at 11:30 p.m. PST, so hurry!

The post OpenStack Live 2015: FAQs on the who, what, where, when, why & how appeared first on MySQL Performance Blog.

apt-get update Linode servers being blocked

Lastest Forum Posts - January 29, 2015 - 2:23pm
I hope this post is in the correct place.

I have 2 linode servers and I've discovered that one of them cannot connect to repo.percona.com.

Here is the mtr:

root@main:~# mtr --report 45.56.87.111
Start: Thu Jan 29 22:02:24 2015
HOST: main.fixtonline.com Loss% Snt Last Avg Best Wrst StDev
1.|-- 23.92.24.2 0.0% 10 0.5 0.6 0.5 0.7 0.0
2.|-- 10ge7-6.core3.fmt2.he.net 0.0% 10 0.4 4.2 0.3 9.9 3.8
3.|-- 10ge10-1.core1.sjc2.he.ne 0.0% 10 0.9 6.6 0.9 12.9 4.2
4.|-- mpr1.sjc7.us 0.0% 10 0.8 0.9 0.7 1.7 0.0
5.|-- ae9.cr1.sjc2.us.zip.zayo. 0.0% 10 1.1 5.1 1.1 26.3 8.3
6.|-- ae0.cr1.lax112.us.zip.zay 0.0% 10 9.4 11.2 9.3 25.3 4.9
7.|-- ae2.cr1.iah1.us.zip.zayo. 0.0% 10 43.8 46.0 43.5 64.2 6.4
8.|-- ae8.cr1.dca2.us.zip.zayo. 10.0% 10 63.6 67.3 63.6 86.7 7.9
9.|-- xe-1-1-0.mpr3.iad1.us.zip 0.0% 10 63.9 65.1 63.9 74.9 3.4
10.|-- xe-1-1-0.mpr3.iad2.us.zip 0.0% 10 66.6 66.2 64.1 80.1 4.9
11.|-- ae0.mpr4.iad2.us.zip.zayo 0.0% 10 64.2 64.1 64.0 64.4 0.0
12.|-- 64.125.198.10.IPYX-069636 0.0% 10 64.6 65.1 64.4 69.6 1.5
13.|-- rst-c04-t2-3-10g.blackmes 0.0% 10 64.1 64.4 64.1 65.4 0.0
14.|-- rst-c03-p1-8g.blackmesh.c 0.0% 10 63.9 64.0 63.8 64.2 0.0
15.|-- ??? 100.0 10 0.0 0.0 0.0 0.0 0.0

I don't have any other issues with any other sites...

Are IPs in this range being blocked?

Run under Windows 7/8?

Lastest Forum Posts - January 29, 2015 - 1:44pm
Is there anyway to get the current version (2.2.8) Xtrabackup to run in Windows?

I have a sales department needing to have copies of their production MySQL server instance on laptops that don't always have internet connectivity, and they have to be windows. I was hoping to rsync the databases backup files and restore with Xtrabackup on the laptops

Restoring tables with innobackupex resulting in bad data

Lastest Forum Posts - January 29, 2015 - 7:19am
I have backups created with
: innobackupex --host=$DB_HOST --user=$DB_USER --password=$DB_PASS --include='db1,db2,db3' --use-memory=10G --rsync --slave-info $BACKUP_DIR I am attempting to restore a few specific tables from our latest 5 monthly backups using the instructions found here but most of the tables have bad data. All of the rows expected appear to be present, but the data is corrupt after a particular column in every row. One table in particular is heavily damaged in 4 of 5 backups.

The table structure used to restore the tables was exported via "mysqldump --no-data" at the time of the backups, so should be correct.

Here's an example of the bad data:
: mysql> SELECT * FROM `table1-2014-10-09` WHERE col1= 1 \G *************************** 1. row *************************** col0: 1 col1: 8 col2: Agoodstringvalue1 col3: enval2 col4: 2008-01-19 14:28:32 col5: 2010-07-25 01:43:04 col6: 10 col7: 282624 col8: 1 col9: Agoodstringvalue2 col10: 20 col11: Agoodstringvalue3 col12: 0000-00-09 05:05:08 col13: 2206-12-10 24:00:00 col14: 8640-03-26 01:50:09 col15: 2717-07-00 01:09:14 col16: 4.939e-29 col17: 1.79366e-43 col18: 0 col19: 0 col20: -128 col21: 31301 col22: -128 col23: 16474 col24: -989815040 col25: -128 col26: 8257568 col27: 109059715 col28: -16637 col29: ;? € € 1405718411 col30: 32 col31: 32 col32: -96 col33: 32 col34: -96 col35: 2.815312038823503e-250 col36: 1.062147479e-314 col37: 0 col38: 0 Every row in the table is like this, data starting at col12 is bad. Note the lack of year on col12 and the years 1000's in the future in 13, 14 and 15. Cols 16 and 17 are floats that should be between 0 and 10,000. col29 is a char value that should be a small string in english. (All columns 12+ are incorrect, these are just examples)

The table's structure
: CREATE TABLE IF NOT EXISTS `table1-2014-10-09` ( `col0` mediumint(8) unsigned NOT NULL, `col1` mediumint(8) unsigned NOT NULL DEFAULT '0', `col2` char(20) NOT NULL DEFAULT '', `col3` enum('enval1','enval2','enval3','enval4') NOT NULL, `col4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `col5` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `col6` tinyint(2) NOT NULL DEFAULT '0', `col7` int(10) unsigned NOT NULL DEFAULT '0', `col8` tinyint(1) NOT NULL DEFAULT '0', `col9` char(30) NOT NULL, `col10` tinyint(2) unsigned NOT NULL DEFAULT '0', `col11` char(30) NOT NULL DEFAULT 'Human', `col12` datetime NOT NULL, `col13` datetime DEFAULT '0000-00-00 00:00:00', `col14` datetime DEFAULT '0000-00-00 00:00:00', `col15` datetime DEFAULT '0000-00-00 00:00:00', `col16` float NOT NULL DEFAULT '0', `col17` float unsigned NOT NULL DEFAULT '0', `col18` smallint(5) unsigned NOT NULL DEFAULT '0', `col19` mediumint(8) unsigned NOT NULL DEFAULT '0', `col20` tinyint(1) NOT NULL DEFAULT '0', `col21` smallint(5) unsigned NOT NULL, `col22` tinyint(1) NOT NULL DEFAULT '0', `col23` smallint(5) NOT NULL, `col24` int(10) NOT NULL DEFAULT '0', `col25` tinyint(1) NOT NULL DEFAULT '0', `col26` int(10) unsigned NOT NULL DEFAULT '0', `col27` int(10) NOT NULL, `col28` smallint(3) NOT NULL DEFAULT '-75', `col29` char(24) NOT NULL, `col30` tinyint(2) unsigned NOT NULL DEFAULT '0', `col31` tinyint(2) unsigned NOT NULL DEFAULT '0', `col32` tinyint(1) NOT NULL DEFAULT '0', `col33` tinyint(1) unsigned NOT NULL, `col34` tinyint(1) NOT NULL, `col35` double unsigned NOT NULL DEFAULT '0', `col36` double NOT NULL DEFAULT '0', `col37` tinyint(2) unsigned NOT NULL DEFAULT '0', `col38` int(10) unsigned NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=875492 DEFAULT CHARSET=latin1;
Of the 5 copies of this table I have restored, the first 3 are just like this example. The fourth backup seems to be OK, no corrupt data. The fifth is similar but the corruption starts at col16.

I'm not even sure what to search for for this issue. When I search for anything about corrupt mysql data, all I find is to do with being unable to even load the data.

If any more info is needed, please ask.

Any suggestions or help would be appreciated.

GHOST vulnerability (CVE-2015-0235) Percona response

Latest MySQL Performance Blog posts - January 29, 2015 - 6:40am

Cloud security company Qualys announced Tuesday the issues prevalent in glibc since version 2.2 introduced in 2000-11-10 (the complete Qualys announcement may be viewed here). The vulnerability, CVE-2015-0235, has been dubbed “GHOST.”

As the announcement from Qualys indicates, it is believed that MySQL and by extension Percona Server are not affected by this issue.

Percona is in the process of conducting our own review into the issue related to the Percona Server source code – more information will be released as soon as it is available.

In the interim the current advisory is to update your glibc packages for your distributions if they are in fact vulnerable. The C code from the Qualys announcement may aid in your diagnostics, section 4 of this document or via this gist. I also wrote a very quick python script to help identify processes which may be running libc that you can access here.

Compiling the above and executing it will yield an output indicating if your glibc version is believed to be vulnerable or not vulnerable.

Distribution Resource Resource Links
    1. RedHat BZ: https://bugzilla.redhat.com/show_bug.cgi?id=CVE-2015-0235
    2. RedHat EL5 Errata: https://rhn.redhat.com/errata/RHSA-2015-0090.html
    3. RedHat EL6 / 7 Errata: https://rhn.redhat.com/errata/RHSA-2015-0092.html
    4. Ubuntu USN: http://www.ubuntu.com/usn/usn-2485-1/ (affects 10.04 12.04)
    5. Debian security tracker: https://security-tracker.debian.org/tracker/CVE-2015-0235

Distributions which use musl-libc (http://www.musl-libc.org/) are not affected by this issue.

Acknowledgements

Qualys

Robert Barabas – Percona
Raghavendra Prabhu – Percona
Laura Byrnes – Percona

The post GHOST vulnerability (CVE-2015-0235) Percona response appeared first on MySQL Performance Blog.

Percona-Server-shared-51 breaks MySQL on CentOS 6.6

Lastest Forum Posts - January 29, 2015 - 4:33am
I am not sure if I should post this here or in the Xtrabackup forum - but the problem seems related to Percona-Server-Shared-51 package.

We have a set of minimal install CentOS 6.6 servers with MySQL 5.1.73-rel14 (the default for CentOS 6) installed.

We use xtrabackup from Percona, installed via yum from the persona-release.repo. This is the only non-CentOS repo we use.

This morning, I came to do a regular round of package updates:

>yum update
--> Running transaction check
---> Package Percona-Server-shared-51.x86_64 0:5.1.73-rel14.12.624.rhel6 will be obsoleting
---> Package dhclient.x86_64 12:4.1.1-43.P1.el6.centos will be updated
---> Package dhclient.x86_64 12:4.1.1-43.P1.el6.centos.1 will be an update
---> Package dhcp-common.x86_64 12:4.1.1-43.P1.el6.centos will be updated
---> Package dhcp-common.x86_64 12:4.1.1-43.P1.el6.centos.1 will be an update
---> Package kernel.x86_64 0:2.6.32-504.8.1.el6 will be installed
---> Package kernel-firmware.noarch 0:2.6.32-504.3.3.el6 will be updated
---> Package kernel-firmware.noarch 0:2.6.32-504.8.1.el6 will be an update
---> Package mysql-libs.x86_64 0:5.1.73-3.el6_5 will be obsoleted

I said 'y' to this on the first server. Yum went ahead, and promptly broke MySQL. Both client and server were broken. An attempt to use the client resulted in:

mysql: relocation error: mysql: symbol strmov, version libmysqlclient_16 not defined in file libmysqlclient.so.16 with link time reference

To fix this, I had to disable the Percona repo, and remove the Persona-Server-Shared-51 package. The latter also removed mysql and mysql-server packages on the way - and when I reinstalled those, they overwrote /etc/my.cnf (my fault for not anticipating this I guess). So that was a bit of fun.

Anyway - I have a few more of these slaves to update, and they're all suggesting they're going to 'obsolete' the Persona-Server-Shared-51 package also - so how do I get round this?

Also - this seems to suggest that by installing just xtrabackup from the repo, our core MySQL installation may have been modified?



Graceful shutdown of individual nodes.

Lastest Forum Posts - January 29, 2015 - 12:41am
Hi folks,

I have been wondering if anyone have been trying to make a "graceful shutdown" of nodes in a Percona xtradb cluster.

To explain my problem.
I have installed a cluser (Percona XtraDB Cluster 5.6) more or less identical to the example in the PXC documentation; three node cluster (let's call them node A, B,C) and a HA proxy server as a load balancer. HA Proxy is polling the three nodes on port 9200 (the clustercheck script also mentioned in the documentation). It works just smooth, but I miss a way to shut down nodes graceful one at a time. I know I can shut down the mysql service on a node, but this will interrupt the clients in their current actions. I know it shouldn't be a problem to the database and the data integrity, and this is mentioned as a graceful shutdown in the documentation (chapter 4.4), but I don't think this is the nicest way to shut down a node. From the servers point of view it is fine, but from the clients point of view it is not exactly graceful.

I figured if I stop the service on port 9200 on one node (I.E. Node A) HA proxy will see node A as down and will not try to make any new connections to this node. But all current MySQL connections to Node A will remain active. This allows the clients to finish whatever they are doing, disconnect and next time they connect the will connect to node B or C. I find this very useful for planned maintenance. When all the connections on node A is gone I can stop the MySQL service, do whatever I need to do and start MySQL and the clustercheck service, and move on to the next node.

But I'm facing little problem. A lot of the clients connecting to my cluster is services running continuously and they have a persistent connection to the database. So even if I stop the clustercheck service on a node, the connections from these services will never disconnect.

Since most of these services is inhouse developed, it is fairly simple to change the code regarding connections to the cluster.

My suggestion would be to instruct the developers to make all services disconnect and reconnect if a connection is open for more than one hour.

But before I do so, I would like to hear if some of you have experienced the same problem, and if you have found another workaround to this matter.



Best Regards,
Lenny Andersen
A happy PXC DBA! :-)

Multi-threaded replication with MySQL 5.6: Use GTIDs!

Latest MySQL Performance Blog posts - January 29, 2015 - 12:00am

MySQL 5.6 allows you to execute replicated events in parallel as long as data is split across several databases. This feature is named “Multi-Threaded Slave” (MTS) and it is easy to enable by setting slave_parallel_workers to a > 1 value. However if you decide to use MTS without GTIDs, you may run into annoying issues. Let’s look at two of them.

Skipping replication errors

When replication stops with an error, a frequent approach is to “ignore now and fix later.” This means you will run SET GLOBAL sql_slave_skip_counter=1 to be able to restart replication as quickly as possible and later use pt-table-checksum/pt-table-sync to resync data on the slave.

Then the day when I hit:

mysql> show slave status; [...] Last_SQL_Error: Worker 0 failed executing transaction '' at master log mysql-bin.000017, end_log_pos 1216451; Error 'Duplicate entry '1001' for key 'PRIMARY'' on query. Default database: 'db1'. Query: 'INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5320, '49123511666-22272014664-85739796464-62261637750-57593947547-00947134109-73607171516-11063345053-55659776318-82888369235', '11400300639-05875856680-20973514928-29618434959-69429576205')' Exec_Master_Log_Pos: 1005432

I tried to use the trick:

mysql> set global sql_slave_skip_counter=1; mysql> start slave;

But:

mysql> show slave status; [...] Last_SQL_Error: Worker 0 failed executing transaction '' at master log mysql-bin.000017, end_log_pos 1216451; Error 'Duplicate entry '1001' for key 'PRIMARY'' on query. Default database: 'db1'. Query: 'INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5320, '49123511666-22272014664-85739796464-62261637750-57593947547-00947134109-73607171516-11063345053-55659776318-82888369235', '11400300639-05875856680-20973514928-29618434959-69429576205')' Exec_Master_Log_Pos: 1005882

Note that the position reported with Exec_Master_Log_Pos has moved forward, but I still have my duplicate key error. What’s wrong?

The issue is that the positions reported by SHOW SLAVE STATUS are misleading when using MTS. Quoting the documentation about Exec_Master_Log_Pos:

When using a multi-threaded slave (by setting slave_parallel_workers to a nonzero value in MySQL 5.6.3 and later), the value in this column actually represents a “low-water” mark, before which no uncommitted transactions remain. Because the current implementation allows execution of transactions on different databases in a different order on the slave than on the master, this is not necessarily the position of the most recently executed transaction.

So the solution to my problem is first to make sure that there is no execution gap, and only then to skip the offending event. There is a specific statement for the first part:

mysql> start slave until sql_after_mts_gaps;

And now I can finally skip the error and restart replication:

mysql> set global sql_slave_skip_counter=1; mysql> start slave; mysql> show slave statusG Slave_IO_Running: Yes Slave_SQL_Running: Yes

The last thing to do is of course to resync the slave.

Backups

If you cannot trust the output of SHOW SLAVE STATUS to get the current binlog position, it means that taking a backup from a slave with parallel replication is tricky.

For instance, if you run mysqldump --dump-slave=2 to get the binlog position of the master, mysqldump will first run STOP SLAVE and then SHOW SLAVE STATUS. Is stopping the slave sufficient to avoid execution gaps? Actually, no.

The only option then seems to be: run STOP SLAVE followed by START SLAVE UNTIL SQL_AFTER_MTS_GAPS, followed by mysqldump while replication is stopped. Not very handy!

GTIDs to the rescue!

The solution for both issues is to use GTIDs.

They help when you want to skip an event because when using GTIDs, you must explicitly specify the transaction you will be skipping. It doesn’t matter whether there are execution holes.

They also help for backups because mysqldump takes the position from gtid_executed which is updated at each transaction commit (XtraBackup does that too).

Conclusion

If your application uses several databases and if you’re fighting with replication lag, MTS can be a great feature for you. But although GTIDs are not technically necessary, you’ll be exposed to tricky situations if you don’t use them.

Is everything rosy when using both GTIDs and MTS? Not exactly… But that will be the topic for a separate post!

By the way, if you are in the Brussels area this weekend, come see me and other great speakers at the MySQL and friends devroom at FOSDEM!

The post Multi-threaded replication with MySQL 5.6: Use GTIDs! appeared first on MySQL Performance Blog.

Backup and Restore : one database

Lastest Forum Posts - January 28, 2015 - 11:36pm
Hi everyone ,

I want to backup one database and restore it .
Here is my cmd for backup : innobackupex --user=root --password=*** --databases=myDB /data/backup/mysql/
But when I want to restore : innobackupex --ibbackup=xtrabackup --copy-back /data/backup/mysql/
there is this error : /var/lib/mysql Original data directory is not empty!

No way to restore only one database ?

Percona Disk Space displaying NaN

Lastest Forum Posts - January 28, 2015 - 6:53pm
Hi

I've downloaded the Linux template and have managed to get statistics working for all graphs except for disk usage.

I have tried setting the volume as "/dev/vda3" and "vda3" but to no avail. I checked the cacti log and no errors found.

Is there anything else I can look at?

SST transfer fails with xtrabackup but gets successfull with rsync

Lastest Forum Posts - January 28, 2015 - 2:25pm
Hi I am using Percona Xtradb cluster of 3 nodes. I have been seeing that one of the nodes get crashed or comes out of cluster and then it fails to sync with other nodes on starting mysql. I have been using xtrabackup as sst method. In my last scenario, it failed to start again but started fine when I used rsync as sst method. I see that this particular node had some network issue as below:

2015-01-23 04:17:37 6659 [Warning] WSREP: Gap in state sequence. Need state transfer.
2015-01-23 04:17:37 6659 [Note] WSREP: You have configured 'xtrabackup' state snapshot transfer method which cannot be performed on a running server. Wsrep provider won't be able to fall back to it if other means of state transfer are unavailable. In that case you will need to restart the server.

I am using percona-xtrabackup-2.2.6-5042.el5. Is this a bug in this version of xtrabackup?

Thanks,
Ashish

Pages

Subscribe to Percona aggregator
]]>