Step by step guide to set up Multi source replication in MariaDB

Expectations:
1. You should be knowing how to set up replication in MySQL
2. You have basic knowledge of MySQL components such as binary logs, relay logs, mysqldump

Multisource Replication

 Multi-source Replication means that one server has many masters from which it replicates. This can be one or more schema from each master instances. Introduced in MariaDB 10.0

My Environment 

 I have multiple servers in my production setup. There are different flavors of MySQL (Percona, MariaDB, MySQL Enterprise edition) running in servers.  Each servers has its own Slaves and the slaves are read only. Applications are connecting to both master and slaves.
Here I am taking two server sets for this example : Core, Messaging

My Requirement

I need to setup a MySQL instance where All schema from Core (cdb1, cdb2 , cdb3)  and 1 schema from Messaging ( mdb1 out of mdb1, mdb2 and mdb3)  has to be replicated for Operational reporting.

Implemantation

 As we know the requirement and understood the environment we can now start implementing the multi-source replication.

1. Download and install MariaDB. 
https://mariadb.com/kb/en/the-mariadb-library/yum/

2. Backup and restore the required schema from each server

mysqldump can be used to take the backup.Do remember to give the option --master-data=2 --single-transaction While taking the dump.

NOTE:  --master-data will impose a global read lock for a short period of time. Read the document carefully.
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_master-data

mysqldump -uroot -p --triggers --routines --events --master-data=2 --single-transaction --databases cdb1 cdb2 cdb3 > coredbs.sql

Alternative option avoid interruption in the master server :
Take backup from a slave which is not used by application. Setup replication from slave to the reporting server we configure now. (log-slave-updates should be set to True for this). Later you can change replication master to the actual master server by stopping the slave for a small duration.
ie, Stop the slave; get the binary log position of the slave; stop the slave in reporting server; change master with the new binary log position and mater as the actual master.

3. Create the replication user for the new slave in the Master database instances with proper privileges. 

CREATE USER 'repl_user'@'repserver.myfirm.com' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'repserver.myfirm.com';


4. Get the binary log position from the dump file.
Binary log position will be there in first section of the dump file OR search for "CHANGE MASTER"  in the dump file

 grep -i "CHANGE MASTER" coredbs.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=2292;

5. Set replication client for different databases with appropriate connection name.

CHANGE MASTER 'core_slave' TO MASTER_HOST="repserver.myfirm.com", MASTER_USER="repl_user", MASTER_PASSWORD="password", MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=2292

Where core_slave is the connection name which is used to identify the connection. It can be any string of your choice.
Do same (step 4 and 5)  for the messaging server as well.

6. Set schemas to be replicated. 

In our case, we are replicating all databases from 'core' and only one out of three databases from 'messaging'. So we will set to replicate only one database that is mdb1 to be replicated from messaging_slave where messaging_slave is the connection name for the messaging slave.

Edit /etc/my.cnf file to add the following.

messaging_slave.replicate_do_db=mdb1

If you just want to ignore only one schema from one server, then we can use the below command.
messaging_slave.replicate_ignore_db=mdb1

Restart the mysql instance to make the changes effect.

service mysql restart

7. Start the slaves.

MariaDB [(none)]> start all slaves;

 MariaDB [(none)]> show all slaves status \G

 You are done. You can see the output with all slaves status

 MariaDB [(none)]> show all slaves status \G
*************************** 1. row ***************************
              Connection_name: core_slave
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: coredb.myfirm.com
                  Master_User: repl_userl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 2292
               Relay_Log_File: mysql_relay-core_slave.000015
                Relay_Log_Pos: 526
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2292
              Relay_Log_Space: 526
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 101
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
      Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
         Retried_transactions: 0
           Max_relay_log_size: 1073741824
         Executed_log_entries: 1256
    Slave_received_heartbeats: 1
       Slave_heartbeat_period: 30.000
               Gtid_Slave_Pos:
*************************** 2. row ***************************
              Connection_name: messaging
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: messaging_db.myfirm.com
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.014108
          Read_Master_Log_Pos: 9973820
               Relay_Log_File: mysql_relay-shared.005953
                Relay_Log_Pos: 8624388
        Relay_Master_Log_File: mysql-bin.014108
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mdb1
          Replicate_Ignore_DB: 
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 9973820
              Relay_Log_Space: 40135315
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 102
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
      Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
         Retried_transactions: 0
           Max_relay_log_size: 1073741824
         Executed_log_entries: 1175801050
    Slave_received_heartbeats: 59
       Slave_heartbeat_period: 30.000
               Gtid_Slave_Pos:
