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:

Script to Move objects from one tablespace to other

There are many occasions where we will move segments from one tablespace to another like reclaim unused space in the segments after a deleting rows.

We can create scripts to move different type of segments from one tablespace to another using following SQL commands.

Here the scripts will be created to move objects (actually segments) from the tablespacce OLD_USERS to NEW_USERS. You can change the tablespace names accordingly.

Moving Tables and Un-partitioned Indexes from one tablespace to another
set lines 200
set pages 0
set feedback off
spool move.sql
select 
'alter ' || segment_type || ' ' || owner || '.' || segment_name || decode( segment_type, 'TABLE', ' move', ' rebuild' ) ||
' tablespace NEW_USERS' || ';'
from dba_segments
where tablespace_name='OLD_USERS'  
and segment_type in('TABLE','INDEX') order by owner,segment_type desc,segment_name;
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move all tables from one tablespace to another and to rebuild all indexes from one partition to another.
@move.sql

Moving index partitions from one tablespace to another
set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter index '|| owner||'.'||segment_name || ' rebuild partition '|| partition_name ||' tablespace NEW_USERS;' 
from dba_segments where segment_type ='INDEX PARTITION' and tablespace_name='OLD_USERS';
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move or rebuild all index partitions from one tablespace to another
@move.sql

Moving index sub-partitions from one tablespace to another
set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter index '||owner||'.'||segment_name ||' rebuild subpartition ' || partition_name ||' tablespace NEW_USERS;'       
from dba_segments where tablespace_name='OLD_USERS' and segment_type = 'INDEX SUBPARTITION';
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move or rebuild all index subpartition segments from one tablespace to another
@move.sql

Moving IOT index segments from one tablespace to another(When only IOT indexes left on the tablespace)

set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter table '||owner||'.'|| table_name || ' move tablespace NEW_USERS;' from dba_indexes where index_name in (
select  segment_name from dba_segments
where tablespace_name='OLD_USERS' and segment_type='INDEX');
spool off
 
Now edit the file move.sql to remove first few lines which will be the command above and run it to move all IOT segments form one tablespace to another
@move.sql

Moving LOB segments from one tablespace to another
set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter table '|| owner || '.' ||table_name|| ' move LOB ('||COLUMN_NAME ||') STORE AS (tablespace NEW_USERS);'
from DBA_LOBS where tablespace_name='OLD_USERS';
spool off

Now edit the file move.sql to remove first few lines which will be the command above and run it to move all LOB segments from one tablespace to another
@move.sql


There could be special objects like AQ (Advanced Queueing)tables. Read here to lean how to move AQ tables from one tablespace to another.

Posted by 5 comments:

How to start oracle automatically after a system restart

Here I am explaining the simplest way to start oracle database and listener automatically after a restart of the server. This will be the best method to implement on your test or development server.

When you do a search on how to start oracle database automatically after a system restart, you will find out the method to start oracle database and the corresponding listener by editing the oratab file and creating scripts in init.d directory. That method is the traditional one and it will start the database along with the system reboot. It will result in a long reboot time for the server depending up on the size of your database.

In the method described here to start oracle database and listener automatically, the database startup will happen after the system reboot only. So you can do other activities on the server by the time the database gets started.

What are the steps you follow to start the database manually after a system reboot? Just write them in a file to create a script. Then run the script after reboot using crontab. This is my idea. It is very simple right?

Now, lets check how to implement this method to start the oracle database and listener automatically after every server reboot.

Just look into the example given below.
1. create a file (db.sh in /root) with the following content. (changes has to be made with respect to your database environment)

su - oracle <<EOS
export ORACLE_SID=orcl
sqlplus -s /nolog <<SQL
conn / as sysdba
startup
exit
SQL
lsnrctl start
EOS

I create the file with this content because these are the commands I use to start the database and listener manually. I have already given the ORACLE_HOME and other environment variables in the bash_profile of the user.

