Solved

MySQL Server 5 with Galera on Debian

Posted on 2014-11-27
5
92 Views
Last Modified: 2016-06-16
i have installed Debian 7.7 with mysql-server 5.5.34-25.9 and galera 25.2.9. all working fine, my Cluster is running perfectly.

for load balancing i use ha_proxy.

now i have a few questions:

1) if i do a mysqldump on any node, why my website cant connect to the db-server? if i stop the mysqldump, all running fine... the same is if i do that whit mysqldumper

2) how i can backup my databases on the Cluster correctly without Connection lost to the db server? and how i can restore that?

3) how i can monitor my Cluster?

My my cnf Looks like this (from one node):
[mysqld]
user=mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
basedir         = /usr
datadir         = /var/lib/mysql

port=3306
log_error=error.log
#log_output=FILE
#relay_log=relay-bin
### INNODB OPTIONS
innodb_buffer_pool_size=11109M
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1

## You may want to tune the below depending on number of cores and disk sub
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_doublewrite=1
innodb_log_file_size=1024M
innodb_log_buffer_size=96M
innodb_buffer_pool_instances=8
innodb_log_files_in_group=2
innodb_thread_concurrency=64
#innodb_file_format=barracuda
innodb_flush_method = O_DIRECT
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
## avoid statistics update when doing e.g show tables
innodb_stats_on_metadata=0
engine_condition_pushdown=1
default_storage_engine=innodb

# CHARACTER SET
#collation_server = utf8_unicode_ci
#init_connect='SET NAMES utf8'
#character_set_server = utf8

# REPLICATION SPECIFIC
#server_id must be unique across all mysql servers participating in replication.
#server_id=SERVERID
#auto_increment_increment=2
#auto_increment_offset=SERVERID
binlog_format=ROW
#log_bin=binlog
#log_slave_updates=1
#relay_log=relay-bin
#expire_logs_days=7
# OTHER THINGS, BUFFERS ETC
key_buffer_size = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 512M
#sort_buffer_size = 256K
#read_buffer_size = 256K
#read_rnd_buffer_size = 512K
#myisam_sort_buffer_size = 8M
skip_name_resolve
memlock=0
sysdate_is_now=1
max_connections=500
thread_cache_size=512
query_cache_type = 0
query_cache_size = 0
table_open_cache=1024
lower_case_table_names=0
##
## WSREP options
##

# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib/galera/libgalera_smm.so

wsrep_node_address=10.18.0.11
# Provider specific configuration options
wsrep_provider_options="gcache.size=16384M"

# Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="db-cluster"

# Group communication system handle
wsrep_cluster_address=gcomm://10.18.0.11,10.18.0.12,10.18.0.13

# Human-readable node name (non-unique). Hostname by default.
wsrep_node_name=10.18.0.11

# Address for incoming client connections. Autodetect by default.
#wsrep_node_incoming_address=

# How many threads will process writesets from other nodes
wsrep_slave_threads=8

# DBUG options for wsrep provider
#wsrep_dbug_option

# Generate fake primary keys for non-PK tables (required for multi-master
# and parallel applying operation)
wsrep_certify_nonPK=1

# Location of the directory with data files. Needed for non-mysqldump
# state snapshot transfers. Defaults to mysql_real_data_home.
#wsrep_data_home_dir=

# Maximum number of rows in write set
wsrep_max_ws_rows=131072

# Maximum size of write set
wsrep_max_ws_size=1073741824

# to enable debug level logging, set this to 1
wsrep_debug=0

# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=0

# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1

# change auto_increment_increment and auto_increment_offset automatically
wsrep_auto_increment_control=1

# replicate myisam
wsrep_replicate_myisam=1
# retry autoinc insert, which failed for duplicate key error
wsrep_drupal_282555_workaround=0

# enable "strictly synchronous" semantics for read operations
wsrep_causal_reads=0

# Command to call when node status or cluster membership changes.
# Will be passed all or some of the following options:
# --status  - new status of this node
# --uuid    - UUID of the cluster
# --primary - whether the component is primary or not ("yes"/"no")
# --members - comma-separated list of members
# --index   - index of this node in the list
#wsrep_notify_cmd=

##
## WSREP State Transfer options
##

# State Snapshot Transfer method
wsrep_sst_method=rsync

# Address on THIS node to receive SST at. DON'T SET IT TO DONOR ADDRESS!!!
# (SST method dependent. Defaults to the first IP of the first interface)
#wsrep_sst_receive_address=

# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:
wsrep_sst_auth=*****:*****

# Desired SST donor name.
#wsrep_sst_donor=

# Protocol version to use
# wsrep_protocol_version=

[client]
port = 3306
socket=/var/run/mysqld/mysqld.sock
#default_character_set=utf8

[mysqldump]
max_allowed_packet = 512M
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

Open in new window


Thanks!

Many greets
0
Comment
Question by:M K
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 30

Accepted Solution

by:
Kerem ERSOY earned 500 total points
ID: 40470119
Hi,

First of all mysqldump will lock tables and this is why you can't keep access during backup. There are 2 alternatives to this:
- You can use another product to backup your database. Such as Percona Xtra backup. Here: PErcona XtraBackup (Open Source tool)
- Another option is create snapshot of your mysql volume and backup the binary files. To achieve this you might need to enable/configure LVM over Debian But this would not allow you to do granular Point-in-time recovery such as percona.

Cheers,
K.
0
 

Author Comment

by:M K
ID: 40470141
Hello Kerem,

with xtrabackup, how i can Setup this? which filebackups i get? and how i can restore this?

want backup (maybe SQL?) and restore? i found nothing for this..

thanks
0
 
LVL 30

Assisted Solution

by:Kerem ERSOY
Kerem ERSOY earned 500 total points
ID: 40471137
Hi MK,

They have binary versions for Debian and RHEL. You can find them here: Percona Xtrabackup Install

What do you mean which filebackups you get? Are you talking about the snapshot backup ? If so you'd need to put your /var/lib/mysql directory on an LVM and you need to create a snapshot before backing-up the database and then copy all files under the snapshot using tar. You would be backing up Databases by directory name.  To restore you need to restore individual directories from your backup set. This is a bit complex and I'd suggest you to use a tool like Percona.
It also support Galera.

The product comes with sevral PDF eBooks. You can check it for how to backup/restore the DB here:
MySQL eBOOKS

Cheers,
K.
0
 
LVL 30

Expert Comment

by:Kerem ERSOY
ID: 40486648
Any development here ?
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Linux boot cd to do hardware report on PC? 3 81
Amazon Glacier backup 6 44
MySQL Persistent Connections 10 36
Nee dhelp identifying the OS and how to access 7 45
Creating and Managing Databases with phpMyAdmin in cPanel.
This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question