2 rows in set (0.00 sec)

 MariaDB [(none)]>


To understand more commands on how to manage the multi source replication
http://alidba.blogspot.com/2017/08/Maria-commands.html

To setup monitoring in zabbix for multisource replication
http://alidba.blogspot.com/2017/08/multisource-replication-zabbix.html

Posted by No comments:

InnoDB tablespace Encryption



InnoDB tablespace encryption uses a two tier encryption key architecture, consisting of a master encryption key and tablespace keys. When an InnoDBtable is encrypted, a tablespace key is encrypted and stored in the tablespace header. When an application or authenticated user wants to access encrypted tablespace data, InnoDB uses a master encryption key to decrypt the tablespace key. 
The InnoDB tablespace encryption feature relies on a keyring plugin for master encryption key management.
Configuration:
 The keyring file plugin has to be loaded before the innoDB engine starts, so instead of installing the plugin we have to use early-plugin-load parameter in  my.cnf
 Create the directory and  make required changes in my.cnf

vi /etc/my.cnf

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/path/to/file/keyringfile

Make sure the path exists and mysql is the owner

The file keyring_file.so should be available in the directory pointed by the parameter plugin_dir.
Location defined by  keyring_file_data  will be used to store the master encryption key data in a keyring file, which is required only while starting the database.

service mysql restart

To check the plugin status:
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'keyring%';
Encrypting the Keyring 
 InnoDB tablespace encryption need the keyring only at startup of the mysql instance. So key is kept encrypted and is decrypted only for starting up the mysql instances.
We can use gpg encryption to accomplish this this. 
Command to encrypt: 
gpg2 -o keyring.gpg -c --cipher-algo AES256 keyring
where keyring is the actual keyring and keyring.gpg is the encrypted file. Passphrase given for encrypting the key ring has to be kept in the password vault. 
Command to decrypt:
 gpg2 -o keyring -d keyring.gpg
where keyring is the actual keyring and keyring.gpg is the encrypted file. Passphrase would given from the password vault. 


======================================================================

How does encryption work:
mysql> create table secret (name varchar(30));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into secret values ('secret name');
Query OK, 1 row affected (0.00 sec)

[root@ip-10-10-45-19 oploc]# strings secret.ibd
infimum
supremum
secret name


mysql> alter table secret  encryption='Y';
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

[root@ip-10-10-45-19 oploc]# strings secret.ibd
5f4889a6-65bc-11e4-9f98-129030d4e0d6
pLBJ
Xpxk
QmC"E~U
M _8
b3iix
<r=r|
w]l\
<l|9
CzOr
_K7o
|<|R
>snm
|JT<gU
=&3Z
ZHNj

To select the encrypted tables:
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES  WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';

Xtrabackup:

Backup: Normal backup commands (provided the xtrabackup version > =2.4.4)
Ex:   innobackupex --slave-info --user=uuuuu --password=xxxx  --socket=/mysql/mysqld.sock --compress --stream=xbstream --compress-threads=4 --parallel=4 ./ > /Backup/fullb.xbstream.qpress
Prepare: have to use xtrabackup --prepare, not innobackupex –apply-log
Ex: xtrabackup --prepare --use-memory=20G --target-dir=/mysql/restore --keyring-file-data=/path/to/file/keyring
Restore: Normal move-back / copy-back
Ex: innobackupex --move-back =/mysql/restore

Before starting the Database, the keyring file from the source has to be copied to the destination’s keyring file location ; and if before encrypting any new table in the destination server, master key has to be rotated and backup of the keyring file has to be taken

REF:

Posted by No comments:

MariaDB commands for Multisource replication

Command Reference for Multi source replication
To work with normal commands without specifying the connection name in the command line, set the default database with the following command. (this will be useful to fix issues in particular connection )
 set default_master_connection=connection_name;
Posted by No comments:

Configuring Zabbix for MySQL in RHEL

Monitoring the database server is very important. Percona has released plugin for monitoring mysql database. We will see how to configure it in step by step

