ProxySQL/Orchestrator/Replication/PMM

PORP LAB : ProxySQL/Orchestrator/Replication/PMM

Summary

PORP Lab will create 4 different nodes. Each node will have below packages/applications/db installed.
app
-- Percona Server 5.7
-- Percona Toolkit 
-- Percona XtraBackup 
-- Sysbench 
-- ProxySQL
-- Orchestrator 
-- PMM

mysql1 / mysql2 / mysql3
-- Percona Server 5.7
-- Percona Toolkit
-- pmm-client
-- Replication
PORP LAB have ProxySQL,Orchestrator and PMM properly configured, we can just create this lab and use it.

Install VirtualBox

Install Vagrant

Install Vagrant plugin hostmanager

vagrant plugin install vagrant-hostmanager

Update Vagrant Plugin

vagrant plugin update

Clone the repo

git clone https://github.com/abhinavbit/PORP-LAB.git

Create Lab

vagrant up --provision 

Connect to each node

vagrant ssh app

vagrant ssh mysql1

vagrant ssh mysql2

vagrant ssh mysql3

Verify Replication

vagrrant ssh mysql2
mysql -e"show slave status\G"

vagrant ssh mysql3
mysql -e"show slave status\G"

Verify ProxySQL

vagrant ssh app

service proxysql restart 

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL> '

ProxySQL> select * from runtime_mysql_servers;
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | mysql1   | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | mysql3   | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | mysql2   | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

root@app:~# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e "START TRANSACTION; SELECT @@hostname; ROLLBACK;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
root@app:~# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e "SELECT @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql2     |
+------------+
root@app:~# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e "SELECT @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql3     |
+------------+

Verify Orchestrator

-- Open below link on web browser
https://192.168.135.101:3000
-- Go to Clusters > Discover. -- Enter all db nodes under Enter Host Name field and then click on submit.
mysql1
mysql2
mysql3
-- Got to Clusters > Dashboard. Now replication between nodes will be visible over dashboard.

Verify PMM

-- Open PMM dashbaord link on web browser
https://192.168.135.101:80

NOTES

  1. If due to any reason like slow ISP, network issues, if create lab vagrant up --provision got failed, then just re-run the vagrant up --provision command to create lab.
  2. Once you done with usage of PORP LAB, you can just simply run vagrant halt command to power off the nodes and Next time when you need again PORP LAB then just run vagrant up --provision to start all lab nodes.
  3. Error :-
Progress state: NS_ERROR_FAILURE
VBoxManage: error: Failed to create the host-only adapter
VBoxManage: error: VBoxNetAdpCtl: Error while adding new interface: failed to open /dev/vboxnetctl: No such file or directory
VBoxManage: error: Details: code NS_ERROR_FAILURE (0x80004005), component HostNetworkInterfaceWrap, interface IHostNetworkInterface
VBoxManage: error: Context: "RTEXITCODE handleCreate(HandlerArg *)" at line 94 of file VBoxManageHostonly.cpp
-- Grant permission to VirtualBox under System Preferences > Security & Privacy > General (this request is new to macOS High Sierra). Open Terminal and run: sudo "/Library/Application Support/VirtualBox/LaunchDaemons/VirtualBoxStartup.sh" restart.

Enjoy PORP LAB 😎

Comments

Popular

SLEEP thread causing "Waiting for table metadata lock"

MySQL Memory Calculator

How to create CentOS VM using Vagrant

errno: 24 - Too many open files

How to properly shutdown MySQL before any maintenance activity

set --server-id to enable either a master or a slave

ERROR 1040 (HY000): Too many connections

GTID Replication (Skip Transaction using empty transaction)

Master has purged binary logs containing GTIDs that the slave requires

How to Prepare for MySQL Certification Exam