Sunday, 30 September 2018

MySQL Health Check Script


MySQL Health Check script is a simple scripts that provide MySQL and OS metrics, connection and replication status.

Idea is to automate daily DBA routine checks and see what’s going on on MySQL servers without having to log in and enter same commands manually day-by-day. 

This script also allows you to collect most of the available MySQL performance counters, OS & MySQL configuration and database schema details 


Usage and download

This tool can run from shell or send a report via email (good for cron). Please feel free to download this tool from GitHub

Sample Report :





Read More

Sunday, 25 February 2018

Creating and Restoring Database Backups With MySQL Enterprise Backup

MySQL Enterprise Backup is a backup utility for MySQL servers. It is a multi-platform, high-performance tool, offering rich features like “hot” (online) backup, incremental and differential backup, selective backup and restore, support for direct cloud storage backup, backup encryption and compression, and many other valuable features.

All MySQL Enterprise Backup functions are executed with the mysqlbackup client. It is used for performing different types of backup and restore operations, as well as other related tasks like backup compression, decompression, validation, and so on.

A hot backup is a backup performed while the database is running. This type of backups does not block normal database operations. It captures even changes that occur while the backup is happening. Comparing to the other backup types, it causes the least disruption to the database server, and it is a desirable backup option when you want to avoid taking your application, web site, or web service offline. 

A warm backup is a backup performed with the database put under a read-only state. This type of backups blocks any write operations to the tables during the backup process, but still allow tables to be read.

When connected to a running MySQL server, MySQL Enterprise Backup backs up all MyISAM and other non-InnoDB tables using the warm backup technique after all InnoDB tables have already been backed up with the hot backup method. 

Kinds of backups :
  • A full backup includes the complete data from the database 
  • A differential backup includes all changes to the data since the last full backup. It is faster than a full backup, saves storage space on the database server, and saves on network traffic when the backup is being transferred to a different server. 
  • An Incremental backup includes all changes to the data since the last backup. It offers similar advantages over a full backup as a differential backup does, and often to a even greater extent by further decreasing the backup size. But it might also require more preparations on a longer series of backups, before a restore can be performed.
Features:
  • High Performance – Save time with faster backup and recovery
  • Incremental Backup – Backup only data that has changed since the last backup
  • Partial Backup – Target particular tables or tablespaces
  • Compression – Cut costs by reducing storage requirements up to 90%
  • Backup to Tape – Stream backup to tape or other media management solutions
  • Fast Recovery – Get servers back online and create replicated servers
  • Point-in-Time Recovery (PITR) – Recover to a specific transaction
  • Partial restore – Recover targeted tables or tablespaces
  • Restore to a separate location – Rapidly create clones for fast replication setup
Types of files contained in a backup :


Files backed-up for innodb

The InnoDB-related data files that are backed up include the ibdata* files (which represent the system tablespace and possibly the data for some user tables), any .ibd files (which contains data from user tables created with the file-per-table setting enabled), and the data extracted from the ib_logfile* files (the redo log information representing changes that occur while the backup is running), which is stored in a new backup file ibbackup_logfile.

If you use the compressed backup feature, the .ibd files are renamed in their compressed form to .ibz files.

The files, as they are originally copied, form a raw backup that requires further processing before it is ready to be restored. You then run the apply step (either as part of a copy-back-and-apply-log command or a backup-and-apply-log command, or as a separate apply-log command), which updates the backup files based on the changes recorded in the ibbackup_logfile file, producing a prepared backup. 

Files Backed up for Data Stored with MyISAM 

mysqlbackup also backs up the .MYD files, .MYI files, and the .frm files associated with the MyISAM tables. 

MyISAM tables and these other types of files cannot be backed up in the same non-blocking way as InnoDB tables can be. They can be backed up using the warm backup technique

Files Generated by mysqlbackup :

  • backup-my.cnf: Records the crucial configuration parameters that apply to the backup. These parameter values are used during a restore operation, so that the original values are used regardless of changes to your server's my.cnf file since the backup.
  • meta/backup_create.xml: Lists the command line arguments and environment in which the backup was created.
  • meta/backup_content.xml: Essential metadata for the files and database definitions of the backup data.
  • server-my.cnf: Contains values of the backed-up server's global variables that are set to non-default values.
  • server-all.cnf: Contains values of all the global variables of the backed-up server.
  • *.bkt: Transfer file created for an encrypted InnoDB table during backup. It contains the reencrypted tablespace key and other information related to the encryption. See Chapter 6, Working with Encrypted InnoDB Tables for detail.
MySQL Enterprise Backup now supports optimistic incremental backup, in which mysqlbackup scans only those InnoDB data files that have been modified since the last backup for changed pages and then saves them into the incremental backup. It potentially makes incremental backups faster. 

Apply-log operations can now be performed with multiple worker threads in parallel, which can improve performance for the operations. The number of threads to be used can be specified with the --process-threads.

Information Needed to Back Up a Database
  • Path to MySQL configuration file : Default system locations, hardcoded application default locations, or from the --defaults-file option in the mysqld startup script.
  • MySQL port : MySQL configuration file or mysqld startup script.
  • Path to MySQL data directory : MySQL configuration file or mysqld startup script.
  • ID and password of privileged MySQL user
  • Size of InnoDB redo log files :Calculated from the values of the innodb_log_file_size and innodb_log_files_in_group configuration variables. Use the technique explained for the --incremental-with-redo-log-only option.
The minimum privileges for the MySQL user with which mysqlbackup connects to the server are:
  • RELOAD on all databases and tables.
  • CREATE, INSERT, DROP, and UPDATE on the tables mysql.backup_progress and mysql.backup_history, and also SELECT on mysql.backup_history.
  • SUPER, to enable and disable logging, and to optimize locking in order to minimize disruption to database processing.
  • REPLICATION CLIENT, to retrieve the binary log position, which is stored with the backup.
  • PROCESS, to process DDL statements with the ALGORITHM = INPLACE clause.
  • ALTER privilege for adding new columns to the backup history table
CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'new-password';
GRANT RELOAD ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'mysqlbackup'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'mysqlbackup'@'localhost';
GRANT SUPER ON *.* TO 'mysqlbackup'@'localhost';
GRANT PROCESS ON *.* TO 'mysqlbackup'@'localhost';
GRANT ALTER ON mysql.backup_history TO 'mysqlbackup'@'localhost';

For working on transportable table space use :

GRANT LOCK TABLES, SELECT, CREATE, DROP, FILE ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_sbt_history TO 'mysqlbackup'@'localhost';

In order to be able to use MySQL Enterprise Backup to perform a backup of a MySQL Server instance, the following MySQL options must be specified in the configuration file of the server instance, under the [mysqld] section 
 
datadir=...
innodb_data_home_dir=...
innodb_data_file_path=...
innodb_log_group_home_dir=...
innodb_log_files_in_group=...
innodb_log_file_size=...

Creating a Full backup :

A full backup can be created using following command 

$ /usr/local/meb/bin/mysqlbackup --user=root --password --backup-dir=/mnt/backup/hotbackups backup-and-apply-log

Output :

MySQL Enterprise Backup version 3.12.0 [2014/11/12] 
Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 /usr/local/meb/bin/mysqlbackup --user=root --password 
        --backup-dir=/mnt/backup/hotbackups backup-and-apply-log 

Enter password: 
 mysqlbackup: INFO: MySQL server version is '5.6.9-rc-log'.
 mysqlbackup: INFO: Got some server configuration information from running server.

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup-and-apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir = /usr/local/mysql/data/
  innodb_data_home_dir = /usr/local/mysql/data
  innodb_data_file_path = ibdata1:40M:autoextend
  innodb_log_group_home_dir = /usr/local/mysql/data
  innodb_log_files_in_group = 2
  innodb_log_file_size = 5242880
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb
  innodb_undo_directory = /usr/local/mysql/data/
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = /mnt/backup/hotbackups/datadir
  innodb_data_home_dir = /mnt/backup/hotbackups/datadir
  innodb_data_file_path = ibdata1:40M:autoextend
  innodb_log_group_home_dir = /mnt/backup/hotbackups/datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 5242880
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb
  innodb_undo_directory = /mnt/backup/hotbackups/datadir
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

 mysqlbackup: INFO: Unique generated backup id for this is 13742482113579320

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
130719 11:36:53 mysqlbackup: INFO: Full Backup operation starts with following threads
  1 read-threads    6 process-threads    1 write-threads
130719 11:36:53 mysqlbackup: INFO: System tablespace file format is Antelope.
130719 11:36:53 mysqlbackup: INFO: Starting to copy all innodb files...
130719 11:36:53 mysqlbackup: INFO: Copying /usr/local/mysql/data/ibdata1 (Antelope file format).
130719 11:36:53 mysqlbackup: INFO: Found checkpoint at lsn 135380756.
130719 11:36:53 mysqlbackup: INFO: Starting log scan from lsn 135380480.
130719 11:36:53 mysqlbackup: INFO: Copying log...
130719 11:36:54 mysqlbackup: INFO: Log copied, lsn 135380756.

<span style="color: blue;"><i>(I have truncated some of the database and table output to save space)</i></span>
.....
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/innodb_index_stats.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/innodb_table_stats.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_master_info.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_relay_log_info.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_worker_info.ibd (Antelope file format).
.....
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/testcert/t1.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/testcert/t3.ibd (Antelope file format).
.....
130719 11:36:57 mysqlbackup: INFO: Copying /usr/local/mysql/data/watchdb/watches.ibd (Antelope file format).
.....
130719 11:36:57 mysqlbackup: INFO: Completing the copy of innodb files.
130719 11:36:58 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
130719 11:36:58 mysqlbackup: INFO: Starting to lock all the tables...
130719 11:36:58 mysqlbackup: INFO: All tables are locked and flushed to disk
130719 11:36:58 mysqlbackup: INFO: Opening backup source directory '/usr/local/mysql/data/'
130719 11:36:58 mysqlbackup: INFO: Starting to backup all non-innodb files in 
 subdirectories of '/usr/local/mysql/data/'