May the "<<EOS" will not be familiar for you, it says the commands between two EOS has to be run as oracle user. In the same way the commands between two SQL has to be run inside the sqlplus. (instead of EOS and SQL you can give any thing say.. ABC, XYZ)

OK. fine.
Now how to run this script at boot or immediately after the boot so that we can start the database and listener automatically. We have an amazing option in crontab.

execute crotab -e and add the following line to it
@reboot /bin/sh /root/db.sh

 Done!!!
You have configured the database and the listener to start automatically after reboot of the server.
Restart your server to test if it is working. 
Posted by No comments:

Script to rebuild all UNUSABLE indexes in oracle

Index rebuild has to be done in oracle databases for different reasons. A planned index rebuild activity can be done monthly or ewe weekly on all indexes that has a benefit after rebuild.

Read Oracle support note on how to find the candidate index to rebuild for performance benefit.

Another immediate index rebuild is to be done when there are unusable indexes in the database.
Oracle index can become unusable for many reasons. Presence of unusable index will effect DML operations in a large extend. Unusable indexes should be rebuilt immediately they are discovered.
(Read: How does an oracle index become unusable)

If there are only a few indexes unusable, We can rebuild the unusable indexes one by one manually. But is there are a lot of indexes to be rebuild, manually rebuilding each index one by one will be a time consuming activity. Also it will take longer if there are partitioned and sub-partitioned indexes.

The script given below will rebuild all indexes selected by the query
   SELECT OWNER, INDEX_NAME,  INDEX_TYPE, PARTITIONED
   FROM ALL_INDEXES 
   WHERE  STATUS = 'UNUSABLE' 
   ORDER BY 1,4;

 The WHERE condition can be changed to change the candidate index set. For example if you want to rebuild all indexes from a particular schema you can give condition on the column OWNER.

 Find the pl/sql script below just copy paste it to rebuild all unusable indexes including index partitions and index subpartitions in your oracle database.

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting: UNUSABLE index rebuild');
DBMS_OUTPUT.PUT_LINE('UNPARTITIONED INDEXES');
DBMS_OUTPUT.PUT_LINE('-------------------------');
    FOR I IN
    (
            SELECT OWNER,INDEX_NAME FROM ALL_INDEXES 
            WHERE STATUS='UNUSABLE' AND PARTITIONED='NO' ORDER BY 1,2
    )
    LOOP
        BEGIN
              EXECUTE IMMEDIATE 'ALTER INDEX ' || I.OWNER ||'.'|| I.INDEX_NAME || ' REBUILD' ;
              DBMS_OUTPUT.PUT_LINE('REBUILT INDEX '|| I.OWNER ||'.'|| I.INDEX_NAME );
        EXCEPTION
              WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILDING INDEX: '|| I.OWNER ||'.'|| I.INDEX_NAME||' ERROR NUM: '|| SQLCODE ||' ERROR MESSAGE: '||SUBSTR(SQLERRM, 1, 100));
        END;
    END LOOP; 
DBMS_OUTPUT.PUT_LINE('PARTITIONED INDEXES');
DBMS_OUTPUT.PUT_LINE('-------------------------');
    FOR I IN
    (
             SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME FROM ALL_IND_PARTITIONS
             WHERE STATUS='UNUSABLE' AND SUBPARTITION_COUNT=0 ORDER BY 1,2,3
                        )
    LOOP
        BEGIN
             EXECUTE IMMEDIATE 'ALTER INDEX ' || I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' REBUILD PARTITION ' || I.PARTITION_NAME;
             DBMS_OUTPUT.PUT_LINE('REBUILDED INDEX '|| I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' PARTITION ' || I.PARTITION_NAME);
        EXCEPTION
             WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILDING PARTITION: '|| I.INDEX_OWNER ||'.'|| I.INDEX_NAME||':'||I.PARTITION_NAME||' ERROR NUM: '|| SQLCODE ||' ERROR MESSAGE: '||SUBSTR(SQLERRM, 1, 100));
        END;
    END LOOP; 