0. I assume you already have a zabbix server otherwise click here to see how to configure zabbix server
in the below explanations zabbix server means the server where you have configured the zabbix
Database server is the server you want to monitor and it is  where you are going to install the client softwares.

1. Find the version of the zabbix software in the zabbix server.


# zabbix_agent --version
Zabbix agent v2.4.7 (revision 56694) (12 November 2015)
Compilation time: Nov 13 2015 05:35:30

2. Download the zabbix and zabbix agent softwares

Check for the same version of zabbix and zabbix_agent from the repository and download it
http://repo.zabbix.com/zabbix/

in my case, I will download the below files

zabbix-2.4.7-1.el6.x86_64.rpm
zabbix-agent-2.4.7-1.el6.x86_64.rpm

3. Install the rpms

# yum install -y zabbix-2.4.7-1.el6.x86_64.rpm
# yum install -y zabbix-agent-2.4.7-1.el6.x86_64.rpm


4. Now we do some thing which we cannot find in the documentation this will help us configuring the mysql monitoring.

The installation will create a user zabbix with nolog

# mkdir -p /home/zabbix
# chown -R zabbix:zabbix /home/zabbix

edit /etc/passwd to give log in for the user zabbix and change home dir

example line:
zabbix:x:498:498:Zabbix Monitoring System:/home/zabbix:/bin/bash

5. switch user to zabbix and copy the configuration file to home

# su - zabbix
# cp -r /etc/zabbix/ ~/
 edit /home/zabbix/zabbix/zabbix_agentd.conf and change the following parameters

Server=IP of Zabbix Server
ServerActive=IP of Zabbix Server
Hostname=use the FQDN of the database server

6. Start zabbix agent with the following command as zabbix user

# zabbix_agentd -c /home/zabbix/zabbix/zabbix_agentd.conf

7. Log in to the zabbix server and do telnet to the database server through the zabbix port and see the connection is fine

telnet database_server_ip 10050

if not working, edit the iptables to allow connection

# iptables -A INPUT -p tcp -m tcp --dport 10050 -j ACCEPT

You have configured the zabix agent now. Let us add the host in the zabbix server
----------------------------------------------------------------------------------

8. log in to the zabbix console and add the host

Go to Configuration -> Hosts -> Create Host -> Host tab and fill the Host name field with the FQDN of the monitored zabbix agent machine,
use the same value as above for Visible name field.
Next, add this host to a group of monitored servers and use the IP Address of the monitored machine at Agent interfaces field –
alternatively you can also use DNS resolution if it’s the case. Use the below screenshots as a guide.

Next, move to Templates tab and hit Select. A new window with templates should open. Choose Template OS Linux then scroll down
and hit on Select button to add it and automatically close the window.
Once the template appears to Link new template box, hit on Add text to link it to zabbix server,
then hit on the lower Add button to finish the process and completely add the monitored host.
The visible name of the monitored host should now appear hosts window.

That’s all! Just assure that the host Status is set to Enabled and wait a few minutes in order for Zabbix server to contact the agent,
process the received data and inform or eventually alert you if something goes bad on the monitored target.

================================================================
Our server is added in the zabbix now, let us add percona plugin for monitoring the database

9. Go to database server

get the percona repo and install the plugin

 # yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
 # yum install -y percona-zabbix-templates

 also we need additional softwares for the pluggin
 # yum install -y php
 # yum install -y php-mysql

10. get the conf file and edit the configuration file to include the same.

# mkdir ~zabbix/zabbix_agentd.conf.d/
# cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /home/zabbix/zabbix_agentd.conf.d/userparameter_percona_mysql.conf

# vi /home/zabbix/zabbix/zabbix_agentd.conf

add the following line in the Include section
Include=/home/zabbix/zabbix_agentd.conf.d/

11. Create the .cnf file for keeping the mysql user and password
# vi /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf
<?php
$mysql_user = 'zabbix';
$mysql_pass = 'zabbix_password';

12. create .cnf file in zabbix home

# vi ~/zabbix/.my.cnf
[client]
user = zabbix
password = zabbix_password

13. Create user zabbix and give privileges
 MySQL> create user zabbix@localhost identified by 'zabbix_password';
 Query OK, 0 rows affected (0.00 sec)
 mysql> GRANT RELOAD, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'zabbix'@'localhost';
 Query OK, 0 rows affected (0.00 sec)