.....
130719 11:36:58 mysqlbackup: INFO: Copying the database directory 'comicbookdb'
.....
130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'mysql'
130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'performance_schema'
.....
130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'test'
.....
130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'watchdb'
130719 11:36:59 mysqlbackup: INFO: Completing the copy of all non-innodb files.
130719 11:37:00 mysqlbackup: INFO: A copied database page was modified at 135380756.
          (This is the highest lsn found on page)
          Scanned log up to lsn 135384397.
          Was able to parse the log up to lsn 135384397.
          Maximum page number for a log record 375
130719 11:37:00 mysqlbackup: INFO: All tables unlocked
130719 11:37:00 mysqlbackup: INFO: All MySQL tables were locked for 1.589 seconds.
130719 11:37:00 mysqlbackup: INFO: Full Backup operation completed successfully.
130719 11:37:00 mysqlbackup: INFO: Backup created in directory '/mnt/backup/hotbackups'
130719 11:37:00 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000013, position 85573

-------------------------------------------------------------
   Parameters Summary         
-------------------------------------------------------------
   Start LSN                  : 135380480
   End LSN                    : 135384397
-------------------------------------------------------------

 mysqlbackup: INFO: Creating 14 buffers each of size 65536.
130719 11:37:00 mysqlbackup: INFO: Apply-log operation starts with following threads
  1 read-threads    1 process-threads
130719 11:37:00 mysqlbackup: INFO: ibbackup_logfile's creation parameters:
          start lsn 135380480, end lsn 135384397,
          start checkpoint 135380756.
 mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 .... 99 Setting log file size to 5242880
Setting log file size to 5242880
130719 11:37:00 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
          lsn 135384397.
 mysqlbackup: INFO: Last MySQL binlog file position 0 85573, file name mysql-bin.000013
130719 11:37:00 mysqlbackup: INFO: The first data file is '/mnt/backup/hotbackups/datadir/ibdata1'
          and the new created log files are at '/mnt/backup/hotbackups/datadir'
130719 11:37:01 mysqlbackup: INFO: Apply-log operation completed successfully.
130719 11:37:01 mysqlbackup: INFO: Full backup prepared for recovery successfully.

mysqlbackup completed OK!

Now,  we can take a look at the backup file that was created:

root@prodserver011: $ pwd
/mnt/backup/hotbackups
root@prodserver011: $ ls -l
total 8
-rw-r--r--   1 root  staff  351 Jul 19 11:36 backup-my.cnf
drwx------  21 root  staff  714 Jul 19 11:37 datadir
drwx------   6 root  staff  204 Jul 19 11:37 meta
$ ls -l datadir
total 102416
drwx------   5 root  staff       170 Jul 19 11:36 comicbookdb
-rw-r-----   1 root  staff   5242880 Jul 19 11:37 ib_logfile0
-rw-r-----   1 root  staff   5242880 Jul 19 11:37 ib_logfile1
-rw-r--r--   1 root  staff      4608 Jul 19 11:37 ibbackup_logfile
-rw-r--r--   1 root  staff  41943040 Jul 19 11:37 ibdata1
drwx------  88 root  staff      2992 Jul 19 11:36 mysql
drwx------  55 root  staff      1870 Jul 19 11:36 performance_schema
drwx------   3 root  staff       102 Jul 19 11:36 test
drwx------  30 root  staff      1020 Jul 19 11:36 testcert
drwx------  19 root  staff       646 Jul 19 11:36 watchdb
 
root@prodserver011: $ ls -l meta
total 216
-rw-r--r--  1 root  staff  90786 Jul 19 11:37 backup_content.xml
-rw-r--r--  1 root  staff   5746 Jul 19 11:36 backup_create.xml
-rw-r--r--  1 root  staff    265 Jul 19 11:37 backup_gtid_executed.sql
-rw-r--r--  1 root  staff    321 Jul 19 11:37 backup_variables.txt

As you can see, the backup was created in /mnt/backup/hotbackups. If I wanted to have a unique folder for this backup, I can use the –with-timestamp.

The –with-timestamp option places the backup in a subdirectory created under the directory you specified above. The name of the backup subdirectory is formed from the date and the clock time of the backup run.

We can run the same backup command again, but with the –with-timestamp option:

$ /usr/local/meb/bin/mysqlbackup --user=root --password --backup-dir=/mnt/backup/hotbackups backup-and-apply-log --with-timestamp

$ pwd
/mnt/backup/hotbackups
root@prodserver011: $ ls -l
total 0
drwx------  5 root  staff  170 Jul 19 11:49 2015-05-19_11-49-48
$ ls -l 2015-05-19_11-49-48
total 8
-rw-r--r--   1 root  staff  371 Jul 19 11:49 backup-my.cnf
drwx------  21 root  staff  714 Jul 19 11:49 datadir
drwx------   6 root  staff  204 Jul 19 11:49 meta

Note: If you don’t use the backup-and-apply-log option you will need to read this: Immediately after the backup job completes, the backup files might not be in a consistent state, because data could be inserted, updated, or deleted while the backup is running. These initial backup files are known as the raw backup.

During the backup, mysqlbackup copies the accumulated InnoDB log to a file called ibbackup_logfile. This log file is used to “roll forward” the backed-up data files, so that every page in the data files corresponds to the same log sequence number of the InnoDB log. This phase also creates new ib_logfiles that correspond to the data files.

Backing Up an Entire MySQL Instance using backup-to-image

$ ./mysqlbackup --user=root --password  --host=127.0.0.1 --backup-image=/mnt/backup/backups/my.mbi \
  --backup-dir=/mnt/backup/backup-tmp backup-to-image

Verifying a Backup

You can check the integrity of your backup using the validate command.

$ ./mysqlbackup --backup-image=/mnt/backup/backups/my.mbi validate

MySQL Enterprise Backup version 4.1.0 Linux-2.6.39-400.215.10.el5uek-x86_64 [2017/02/13] 
Copyright (c) 2003, 2017, Oracle and/or its affiliates. All Rights Reserved.

170214 17:46:09 MAIN    INFO: A thread created with Id '139996701841216' 
170214 17:46:09 MAIN    INFO: Starting with following command line ...
 ./mysqlbackup --backup-image=/mnt/backup/backups/my.mbi validate 

170214 17:46:09 MAIN    INFO: 
IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'validate' run mysqlbackup
           prints "mysqlbackup completed OK!".

170214 17:46:09 MAIN    INFO: Backup Image MEB version string: 4.1.0 [2017/02/13]
170214 17:46:09 MAIN    INFO: MySQL server version is '5.7.17'
170214 17:46:09 MAIN    INFO: Creating 14 buffers each of size 16777216.
170214 17:46:09 MAIN    INFO: Validate operation starts with following threads
  1 read-threads    6 process-threads
170214 17:46:09 MAIN    INFO: Validating image ... /mnt/backup/backups/my.mbi
170214 17:46:09 PCR1    INFO: A thread created with Id '139996431771392' 
170214 17:46:09 PCR2    INFO: A thread created with Id '139996423378688' 
170214 17:46:09 PCR3    INFO: A thread created with Id '139996414985984' 
170214 17:46:09 PCR4    INFO: A thread created with Id '139996406593280' 
170214 17:46:09 PCR5    INFO: A thread created with Id '139996263982848' 
170214 17:46:09 PCR6    INFO: A thread created with Id '139996398200576' 
170214 17:46:09 RDR1    INFO: A thread created with Id '139996440164096' 
170214 17:46:09 PCR1    INFO: Validate: [Dir]: meta
170214 17:46:10 PCR4    INFO: Validate: [Dir]: datadir/mysql
170214 17:46:10 PCR2    INFO: Validate: [Dir]: datadir/performance_schema
170214 17:46:10 PCR2    INFO: Validate: [Dir]: datadir/pets
.
.
.
170214 17:46:10 MAIN    INFO: datadir/mysql/time_zone_leap_second.ibd validated.
170214 17:46:10 MAIN    INFO: datadir/mysql/time_zone_name.ibd validated.
170214 17:46:10 MAIN    INFO: datadir/mysql/time_zone_transition.ibd validated.
170214 17:46:10 MAIN    INFO: datadir/mysql/time_zone_transition_type.ibd validated.
170214 17:46:10 MAIN    INFO: datadir/pets/cats.ibd validated.
170214 17:46:10 MAIN    INFO: datadir/sys/sys_config.ibd validated.
170214 17:46:10 MAIN    INFO: Validate operation completed successfully.
170214 17:46:10 MAIN    INFO: Backup Image validation successful.
170214 17:46:10 MAIN    INFO: Source Image Path = /mnt/backup/backups/my.mbi

mysqlbackup completed OK!

Restoring a Database

To restore a MySQL instance from a backup to a database server:
  • Shut down the database server.
  • Delete all files inside the server's data directory. If the data directory is not empty, you have to use the --force option to allow overwriting of the old data, or mysqlbackup will error out.
  • Use, for example, the copy-back-and-apply-log command, which converts the raw backup into a prepared backup by updating it to a consistent state, and then copies the tables, indexes, metadata, and any other required files onto a target server.
  • When the following InnoDB settings for the backup are different from those on the target server, it is important to supply the values for the backup to mysqlbackup during restore and to mysqld when you start the restored server (otherwise, the restore might fail, and you might have problem starting the restored server) :
          innodb_data_file_path
          innodb_log_file_size
          innodb_log_files_in_group
          innodb_page_size
          innodb_checksum_algorithm
          innodb_undo_tablespaces
          innodb_buffer_pool_filename

If you are not sure about those settings for your backup, they are stored in the backup-my.cnf file during the backup—you can find the file either in the temporary directory you specified with --backup-dir when you created the single-image backup, or in a backup directory you can create by unpacking the backup image using the extract command.

$ ./mysqlbackup  --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql  \
   --backup-image=/mnt/backup/backups/my.mbi --backup-dir=/mnt/backup/restore-tmp copy-back-and-apply-log

MySQL Enterprise Backup version 4.1.0 Linux-2.6.39-400.215.10.el5uek-x86_64 [2017/02/13] 
Copyright (c) 2003, 2017, Oracle and/or its affiliates. All Rights Reserved.

