Wednesday, November 18, 2015

How to grow xfs file system after extending the LVM Volume

[root@vmxdb01 ~]# df -h
Filesystem           Size  Used Avail Use% Mounted on
/dev/mapper/ol-root   36G   33G  3.7G  90% /
devtmpfs             1.6G     0  1.6G   0% /dev
tmpfs                2.0G  1.5G  582M  72% /dev/shm
tmpfs                1.6G  8.8M  1.6G   1% /run
tmpfs                1.6G     0  1.6G   0% /sys/fs/cgroup
/dev/sda1            997M  248M  749M  25% /boot

[root@vmxdb01 ~]# xfs_growfs /dev/mapper/ol-root
meta-data=/dev/mapper/ol-root    isize=256    agcount=4, agsize=2355968 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=0        finobt=0
data     =                       bsize=4096   blocks=9423872, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=0
log      =internal               bsize=4096   blocks=4601, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
data blocks changed from 9423872 to 12045312

[root@vmxdb01 ~]# df -h
Filesystem           Size  Used Avail Use% Mounted on
/dev/mapper/ol-root   46G   33G   14G  71% /
devtmpfs             1.6G     0  1.6G   0% /dev
tmpfs                2.0G  1.5G  582M  72% /dev/shm
tmpfs                1.6G  8.8M  1.6G   1% /run
tmpfs                1.6G     0  1.6G   0% /sys/fs/cgroup
/dev/sda1            997M  248M  749M  25% /boot

Monday, November 9, 2015

Quick Steps on how to setup MariaDB Enterprise Cluster (Galera Cluster)

Repeat below steps on servers “mysql01”, “mysql02” & “mysql03”

[root@mysql01 ~]# groupadd -g 500 mysql
[root@mysql01 ~]# useradd -g mysql -u 500 -m -d /home/mysql mysql
[mysql@mysql01 my]$ cd /my
[mysql@mysql01 my]$ tar zxvf repo/mariadb-enterprise-cluster-10.0.21-linux-x86_64.tar.gz
[mysql@mysql01 my]$ ln -s mariadb-enterprise-cluster-10.0.21-linux-x86_64 mysql
[mysql@mysql01 mysql]$ mkdir /my/data

Follow the steps based on server name. (Sample my.cnf referring to end of this post)


[mysql@mysql01 mysql]$ cp mariadb-enterprise.cnf /my/my.cnf
[mysql@mysql01 mysql]$ ./scripts/mysql_install_db --defaults-file=/my/my.cnf