14. Now test the checks
# /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh running-slave
0
Should return 0 or 1 but not the “Access denied” error.


# /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg
 405647

 you sould get some number. error will be as given below
 ERROR: run the command manually to investigate the problem: /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg

 After running that

# /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg
ERROR: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

If mysql is runnig on a non default port(3306), you will get the above error. then the simplest solution is get your actual socket
and create a soft like to /var/lib/mysql/mysql.sock

# grep socket /etc/my.cnf
socket = /mysqldata/mysql.sock

# ln -s /mysqldata/mysql.sock /var/lib/mysql/mysql.sock

15. Add the percona template to the host

Goto zabbix GUI configuration->hosts and click on the host we have added now
go to template tab, search for percona , add the template and update the server

To know How to add percona template in server, click here

Posted by No comments:

Monitor Multi source Replication in Zabbix

It is very easy to add a new check in Zabbix.

Even though there are hundreds of templates available for monitoring using zabbix, we may still need to monitor something special in our environment. You may be able to write a script for this purpose, but you will need to add that to the standardized monitoring tool you have in your organization. There are multiple ways to add a new check in zabbix monitoring, I found it very easy using UserParameter.

I will show you How did I configured a new check in zabbix for monitoring multi source  replication in maria DB.


My Requirement:
I have four slaves running in a Maria DB instance. I want to monitor if any of the threads are not running, and I should get a pager if any thread is down.

The output of the below command should be 0 at any point. More than zero means there is a failure

mysql -umysql_backup -pxxxx -e"show all slaves status \G"| grep "Running: No"| wc -l

Implementation:

Go to the server to be monitored, My case it is mysqlslave.myfirm.com

Edit the zabbix configuration file. ( location may be different in your environment)

vi /etc/zabbix/zabbix_agentd.conf

Add a line like the below one.

UserParameter=mysql.slaves_count,mysql -umysql_backup -pxxxx -e"show all slaves status \G" | grep "Running: No" | wc -l

mysql.slaves_count is an unique identifier. It can be any string. This key will be used in zabbix server.

Now restart the zabbix agent to make changes effective.

Once the agent is restarted go to zabbix User Interface.  Go to Configuration  --> Hosts (and select your host to be monitored) -- > Items

Add new item with Key=mysql.slaves_count  (this is the key gave in agent config file) to the monitored host. Type of the item must be either Zabbix Agent or Zabbix Agent (active).

Zabbix will now start monitoring the item and you will get the value in the monitoring screen.

As I need zabbix to trigger an alert where the value of the out put is greater than zero, I will add a trigger of the host with below expression.
Go to Configuration  --> Hosts (and select your host to be monitored) -- > Trigger
 Expression:
{mysqlslave.myfirm.com:mysql.slaves_count.last(0)}>1

Remember to Give a proper name for the trigger to understand the incident from the trigger name. I gave name as Failed_tread_count_in_{HOST.NAME}

Remember to select proper severity for the trigger so that the expected "actions" to be trirgered. I have pagerduty media type for High severity hence I selected high here. (it depends on your configuration)

REF:
https://www.zabbix.com/documentation/1.8/manual/tutorials/extending_agent
Posted by No comments:

Add a New User Defined Check in Zabbix

It is very easy to add a new check in Zabbix.

Even though there are hundreds of templates available for monitoring using zabbix, we may still need to monitor something special in our environment. You may be able to write a script for this purpose, but you will need to add that to the standardized monitoring tool you have in your organization. There are multiple ways to add a new check in zabbix monitoring, I found it very easy using UserParameter.

I will show you How did I configured a new check in zabbix for monitoring multi source  replication in maria DB.


My Requirement:
I have four slaves running in a Maria DB instance. I want to monitor if any of the threads are not running, and I should get a pager if any thread is down.

The output of the below command should be 0 at any point. More than zero means there is a failure

mysql -umysql_backup -pxxxx -e"show all slaves status \G"| grep "Running: No"| wc -l

You can create a command like this for anything you want to monitor, and it should be returning a number or character.

Implementation:

Go to the server to be monitored, My case it is mysqlslave.myfirm.com

Edit the zabbix configuration file. ( location may be different in your environment)

vi /etc/zabbix/zabbix_agentd.conf

Add a line like the below one.