170214 22:11:38 MAIN    INFO: A thread created with Id '140027243398976' 
170214 22:11:38 MAIN    INFO: Starting with following command line ...
 ./mysqlbackup --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql 
        --backup-image=/mnt/backup/backups/my.mbi 
        --backup-dir=/mnt/backup/restore-tmp copy-back-and-apply-log 

170214 22:11:38 MAIN    INFO: 
IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'copy-back-and-apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".

170214 22:11:38 MAIN    INFO: Backup Image MEB version string: 4.1.0 [2017/02/13]
170214 22:11:38 MAIN    INFO: MySQL server version is '5.7.17'
170214 22:11:38 MAIN WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In 
that case you need to add 'innodb_data_file_path=ibdata1:12M:autoextend' to the target server configuration.
170214 22:11:38 MAIN WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. 
In that case you need to add 'innodb_log_files_in_group=2' to the target server configuration.
170214 22:11:38 MAIN WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In th
at case you need to add 'innodb_log_file_size=50331648' to the target server configuration.
170214 22:11:38 MAIN    INFO: Keyring service not initialized. MEB cannot validate the encryption keys.
170214 22:11:38 MAIN    INFO: Valid keyring to be specified on server start to access the encrypted tables.
170214 22:11:38 MAIN    INFO: MEB logfile created at /mnt/backup/restore-tmp/meta/MEB_2017-02-14.22-11-38_copy_back_img_to_datadir.log

.
.
.
170214 22:11:40 PCR5    INFO: Copying database directory: sys
170214 22:11:40 MAIN    INFO: Total files as specified in image: 297
170214 22:11:40 MAIN    INFO: MySQL server version is '5.7.17'
170214 22:11:40 MAIN    INFO: MySQL server compile os version is 'Linux'
170214 22:11:40 MAIN    INFO: Writing config file for server '5.7.17'.
170214 22:11:40 MAIN    INFO: Creating server config files server-my.cnf and server-all.cnf in /var/lib/mysql

[Some lines of output not shown]

170214 22:11:40 MAIN    INFO: Copy-back operation completed successfully.
170214 22:11:40 MAIN    INFO: Source Image Path = /mnt/backup/backups/my.mbi


170214 22:11:40 MAIN    INFO: MySQL server version is '5.7.17'
170214 22:11:40 MAIN    INFO: Restoring ...5.7.17 version
170214 22:11:40 MAIN    INFO: Creating 14 buffers each of size 65536.
170214 22:11:40 MAIN    INFO: Apply-log operation starts with following threads
  1 read-threads    1 process-threads    6 apply-threads
170214 22:11:40 MAIN    INFO: Using up to 100 MB of memory.
170214 22:11:40 MAIN    INFO: ibbackup_logfile's creation parameters:
          start lsn 2543104, end lsn 2543132,
          start checkpoint 2543123.
170214 22:11:40 ALW1    INFO: A thread created with Id '140026922972928' 
170214 22:11:40 ALW6    INFO: A thread created with Id '140027204466432' 
170214 22:11:40 ALW5    INFO: A thread created with Id '140027212859136' 
170214 22:11:40 ALW4    INFO: A thread created with Id '140026948151040' 
170214 22:11:40 ALW3    INFO: A thread created with Id '140026939758336' 
170214 22:11:40 ALW2    INFO: A thread created with Id '140026931365632' 
170214 22:11:40 RDR1    INFO: A thread created with Id '140027196073728' 
170214 22:11:40 PCR1    INFO: A thread created with Id '140027187681024' 
170214 22:11:40 PCR1    INFO: InnoDB: Doing recovery: scanned up to log sequence number 2543132.
170214 22:11:40 PCR1    INFO: InnoDB: Starting an apply batch of log records to the database...
.
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 
 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 
 94 95 96 97 98 99 
170214 22:11:40 PCR1    INFO: InnoDB: Setting log file size to 50331648.
170214 22:11:45 PCR1    INFO: InnoDB: Setting log file size to 50331648.
170214 22:11:50 PCR1    INFO: We were able to parse ibbackup_logfile up to
          lsn 2543132.
170214 22:11:50 PCR1    INFO: The first data file is '/var/lib/mysql/ibdata1'
                              and the new created log files are at '/var/lib/mysql'
170214 22:11:50 MAIN    INFO: MySQL server version is '5.7.17'
170214 22:11:50 MAIN    INFO: Restoring ...5.7.17 version
170214 22:11:50 MAIN    INFO: Apply-log operation completed successfully.
170214 22:11:50 MAIN    INFO: Full Backup has been restored successfully.

mysqlbackup completed OK! with 3 warnings

Depending on how you are going to start the restored server, you might need to adjust the ownership of the restored data directory. 

$ chown -R mysql:mysql /path/to/datadir

Making a Single-File Backup

This command creates a single backup image on the given absolute path. It still requires --backup-dir, which is used to hold temporary output, status, and metadata files.

$ mysqlbackup --backup-image=/backups/sales.mbi --backup-dir=/backup-tmp backup-to-image

Single-File Backup to Relative Path

$ mysqlbackup --backup-image=sales.mbi --backup-dir=/backups backup-to-image

Extract Existing Image to Backup Directory

$ mysqlbackup --backup-dir=/var/backup --backup-image=/backup/my.mbi image-to-backup-dir

List Single-File Backup Contents

$ mysqlbackup --backup-image=/backup/my.mbi list-image

Extract Single-File Backup into Current Directory

$ mysqlbackup --backup-image=/var/my.mbi extract

Extract Single-File Backup into a Backup Directory

$ mysqlbackup --backup-image=/var/my.mbi --backup-dir=/var/backup extract

Selective Extract of Single File

$ mysqlbackup --backup-image=/var/my.mbi --src-entry=meta/comments.txt extract

Selective Extract of Single Directory

$ mysqlbackup --backup-image=/backup/my.mbi --src-entry=meta extract

Streaming the Backup Data to Another Device or Server

The following command streams the backup as a single-file output to a remote host, where it may be saved directly to a tape device. --backup-dir=/tmp designates the directory for storing temporary files rather than the final output file.

$ mysqlbackup --backup-image=- --backup-dir=/tmp backup-to-image | \
ssh user@host command arg1 arg2...

The following command streams the backup as a single backup file to be restored on a remote MySQL server:

$ mysqlbackup  --backup-dir=backup --backup-image=-  --compress backup-to-image | \
ssh <user name>@<remote host name> 'mysqlbackup --backup-dir=backup_tmp --datadir=/data \
--innodb_log_group_home_dir=. \
--innodb_log_files_in_group=<innodb_log_files_in_group_of_backedup_server> \
 --innodb_log_file_size=<innodb_log_file_size_of_backedup_server> \ 
--innodb_data_file_path=<innodb_data_file_path_of_backedup_server> \ 
--uncompress --backup-image=- copy-back-and-apply-log'

Stream a Backup Directory to a Remote MySQL Server

$ mysqlbackup --backup-image=- --backup-dir=/path/to/my/backup backup-dir-to-image | \
ssh <user name>@<remote host name> \ 
'mysqlbackup --backup-dir=backup_tmp --datadir=/data --backup-image=- copy-back-and-apply-log'

Creating a Cloud Backup in an Oracle Cloud Storage Service Instance

$ mysqlbackup \
--include-tables=testdb.t1 --use-tts=with-full-locking \
--cloud-service=openstack  --cloud-container=<oracle storage cloud container> \
--cloud-user-id=<serviceInstanceName>-<identityDomainName>:<userName> --cloud-password=<password> \
--cloud-tempauth-url=https://<dataCenterCode>.storage.oraclecloud.com \
--cloud-trace=1 --cloud-object=image_900.mbi \
--backup-dir=/home/user/dba/orbackuptmpdir \
--backup-image=- \
backup-to-image

Creating a Cloud Backup on Amazon S3

$ mysqlbackup\
--cloud-service=s3 --cloud-aws-region=<aws region> \
--cloud-access-key-id=<aws access key id> --cloud-secret-access-key=< aws secret access key> \
--cloud-bucket=<s3 bucket name> --cloud-object-key=<aws object key> \
--backup-dir=/home/user/dba/s3backuptmpdir \
--backup-image=- \
backup-to-image

Extract an Existing Image from an Oracle Storage Cloud Service instance to a Backup Directory

$ mysqlbackup \
--cloud-service=openstack  --cloud-container=<oracle storage cloud container> \
--cloud-user-id=<serviceInstanceName>-<identityDomainName>:<userName> --cloud-password=<password> \
--cloud-tempauth-url=https://<dataCenterCode>.storage.oraclecloud.com \
--cloud-object=image_930.mbi \
--backup-dir=/home/user/dba/orbackupdir \
--backup-image=- \
image-to-backup-dir

Extract an Existing Image from Amazon S3 Cloud Storage to a Backup Directory

$ mysqlbackup\
--cloud-service=s3 --cloud-aws-region=<aws region> \
--cloud-access-key-id=<aws access key id> --cloud-secret-access-key=< aws secret access key> \
--cloud-bucket=<s3 bucket name> --cloud-object-key=<aws object key> \
--backup-dir=/home/user/dba/s3backupdir \
--backup-image=- \
image-to-backup-dir

Creating Incremental Backups Using Only the Redo Log

The changes to InnoDB tables are determined based on the contents of the InnoDB redo log. Since the redo log files have a fixed size that you know in advance, it can require less I/O to read the changes from them than to scan the InnoDB tablespace files to locate the changed pages, depending on the size of your database, amount of DML activity, and size of the redo log files.

To calculate the size of the redo log, issue the command SHOW VARIABLES LIKE 'innodb_log_file%' and, based on the output, multiply the innodb_log_file_size setting by the value of innodb_log_files_in_group. To compute the redo log size at the physical level, look into the datadir directory of the MySQL instance and sum up the sizes of the files matching the pattern ib_logfile*.

The InnoDB LSN value corresponds to the number of bytes written to the redo log. To check the LSN at some point in time, issue the command SHOW ENGINE INNODB STATUS and look under the LOG heading. While planning your backup strategy, record the LSN values periodically and subtract the earlier value from the current one to calculate how much redo data is generated each hour, day, and so on.