[mysql@mysql01 my]$ scp my.cnf  mysql02:/my
[mysql@mysql01 my]$ scp my.cnf  mysql03:/my
[mysql@mysql01 my]$ scp -rp data mysql02:/my/
[mysql@mysql01 my]$ scp -rp data mysql03:/my/
[mysql@mysql01 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep-new-cluster &
[mysql@mysql02 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61 &
[mysql@mysql03 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61 &
[mysql@mysql01 my]$ /my/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock shutdown
[mysql@mysql02 my]$ /my/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock shutdown
[mysql@mysql03 my]$ /my/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock shutdown
[mysql@mysql01 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm:// &
[mysql@mysql02 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61,192.168.6.63 &
[mysql@mysql03 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61,192.168.6.62 &


MariaDB [(none)]> show status like 'wsre%';
+------------------------------+-------------------------------------------------------+
| Variable_name                | Value                                                 |
+------------------------------+-------------------------------------------------------+
| wsrep_local_state_uuid       | ec8dda0f-86f0-11e5-945a-53d2400df46b                  |
| wsrep_protocol_version       | 7                                                     |
| wsrep_last_committed         | 0                                                     |
| wsrep_replicated             | 0                                                     |
| wsrep_replicated_bytes       | 0                                                     |
| wsrep_repl_keys              | 0                                                     |
| wsrep_repl_keys_bytes        | 0                                                     |
| wsrep_repl_data_bytes        | 0                                                     |
| wsrep_repl_other_bytes       | 0                                                     |
| wsrep_received               | 10                                                    |
| wsrep_received_bytes         | 832                                                   |
| wsrep_local_commits          | 0                                                     |
| wsrep_local_cert_failures    | 0                                                     |
| wsrep_local_replays          | 0                                                     |
| wsrep_local_send_queue       | 0                                                     |
| wsrep_local_send_queue_max   | 1                                                     |
| wsrep_local_send_queue_min   | 0                                                     |
| wsrep_local_send_queue_avg   | 0.000000                                              |
| wsrep_local_recv_queue       | 0                                                     |
| wsrep_local_recv_queue_max   | 1                                                     |
| wsrep_local_recv_queue_min   | 0                                                     |
| wsrep_local_recv_queue_avg   | 0.000000                                              |
| wsrep_local_cached_downto    | 18446744073709551615                                  |
| wsrep_flow_control_paused_ns | 0                                                     |
| wsrep_flow_control_paused    | 0.000000                                              |
| wsrep_flow_control_sent      | 0                                                     |
| wsrep_flow_control_recv      | 0                                                     |
| wsrep_cert_deps_distance     | 0.000000                                              |
| wsrep_apply_oooe             | 0.000000                                              |
| wsrep_apply_oool             | 0.000000                                              |
| wsrep_apply_window           | 0.000000                                              |
| wsrep_commit_oooe            | 0.000000                                              |
| wsrep_commit_oool            | 0.000000                                              |
| wsrep_commit_window          | 0.000000                                              |
| wsrep_local_state            | 4                                                     |
| wsrep_local_state_comment    | Synced                                                |
| wsrep_cert_index_size        | 0                                                     |
| wsrep_causal_reads           | 0                                                     |
| wsrep_cert_interval          | 0.000000                                              |
| wsrep_incoming_addresses     | 192.168.6.61:3306,192.168.6.62:3306,192.168.6.63:3306 |
| wsrep_evs_delayed            |                                                       |
| wsrep_evs_evict_list         |                                                       |
| wsrep_evs_repl_latency       | 0/0/0/0/0                                             |
| wsrep_evs_state              | OPERATIONAL                                           |
| wsrep_gcomm_uuid             | ec8ccb15-86f0-11e5-9d6e-0632737bbcb8                  |
| wsrep_cluster_conf_id        | 3                                                     |
| wsrep_cluster_size           | 3                                                     |
| wsrep_cluster_state_uuid     | ec8dda0f-86f0-11e5-945a-53d2400df46b                  |
| wsrep_cluster_status         | Primary                                               |
| wsrep_connected              | ON                                                    |
| wsrep_local_bf_aborts        | 0                                                     |
| wsrep_local_index            | 0                                                     |
| wsrep_provider_name          | Galera                                                |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>                     |
| wsrep_provider_version       | 25.3.9(r3387)                                         |
| wsrep_ready                  | ON                                                    |
| wsrep_thread_count           | 2                                                     |
+------------------------------+-------------------------------------------------------+
57 rows in set (0.00 sec)


Sample “/my/my.cnf” on mysql01.

[mariadb]
plugin-maturity=gamma

feedback-user-info='enterprise'

## -- Remove the following line to enable feedback reporting to mariadb.org
feedback-url=''

[mysqld]
# -- Disable for applications using old auth protocol
enable-secure-auth
# -- Auditing - pre-load Plugin
plugin-load=server_audit

###########################################
[mysqld]

# General
datadir=/my/data
basedir=/my/mysql
# bind_address = 127.0.0.1
skip-name-resolve
max-allowed-packet                       = 64M
open-files-limit                         = 65535
max-connect-errors                       = 100000
max-connections                          = 1000
thread-cache-size                        = 200
back-log                                 = 128
#net_buffer_length                       = 16K
#connect_timeout                         = 10
#net_read_timeout                        = 30
#net_write_timeout                       = 60
#interactive_timeout                     = 28800
#wait_timeout                            = 28800
#net_retry_count                         = 10
character-set-server                     = 'utf8'
collation-server                         = 'utf8_general_ci'
# -- Do not change these defaults, unless you really know.
#read_rnd_buffer_size                    = 256K
#sort_buffer_size                        = 2M
#read_buffer_size                        = 128K
#max_tmp_tables                          = 32
tmp-table-size                           = 32M
max-heap-table-size                      = 32M
# -- This will enable a stricter SQL mode to avoid bad data
sql-mode                                 = STRICT_TRANS_TABLES

optimizer_search_depth                   = 0
optimizer_switch                         = 'index_merge=on'
optimizer_switch                         = 'index_merge_union=on'
optimizer_switch                         = 'index_merge_sort_union=on'
optimizer_switch                         = 'index_merge_intersection=on'
optimizer_switch                         = 'index_merge_sort_intersection=off'
optimizer_switch                         = 'index_condition_pushdown=on'
optimizer_switch                         = 'derived_merge=on'
optimizer_switch                         = 'derived_with_keys=on'
optimizer_switch                         = 'firstmatch=on'
optimizer_switch                         = 'loosescan=on'
optimizer_switch                         = 'materialization=on'
optimizer_switch                         = 'in_to_exists=on'
optimizer_switch                         = 'semijoin=on'
optimizer_switch                         = 'partial_match_rowid_merge=on'
optimizer_switch                         = 'partial_match_table_scan=on'
optimizer_switch                         = 'subquery_cache=on,mrr=on'
optimizer_switch                         = 'mrr_cost_based=on'
optimizer_switch                         = 'mrr_sort_keys=off'
optimizer_switch                         = 'outer_join_with_cache=on'
optimizer_switch                         = 'semijoin_with_cache=on'
optimizer_switch                         = 'join_cache_incremental=on'
optimizer_switch                         = 'join_cache_hashed=on'
optimizer_switch                         = 'join_cache_bka=on'
optimizer_switch                         = 'optimize_join_buffer_size=on'
optimizer_switch                         = 'table_elimination=on'
optimizer_switch                         = 'extended_keys=on'

join-buffer-space-limit                  = 4M
join-cache-level                         = 6
join-buffer-size                         = 4M

# Security
symbolic-links                           = 0
local-infile                             = 0

# Logging
log-warnings                             = 2
slow-query-log                           = 1
long-query-time                          = 5
log-slow-verbosity                       = 'query_plan,innodb'
#log_slow_rate_limit                     = 1000

# Replication / Binary logs
# -- Set to unique number on each server participating in replication
server-id                                 = 1
slave_net_timeout                         = 60
#max_prepared_stmt_count                   = 16382
# -- Annotate queries as comments in binary log, for row based events (Breaks replication to MySQL)
#If you plan to downgrade to MySQL don’t set binlog_annotate_row_events to ON.
binlog-annotate-row-events               = ON
log-bin=mariadb-bin
# BIN.2  Binlog File
binlog-format                            = ROW
expire-logs-days                         = 7
max-binlog-size                          = 1024M
# -- Recommended for binary log durability. Expect 60% performance hit without write cache on controller
sync-binlog                              = 1
binlog-stmt-cache-size                   = 128K
binlog-cache-size                        = 256K
#slave_compressed_protocol                = ON
slave-transaction-retries                = 10
#relay_log_recovery                       = ON
#sync_master_info                         = 1
#sync_relay_log                           = 0
#sync_relay_log_info                      = 1

# InnoDB
default-storage-engine                   = 'InnoDB'
innodb-stats-on-metadata                 = 0
innodb-stats-sample-pages                = 32
table-definition-cache                   = 2048
table-open-cache                         = 2048
transaction-isolation                    = READ-COMMITTED
# To be set to 0 if not multi transactional storage engine
innodb-support-xa                        = ON

# Query Cache
query-cache-size                         = 0
query-cache-type                         = 0
innodb-buffer-pool-instances             = 8
# -- Set the following to maximum 60/70% of physical RAM.
innodb-buffer-pool-size                  = 2G
# innodb_max_dirty_pages_pct should be compute with redo log size in mind: It’s recommended to set the dirty pages smaller than redo log space. The more you have dirty page, the less io will be produced on disk.
innodb-max-dirty-pages-pct               = 50
innodb-file-per-table                    = 1
innodb-file-format                       = Barracuda
innodb-flush-log-at-trx-commit           = 1
#Save and restore buffer pool to be transparent for user
#innodb_flush_method                      = O_DIRECT
innodb-log-buffer-size                   = 64M
innodb-log-files-in-group                = 2
#innodb-log-file-size                     = 1024M
#innodb_purge_threads                     = 1
# -- Raid1: 200. Raid10: 200 x write arrays. SSD: 5000. FusionIO: 20000.
#innodb_io_capacity                      = 200
# -- SSD & FusionIO can flush pages in random disk order, keep default for spinning disks
#innodb_flush_neighbors                   = 0
# -- Increase these numbers to increase global throughput on SAN (16,32,64)
innodb-read-io-threads                   = 8
innodb-write-io-threads                  = 8
# -- Set this to 2-4 times # of Cpus, maximum recommended is 64.
# -- iostat report io service time: io_capacity / service_time is a good starting point for innodb_write_io_threads
# -- innodb_read_io_threads starting point monitoring status # read ahead per second
#innodb-thread-concurrency                = 64

innodb-open-files                       = 2048

# MyISAM
key-buffer-size                          = 64M
flush                                    = OFF
myisam-recover-options                   = BACKUP,FORCE
myisam-sort-buffer-size                  = 64M

# Other
# -- Enable INFORMATION_SCHEMA.%_STATISTICS tables
userstat                                 = ON
archive                                  = OFF
blackhole                                = OFF
#federated                               = OFF
#innodb                                  = FORCE

[mysql]
default-character-set                    = 'utf8'
auto-rehash                              = FALSE
local-infile                             = 1
max-allowed-packet                       = 64M
secure-auth                              = TRUE

[mysqldump]
max-allowed-packet                       = 1G
default-character-set                    = 'utf8'

[myisamchk]
key-buffer-size                          = 1G
sort-buffer-size                         = 1G
read-buffer-size                         = 8M
write-buffer-size                        = 8M

#
# * Galera-related settings
#

[galera]
# Mandatory settings:
# -- The path to the galea library, usually its
# -- /usr/lib(64)/galera/libgalera_smm.so
wsrep_provider=/my/mysql/lib/libgalera_smm.so
# -- Address of any single member node of the cluster. You can also specify
# -- addresses of multiple nodes in the cluster (recommended). It takes the
# -- address(es) in the following format : gcomm://ip[:port][,ip[:port]].
# -- An empty address (gcomm://) would cause the node to bootstrap.
# below Bootstrap for first time initialization on mysql01
#wsrep_cluster_address=gcomm://

#After this single-node cluster is started,
#variable wsrep_cluster_address should be updated to the list of all nodes in the cluster
wsrep_cluster_address=gcomm://192.168.6.61,192.168.6.62,192.168.6.63


# -- Binary log format for write sets.
binlog_format=row
# -- Default storage engine to create tables.
default_storage_engine=InnoDB
# -- interleaved lock-mode
innodb_autoinc_lock_mode=2
# -- Accept remote connections (for mysqldump state-transfer method).
bind-address=0.0.0.0
#
# Optional settings:
# -- Number of worker threads to apply replicated transactions in parallel.
#wsrep_slave_threads=1
# -- Do not write log buffer to log file at transaction commit, but once per
# -- second.
innodb_flush_log_at_trx_commit=0
# -- Do not allow to create table without primary key.
innodb_force_primary_key=1

VM Image Clone Problem: eth0 Renamed As eth1

After cloning a Linux image, noticed that Linux server renamed eth0 as eth1.

Further study discovered that this issue caused by previous entries in “70-persistent-net.rules”, after clean up these obsoleted lines related to previous image, it works perfect.

[root@mysql03 ~]# cp /etc/udev/rules.d/70-persistent-net.rules /root/
[root@mysql03 ~]# diff /etc/udev/rules.d/70-persistent-net.rules /root/70-persistent-net.rules
8c8,14
< SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="00:50:56:26:1e:69", ATTR{type}=="1", KERNEL=="eth*", NAME="eth0"
---

> SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="00:0c:29:8c:f7:1e", ATTR{type}=="1", KERNEL=="eth*", NAME="eth0"
>
> # PCI device 0x8086:0x100f (e1000)
> SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="00:0c:29:0d:4f:31", ATTR{type}=="1", KERNEL=="eth*", NAME="eth1"
>
> # PCI device 0x8086:0x100f (e1000)
> SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="00:50:56:26:1e:69", ATTR{type}=="1", KERNEL=="eth*", NAME="eth2"

How do I find which rpm package supplies a file I'm looking for?

[mysql@mysql01 lib]$ ldd /my/mysql/lib/libgalera_smm.so
        linux-vdso.so.1 =>  (0x00007fff1affe000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f6d4daa6000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f6d4d89e000)
        libssl.so.6 => not found
        libcrypto.so.6 => not found

        libm.so.6 => /lib64/libm.so.6 (0x00007f6d4d619000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f6d4d402000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f6d4d06e000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003094000000)

       
[mysql@mysql01 lib]$ yum whatprovides 'libssl.so.6'
Loaded plugins: security, ulninfo
openssl098e-0.9.8e-18.0.1.el6_5.2.i686 : A compatibility version of a general cryptography and TLS library
Repo        : public_ol6_latest
Matched from:
Other       : libssl.so.6

 

openssl098e-0.9.8e-17.0.1.el6.i686 : A compatibility version of a general cryptography and TLS library
Repo        : public_ol6_latest
Matched from:
Other       : libssl.so.6

 

openssl098e-0.9.8e-17.0.1.el6_2.2.i686 : A compatibility version of a general cryptography and TLS library
Repo        : public_ol6_latest
Matched from:
Other       : libssl.so.6

 

[mysql@mysql01 lib]$  yum whatprovides 'libcrypto.so.6'
Loaded plugins: security, ulninfo
openssl098e-0.9.8e-18.0.1.el6_5.2.i686 : A compatibility version of a general cryptography and TLS library
Repo        : public_ol6_latest
Matched from:
Other       : libcrypto.so.6

 

openssl098e-0.9.8e-17.0.1.el6.i686 : A compatibility version of a general cryptography and TLS library
Repo        : public_ol6_latest
Matched from:
Other       : libcrypto.so.6

 

openssl098e-0.9.8e-17.0.1.el6_2.2.i686 : A compatibility version of a general cryptography and TLS library
Repo        : public_ol6_latest
Matched from:
Other       : libcrypto.so.6

[root@mysql01 ~]# yum install openssl098e

[mysql@mysql01 lib]$ ldd /my/mysql/lib/libgalera_smm.so
        linux-vdso.so.1 =>  (0x00007fff418b9000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f7b6dc57000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f7b6da4f000)
        libssl.so.6 => /usr/lib64/libssl.so.6 (0x00007f7b6d7ff000)
        libcrypto.so.6 => /usr/lib64/libcrypto.so.6 (0x00007f7b6d4a3000)

        libm.so.6 => /lib64/libm.so.6 (0x00007f7b6d21f000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f7b6d008000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f7b6cc74000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003094000000)
        libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f7b6ca30000)
        libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f7b6c748000)
        libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f7b6c544000)
        libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f7b6c318000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f7b6c113000)
        libz.so.1 => /lib64/libz.so.1 (0x00007f7b6befd000)
        libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f7b6bcf2000)
        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f7b6baee000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f7b6b8d4000)
        libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f7b6b6b4000)