UserParameter=mysql.slaves_count,mysql -umysql_backup -pxxxx -e"show all slaves status \G" | grep "Running: No" | wc -l

mysql.slaves_count is an unique identifier. It can be any string. This key will be used in zabbix server.

Now restart the zabbix agent to make changes effective.

Once the agent is restarted go to zabbix User Interface.  Go to Configuration  --> Hosts (and select your host to be monitored) -- > Items

Add new item with Key=mysql.slaves_count  (this is the key gave in agent config file) to the monitored host. Type of the item must be either Zabbix Agent or Zabbix Agent (active).

Zabbix will now start monitoring the item and you will get the value in the monitoring screen.

As I need zabbix to trigger an alert where the value of the out put is greater than zero, I will add a trigger of the host with below expression.
Go to Configuration  --> Hosts (and select your host to be monitored) -- > Trigger
 Expression:
{mysqlslave.myfirm.com:mysql.slaves_count.last(0)}>1

Remember to Give a proper name for the trigger to understand the incident from the trigger name. I gave name as Failed_tread_count_in_{HOST.NAME}

Remember to select proper severity for the trigger so that the expected "actions" to be trirgered. I have pagerduty media type for High severity hence I selected high here. (it depends on your configuration)

REF:
https://www.zabbix.com/documentation/1.8/manual/tutorials/extending_agent
Posted by No comments:

Table 'mysql.gtid_slave_pos' doesn't exist, Internal MariaDB error code: 1146


After setting up the replication in Maria DB, I was seeing error message in the error log and show slave status\G out put and the replication is working.

ERROR:

[ERROR] Slave I/O: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist, Internal MariaDB error code: 1146



So I tried a `mysql_upgrad` which told me to try --force as it looked already up to date, so I tried --force, which gave:
mysql.gtid_slave_pos
Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status : Operation failed

I checked in the db/mysql folder and found that the .frm and .ibd is aready existing.
gtid_slave_pos.frm
gtid_slave_pos.ibd

I just removed the files and recreated the table with MyISAM engine, which solved the issue.

STEPS:

 Go to the mysql folder ( default path in column store /usr/local/mariadb/columnstore/mysql/db/mysql )
drop the following files:

rm gtid_slave_pos.frm
rm gtid_slave_pos.ibd

mysql -uroot -p -s mysql

 MariaDB [mysql]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 156.23.25.15
                  Master_User: mysqlrepli
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000465
          Read_Master_Log_Pos: 809269330
               Relay_Log_File: relay.000190
                Relay_Log_Pos: 790004371
        Relay_Master_Log_File: mysql-bin.000465
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1932
                   Last_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 809269330
              Relay_Log_Space: 790004710
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1932
               Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
      Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
                Parallel_Mode: conservative
1 row in set (0.00 sec)


MariaDB [mysql]> CREATE TABLE `gtid_slave_pos` (
    ->   `domain_id` int(10) unsigned NOT NULL,
    ->   `sub_id` bigint(20) unsigned NOT NULL,
    ->   `server_id` int(10) unsigned NOT NULL,
    ->   `seq_no` bigint(20) unsigned NOT NULL,
    ->   PRIMARY KEY (`domain_id`,`sub_id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';
Query OK, 0 rows affected (0.01 sec)


 MariaDB [mysql]> stop slave;
Query OK, 0 rows affected (0.03 sec)

 MariaDB [mysql]> start slave;
Query OK, 0 rows affected (0.00 sec)

show slave status \G

 MariaDB [mysql]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 156.23.25.15
                  Master_User: mysqlrepli
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000465
          Read_Master_Log_Pos: 809289283
               Relay_Log_File: relay.000191
                Relay_Log_Pos: 15483
        Relay_Master_Log_File: mysql-bin.000465
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 809289283
              Relay_Log_Space: 790022182
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
      Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
                Parallel_Mode: conservative
1 row in set (0.00 sec)



 Hope it helps
Posted by 9 comments:

How to check Version of Maria Column Store


To check the version of mysql in Maria DB Column Store, command  is mysql --version  or mysql>SELECT VERSION();,  but to check the version of  ColumnStore, we have to check the a file in the install folder.

In a default installation, the file will be, 
/usr/local/mariadb/columnstore/releasenum
Posted by 1 comment:
Subscribe to: Posts (Atom)