The incremental backup feature is primarily intended for InnoDB tables, or non-InnoDB tables that are read-only or rarely updated. Incremental backups detect changes at the level of pages in the InnoDB data files, as opposed to table rows; each page that has changed is backed up.

You cannot perform incremental backups with the --compress option.

To create an incremental backup image with the --start-lsn option, use the following command, specifying with --incremental-backup-dir the backup directory, which, in this case, is a directory for storing the metadata for the backup and some temporary files:

$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --incremental \
  --start-lsn=2654255716 \
  --with-timestamp \
  --incremental-backup-dir=/incr-tmp \
 --backup-image=/incr-backup/incremental_image.bi
  backup-to-image

In the following example though, because --backup-image does not provide a full path to the image file to be created, the incremental backup image is created under the folder specified by --incremental-backup-dir:
  
$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --incremental \
  --start-lsn=2654255716 \
  --with-timestamp \
  --incremental-backup-dir=/incr-images \
  --backup-image=incremental_image1.bi
  backup-to-image

In the following example, the --incremental-base=history:last_backup option is used, given which mysqlbackup fetches the LSN of the last successful (non-TTS) full or partial backup from the mysql.backup_history table and performs an incremental backup basing on that.

$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf \
  --incremental --incremental-base=history:last_backup \
  --backup-dir=/home/dbadmin/temp_dir \
  --backup-image=incremental_image1.bi 
   backup-to-image

Use the following commands to create incremental directory backups, using the --incremental-base

$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --incremental \
  --incremental-base=dir:/incr-backup/wednesday \
  --incremental-backup-dir=/incr-backup/thursday \
  backup

Making a Compressed Backup

You can only use the --compress option for full backups, not for incremental backups.
  
The backup compression feature works only for InnoDB tables. After the InnoDB tablespace files are compressed during backup, they receive the .ibz extension.
  
 The binary log and relay log files are compressed and saved with the .bz extension when being included in a compressed backup.

$ mysqlbackup --defaults-file=/etc/my.cnf --compress --compress-level=5 \
  --backup-image=backup.img backup-to-image

This is a sample command for making a compressed directory backup:

$ mysqlbackup --defaults-file=/etc/my.cnf --compress --compress-level=5 backup
$ mysqlbackup --defaults-file=/etc/my.cnf --compress --compress-level=5 backup-and-apply-log

Making a Partial Backup

Including or excluding specific tables by their names. This uses the --include-tables or --exclude-tables option.Including some or all InnoDB tables, but not other table types. This uses the --only-innodb option.
  
  Note : Typically, a partial backup is more difficult to restore than a full backup, because the backup data might not include the necessary interrelated pieces to constitute a complete MySQL instance. In particular, InnoDB tables have internal IDs and other data values that can only be restored to the same instance, not a different MySQL server. Always fully test the recovery procedure for any partial backups to understand the relevant procedures and restrictions.
  
 Because the InnoDB system tablespace holds metadata about InnoDB tables from all databases in an instance, restoring a partial backup on a server that includes other databases could cause the system to lose track of those InnoDB tables in the other databases. Always restore partial backups on a fresh MySQL server instance without any other InnoDB tables that you want to preserve.Including all tables with names starting with “emp” into the backup:

$ mysqlbackup \
 --host=localhost --user=mysqluser --protocol=TCP --port=3306 \
 --backup-dir=$MEB_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \
 --include-tables="\.emp" \
 backup-to-image

Taking a backup of all tables except tables from the “mysql” and “performance_schema” databases:

$ mysqlbackup \
 --host=localhost --user=mysqluser --protocol=TCP --port=3306 \
 --backup-dir=$MEB_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \
 --exclude-tables="^(mysql|performance_schema)\." \
 backup-to-image

Taking a backup of all tables in the “sales reps” database, but excludes the table with the name “euro-asia”

$ mysqlbackup \
 --host=localhost --user=mysqluser --protocol=TCP --port=3306 \
 --backup-dir=$MEB_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \
 --include-tables="^sales reps\." --exclude-tables="^sales reps\.euro-asia" \
 backup-to-image

Backing up all InnoDB tables, but not .frm files:

$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --only-innodb backup-to-image

The --only-innodb and --only-innodb-with-frm options back up InnoDB tables only, skipping those of other storage engines. You might also use them together with the --include option to make selective backup of InnoDB tables while excluding all other files created by other storage engines.

Optimistic Backup Using the Option optimistic-time=YYMMDDHHMMSS

In this example, tables that have been modified since the noon of May 16, 2011 are treated as busy tables and backed up in the normal phase of an optimistic backup, and all other tables are backed up in the optimistic phase:

$ mysqlbackup --defaults-file=/etc/my.cnf --optimistic-time=110516120000 backup-to-image

Optimistic Backup Using the Option optimistic-time=now

$ mysqlbackup --defaults-file=/etc/my.cnf --optimistic-time=now backup-to-image

Restoring a Database

$ mysqlbackup --defaults-file=<my.cnf> -uroot --backup-image=<image_name> \
  --backup-dir=<backupTmpDir> --datadir=<restoreDir> copy-back-and-apply-log

Restoring a Compressed Backup
$ mysqlbackup --defaults-file=<my.cnf> -uroot --backup-image=<image_name> \
  --backup-dir=<backupTmpDir> --datadir=<restoreDir> --uncompress copy-back-and-apply-log

Restoring a Compressed Directory Backup
$ mysqlbackup --defaults-file=<my.cnf> -uroot --backup-dir=<backupDir> --datadir=<restoreDir> \
  --uncompress copy-back-and-apply-log

Restoring an Incremental Backup Image
$ mysqlbackup --defaults-file=<my.cnf> -uroot --backup-image=<inc_image_name> \
  --incremental-backup-dir=<incBackupTmpDir> --datadir=<restoreDir> --incremental \
  copy-back-and-apply-log

Restoring an Incremental Backup Directory
$ mysqlbackup --backup-dir=/full-backup/2010-12-08_17-14-11 apply-log
..many lines of output...
101208 17:15:10  mysqlbackup: Full backup prepared for recovery successfully!

101208 17:15:10 mysqlbackup: mysqlbackup completed OK!
Then, we apply the changes from the incremental backup using the apply-incremental-backup command:


$ mysqlbackup --incremental-backup-dir=/incr-backup/2010-12-08_17-14-48 
  --backup-dir=/full-backup/2010-12-08_17-14-11 apply-incremental-backup
...many lines of output...
101208 17:15:12 mysqlbackup: mysqlbackup completed OK!


Read More

Sunday, 18 February 2018

Multi-Source Replication in MySQL 5.7

MySQL Multi-Source Replication enables a replication slave to receive transactions from multiple sources simultaneously. Multi-source replication can be used to back up multiple servers to a single server, to merge table shards, and consolidate data from multiple servers to a single server. 

Masters in a multi-source replication topology can be configured to use either global transaction identifier (GTID) based replication, or binary log position-based replication.


Slaves in a multi-source replication topology require TABLE based repositories.


master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1

There are 2 ways to configure in multi source Replication.

1) Global transaction identifier (GTID) based replication.

2) Binary log position-based replication.

Global transaction identifier (GTID) based replication.

Enable the GTID_mode=ON. ( log_slave_update is not mandatory for GTID in MySQL 5.7 )


gtid-mode=on
enforce-gtid-consistency

Use the CHANGE MASTER TO statement to add a new master to a channel by using a FOR CHANNEL channel clause.

CHANGE MASTER TO MASTER_HOST='master_server1', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl_password', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master-1';

Multi-source replication is compatible with auto-positioning.Repeat this process for each extra master that you want to add to a channel, changing the host name, port and channel as appropriate.

Binary Log Based Multi-Source Replication

We have  to enable  binary logging on the master using log_bin and we need  current master_log_file and master_log_position and add the master channel by using For Channel channel_name.

CHANGE MASTER TO MASTER_HOST='master_server1', MASTER_USER='repl', MASTER_PORT=3306, MASTER_PASSWORD='repl_password' MASTER_LOG_FILE='master1-bin.000006', MASTER_LOG_POS=628 FOR CHANNEL 'master-1';

Each communication channel is a connection from slave to master to get the binary log events. That means we will have one IO_THREAD for each communication channel. We will need to run different “CHANGE MASTER” commands, one for each master, with the “FOR CHANNEL” argument that we will use to give a name to that channel.

Starting and Stopping Multi-Source Replication Slaves
To start the slave processes you need to specify what channel are you referring to:



slave > start slave for channel="master-1";
slave > start slave for channel="master-2";

To stop only a named channel, use a FOR CHANNEL channel clause:

slave > stop slave for channel="master-1";
slave > stop slave for channel="master-2";

To reset only a named channel, use a FOR CHANNEL channel clause:


slave > RESET SLAVE FOR CHANNEL="master-1";

Now, we want to check the status of the slave:


slave > SHOW SLAVE STATUS FOR CHANNEL="master-1"G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master_server1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master1-bin.000006
          Read_Master_Log_Pos: 629
               Relay_Log_File: relay-bin-master1.000003
                Relay_Log_Pos: 395
        Relay_Master_Log_File: master1-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

To monitor the connection status of all channels we can use :

mysql> SELECT * FROM replication_connection_status\G;


mysql> SELECT * FROM replication_connection_status\G;
*************************** 1. row ***************************
CHANNEL_NAME: master-1
GROUP_NAME:
SOURCE_UUID: 046e41f8-a223-11e4-a975-0811960cc264
THREAD_ID: 24
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
RECEIVED_TRANSACTION_SET: 046e41f8-a223-11e4-a975-0811960cc264:4-37
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

It will shows the channel name , source_UUID , thread_ID .

The column LAST_HEARTBEAT_TIMESTAMP can be used find the lag.

Multi-Source Replication setup using Percona-Xtrabackup :
  • Restore a full backup using percona xtrabackup from Master 1 on slave 
  • From Master 2 dump all the tables structures (CREATE TABLE) to slave. You can use mysqldump with --no-data
  • Take a full backup from Master 2 and restore individual table using export option 
           Ref: https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/restoring_individual_tables_ibk.html          