DBMS_OUTPUT.PUT_LINE('SUBPARTITIONED INDEXES');
DBMS_OUTPUT.PUT_LINE('-------------------------');
    FOR I IN
    (
             SELECT INDEX_OWNER, INDEX_NAME, SUBPARTITION_NAME FROM ALL_IND_SUBPARTITIONS
             WHERE  STATUS='UNUSABLE' ORDER BY 1,2,3
     )
     LOOP
         BEGIN
             EXECUTE IMMEDIATE 'ALTER INDEX ' || I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' REBUILD SUBPARTITION ' || I.SUBPARTITION_NAME;
             DBMS_OUTPUT.PUT_LINE('REBUILDED INDEX '||  I.INDEX_OWNER ||'.'|| I.INDEX_NAME || ' SUBPARTITION ' || I.SUBPARTITION_NAME);
        EXCEPTION
             WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILDING SUBPARTITION: '|| I.INDEX_OWNER ||'.'|| I.INDEX_NAME||':'||I.SUBPARTITION_NAME||' ERROR NUM: '|| SQLCODE ||' ERROR MESSAGE: '||SUBSTR(SQLERRM, 1, 100));
        END;
    END LOOP;
DBMS_OUTPUT.PUT_LINE('END: UNUSABLE index rebuild');               
END;
/

Here in the script only basic offline rebuild is included. Other clauses like ONLINE, PARALLEL, NOLOGGING etc can be given as per your requirement.


Posted by 2 comments:

How to insert & in Oracle table example

I used to get so many calls from developers asking how to insert & into a varchar2 column in oracle?
It is not a difficult question to answer. There are many ways to insert & into a  table column in oracle database.

First of all, if you want to insert simply an &, then have you tried this?

SQL>CREATE TABLE my_table (col1 VARCHAR2(15));
SQL>INSERT INTO my_table VALUES ('&');
1 row created.

It will work perfectly!!. The problem arises when there is something written after &. For example,  When you try to insert the string  'SQL & JAVA' in oracle table, into a varchar2 column,You will get a message 'Enter value for JAVA:'
Do you know why?
Because the character & is set as the define character in oracle by default.
So it defines JAVA as a substitution variable and asks value to be substituted.
You can check it as given below.

SQL> show define
define "&" (hex 26)

Let us move into the topic of how to insert & in oracle table. You have different ways to insert & into oracle table. Here I am explaining them.

1. Put nothing after &

When there is something after &, oracle defines it as a substitution variable(because & means define), and will ask for entering a value for the variable when you execute the command.
Here, I need to insert the string 'SQL & JAVA', then how can I put nothing after &? how will I add 'JAVA' to the string?
 I will split the string into two, 'SQL &' and ' JAVA', and then I will concatenate them. as given in below example.

SQL>INSERT INTO my_table VALUES('SQL &' || ' JAVA');
1 row created.

2.Unset the define character

As I have shown above, & is the default define character. We can unset it and insert & in any oracle table.
Here is the example:

SQL> set define off
SQL>INSERT INTO my_table VALUES('SQL & JAVA');
1 row created.
SQL> set define &

The last line is added to set the define character back to the default settings after inserting the string including & into the desired column.

3. Use escape character

There is no default escape character in Oracle, So we have to define an escape character if you want to use one. You can define any character as an escape character. 

Here is the example of inserting & into an oracle table using escape character,

SQL> show escape
escape OFF
SQL> set escape \
SQL> show escape
escape "\" (hex 5c)
SQL>INSERT INTO my_table VALUES('SQL \& JAVA');
1 row created.
SQL> set escape off

4. Use ascii instead of &

This actually is an over head, but it will be helpful in some type of programs.
The ascii of & is (hex 26) or (dec 38). We can just check it out.

