MySQL cluster installation, load balancing, and backup recovery

MySQL Cluster installation learning notes

"One" server preparation (OS Linux, I use RHEL6.4):
A Management node 10.101.4.32
Two data nodes 10.101.4.33 10.101.4.34
Two query nodes 10.101.4.36 10.101.4.37

Download the latest version of MySQL (I'm using Mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar)

"Two" All node configurations:
1, hosts configuration, increase the IP of all nodes and host name corresponding

2. Adding MySQL users and groups, and add MySQL user to sudo group
Groupadd MySQL
Useradd mysql-g MySQL

3. Turn off the firewall
Chkconfig iptables off
Service Iptables Stop

4. Turn off SELinux
Vi/etc/sysconfig/selinux
Set selinux=disabled

5. Install MySQL
Upload the tar package to the/tmp directory
Cd/tmp
TAR-XVF Mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar
MV Mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64/usr/local/mysql
RM-RF Mysql-cluster-gpl-7.3.7-linux-glibc2.5-x86_64.tar
Chown-r Mysql:mysql/usr/local/mysql
Su-mysql
Cd/usr/local/mysql
scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data--user=mysql

6. Setting Environment variables
Vi/etc/profile
Export Mysql_home=/usr/local/mysql
Export path= $MYSQL _home/bin: $PATH
Source/etc/profile


"Three" Management node configuration:
1. Setting up the configuration file
Mkdir/var/lib/mysql-cluster
Vi/var/lib/mysql-cluster/config.ini
Paste the following content, the server IP information to your actual address, here is simply configured, detailed config parameter configuration self-Baidu

[TCP DEFAULT]
sendbuffermemory=256k
receivebuffermemory=64k
#PortNumber =2202

[NDBD DEFAULT]
noofreplicas=2
datamemory=2500m
indexmemory=500m

#MaxNoOfConcurrentTransactions =
maxnoofconcurrentoperations=327680
#MaxNoOfLocalOperations =3276800
maxnoofattributes=5120

Timebetweenlocalcheckpoints=6

filesystempath=/usr/local/mysql/
backupdatadir=/usr/local/mysql/data/
Backupdatabuffersize=2m
Backuplogbuffersize=2m
backupmemory=4m
backupwritesize=32k

[NDB_MGMD]
Nodeid=1
hostname=10.101.4.32
Logdestination=file:filename=/var/lib/mysql-cluster/mgmd.log
Datadir=/var/lib/mysql-cluster

[NDBD]
nodeid=11
hostname=10.101.4.33
Datadir=/usr/local/mysql/data
[NDBD]
Nodeid=12
hostname=10.101.4.34
Datadir=/usr/local/mysql/data

[MYSQLD DEFAULT]
Arbitrationrank=0
Arbitrationdelay=0

[MYSQLD]
Nodeid=21
hostname=10.101.4.36
[MYSQLD]
Nodeid=22
hostname=10.101.4.37
# NULL MYSQLD node for Backup-restore
[MYSQLD]


2. Start the Management node
Chown-r Mysql:mysql/var/lib/mysql-cluster
Su-mysql
/usr/local/mysql/bin/ndb_mgmd-f/var/lib/mysql-cluster/config.ini # If the configuration file changes, you need to add--reload at startup

3. Verify that the node is started successfully
Ps-ef|grep MGMD
Netstat-lnput | grep 1186


"Quad" Data node configuration:
1. Add configuration file
Vi/etc/my.cnf
Paste the content below

[Mysqld]
Ndbcluster
ndb-connectstring=10.101.4.32
Default_storage_engine=ndb
Default_tmp_storage_engine=ndb

[Mysql_cluster]
ndb-connectstring=10.101.4.32

2. Start the Data node
Su-mysql
/USR/LOCAL/MYSQL/BIN/NDBD--initial # Only the first boot needs to add--initial parameter

3. Verify that the node is started successfully
Ps-ef|grep NDBD
Netstat-lnput | grep 2202

"Five" Query node configuration:
1. Add configuration file
Vi/etc/my.cnf
Paste the content below

[Mysqld]
Basedir=/usr/local/mysql
Datadir=/usr/local/mysql/data
Socket=/var/lib/mysql/mysql.sock
Ndbcluster
ndb-connectstring=10.101.4.32
Default_storage_engine=ndb
Default_tmp_storage_engine=ndb

wait_timeout=86400
interactive_timeout=86400
max_allowed_packet=400m

max_open_files=8192
table_cache=2000

Skip-name-resolve
Lower_case_table_names=1
init_connect= ' SET autocommit=0 '

tmp_table_size=1024m
max_heap_table_size=1024m

[Mysql_cluster]
ndb-connectstring=10.101.4.32


2. Start MySQL
The first time to start the most useful
/usr/local/mysql/bin/mysqld_safe &

Add to boot from
Cp-rf/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld
Chown Mysql:mysql/etc/init.d/mysqld
Service mysqld Restart

3. Verify that the node is started successfully
Ps-ef|grep mysqld
Netstat-lnput | grep 3306

4. Add Mysql.sock soft chain under TMP
Cd/tmp
Ln-s/var/lib/mysql/mysql.sock Mysql.sock

5. Modify the MySQL root user password
Su-mysql
mysqladmin-u root password ' mysql '

"Six" Management node operations
1. See if the cluster is functioning properly
Su-mysql
Enter the console with NDB_MGM
Show
If the following message appears, then congratulations.

Connected to Management Server at:localhost:1186
Cluster Configuration
---------------------
[NDBD (NDB)] 2 node (s)
id=11 @10.101.4.33 (mysql-5.6.21 ndb-7.3.7, nodegroup:0, *)
Id=12 @10.101.4.34 (mysql-5.6.21 ndb-7.3.7, nodegroup:0)

[NDB_MGMD (MGM)] 1 node (s)
Id=1 @10.101.4.32 (mysql-5.6.21 ndb-7.3.7)

[Mysqld (API)] 2 node (s)
Id=21 @10.101.4.36 (mysql-5.6.21 ndb-7.3.7)
Id=22 @10.101.4.37 (mysql-5.6.21 ndb-7.3.7)

2. Stop cluster Management node and data node
Shutdown

3. View Data Node usage status
All report Memory

4. View the partition of table data storage
Ndb_desc-c 10.101.4.32-d erp2014 xt_product-p


"Seven" query node operations
1. Create a database
Create Database erp2014;
2. Increase database remote access
Because the user table is not a ndbcluster engine, you have to execute it on each node
GRANT all privileges the erp2014.* to ' admin ' @ ' localhost ' identified by ' admin ' with GRANT OPTION;
GRANT all privileges the erp2014.* to ' admin ' @ ' 10.101.50.30 ' identified by ' admin ' with GRANT OPTION;
GRANT all privileges the erp2014.* to ' admin ' @ ' 10.101.4.32 ' identified by ' admin ' with GRANT OPTION;
FLUSH privileges;

Replace * * to separate the database and the table for authorized access, replacement% fixed host, only allow the host connection, which is consistent with MySQL non-cluster version management

2. When you build the table, you must specify that the engine for the database table is NDB or ndbcluster, otherwise the synchronization fails.

3. Which great God can tell me how can stored procedures and functions created on a query node automatically synchronize to other query nodes???

"Eight. One" Load balancer setting--nginx
1, download the latest version of Nginx
Cd/usr/local
wget http://nginx.org/download/nginx-1.7.7.tar.gz

2, because Nginx is a seven-tier architecture, supported by the HTTP protocol, itself does not support the TCP protocol, so can not proxy MySQL and other load balancing (LVS and Haproxy), so need to download a TCP-supported module
wget Https://github.com/yaoweibin/nginx_tcp_proxy_module/archive/master.zip

3, decompression, patching
TAR-XVF nginx-1.7.7.tar.gz
Unzip Master.zip
MV nginx-1.7.7 Nginx
CD Nginx
Patch-p1 </usr/local/nginx_tcp_proxy_module-master/tcp.patch

4, compile, install
./configure--add-module=/usr/local/nginx_tcp_proxy_module-master/--prefix=/usr/local/nginx--conf-path=/usr/ Local/nginx/nginx.conf
The following error may be prompted during the
./configure:error:the HTTP Rewrite module requires the PCRE library.
./CONFIGURE:ERROR:SSL modules require the OpenSSL library.
then install it.
sudo apt-get install pcre-devel openssl-devel
Make
Make install

5, modify the configuration file, because the beginning to specify the conf-path=/usr/local/nginx/, so also to modify the directory under the configuration file, not the/usr/local/nginx/conf/directory, In addition, the server in the listening port if configured to 3306, will cause nginx can not boot the port, a different one.
VI nginx.conf
Add the following:
TCP {
Timeout 1d;
Proxy_read_timeout 10d;
Proxy_send_timeout 10d;
Proxy_connect_timeout 30;

Upstream MySQL {
Server 10.101.4.36:3306 weight=1;
Server 10.101.4.37:3306 weight=1;
Check interval=60000 rise=2 fall=5 timeout=10000 type=tcp;
}

server {
server_name 10.101.4.32;
Listen 10.101.4.32:13306;
Proxy_pass MySQL;
So_keepalive on;
Tcp_nodelay on;
}
}

6. Start Nginx
Cd/usr/local/nginx/xbin
./nginx
lsof | grep 13306
If you find that port 13306 is already in the listening state, the configuration is successful.

"Eight. Two" Load balancer settings--haproxy
1, installation Haproxy
Upload haproxy-1.5.3.tar.gz to/usr/local/, I am on the Mgmt (10.101.4.32) node, the formal deployment requires a separate server
Cd/usr/local
TAR-XVF haproxy-1.5.3.tar.gz
MV haproxy-1.5.3 Haproxy
Cd/usr/local/haproxy
Make Target=linux26 Prefix=/usr/local/haproxy
Make install Prefix=/usr/local/haproxy

2. Setting up the configuration file
mkdir haproxy/etc
VI haproxy.cfg
Paste the following content:

# This config needs haproxy-1.1.28 or haproxy-1.2.1

Global
Log 127.0.0.1 local0 Info
Log 127.0.0.1 Local1 Notice
Maxconn 40960
Chroot/usr/local/haproxy
UID Root
GID Root
Daemon
Pidfile/usr/local/haproxy/haproxy.pid

Defaults
Log Global
Mode http
Option Dontlognull
Option Redispatch
Retries 3
Balance Roundrobin
#balance Source
#balance Leastconn

Timeout Connect 5000
Timeout client 50000
Timeout server 50000
Timeout Check 2000

Listen MySQL
Mode TCP
Bind 0.0.0.0:23306
Balance Roundrobin
#option Mysql-check
Server mysql36 10.101.4.36:3306 weight 1 check inter 1s rise 3 fall 3
Server mysql37 10.101.4.37:3306 weight 1 check inter 1s rise 3 fall 3

Listen stats
Bind 0.0.0.0:8888
Mode http
Stats enable
Stats Uri/admin
Stats Refresh 5s
Stats Realm haproxy\ Statistics
Stats Auth Admin:admin


3. Start Haproxy
/usr/local/haproxy/sbin/haproxy-f/usr/local/haproxy/etc/haproxy.cfg
Access Http://10.101.4.32:8888/admin to see if each query node is properly accessible
The application needs to connect to the IP 10.101.4.32, Port 23306 (can be changed in the configuration above)

"Nine" Add data node online
1, modify the Management node's Config.ini file, add two data nodes
[NDBD]
Nodeid=13
hostname=10.101.4.36
Datadir=/usr/local/mysql/data
[NDBD]
Nodeid=14
hostname=10.101.4.37
Datadir=/usr/local/mysql/data

2, prepare two new machines (this example uses two query nodes part-time), the implementation of step "two"
Copy a my.cnf file on an existing data node
Sequentially restarts all nodes in the original cluster, managing nodes--data nodes--querying nodes
Start a new data node NDBD--initial

3. Increase Node Group
Go to the Management node console
Create Nodegroup 13,14

4. Data re-sharding
ALTER ONLINE TABLE erp2014.cwpos_detail REORGANIZE PARTITION;

"Ten" Backup recovery
1. Perform a backup on the management node
Su-mysql
Ndb_mgm
Start Backup
After execution, a backup folder is generated in the/usr/local/mysql/data/directory with a BACKUP-1 folder, where 1 is the number

2, after some delete data or delete the table operation, close the cluster
Shutdown

3. Restart the cluster sequentially
Management node:
Su-mysql
/usr/local/mysql/bin/ndb_mgmd-f/var/lib/mysql-cluster/config.ini
Data node:
Su-mysql
NDBD (Note If you need to restore metadata information for tables, views, stored procedures, and so on, you need to start with ndbd--initial)

4. Recovering data
Because the data is fragmented on each data node, a recovery instruction is executed on each data node (parameter description:-c Specifies the id,-b of the specified data node ip,-n the specified backup set number,-m specifies that the metadata needs to be restored,-r specifies that the data record needs to be recovered-- Backup_path specify the path to which the backup set resides)
Data node 11:
Su-mysql
Ndb_restore-c10.101.50.44-n11-b1-m-R--backup_path=/usr/local/mysql/data/backup/backup-1/

Data Node 12:
Su-mysql
Ndb_restore-c10.101.50.44-n12-b1-r--backup_path=/usr/local/mysql/data/backup/backup-1/

Data Node 13:
Su-mysql
Ndb_restore-c10.101.50.44-n13-b1-r--backup_path=/usr/local/mysql/data/backup/backup-1/

Data Node 14:
Su-mysql
Ndb_restore-c10.101.50.44-n14-b1-r--backup_path=/usr/local/mysql/data/backup/backup-1/

5. View data
Log in to the query node, found that the backup data are back, but this can only restore the data to the point of backup, if after the backup set, I mistakenly deleted some data, I do not know how to recover, this left to the back of the study

"11" uses Binlog to achieve flash back function
1, after the tenth chapter of the doubt, find the next information, found that you can use the Binlog function of MySQL to achieve the operation of misoperation data recovery

2, first in the query node on the my.cnf file to add the parameter start Binlog
Log_bin=/usr/local/mysql/data/binlog/mysql-bin.log

3, after restarting the database, you will find that there are mysql-bin.000001 binaries in the directory (each time the database is restarted or the flush logs will increase the number of files), and then the data table for the addition of the DML operations such as the steps will be recorded in the north.

4. Can then be managed by MySQL's own mysqlbinlog command
Su-mysql
Display log information for a specified interval directly in the terminal
mysqlbinlog-d/usr/local/mysql/data/binlog/mysql-bin.000001--start-position=4--stop-position=1862--database= Aabc
Writes log information for the specified interval to the SQL file
mysqlbinlog-d/usr/local/mysql/data/binlog/mysql-bin.000001--start-position=4--stop-position=1862--database= AABC > Oper.sql
The log information for the specified interval is executed again in the database (the-d parameter is Disable-log-bin, which is intended to not log logs for this re-executed instruction)
mysqlbinlog-d/usr/local/mysql/data/binlog/mysql-bin.000001--start-position=4--stop-position=1862--database= AABC | Mysql-uroot-p

Skills:
1) A regular expression can be used to specify the log file, such as Mysql-bin. [0-9]*
2) The specified database can be restored by adding the parameter--one-database dbname after mysql-uroot-ppwd

5, the relevant instructions of the log
Su-mysql
Mysql-uroot-p

Show master status; --View the current database log written in the first few log files, and the next position number
Show master logs;---View binary file list and file size, also available show binary logs
Show Binlog events in ' mysql-bin.000001 '; --View the event_type in the log and the starting position number, and filter the number of rows by using the limit parameter
flush logs;--Start a new log file, typically used after a database backup
Reset Master; --Clear all log files (can also be set expire_logs_days parameters to achieve)
Purge master logs to ' mysql-bin.000008 '; --delete the binary log file before mysql-bin.000008
Purge master logs before ' 2014-12-10 04:07:00 '--delete the log before the date

MySQL cluster installation, load balancing, and backup recovery