Replication events are somehow serialized at slave side, just like a global counter that is not well documented yet. In reality this means that you need to be very careful when troubleshooting issues because you may suffer unexpected issues, for instance if you have 2 replication channels failing with a duplicate key error then is not easy to predict which even you will skip when running set global sql_slave_skip_counter=1
Reference for Multi-Master and Multi-Source replication click here
Read More

Friday, 16 February 2018

MySQL InnoDB Cluster - A complete High Availability solution for MySQL

MySQL InnoDB cluster provides a complete high availability solution for MySQL.Each MySQL server instance runs MySQL Group Replication, which provides the mechanism to replicate data within InnoDB clusters, with built-in failover.

In the default single-primary mode, an InnoDB cluster has a single read-write server instance - the primary. Multiple secondary server instances are replicas of the primary. If the primary fails, a secondary is automatically promoted to the role of primary. 



InnoDB Cluster uses the Group Replication plugin to allow for virtually synchronous replication, while also providing a MySQL Router that is aware of the cluster state.


InnoDB Cluster also provides a new MySQL Shell to interact with the cluster commands.


InnoDB Cluster Requirements

InnoDB cluster uses Group Replication and therefore your server instances must meet the same requirements. 
  • All the tables must have InnoDB storage engine.
  • Primary Keys : Every table that is to be replicated by the group must have a defined primary key, or primary key equivalent where the equivalent is a non-null unique key. 
  • IPv4 Network : The group communication engine used by MySQL Group Replication only supports IPv4.
  • Network Performance : Group Replication is designed to be deployed in a cluster environment where server instances are very close to each other, and is impacted by both network latency as well as network bandwidth.
  • Set Binary Log Active (Set --log-bin[=log_file_name] ) 
  • Slave Updates Logged : (Set --log-slave-updates). Servers need to log binary logs that are applied through the replication applier. Servers in the group need to log all transactions that they receive and apply from the group. This is required because recovery is conducted by relying on binary logs form participants in the group.
  • Set Binary Log format to ROW : Set --binlog-format=row. Group Replication relies on row-based replication format to propagate changes consistently among the servers in the group. 
  • Global Transaction Identifiers On : (Set --gtid-mode=ON). Group Replication uses global transaction identifiers to track exactly which transactions have been committed on every server instance and thus be able to infer which servers have executed transactions that could conflict with already committed transactions elsewhere. 
  • Replication Information Repositories :  (Set --master-info-repository=TABLE ) and --relay-log-info-repository=TABLE. The replication applier needs to have the master information and relay log metadata written to the mysql.slave_master_info and mysql.slave_relay_log_info system tables. 
  • Transaction Write Set Extraction :  Set --transaction-write-set-extraction=XXHASH64 so that while collecting rows to log them to the binary log, the server collects the write set as well. The write set is based on the primary keys of each row and is a simplified and compact view of a tag that uniquely identifies the row that was changed. 
  • Multi-threaded Appliers : Group Replication members can be configured as multi-threaded appliers, enabling transactions to be applied in parallel. Set --slave-parallel-workers=N (where N is the number of parallel applier threads), --slave-preserve-commit-order=1, and --slave-parallel-type=LOGICAL_CLOCK. Setting --slave-parallel-workers=N enables the multi-threaded applier on the member. Group Replication relies on consistency mechanisms built around the guarantee that all participating members receive and apply committed transaction in the same order, so you must also set --slave-preserve-commit-order=1 to ensure that the final commit of parallel transactions is in the same order as the original transactions. Finally, in order to determine which transactions can be executed in parallel, the relay log must contain transaction parent information generated with --slave-parallel-type=LOGICAL_CLOCK. 
  • Install Python : The provisioning scripts that MySQL Shell uses to configure servers for use in InnoDB cluster require access to Python version 2.7. For a sandbox deployment Python is required on the single machine used for the deployment, production deployments require Python on each server instance.
Server Requirements :

To start using InnoDB cluster you need to install the following:
  • MySQL Server 5.7.17 or higher
  • MySQL Shell 1.0.8 or higher
  • MySQL Router 2.1.2 or higher
  • Python 2.7.x
Setting up Server :

Ubuntu :

sudo
To properly configure the hosts for InnoDB cluster we need sudo to execute commands with super-user privileges.

To install it run the following commands

$ su
$ apt-get install sudo

To configure it, open the sudoers file and add your user and set the required permissions:

$ sudo nano /etc/sudoers
$ sudo apt-get install python

To configure the host mapping, edit the hosts file:

$ sudo nano /etc/hosts
The file should have the following entrances:

192.168.1.145 prodserver11
192.168.1.146 prodserver22
192.168.1.147 prodserver44
192.168.1.148 prodserver55

Add the IP(s) of your host(s) and the name(s). Press ctrl+o and then enter to save the file. Press ctrl+x to close the file.
Note: Ubuntu will configure a loopback interface (127.0.1.1) for the hostname by default. Make sure to remove the loopback interface entry as it can’t be used to connect from other hosts.

Install the MySQL APT repository

Open a terminal and use wget to download the official APT repository and then install the package:

$ sudo wget http://dev.mysql.com/get/mysql-apt-config_0.8.4-1_all.deb
$ sudo dpkg -i ./mysql-apt-config_0.8.4-1_all.deb

Install MySQL Server and MySQL Shell

$ sudo apt-get install mysql-server mysql-shell

Configure the local instance calling the following function, and type the password for the user when prompted:

mysql-js> dba.configureLocalInstance();

MySQL Shell will find the default configuration file and ask you if it is ok to modify it, type “Y”. Since root cannot do remote logins, you have three options to continue with the configuration: enable the remote connections for root, create a new user or not enable remote connections for root neither create a new user.

In this tutorial, we choose to create a new user.

You will see a report with the changes made by MySQL Shell and a message saying that you need to restart the MySQL service to apply them.

$ sudo systemctl restart mysql.service


Redhat 7/CentOS 7:

Perform the host mapping as below :

vi /etc/hosts 

192.168.1.145 prodserver11
192.168.1.146 prodserver22
192.168.1.147 prodserver44
192.168.1.148 prodserver55

Configure each machine to map the IP of each other machine to a hostname.

$ sudo yum -y install python

$ sudo yum install -y mysql-shell

Configure the report_host variable in the MySQL configuration of each instance.

Deploying Sandbox Instances 

The first step is to deploy sandbox MySQL Server instances, so that we can play around and experiment safely, without affecting any existing MySQL databases.

Start MySQL Shell (as your ordinary, non-root OS user):

$ mysqlsh

mysql-js> dba.deployLocalInstance(3310)

The argument to deployLocalInstance() is the TCP port number where MySQL Server should listen for connections. By default, the sandbox is created in a directory named $HOME/mysql-sandboxes/<port>.

You will be prompted to pick a MySQL root account password for that instance, this is the password that you will use to connect to that instance in the future.

Note: use the same password for all your sandboxes in the same cluster.

Repeat the above command two more times, using different port numbers each time. This will allow us to create an InnoDB cluster that is tolerant to up to one failure.

$ mysqlsh
Welcome to MySQL Shell 1.0.5-labs Development Preview
 
Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.
 
Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> dba.deployLocalInstance(3310)
A new MySQL sandbox instance will be created on this host in 
/home/kojima/mysql-sandboxes/3310
 
Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...
 
Instance localhost:3310 successfully deployed and started.
Use '\connect root@localhost:3310' to connect to the instance.
 
mysql-js> dba.deployLocalInstance(3320)
A new MySQL sandbox instance will be created on this host in 
/home/kojima/mysql-sandboxes/3320
 
Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...
 
Instance localhost:3320 successfully deployed and started.
Use '\connect root@localhost:3320' to connect to the instance.
 
mysql-js> dba.deployLocalInstance(3330)
A new MySQL sandbox instance will be created on this host in 
/home/kojima/mysql-sandboxes/3330
 
Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...
 
Instance localhost:3330 successfully deployed and started.
Use '\connect root@localhost:3330' to connect to the instance.

Production Deployment of InnoDB Cluster

In this tutorial we will deploy three production clusters and one router 

Setup as below :

prodserver55  (MySQL server 1)
prodserver11  (MySQL server 2)
prodserver22  (MySQL server 3)
prodserver44  (MySQL MySQL Router )




Prerequisites :

Three Mysql 5.7 instance nodes up and running each on prodserver55, prodserver11 and prodserver22 

It is recommended to disable SELinux Policy 

Firewall Ports :

Open following firewall Ports:

$ firewall-cmd --zone=public --add-port=3306/tcp --permanent
$ firewall-cmd --zone=public --add-port=3306/udp --permanent
$ firewall-cmd --zone=public --add-port=6446/tcp --permanent
$ firewall-cmd --zone=public --add-port=6446/udp --permanent
$ firewall-cmd --zone=public --add-port=6447/tcp --permanent
$ firewall-cmd --zone=public --add-port=6447/udp --permanent
$ firewall-cmd --zone=public --add-port=64460/tcp --permanent
$ firewall-cmd --zone=public --add-port=64460/udp --permanent
$ firewall-cmd --zone=public --add-port=64470/tcp --permanent
$ firewall-cmd --zone=public --add-port=64470/udp --permanent

$ firewall-cmd --reload

$ systemctl restart firewalld

$ firewall-cmd --list-ports

Create User :

The user account used to administer an instance does not have to be the root account, however the user needs to be assigned full read and write privileges on the InnoDB cluster metadata tables in addition to full MySQL administrator privileges (SUPER, GRANT OPTION, CREATE, DROP and so on). 

CREATE USER innob_user@'%' IDENTIFIED BY 'user_Password' ;
 
GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.* TO innob_user@'%' WITH GRANT OPTION;
GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, \
CREATE USER ON *.* TO innob_user@'%' WITH GRANT OPTION;
GRANT SELECT ON *.* TO innob_user@'%' WITH GRANT OPTION;

If only read operations are needed (such as for monitoring purposes), an account with more restricted privileges can be used.