SQL> select chr(38) from dual;

 C
-
& 

 So in the statement 
INSERT INTO my_table VALUES('SQL & JAVA'); 
we will replace the & with, '||chr(38)||'
so the statement will be 
SQL>INSERT INTO my_table VALUES('SQL '||chr(38)||' JAVA'); 
1 row created.

 -----------------------

 Let us check whether all values in the table MY_TABLE is inserted correctly by selecting from the table.

 SQL> select * from my_table;

 COL1
---------------
&
SQL & JAVA
SQL & JAVA
SQL & JAVA
SQL & JAVA

So, The data were inserted correctly. We have learned how to insert & in oracle table.

 If you have any other idea, Please post as comment. 

Posted by No comments:

Session generating huge Archive logs

Yesterday we had an issue in our database, I was not there in office and my junior was taking care of the databases.

He was informed that one of the mount point is growing very fast and is about 90% used. The infrastructure team was afraid  of running the server out of space. He some how found out it was huge generation of archive log caused the issue. The growth rate was 1GB per minute.

As an immediate response he took a compressed backup of the archive logs, to the backup mount point from where it will be moved to tape later. He made enough room in the mount point to accommodate archive logs. The archive log generation went normal after some time. But he did not try to catch the session generating huge archive logs.

Today,  I need to find the sessions generated this much archive logs. And also the queries which was responsible for the huge archive log generation yesterday.

Well, I could have used  the query below to list the sessions and the generated redo in MB if the session is still connected. This was the query I used to use whenever I notice a huge archive log generation.
select s.username, s.osuser, s.status,s.sql_id,  sr.*  from 
  (select sid, round(value/1024/1024) as "RedoSize(MB)" 
          from v$statname sn, v$sesstat ss
          where sn.name = 'redo size'
                and ss.statistic# = sn.statistic#
          order by value desc) sr,
   v$session s
where sr.sid = s.sid
and   rownum <= 10;

 This could not help me as the connected sessions listed were not responsible for huge redo generation. The session would have logged out yesterday itself.

I thought of changing the above query to list it from the dba_hist views. Well, I can get the history of v$session from dba_hist_active_sess_history. and the v$statsname is static, we just need to get the statistics name from there. But the historical v$sessstat is not there. DBA_HIST_SESSMETRIC_HISTORY is supposed to have the history but the view is not populated(!).

I googled a lot to get some hints. Searched in the Oracle support site. Got a lot of information about redo generation, redo and archive log maintenance. I have listed some of the links in the bottom.

I took an AWR report for the problematic window, and scrolled up and down many times. My eye stuck on the section  'Segments by DB Blocks Changes' where one of the segment was having very huge DB_BLOCK_CHANGES. Which means what? I got the segment which was having huge block changes. Now I just need to find out what was the SQL statement causing this segment change a lot. That will be the SQL statement caused the huge archive generation.  Then I searched in the section 'Complete List of SQL Text' of AWR report to find out the list of SQLs which refers the mentioned segment. With God's grace I could find out the query and session generated huge archive logs yesterday in our database. 

 Later on I found this blog related to the same what I need. This is really helpful, He is giving queries to find out the same I did using AWR.
http://appcrawler.com/wordpress/2009/04/15/who-is-generating-all-the-redo/

Some other useful  links:
http://www.4tm.com.ar/blog/2008/oracle-howto-collect-session-history-metrics.html
Master Note: Troubleshooting Redo Logs and Archiving[Article ID 1507157.1]
SQL: How to Find Sessions Generating Lots of Redo or Archive logs[Article ID 167492.1]
Troubleshooting High Redo Generation Issues[Article ID 782935.1]
http://4dag.cronos.be/village/dvp_forum.OpenThread?ThreadIdA=42511
http://www.orafaq.com/forum/t/180221/0/
Posted by 3 comments:
Subscribe to: Posts (Atom)