GRANT SELECT ON mysql_innodb_cluster_metadata.* TO innob_user@'%';
GRANT SELECT ON performance_schema.global_status TO innob_user@'%';
GRANT SELECT ON performance_schema.replication_applier_configuration TO innob_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status TO innob_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_coordinator TO innob_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_worker TO innob_user@'%';
GRANT SELECT ON performance_schema.replication_connection_configuration TO innob_user@'%';
GRANT SELECT ON performance_schema.replication_connection_status TO innob_user@'%';
GRANT SELECT ON performance_schema.replication_group_member_stats TO innob_user@'%';
GRANT SELECT ON performance_schema.replication_group_members TO innob_user@'%';
Start MySQL Shell

Verbose Logging :

When working with a production deployment it can be useful to configure verbose logging for MySQL Shell

shell> mysqlsh --log-level=DEBUG3

In addition to enabling the MySQL Shell log level, you can configure the amount of output Admin API provides in MySQL Shell after each call to the API. 

mysql-js> dba.verbose=2

2 adds debug output to the verbose output providing full information about what each call to Admin API executes.

Checking Instance Configuration

Before creating a production deployment from server instances you need to check that MySQL on each instance is correctly configured by using the dba.checkInstanceConfiguration() function. 

First, we will check the configuration of one of our MySQL server. Some changes are required, we will perform them using the Shell and we will restart mysqld:

# mysqlsh
mysql-js> dba.checkInstanceConfiguration('innob_user@prodserver55:3306')

The restart_required field in the final part of the report tells you whether MySQL on the instance requires a restart to detect any change made to the configuration file.


Configuring the Instance

AdminAPI provides the dba.configureLocalInstance() function that finds the MySQL server's option file and modifies it to ensure that the instance is correctly configured for InnoDB cluster. Alternatively make the changes to the instance's option file manually based on the information in the report. 

mysql-js> dba.configureLocalInstance('innob_user@prodserver55:3306')

Now MySQL has all the required mandatory settings to run Group Replication. We can verify the configuration again in the Shell with dba.checkInstanceConfiguration() function.

Restart the mysqld service 

$ systemctl restart mysqld

Repeat this process for each server instance that you added to the cluster. Similarly if you modify the cluster structure, for example changing the number of instances, you need to repeat this process for each server instance to update the InnoDB cluster metadata accordingly for each instance in the cluster.

MySQL InnoDB Cluster Creation

Once you have prepared your instances, use the dba.createCluster() function to create the cluster. The machine which you are running MySQL Shell on is used as the seed instance for the cluster. The seed instance is replicated to the other instances which you add to the cluster, making them replicas of the seed instance. Log in to the instance and run MySQL Shell locally.

To persist the InnoDB cluster metadata for all instances, log in to each instance that you added to the cluster and run MySQL Shell locally.

# mysqlsh

mysql-js> var i1='innob_user@prodserver55:3306'
mysql-js> var i2='innob_user@prodserver11:3306'
mysql-js> var i3='innob_user@prodserver22:3306'

mysql-js> shell.connect(i1)

MySQL Shell must be connected to an instance before you can create a cluster because when you issue dba.createCluster(name) MySQL Shell creates a MySQL protocol session to the server instance connected to the MySQL Shell's current global session.

mysql-js> var cluster = dba.createCluster('prodcluster')

Note :

If you encounter an error related to metadata being inaccessible you might have the loop-back network interface configured. For correct InnoDB cluster usage disable the loop-back interface.

The createCluster() command takes one parameter, which is a symbolic name you give to this InnoDB cluster. This will, among other things:

  • Deploy the metadata schema in that instance (mysql.mysql_innodb_cluster_metadata)
  • Verify that its configuration is correct for Group Replication, making changes if necessary
  • Register it as the seed instance of the new cluster
  • Create necessary internal administrative accounts
  • Start Group Replication

To check the cluster has been created, use the cluster instance's status() function.

mysql-js> cluster.status()

mysql-js> cluster.status()
{
    "clusterName": "prodcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "prodserver55:3306",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "prodserver55:3306": {
                "address": "prodserver55:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    }
}

Once server instances belong to a cluster it is important to only administer them using MySQL Shell and AdminAPI. Attempting to manually change the configuration of Group Replication on an instance once it has been added to a cluster is not supported. 

Similarly, modifying server variables critical to InnoDB cluster, such as server_uuid after an instance is configured using AdminAPI is not supported.

We can now validate that the dataset on the other instances is correct (no extra transactions executed). This is done by validating the GTIDs. 

Now validate the remaining two cluster nodes 


mysql-js> cluster.checkInstanceState(i2)
Please provide the password for 'innob_user@prodserver11:3306':
Analyzing the instance replication state...

The instance 'prodserver11:3306' is valid for the cluster.
The instance is new to Group Replication.

{
    "reason": "new",
    "state": "ok"
}


mysql-js> cluster.checkInstanceState(i3)
Please provide the password for 'innob_user@prodserver22:3306':
Analyzing the instance replication state...

The instance 'prodserver22:3306' is valid for the cluster.
The instance is new to Group Replication.

{
    "reason": "new",
    "state": "ok"
}

Use the cluster.addInstance(instance) function to add more instances to the cluster, where instance is a URI type string to connect to the local instance.

mysql-js> cluster.addInstance(i2)

To verify the instance has been added, use the cluster instance's status() function.


mysql-js> cluster.status()
{
    "clusterName": "prodcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "prodserver55:3306",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "prodserver55:3306": {
                "address": "prodserver55:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "prodserver55:3406": {
                "address": "prodserver11:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    }
}


Note : If you have SELinux enabled this step may fail, because you may need to run additional commands to add a policy and enabled this.

The following command is an example of the usage of the ipWhiteList and memberSslMode options to add a new instance to a cluster:


mysql-js> cluster.addInstance('inno_b@prodserver55:3306', { memberSslMode: 'REQUIRED', ipWhitelist:'192.168.1.145/32,192.168.1.145/32'});

Remember to set the same memberSslMode value set in the cluster creation if it’s not ‘AUTO’, when you try to add a new instance to the cluster.

Add the third node to cluster 

mysql-js> cluster.addInstance(i3)

mysql-js> cluster.status()
{
    "clusterName": "prodcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "prodserver55:3306",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "prodserver55:3306": {
                "address": "prodserver55:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "prodserver11:3306": {
                "address": "prodserver11:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "prodserver22:3306": {
                "address": "prodserver22:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    }
}

This command queries the current status of the InnoDB cluster and produces a short report. The status field for each instance should show either ONLINE or RECOVERING. RECOVERING means that the instance is receiving updates from the seed instance and should eventually switch to ONLINE.


Another point to note is that one of the instances (the PRIMARY) is marked R/W (read/writable), while the other two are marked R/O (read only). Only the instance marked R/W can execute transactions that update the database. If that instance becomes unreachable for any reason (like a system crash), one of the remaining two instances automatically takes over its place and becomes the new PRIMARY.

Cluster Node States :

ONLINE: The instance is online and participating in the cluster.

OFFLINE: The instance has lost connection to the other instances.

RECOVERING: The instance is attempting to synchronize with the cluster by retrieving transactions it needs before it can become an ONLINE member.

UNREACHABLE: The instance has lost communication with the cluster.

ERROR: The instance has encountered an error during the recovery phase or while applying a transaction.


MISSING : The state of an instance which is part of the configured cluster, but is currently unavailable.

Read InnoDB Cluster Navigation here 

Adopting a Group Replication Deployment

If you have an existing deployment of Group Replication and you want to use it to create a cluster, pass the adoptFromGR option to the dba.createCluster() function. 

Note :
Group Replication members might contain MyISAM tables. Convert all such tables to InnoDB before adopting the group to an InnoDB cluster.


mysql-js> var cluster = dba.createCluster('prodCluster', {adoptFromGR: true});

Deploy MySQL Router

In order for applications to handle failover, they need to be aware of the topology of the InnoDB cluster. They also need to know, at any time, which of the instances is the PRIMARY. While it’s possible for applications to implement that logic by themselves, MySQL Router can do that for you, with minimal work and no code changes in  applications.

MySQL router provides you the ability to hide your network configuration behind a proxy and map the data requests to the cluster.

MySQL Router can configure itself based on the InnoDB cluster's metadata using the --bootstrap option. 
This configures MySQL Router automatically to route connections to the cluster's server instances. Client applications connect to the ports MySQL Router provides, without any need to be aware of the InnoDB cluster topology.

Ubuntu :

$ sudo apt-get install mysql-router

Redhat/CentOS :

$ sudo yum install mysql-router

The recommended deployment of MySQL Router is on the same host as the application.You need the MASTER key of the InnoDB cluster to auto-configure MySQL Router.

Assuming MySQL Router is already installed, all we need to do is to bootstrap it with the metadata server, calling mysqlrouter with the following command line option from the system’s shell:

MySQL Router uses the included metadata cache plugin to retrieve the InnoDB cluster's metadata, consisting of a list of server instance addresses which make up the InnoDB cluster and their role in the cluster. 

$ sudo mysqlrouter --bootstrap innob_user@prodserver55:3306  --directory /mysqlrouter  --user=mysql

[root@prodserver44 /]# sudo mysqlrouter --bootstrap innob_user@prodserver55:3306  --directory /mysqlrouter  --user=mysql
Please enter MySQL password for innob_user:

Bootstrapping MySQL Router instance at /mysqlrouter...
MySQL Router  has now been configured for the InnoDB cluster 'prodcluster'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'prodcluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447

X protocol connections to cluster 'prodcluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470


MySQL Router connects to the InnoDB cluster, fetches its metadata and configures itself for use. The generated configuration creates 2 TCP ports: one for read-write sessions (which redirect connections to the PRIMARY) and one for read-only sessions (which redirect connections to one of the SECONDARY instances, in a round-robin fashion).

The MySQL Router bootstrap process creates a mysqlrouter.conf file, with the settings based on the cluster metadata retrieved from the address passed to the --bootstrap option.

Based on the InnoDB cluster metadata retrieved, MySQL Router automatically creates a configuration file, including a metadata_cache section with bootstrap_server_addresses containing the addresses for all server instances in the cluster.

[root@prodserver44 /]# cd mysqlrouter/
[root@prodserver44 mysqlrouter]# ls
data  log  mysqlrouter.conf  mysqlrouter.key  run  start.sh  stop.sh

[root@prodserver44 mysqlrouter]# cat mysqlrouter.conf
# File automatically generated during MySQL Router bootstrap
[DEFAULT]
user=mysql
logging_folder=/mysqlrouter/log
runtime_folder=/mysqlrouter/run
data_folder=/mysqlrouter/data
keyring_path=/mysqlrouter/data/keyring
master_key_path=/mysqlrouter/mysqlrouter.key

[logger]
level = INFO

[metadata_cache:prodcluster]
router_id=1
bootstrap_server_addresses=mysql://prodserver55:3306,mysql://prodserver11:3306,mysql://prodserver22:3306
user=mysql_router1_aebolhe5ougc
metadata_cluster=prodcluster
ttl=300

[routing:prodcluster_default_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://prodcluster/default?role=PRIMARY
mode=read-write
protocol=classic

[routing:prodcluster_default_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://prodcluster/default?role=SECONDARY
mode=read-only
protocol=classic

[routing:prodcluster_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://prodcluster/default?role=PRIMARY
mode=read-write
protocol=x

[routing:prodcluster_default_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://prodcluster/default?role=SECONDARY
mode=read-only
protocol=x

Note : 

When you change the topology of a cluster by adding another server instance after you have bootstrapped MySQL Router, you need to update bootstrap_server_addresses based on the updated metadata. Either restart MySQL Router using the --bootstrap option, or manually edit the bootstrap_server_addresses section of the mysqlrouter.conf file and restart MySQL Router.

The generated MySQL Router configuration creates TCP ports which you use to connect to the cluster. Ports for communicating with the cluster using both Classic MySQL protocol and X Protocol are created. To use X Protocol the server instances must have X Plugin installed and configured. For a sandbox deployment, instances have X Plugin set up automatically.

For a production deployment, if you want to use X Protocol you need to install and configure X Plugin on each instance, see Section 19.3, “Setting Up MySQL as a Document Store”. 

The default available ports are :


PortsDescription
6446For Classic MySQL protocol read-write sessions, which MySQL Router redirects incoming connections to primary server instances.
6447For Classic MySQL protocol read-only sessions, which MySQL Router redirects incoming connections to one of the secondary server instances.
64460For X Protocol read-write sessions, which MySQL Router redirects incoming connections to primary server instances.
64470For X Protocol read-only sessions, which MySQL Router redirects incoming connections to one of the secondary server instances.

When using a single-primary cluster, read-write sessions are redirected to the single primary, with a multi-primary cluster read-write sessions are redirected to one of the primary instances. For incoming read-only connections MySQL Router redirects connections to one of the secondary instances in a round-robin fashion.

Once bootstrapped and configured, start MySQL Router:


/mysqlrouter/start.sh


default :

shell> mysqlrouter &


# /mysqlrouter/start.sh
[root@prodserver44 mysqlrouter]# PID 2869 written to /mysqlrouter/mysqlrouter.pid

ps -ef | grep mysql

mysql     2869  2868  0 18:58 pts/0    00:00:00 /bin/mysqlrouter -c /mysqlrouter/mysqlrouter.conf --user=mysql
root      2881  2263  0 18:58 pts/0    00:00:00 grep --color=auto mysql

Tail the log

[root@prodserver44 log]# tail -n50 mysqlrouter.log
2018-02-12 18:58:45 INFO    [7f7e7e75c700] [routing:prodcluster_default_x_ro] started: listening on 0.0.0.0:64470; read-only
2018-02-12 18:58:45 INFO    [7f7e7df5b700] [routing:prodcluster_default_x_rw] started: listening on 0.0.0.0:64460; read-write
2018-02-12 18:58:45 INFO    [7f7e7ff5f700] Starting Metadata Cache
2018-02-12 18:58:45 INFO    [7f7e7ff5f700] Connections using ssl_mode 'PREFERRED'
2018-02-12 18:58:45 INFO    [7f7e7f75e700] [routing:prodcluster_default_ro] started: listening on 0.0.0.0:6447; read-only
2018-02-12 18:58:45 INFO    [7f7e7ef5d700] [routing:prodcluster_default_rw] started: listening on 0.0.0.0:6446; read-write
2018-02-12 18:58:45 INFO    [7f7e7ff5f700] Connected with metadata server running on prodserver55:3306
2018-02-12 18:58:45 INFO    [7f7e7ff5f700] Changes detected in cluster 'prodcluster' after metadata refresh
2018-02-12 18:58:45 INFO    [7f7e7ff5f700] Metadata for cluster 'prodcluster' has 1 replicasets:
2018-02-12 18:58:45 INFO    [7f7e7ff5f700] 'default' (3 members, single-master)
2018-02-12 18:58:45 INFO    [7f7e7ff5f700]     prodserver55:3306 / 33060 - role=HA mode=RW
2018-02-12 18:58:45 INFO    [7f7e7ff5f700]     prodserver11:3306 / 34060 - role=HA mode=RO
2018-02-12 18:58:45 INFO    [7f7e7ff5f700]     prodserver22:3306 / 35060 - role=HA mode=RO
2018-02-12 18:58:45 INFO    [7f7e6effd700] Connected with metadata server running on prodserver55:3306
2018-02-12 19:03:46 INFO    [7f7e6effd700] Connected with metadata server running on prodserver55:3306

To stop MySQL Router, in a terminal run the stop script generated:

$ /mysqlrouter/stop.sh

You can now connect a MySQL client, such as MySQL Shell to one of the incoming MySQL Router ports as described above and see how the client gets transparently connected to one of the InnoDB cluster instances.


[root@prodserver44 /]# mysqlsh  innob_user@prodserver55:6446
Creating a Session to 'innob_user@prodserver55:6446'
Enter password:
Your MySQL connection id is 57
Server version: 5.7.21-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL Shell 1.0.11

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

Putting the InnoDB Cluster to for High Availability


To test if high availability works, simulate an unexpected halt by killing an instance. The cluster detects the fact that the instance left the cluster and reconfigures itself. Exactly how the cluster reconfigures itself depends on whether you are using a single-primary or multi-primary cluster, and the role the instance serves within the cluster.


If the current primary leaves the cluster, one of the secondary instances is elected as the new primary, with instances prioritized by the lowest server_uuid. MySQL Router redirects read-write connections to the newly elected primary.

If a current secondary leaves the cluster, MySQL Router stops redirecting read-only connections to the instance.

There are various ways to simulate an instance leaving a cluster, for example you can forcibly stop the MySQL server on an instance, or use the AdminAPI dba.killSandboxInstance() if testing a sandbox deployment. 

mysql-js> dba.killSandboxInstance(3310)

Login to an instance via mysql router 

[root@prodserver44 mysqlrouter]#  mysqlsh  innob_user@prodserver55:6446
Creating a Session to 'innob_user@prodserver55:6446'
Enter password:
Your MySQL connection id is 46
Server version: 5.7.21-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL Shell 1.0.11

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| prodserver55 |
+--------------+
1 row in set (0.00 sec)
mysql-sql> select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)
mysql-sql> use testdb ;
Query OK, 0 rows affected (0.00 sec)
mysql-sql> select * from testsample;
+----+-----------+----------+------------------+--------------------+
| id | firstname | lastname | email            | reg_date           |
+----+-----------+----------+------------------+--------------------+
|  1 | John      | Doe      | john@example.com | 2000-02-13 1:22:23 |
|  2 | Harry     | Potter   | john@example.com| 2002-00-00 0:00:00  |
|  3 | Arav      | Paoe     | john@example.com | 2012-02-13 1:22:23 |
|  4 | Micheael  | Vio      | john@example.com | 2018-03-13 1:22:23 |
|  5 | Asibo     | Dildon   | john@example.com | 2017-02-13 1:22:23 |
|  6 | Venu      | Gopal    | john@example.com | 2018-01-13 1:22:23 |
|  7 | Umman     | Chandy   | john@example.com | 2018-07-13 1:22:23 |
+----+-----------+----------+------------------+--------------------+
7 rows in set (0.01 sec)
mysql-sql>

Here we will stop the mysql instance in prodserver55:3306 

[root@prodserver55 mysqlrouter]# systemctl stop mysqld
[root@prodserver55 mysqlrouter]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html

Feb 16 16:48:35 prodserver55 systemd[1]: Started MySQL Server.
Feb 16 17:08:42 prodserver55 systemd[1]: Stopping MySQL Server...
Feb 16 17:08:54 prodserver55 systemd[1]: Stopped MySQL Server.

MySQL Router status :

mysql-sql> select * from testsample;
ERROR: 2013 (HY000): Lost connection to MySQL server during query
The global session got disconnected.
Attempting to reconnect to 'innob_user@prodserver55:6446'..
The global session was successfully reconnected.
mysql-sql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| prodserver11 |
+--------------+
1 row in set (0.01 sec)
mysql-sql> select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)
mysql-sql> use testdb ;
Query OK, 0 rows affected (0.00 sec)
mysql-sql> select * from testsample;
+----+-----------+----------+------------------+--------------------+
| id | firstname | lastname | email            | reg_date           |
+----+-----------+----------+------------------+--------------------+
|  1 | John      | Doe      | john@example.com | 2000-02-13 1:22:23 |
|  2 | Harry     | Potter   | john@example.com| 2002-00-00 0:00:00  |
|  3 | Arav      | Paoe     | john@example.com | 2012-02-13 1:22:23 |
|  4 | Micheael  | Vio      | john@example.com | 2018-03-13 1:22:23 |
|  5 | Asibo     | Dildon   | john@example.com | 2017-02-13 1:22:23 |
|  6 | Venu      | Gopal    | john@example.com | 2018-01-13 1:22:23 |
|  7 | Umman     | Chandy   | john@example.com | 2018-07-13 1:22:23 |
+----+-----------+----------+------------------+--------------------+
7 rows in set (0.01 sec)

Cluster status :

mysql-js> var cluster = dba.getCluster()
mysql-js> cluster.status()
{
    "clusterName": "prodcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "prodserver11:3306",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active",
        "topology": {
            "prodserver55:3306": {
                "address": "prodserver55:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "(MISSING)"
            },
            "prodserver11:3306": {
                "address": "prodserver11:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "prodserver22:3306": {
                "address": "prodserver22:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    }
}

Describing Structure of InnoDB Cluster  

To get information about the structure of the InnoDB cluster itself, use the cluster.describe() function:

mysql-js> cluster.describe();
{
    "clusterName": "prodcluster",
    "defaultReplicaSet": {
        "instances": [
            {
                "host": "prodserver55:3306",
                "label": "prodserver55:3306",
                "role": "HA"
            },
            {
                "host": "prodserver11:3306",
                "label": "prodserver11:3306",
                "role": "HA"
            },
            {
                "host": "prodserver22:3306",
                "label": "prodserver22:3306",
                "role": "HA"
            }
        ],
        "name": "default"
    }
}

Removing instances from database cluster 

This can be done with the removeInstance() method, as in the following example:

mysql-js> cluster.removeInstance('innob_user@prodserver55:3306')

How To Rejoin A Node To The Cluster?
If an instance leaves the cluster, for example because it lost connection and did not or could not automatically rejoin the cluster, it might be necessary to rejoin it to the cluster at a later stage. To rejoin an instance to a cluster issue cluster.rejoinInstance().

First validate the instance using  dba.validateInstance()
mysql-js> dba.validateInstance('innob_user@prodserver55:3306')

Once the instance is valid for cluster usage issue below command 

mysql-js> cluster.rejoinInstance('innob_user@prodserver55:3306');

Then connect to the instance, run MySQL Shell locally and issue dba.configureLocalInstance(). This ensures the InnoDB cluster configuration is persisted to the instance's option file to enable it to rejoin the cluster automatically.

Restoring a Cluster from Quorum Loss

If a instance (or instances) fail, then a cluster can lose its quorum, which is the ability to vote in a new primary. In this case you can re-establish quorum using the method cluster.forceQuorumUsingPartitionOf(), as shown in the following MySQL Shell example:

mysql-js> var cluster =  dba.getCluster("prodcluster")
  // The cluster lost its quorum and its status shows
  // "status": "NO_QUORUM"


mysql-js> cluster.forceQuorumUsingPartitionOf("innob_user@prodserver55:3306")

Rebooting a Cluster from a Major Outage
If your cluster suffers from a complete outage, you can ensure it is reconfigured correctly using dba.rebootClusterFromCompleteOutage(). In the event that a cluster has completely stopped, the instances must be started and only then can the cluster be started. 

Make sure that all the three instances are up and running 
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 516d2ea6-0d94-11e8-825b-000c2958682d | prodserver11 |        3306 | OFFLINE      |
| group_replication_applier | d4786d8c-0d9e-11e8-b10c-000c2958682d | prodserver22 |        3306 | OFFLINE      |
| group_replication_applier | da2d0b92-0d8a-11e8-bc4c-000c2958682d | prodserver55 |        3306 | OFFLINE      |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql-js> var i1='innob_user@prodserver55:3306'
mysql-js>  shell.connect(i1)
mysql-js> dba.rebootClusterFromCompleteOutage('prodcluster')


mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 516d2ea6-0d94-11e8-825b-000c2958682d | prodserver11 |        3306 | ONLINE       |
| group_replication_applier | d4786d8c-0d9e-11e8-b10c-000c2958682d | prodserver22 |        3306 | ONLINE       |
| group_replication_applier | da2d0b92-0d8a-11e8-bc4c-000c2958682d | prodserver55 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.00 sec)

Also verify the cluster status using status() command 
mysql-js> var cluster = dba.getCluster()
mysql-js> cluster.status()

Rescanning a Cluster

If changes to an instance's configuration are made without using AdminAPI, you need to rescan the cluster to update the InnoDB cluster metadata. 
Rescan the cluster with cluster.rescan() to update the InnoDB cluster metadata.

Checking Instance State
The cluster.checkInstanceState() function can be used to to verify the existing data on an instance does not prevent it from joining a cluster. 

mysql-js> cluster.checkInstanceState('innob_user@prodserver55:3406')


Please provide the password for 'innob_user@prodserver55:3406':
Analyzing the instance replication state...

The instance 'prodserver55:3406' is valid for the cluster.
The instance is fully recoverable.

{
    "reason": "recoverable",
    "state": "ok"
}

The output of this function can be one of the following:

OK new: the instance has not executed any GTID transactions, therefore it cannot conflict with the GTIDs executed by the cluster.

OK recoverable: the instance has executed GTIDs which do not conflict with the executed GTIDs of the cluster seed instances.

ERROR diverged: the instance has executed GTIDs which diverge with the executed GTIDs of the cluster seed instances.

ERROR lost_transactions: the instance has more executed GTIDs than the executed GTIDs of the cluster seed instances.

Dissolving an InnoDB Cluster
To dissolve an InnoDB cluster you connect to a read-write instance, for example the primary in a single-primary cluster, and use the Cluster.dissolve() command. 

mysql-js> var cluster = dba.getCluster()
mysql-js> cluster.dissolve({force:true})

Sample my.cnf 
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
default-time-zone=SYSTEM
server_id=101
innodb_file_per_table=1
log-bin=mysql-binlog
relay-log=relay-log
binlog_format= ROW
bind-address = 0.0.0.0
slow_query_log=ON
long_query_time=0
innodb_monitor_enable=all
performance_schema=ON
report-host=prodserver55
report-port = 3306
log-slave-updates=1
gtid-mode=ON
enforce-gtid-consistency=ON
master-info-repository=TABLE
relay-log-info-repository=TABLE
transaction-write-set-extraction=XXHASH64
slave-parallel-workers=3
slave-preserve-commit-order=1
slave-parallel-type=LOGICAL_CLOCK
binlog_checksum=NONE
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
port=3306
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_timestamps= SYSTEM
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
group_replication_start_on_boot = ON
group_replication = ON
group_replication_allow_local_disjoint_gtids_join = OFF
group_replication_allow_local_lower_version_join = OFF
group_replication_auto_increment_increment = 7
group_replication_bootstrap_group = OFF
group_replication_components_stop_timeout = 31536000
group_replication_compression_threshold = 1000000
group_replication_enforce_update_everywhere_checks = OFF
group_replication_flow_control_applier_threshold = 25000
group_replication_flow_control_certifier_threshold = 25000
group_replication_flow_control_mode = QUOTA
group_replication_force_members
group_replication_group_name = d0187a28-0dc8-11e8-aa4c-000c2958682d
group_replication_group_seeds = prodserver55:13406,prodserver55:13506
group_replication_gtid_assignment_block_size = 1000000
group_replication_ip_whitelist = AUTOMATIC
group_replication_local_address = prodserver55:13306
group_replication_member_weight = 50
group_replication_poll_spin_loops = 0
group_replication_recovery_complete_at = TRANSACTIONS_APPLIED
group_replication_recovery_reconnect_interval = 60
group_replication_recovery_retry_count = 10
group_replication_recovery_ssl_ca
group_replication_recovery_ssl_capath
group_replication_recovery_ssl_cert
group_replication_recovery_ssl_cipher
group_replication_recovery_ssl_crl
group_replication_recovery_ssl_crlpath
group_replication_recovery_ssl_key
group_replication_recovery_ssl_verify_server_cert = OFF
group_replication_recovery_use_ssl = ON
group_replication_single_primary_mode = ON
group_replication_ssl_mode = REQUIRED
group_replication_transaction_size_limit = 0
group_replication_unreachable_majority_timeout = 0
auto_increment_increment = 1
auto_increment_offset = 2
[client]
socket=/var/lib/mysql/mysql.sock
port=3306
protocol=TCP

Group Replication Flow Control 

When using MySQL Group Replication, it’s possible that some members are lagging behind the group. Due to load, hardware limitation, etc… This lag can become problematic to keep good certification behavior regarding performance and keep the possible certification failure as low as possible. 

Every member of the Group send some statistics about its queues (applier queue and certification queue) to the other members. Then every node decide to slow down or not if they realize that one node reached the threshold for one of the queue:

group_replication_flow_control_applier_threshold   (default is 25000)
group_replication_flow_control_certifier_threshold (default is 25000)

When group_replication_flow_control_mode is set to QUOTA on the node seeing that one of the other members of the cluster is lagging behind (threshold reached), it will throttle the write operations to the the minimum quota. This quota is calculated based on the number of transactions applied in the last second, and then it is reduced below that by subtracting the “over the quota” messages from the last period.

Handling Slowness due to Flow Control
In the performance_schema.replication_group_member_stats table, you have the mount of transaction in the apply queue (COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE):

We can use the below Query 

mysql> SELECT MEMBER_HOST, COUNT_TRANSACTIONS_IN_QUEUE TRX_LOCAL_Q, 
              COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE TRX_APPLY_Q 
       FROM performance_schema.replication_group_member_stats t1 
       JOIN performance_schema.replication_group_members t2 
         ON t2.MEMBER_ID=t1.MEMBER_ID;
+-------------+-------------+-------------+
| MEMBER_HOST | TRX_LOCAL_Q | TRX_APPLY_Q |
+-------------+-------------+-------------+
| mysql1      |           0 |           0 |
| mysql3      |           0 |        8415 |
| mysql2      |           0 |           0 |
+-------------+-------------+-------------+

Also find the SYS Schema view from lefred blog 
select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | NO        |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+

You can download the file also from here

When the incident is too long and flow controls starts to kick in, all the cluster will start slowing down… if this is an incident expected to be long to solve or a maintenance, the first thing we would like to do is to stop sending MySQL traffic to the node (reads and/or writes in case of Multi-Primary cluster). Then we want that the specific node stops sending its statistics to the other nodes.

We can stop sending its statistics related to the flow control between the nodes :


mysql> set global group_replication_flow_control_mode='DISABLED';

Now that this node won’t trigger any flow control anymore, the cluster will run as its optimal speed and this provides you extra time to fix the problem or finish the maintenance. 

As soon as the maintenance is finished and the queue recovered, you can set it back to ‘QUOTA’. 


Read More