MySQL Enterprise Monitor 3.0: viewing Query Analyzer for 5.5.x servers.

So, the good thing about MEM 3.0 is that it’s agentless, i.e. you don’t need an agent to use Query Analyzer data and see when performance is at it’s worst and dive into the offending SQL’s and explain plans to see what’s happening.

That’s great, however, sometimes it’s not always an easy road to migrate to 5.6 and even if you’re doing so, there’s nearly always a time when you want to continue viewing things in 5.5.x and compare performance between the 2.

The thing is, that in order to see the Explain Plans we need 5.6.14 or upwards (and setting “UPDATE performance_schema.setup_consumers SET enabled = ‘YES’ WHERE name = ‘events_statements_history_long';” ).

So, here’s how to do it:

- Use the MEM 2.3 Agent & proxy.

It’s really that simple. How simple? (Ref.Man: Using the 2.3 Agent Proxy)

./mysqlmonitoragent-2.3.17.2217-linux-glibc2.3-x86-64bit-installer.bin

- make sure you reply “yes” in order to use the “Proxy Enabled:”, default port 6446, and the “Backend Port:” is your MySQL Server’s port, 3306.

- next it’s going to ask you for a user that can connect to the server, in name of the agent, with the appropriate privileges, in order to retrieve all the info for the MEM 3 repository. I used “memagent23″. I pre-created this user as follows:

GRANT PROCESS ON *.* TO 'memagent23'@'127.0.0.1' IDENTIFIED BY password;
GRANT SELECT, INSERT, UPDATE ON `sakila`.* TO 'memagent23'@'127.0.0.1';
GRANT SELECT ON `mysql`.`inventory` TO 'memagent23'@'127.0.0.1';

BUT, I bet you’ve looked on MEM 3 before installing any agent, and if you have another instance on the same server already being monitored, you’ve seen that it will have automatically detected the 5.5.x instance.

Did you click on ‘monitor unmonitored instance’? It doesn’t matter. If you did, no problems as, at the most, it will have created the required table mysql.inventory, that for earlier versions like 5.1.x we’d need to do this anyway.

Just delete this instance before we start up the proxied agent.

Now, let’s start the agent up, with the standard:

service mysql-monitor-agent start

In mysql-monitor-agent.log you will see what’s happening, and if there are any privilege errors, they’ll appear here.

If you’re on another server or whatever, you might want to test connectivity, just in case firewalls are enforcing rules that you’re unaware of, etc.

And depending on your setup, you should see your 5.6.x instances using the built-in 3.0 agent, and the 5.5.x using the 2.3 agent.

Time to show off:

- execute your app (or in my case, use Workbench and run some slow queries on sakila tables) and make sure you’re connecting to the proxy port, 6446, so that you’re feeding the MEM repository.

- Go to the Query Analyzer tab, click on the 5.5.x server, and you should start to see all the SQL queries, and then be able to click on them, and then choose the Explain Plan tab, and hey presto, we’re focused on SQL Tuning now.

I hope this has helped someone else.

 

MySQL Enterprise Backup 3.10: Teasing compression.

Ok, so I wanted to look into the new compression options of MEB 3.10.

And I would like to share my tests with you. Remember, they’re just this, tests, so please feel free to copy n paste and obtain your own results and conclusions, and should I say it, baselines, in order to compare future behaviour, on your own system.

An Oracle Linux 6.3 virtual machine with 3Gb RAM, 2 virtual threads, on a 1x quad core, windows laptop. Not pretty, but hey.

So, these tests are solely about backup. I’ll do restore when I get some *more* time.

 

First up, lets compare like with like, i.e. MEB version 3.9 & 3.10:

Let’s make this interesting, hence, want to use as much resources available as possible, read, write, process threads and number of buffers.

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql5614.sock \
--backup-dir=/home/mysql/MEB/test --with-timestamp --read-threads=4 --write-threads=4 \
--process-threads=4 --limit-memory=300 backup

empty DB         1.2Gb        2.2Gb     2.6Gb

v3.9          0:14 min            2:37          4:26        6:04

v3.10        0:07                  2:34          4:03        5:52

So, with a bit more adjustments to use as much as possible, without compression, here’s testing 3.10:

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql5614.sock \
--backup-dir=/home/mysql/MEB/test --with-timestamp --read-threads=2 --write-threads=2 \
--process-threads=8 --limit-memory=1280 --number-of-buffers=80 backup

This takes 5:20 mins. 2685708 Kb backup size.

Now onto compression.

And I want to double check, let’s check how compression is in v3.9:

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql5614.sock \
--backup-dir=/home/mysql/MEB/test --backup-image=full_$BKUP_DATE.img --with-timestamp \
--read-threads=2 --write-threads=2 --process-threads=8 --limit-memory=1280 --number-of-buffers=80 \
--compress backup-to-image

6:31 min    867264 Kb (32% of original size, 68% compression)

 

Time for v3.10. Same options, just using the mysqlbackup binary in another location, /usr/local/meb310/bin/mysqlbackup:

4:28    Lz4 default compression. 1271720 Kb (47% of original size, 53% compression)

Ok, now let’s use 3.10 properly:

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql5614.sock \
--backup-dir=/home/mysql/MEB/test --backup-image=full_$BKUP_DATE.img --with-timestamp \
--read-threads=2 --write-threads=2 --process-threads=8 --limit-memory=1280 --number-of-buffers=80 \
--compress --compress-level=1 --compress-method=lzma backup-to-image

3:43    469916 Kb (82.6% compression).

changing it slightly, with:

--compress-level=9 --compress-method=lzma

4:35    2512022 Kb (compression 6.33%)

Ok, so now I feel I have something useful, compress-level=1 and lzma for my environment is the best option, with it taking ~83% less disk, and at 1:37 min faster. This obviously will depend on the memory (number-of-buffers) available, and of course the processing power and core’s available. Not to mention that I expect lots of you to have much better IO rates than me, so, now it’s up to you to tease MEB compression…

Try it.. just for me.

–use-tts backup & restore

In addition to my recent post, I just had to go into using the –use-tts for specific tables and selective backup sets.

As all my schemas were employeesn, I thought it would be a good idea to run:

mysqlbackup -uroot --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/restore \
--with-timestamp --use-tts --include=employees* backup

as I want all the tables. If I only wanted a specific table, say ‘salaries’ I could have done:

mysqlbackup -uroot --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/restore \
--with-timestamp --use-tts --include=employees*\.*salaries backup

and backup just the ‘salaries’ tables but from all the employeesn databases.

then.. be a mean little dba:

drop database employees;
drop database employees10;
drop database employees11;
..
..
drop database employees2;
drop database employees20;
drop database employees3;
..
drop database employees9;

And now prepare my backup to be applied:

mysqlbackup --backup-dir=/home/mysql/MEB/restore/2014-02-04_23-09-42/ apply-log

(Note: There’s a little hint in the log output that says:

"mysqlbackup: INFO: Backup was originally taken with the --include regexp option"

)

And now, to restore:

mysqlbackup --defaults-file=/home/mysql/MEB/restore/2014-02-04_23-09-42/backup-my.cnf -uroot -poracle \
--socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/restore/2014-02-04_23-09-42 \
--datadir=/opt/mysql/5615/data copy-back

sit back, and watch all the schemas and tables be restored:

 mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
140204 23:43:02 mysqlbackup: INFO: Copy-back operation starts with following threads
                1 read-threads    1 write-threads
140204 23:43:02 mysqlbackup: INFO: Creating table: employees.departments.
140204 23:43:03 mysqlbackup: INFO: Creating table: employees.dept_emp.
140204 23:43:04 mysqlbackup: INFO: Creating table: employees.dept_manager.
140204 23:43:04 mysqlbackup: INFO: Creating table: employees.employees.
140204 23:43:05 mysqlbackup: INFO: Creating table: employees.salaries.
140204 23:43:06 mysqlbackup: INFO: Creating table: employees.titles.
140204 23:43:06 mysqlbackup: INFO: Creating table: employees1.departments.
140204 23:43:07 mysqlbackup: INFO: Creating table: employees1.dept_emp.
140204 23:43:08 mysqlbackup: INFO: Creating table: employees1.dept_manager.
...
140205 00:01:04 mysqlbackup: INFO: Importing table: employees9.titles.
140205 00:01:09 mysqlbackup: INFO: Copy-back operation completed successfully.
140205 00:01:09 mysqlbackup: INFO: Finished copying backup files to '/opt/mysql/5615/data/'

mysqlbackup completed OK!

And that’s it! All restored.

And if we want to get creative with the backup options, not only can we get all the ‘salaries’ tables from all the employeesn databases, but we could also backup as many databases as we want, using the regexp connotation such that:

mysqlbackup -uroot -poracle --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/test/ \
--with-timestamp --use-tts --include=employees*\|sakila* backup

will work too.

Note: When taking these types of backups, please take into consideration the requirements.

MySQL Enterprise Backup: parallel config & backup n restore results.

In this post I go into some performance metrics and time spent on using MySQL Enterprise Backup instead of mysqldump, and seeing how far I could go with some parallel configuration.

Setup:

It’s on an old laptop:

–Ubuntu 12.04 LTS, 32bit Intel Pentium M 1.86Ghz, 2Gb
–Source disk:  internal 80Gb ATA ST9808211A
–Destination:  external 1Tb SAMSUNG HD103SI
–MySQL Enterprise Edition 5.6.15
–MySQL Enterprise Backup 3.9.0
Employees sample database duplicated via MySQL Utilities 1.3.6 (on Win7 PC) to generate a ~5Gb MySQL Server.
And to simulate data size, I used the MySQL Utilities:
mysqldbcopy --source=root:pass@host:3356 --destination=root:pass@host:3356 employees:employees1 \
employees:employees2 employees:employees3 employees:employees4 ... employees:employees18 \
employees:employees19 employees:employees20
But before we jump right in, how fast can I expect to go anyway. Simple, nothing fancy:
local disk:
dd if=/dev/zero of=/root/ddIOtest1 bs=1G count=1 oflag=direct
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 41.0825 s, 26.1 MB/s
external HD disk:
dd if=/dev/zero of=/media/1T_iomega/ddIOtest1 bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 41.214 s, 26.1 MB/s
To external HD disk via symbolic link:
dd if=/dev/zero of=/home/mysql/MEB/test/ddIOtest1 bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 41.3047 s, 26.0 MB/s

Other dd tests were run: bs=300M count=3 @ 26.8 MB/s, bs=200M count=5 @ 26.7 MB/s, bs=100M count=10 @ 26.7 MB/s, bs=300M count=5 @ 26.1 MB/s.

Ubuntu helped a little with some nice pretty graphs for the internal & external disk:

mysqldump:

So the times we’re looking at before we go Enterprise are:

Backup:

mysqldump -uroot -ppass -S /tmp/mysql.sock --all-databases --add-drop-database -r mysqldump_full.sql
start: Fri Jan  3 13:30:36 CET 2014
end:   Fri Jan  3 13:41:17 CET 2014
10:41 min.
$ du –sk     3453640 mysqldump_full.sql
5387 Kb/sec.

Restoring:

$ cp /usr/local/mysql/my.cnf mysqldump_full_test
$ cd mysqldump_full_test
$ vi my.cnf  
    (change all file paths, port numbers, change server-id (just in case) and add a new socket dir.)
$ cd /usr/local/mysql
$ scripts/mysql_install_db --defaults-file=/home/mysql/MEB/test/mysqldump_full_test/my.cnf \
--user=mysql --datadir=/home/mysql/MEB/test/mysqldump_full_test/datadir
$ mysqld_safe --defaults-file=/home/mysql/MEB/my.cnf --user=mysql \
--datadir=/home/mysql/MEB/test/mysqldump_full_test/datadir --socket=/tmp/mysql_mysqldump_restore.sock \
--port=13356
$ mysqladmin -uroot password ‘pass' -S /tmp/mysql_mysqldump_restore.sock
$ mysql -uroot -ppass -S /tmp/mysql_mysqldump_restore.sock
mysql> show databases;
(To avoid the GTID error, you might have to execute "reset master;" before trying the restore.)

mysql -uroot –ppass -S /tmp/mysql_mysqldump_restore.sock < mysqldump_full.sql
start: Fri Jan  3 15:16:45 CET 2014
end:   Fri Jan  3 16:26:26 CET 2014
1:09:41 min. (1hr 9 mins 41 sec.) 4181 sec.
$ du –sk   4974081 (21x employee schemas dir's)
1189.68 Kb / sec.

MySQL Enterprise Backup:

Now to use ‘mysqlbackup':

mysqlbackup --user=root --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/test --with-timestamp \
 backup
And the results:
start: Tue Dec 31 13:09:05 CET 2013
end:   Tue Dec 31 13:13:56 CET 2013
5054864 2013-12-31_13-09-05
4:51 min.
17370.66666666667 Kb/sec.

Configuration and Performance Times:

Parallel Backup
By using parallelization configuration we can improve backup performance significantly. The following is the default setting for MEB 3.9 (for more information see parallel backup description):
--read-threads=1 --process-threads=6 --write-threads=1 --limit-memory=300

And again, the results:

read threads

process threads

write threads

limit memory

number

of buffers

(16M each)

Backup

size

(Kb)

Duration (hh:mm:ss)

Kb/sec

1

6

1

300

14

5054864

00:04:50

17430.57

2

3

2

300

10

5054872

00:04:32

18584.09

3

6

3

600

18

5054872

00:04:11

20138.93

3

3

3

300

12

5054868

00:03:57

21328.56

4

4

4

300

16

5054872

00:03:51

21882.56

4

4

4

320

16

5054876

00:03:37

23294.35

4

4

4

640

16

5054876

00:03:37

23294.35

4

8

4

640

20

5054876

00:03:37

23294.35

(apologies for the formatting)

Restoring:

Scenario specifics:

We want to restore a single table, Partial Recovery, not the whole server.

  • The table to be restored is on a running server and can’t be stopped.
  • No compression is being used.

What about having specified –user-tts at backup time?

  • Backups taken with this option are meant to transport the table to another, new environment, i.e. tables can’t be restored selectively from these types of backups.
  • The tables to be restored can’t exist on the destination server.
  • mysqlbackup is already flexible enough to be able to single out a table and restore it via the transportable tablespace definition, i.e. discard / import tablespace.

Converting the on-disk backup to image.

First convert to image, so that we can then extract the table, or apply-logs from incremental backups taken post-Full backup:

mysqlbackup --user=root --socket=/tmp/mysql.sock \ 
--backup-image=/home/mysql/MEB/test/2014-01-02_13-57-17.mbi \
--backup-dir=/home/mysql/MEB/test/2014-01-02_13-57-17 --read-threads=4 \
--write-threads=4 --process-threads=8 --limit-memory=320 backup-dir-to-image
Start: 140102 15:57:34
End:   140102 16:04:50
7:16 min.
5054052 /home/mysql/MEB/test/2014-01-02_13-57-17.mbi
11591.86 Kb/sec.

Or…

… have backed up directly to the single image file :

mysqlbackup --user=root --socket=/tmp/mysql.sock \
--backup-image=/home/mysql/MEB/test/full.mbi –backup-dir=/home/mysql/MEB/test/full \
--with-timestamp --read-threads=4 --write-threads=4 --process-threads=8 --limit-memory=320 \
backup-to-image
Start: 140102 14:43:01
End:   140102 14:47:43
4:42 min.
5054124 full.mbi
16735.50 Kb/sec

Now to restore…

Native Transportable tablespace

Knowing we’re going to use transportable tablespace from the image backup file, and that the table has just lost some rows (as if it had been deleted, we’d have to recover in another env and then mysqldump it out).

First check the image file and list the table location:

mysqlbackup --user=root --socket=/tmp/mysql.sock \
--backup-image=/home/mysql/MEB/test/full.mbi --read-threads=4 --write-threads=4 \
--process-threads=8 --limit-memory=320 --sleep=0 list-image

Restore:

mysql> lock tables salaries write;
mysql> alter table salaries discard tablespace;
mysqlbackup --user=root --socket=/tmp/mysql.sock --backup-image=/home/mysql/MEB/test/full.mbi \
--read-threads=4 --write-threads=4 --process-threads=8 --limit-memory=320 --sleep=0 \
--src-entry=datadir/employees/salaries.ibd --dst-entry=/opt/mysql/5615/data/employees/salaries.ibd \
extract
Start: 140102 16:48:04
End:   140102 16:48:09
0:05 min.
143360 /opt/mysql/5615/data/employees1/salaries.ibd
28672.00 Kb/sec.
mysql> alter table salaries import tablespace;
mysql> desc salaries;

A Full Restore

Consider that the table has been deleted and we need to restore the full backup.

  • Restore the image backup to a new backup / datadir.
  • Start the restored environment with a modified my.cnf.
  • Export & import the desired data for the affected table into the working environment.
  • Carry on working with some minor disruption to the affected table.

The details:

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql.sock \
--backup-image=/home/mysql/MEB/test/full.mbi –backup-dir=/home/mysql/MEB/test/full-img-restore \
--read-threads=4 --write-threads=4 --process-threads=8 --limit-memory=320 --sleep=0 \
image-to-backup-dir
start: Thu Jan 2 16:58:28 CET 2014
end:   Thu Jan 2 17:04:46 CET 2014
6:18 min.
5054972 full-img-restore
13372.94 Kb/sec.

Now, we can start up the restored env to use and abuse the salaries table we’re worried about:

$ cd /hoe/mysql/MEB/test/full-img-restore
$ vi server-my.cnf
  change all paths (datadir, socket, pid_file, log_bin, log_bin_index, log_error, port & report 
  port if using replication.
$ mysqld_safe --defaults-file=/home/mysql/MEB/test/full-img-restore/server-my.cnf --user=mysql \
--datadir=/home/mysql/MEB/test/full-img-restore/datadir -P13356 --socket=/tmp/mysql_restore.sock &
$ mysql -uroot –ppass -S /tmp/mysql_restore.sock
mysql> use employees
mysql> show table like salaries;

Now to double check the instance where we’re missing the salaries table:

$ mysql -uroot –ppass -S /tmp/mysql.sock
mysql> use employees
mysql> show table status;
mysql> show create table salaries;
ERROR 1146 (42S02): Table 'employees.salaries' doesn't exist

So now to import / recreate the previously dropped ‘employees.salaries’ table, and check times, after all, we are interested:

mysqldump –uroot –ppass -S /tmp/mysql_restore.sock --tables employees salaries | mysql -uroot –ppass \
-S /tmp/mysql.sock employees
Start: Fri Jan 3 13:16:38 CET 2014
End:   Fri Jan 3 13:19:37 CET 2014
2:59 min
143364 /opt/mysql/5615/data/employees/salaries.ibd
800.91 Kb/sec.

Checking & Alternatives

MySQL Utilities 1.3.6

Once the backup has been restored in another environment, use MySQL Utilities ‘mysqldiff’ to see if it’s missing:

mysqldiff --server1=root:oracle@141.144.12.45:3356 --server2=root:oracle@141.144.12.45:13356  \
employees.salaries:employees.salaries

If the row number is different, mysqldiff doesn’t detect this, it’s only for object and structure differences.

# server1 on 141.144.12.45: ... connected.
# server2 on 141.144.12.45: ... connected.
# Comparing employees.salaries to employees.salaries         [PASS]
Success. All objects are the same.

Use Utilities mysqldbcompare to get exact changes and data consistency checking. Saves having to restore the whole table:

mysqldbcompare --server1=root:oracle@141.144.12.45:3356 --server2=root:oracle@141.144.12.45:13356 \
employees:employees -a -d differ –vvv

mysqlfrm could be used, however be wary of auto-increment sequences & Foreign Keys.

Also restore the .frm from the backup image fileset, to change the datadir for that specific tablespace and then use mysqldump | mysql to import the row data.

 Conclusion

mysqlbackup restores the single transportable tablespace at 28672.00 Kb/sec where mysqldump is done at 800.91 Kb/sec: 35.79 times faster.

(In this scenario, a 4 column table with 2844047 rows).

MySQL Utilities: copy, replicate, show, failover… over and over again.

So, after installing Workbench 6.0.7 on my pc, and playing around with the MySQL Utilities that are included, I thought I’d do similar to what others have done (Thanks Tony D.) and share my experience on how I’ve used them. If you haven’t installed Workbench before, you might want to check your platform first: http://www.mysql.com/support/supportedplatforms/workbench.html.

So, even if you’re not using any of the recent versions and editions of Workbench (Utilities comes with all of them, Tools menu -> “Start Shell for MySQL Utilities“) you can just download the standalone scripts on a Linux machine. Flexibility being the name of the game again.

Connector/Python install

I’ll install python (& distutils) & the Connector/Python on Oracle Linux and execute it all from the linux slave I want to create.

On Oracle Linux 6.3:

1. Download Connector/python & Install.

 cd /usr/local
 tar zxvf mysql-connector-python-1.0.12.tar.gz
 cd mysql-connector-python-1.0.12

– check python is ok:

 python
 >>> from distutils.sysconfig import get_python_lib
 >>> print get_python_lib()
 /usr/lib/python2.6/site-packages
 >>> print (get_python_lib())
 /usr/lib/python2.6/site-packages
 >>> quit()
 python setup.py install

2. Install the Utilities

 cd /usr/local
 tar zxvf mysql-utilities-1.3.5_Linux.tar.gz
 cd mysql-utilities-1.3.5
 python ./setup.py build
 python ./setup.py install
 which mysqldbexport
 /usr/bin/mysqldbexport

Setup Replication

On master node:

 cd /usr/local
 tar zxvf mysql-5.6.14-linux-glibc2.5-i686.tar.gz
 ln -s /usr/local/mysql-5.6.14-linux-glibc2.5-i686 mysql
 cd mysql
 chown -R mysql:mysql .
 cp my56.cnf /etc/my.cnf
 vi /etc/my.cnf
 - SET "server-id = 21"
 - make sure basedir & datadir are ok.
 - make sure hostname is correct.
 - make sure log  are ok.
 mkdir -p /opt/mysql/5.6.14/data
 cd /opt/mysql/5.6.14
 chown -R mysql:mysql .
 cd /usr/local/mysql
 scripts/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql --datadir=/opt/mysql/5.6.14/data --basedir=/usr/local/mysql
 chown -R root .
 chown -R mysql data
 bin/mysqld_safe --user=mysql --datadir=/opt/mysql/5.6.14/data &
 /usr/local/mysql/bin/mysqladmin -u root password 'oracle'
 mysql -uroot -poracle
 create database nexus;
 use nexus
 source cre_tab_replicant.sql;
 source insert_nexus.sql;
 grant replication slave on *.* to 'replicant'@'192.168.56.106' identified by 'pkdick';
 grant all on *.* to 'root'@'ol63uek03' identified by 'oracle';
 grant all on *.* to 'root'@'ol63uek02' identified by 'oracle' with grant option;

On slave node:

 cd /usr/local
 tar zxvf mysql-5.6.14-linux-glibc2.5-i686.tar.gz
 ln -s /usr/local/mysql-5.6.14-linux-glibc2.5-i686 mysql
 cd mysql
 chown -R mysql:mysql .
 cp my56.cnf /etc/my.cnf
 vi /etc/my.cnf
 - SET "server-id = 31"
 - make sure basedir & datadir are ok.
 - make sure hostname is correct.
 . make sure log  are ok.
 mkdir -p /opt/mysql/5.6.14/data
 cd /opt/mysql/5.6.14
 chown -R mysql:mysql .
 cd /usr/local/mysql
 scripts/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql --datadir=/opt/mysql/5.6.14/data --basedir=/usr/local/mysql
 chown -R root .
 chown -R mysql data
 bin/mysqld_safe --user=mysql --datadir=/opt/mysql/5.6.14/data &
 /usr/local/mysql/bin/mysqladmin -u root password 'oracle'
 grant replication slave on *.* to 'replicant'@'ol63uek02' identified by 'pkdick';
 grant all on *.* to 'root'@'ol63uek02' identified by 'oracle' with grant option;

Time to copy the data across:
On Master:

mysqldbcopy    --source=root:oracle@ol63uek02:3356 --destination=root:oracle@ol63uek03:3356 nexus:nexus

Let’s get replication up and running:

mysqlreplicate --master=root:oracle@ol63uek02:3356 --slave=root:oracle@ol63uek03:3356 --rpl-user=replicant:pkdick -vvv
mysqlrplshow --master=root:oracle@ol63uek02:3356 -v -l -r --discover-slaves-login=root:oracle
insert into replicant (`First Name`,`Last Name`,`Replicant`) values ('Ridley', 'Tyrell','No'), ('Eldon','Scott', 'No');

– check slave, from a mysql master connection:

mysql> show slave hosts;
mysqlrpladmin health  --master=root:oracle@ol63uek02:3356 --discover-slaves-login=root:oracle

Ok. It’s working.
Now this is a simple sample environment and not production nor has it been existing for any long period of time. In any ‘normal’ environment, you’ll have purged binlogs and backed up plenty of times since t=0, install & creation time. What does this mean? When you try and set it up with master-auto-position=1, you might get:

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

So we have some alternatives here:
– Use old log_file & log_position. And then activate GTID_MODE after replication has been setup (or not use GTID, but that’s not an option I want to consider).
– Run “reset master” on the slave and then import the mysqldump file obtained from running with –set-gtid-purged=ON. The resulting dump file contains the “set @@GLOBAL.GTID_PURGED=” line that will allow us to align master and slave.
– Take a more ‘hands-on’ approach, and that’s to manually enter the “set global.GTID_PURGED” on the slave, taking the masters “gtid_executed” value, from “show global variables like ‘gtid_executed';”

Time to think about Failing over:

mysqlfailover --master=root:oracle@ol63uek02:3356 --log=utilfailover.log  --discover-slaves-login=root:oracle --force

Kill -9 mysqld process
Watch slave become master.

To go backwards, to initial situation:

On ol63uek03:

mysql> reset master;

On ol63uek02:

mysqlreplicate --master=root:oracle@ol63uek02:3356 --slave=root:oracle@ol63uek03:3356 --rpl-user=replicant:pkdick -vvv

MEM 3.0: Getting started

Time to install MEM 3.0, and get its built-in agent working.

[ If you want some tips on What’s New, have a look here. ]

I’ve downloaded the Monitor Server and the agent zipped s/w for Linux & Win from http://edelivery.oracle.com:

mysqlmonitor-3.0.0.2887-linux-x86-installer.bin
mysqlmonitoragent-3.0.0.2887-linux-glibc2.3-x86-32bit-installer.bin
mysqlmonitoragent-3.0.0.2887-windows-installer.exe

The Monitor install

So, on my Oracle Linux machine:

./mysqlmonitor-3.0.0.2887-linux-x86-installer.bin

It all installs fine. No issues, if you’re used to MEM 2.3.

Double check your configuration_report.txt :

MySQL Enterprise Monitor (Version 3.0.0.2887 : 3.0.0.2887)

Here are the settings you specified:
Application hostname and port: http://127.0.0.1:18080
Tomcat Ports: 18080 - 18443 (SSL)
MySQL Port : 13306

Use the following command to login to the MySQL Enterprise Monitor database:
mysql -u**** -p**** -P13306 -hlocalhost

To startup | shutdown:

service mysql-monitor-server start | stop

Before looking any further, once it’s started up, I personally see that my poor little Virtualbox VM is screaming for more memory, eating swap. My fault for keeping it too tight. java, jsvc & mysqld procs are a little high, for my demo env.

Especially as I plan to do as much as possible in this env (replication, utilities, etc.) so I’ll end up with 3x mysqld’s, as well as java, etc.

Remember: this is my own doing. You won’t want to do this in a proper production environment. Au contraire, you’ll probably want to assign more memory, etc.

cd /opt/mysql/enterprise/monitor/apache-tomcat/bin
vi setenv.sh
#JAVA_OPTS="-Xmx768M -Xms768M -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/opt/mysql/enterprise/monitor/apache-tomcat/temp -XX:+UseParallelOldGC -XX:MaxPermSize=512M"
JAVA_OPTS="-Xmx400M -Xms400M -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/opt/mysql/enterprise/monitor/apache-tomcat/temp -XX:+UseParallelOldGC -XX:MaxPermSize=256M"

cd ../..
./mysqlmonitorctl.sh restart
./mysqlmonitorctl.sh status

To see which the ‘root’ user is for mysql CLI access:

/opt/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/config.properties

don’t know why you’d want to play around there though.


Now go to the console http://192.168.56.104:18080 and complete the setup instructions. You’ll see a more blank version of the console, but it’s something like this: install_MEMDashboardAlso, the cool thing is, before we actually go and install any agent, the monitor will automatically detect the other MySQL instances on the same server. In fact, what you can see here is when I actually deleted an existing entry for a specific previously detected and monitored instance. MEM acknowledges that you’re deleted it, but it will still tell you that you’ve an unmonitored instance.. just waiting for you to add it in:

Unmonitored Instance

Unmonitored Instance

Agent install

./mysqlmonitoragent-3.0.0.2887-linux-glibc2.3-x86-32bit-installer.bin
Language Selection

Please select the installation language
[1] English - English
[2] Japanese - æ¥æ¬èª
Please choose an option [1] :
----------------------------------------------------------------------------
Welcome to the MySQL Enterprise Monitor Agent Setup Wizard.

----------------------------------------------------------------------------
Installation directory

Please specify the directory where MySQL Enterprise Monitor Agent will be
installed

Installation directory [/opt/mysql/enterprise/agent]: /opt/mysql/enterprise/agent56

Agent Service Name [mysql-monitor-agent]: mysql-monitor-agent56

I’m giving it a specific path under “agent56” as I know I’ll be installing other agents later…

How will the agent connect to the database it is monitoring?

[1] TCP/IP
[2] Socket
Please choose an option [1] :
----------------------------------------------------------------------------
Monitoring Options
You can configure the Agent to monitor this host (file systems, CPU, 
RAM, etc.) and then use the Monitor UI to furnish connection parameters
for all current and future running MySQL Instances. This can be automated
or done manually for each MySQL Instance discovered by the Agent. (Note: 
scanning for running MySQL processes is not available on Windows, but you
can manually add new connections and parameters from the Monitor UI as 
well.)

Visit the following URL for more information:
http://dev.mysql.com/doc/mysql-monitor/3.0/en/mem-qanal-using-feeding.html

Monitoring options:
[1] Host only: Configure the Agent to monitor this host and then use the
Monitor UI to furnish connection parameters for current and future running
MySQL Instances.

[2] Host and database: Configure the Agent to monitor this host and 
furnish connection parameters for a specific MySQL Instance now. This
process may be scripted. Once installed, this Agent will also 
continuously look for new MySQL Instances to monitor as described above.

Please choose an option [2] : 2

----------------------------------------------------------------------------
Setup is now ready to begin installing MySQL Enterprise Monitor Agent on
your computer.

Do you want to continue? [Y/n]:

----------------------------------------------------------------------------
Please wait while Setup installs MySQL Enterprise Monitor Agent on your
computer.
 Installing
 0% ______________ 50% ______________ 100%
 #########################################
----------------------------------------------------------------------------
MySQL Enterprise Monitor Options

Hostname or IP address []: 192.168.56.104

Tomcat SSL Port [18443]:

Agent Username [agent]: memagent

Agent Password :
Re-enter :
----------------------------------------------------------------------------
Monitored Database Information

IMPORTANT: The Admin user account specified below requires special MySQL
privileges.

Visit the following URL for more information:
http://dev.mysql.com/doc/mysql-monitor/3.0/en/mem-agent-rights.html

MySQL hostname or IP address [127.0.0.1]: 192.168.56.104

Validate MySQL hostname or IP address [Y/n]:
MySQL Port [3306]: 3356

Admin User []: root

Admin Password :
Re-enter Password :
Monitor Group []: ol63uek01

Warning: Error running /opt/mysql/enterprise/agent56/bin/agent.sh
--test-credentials -f --host=192.168.56.104 --port=3356 --admin-user=root :
[1045] Access denied for user 'root'@'192.168.56.104' (using password: YES)
Press [Enter] to continue :
----------------------------------------------------------------------------
Monitored Database Information
IMPORTANT: The Admin user account specified below requires special MySQL
privileges.

Visit the following URL for more information:
http://dev.mysql.com/doc/mysql-monitor/3.0/en/mem-agent-rights.html

Ok, so I forgot to let root access from that IP. Let’s keep it simple:

MySQL hostname or IP address [192.168.56.104]: 127.0.0.1
Validate MySQL hostname or IP address [Y/n]:
MySQL Port [3356]:
Admin User [root]:
Admin Password [********] :
 Re-enter Password [********] :
 Monitor Group [ol63uek01]:
----------------------------------------------------------------------------
Agent User Account Creation

IMPORTANT: Less privileged accounts can be used to enhance monitoring security,
and can even be created for you if the Admin account has GRANT privileges.

Visit the following URL for more information:
http://dev.mysql.com/doc/mysql-monitor/3.0/en/mem-agent-rights.html

Auto-Create Less Privileged Users [Y/n]:

General Username []: general

General Password :
Re-enter Password :
Limited Username []: limited

Limited Password :
Re-enter Password :
----------------------------------------------------------------------------
Configuration Report
MySQL Enterprise Monitor Agent (Version 3.0.0.2887 : 3.0.0.2887)

The settings you specified are listed below.

Note that if you are using a Connector to collect Query Analyzer data,
you will need some of these settings to configure the Connector. See
the following for more information:
http://dev.mysql.com/doc/mysql-monitor/3.0/en/mem-qanal-using-feeding.html

Installation directory: /opt/mysql/enterprise/agent56

MySQL Enterprise Monitor UI:
-------------------------
Hostname or IP address: 192.168.56.104
Tomcat Server Port: 18443
Use SSL: yes

Monitored MySQL Database:
-------------------------
Hostname or IP address: 127.0.0.1
Port: 3356
Press [Enter] to continue :

Press [Enter] to continue :

----------------------------------------------------------------------------
Start MySQL Enterprise Monitor Agent

Info to start the MySQL Enterprise Monitor Agent, Aggregator, and Proxy

The MySQL Enterprise Monitor Agent was successfully installed. To start the
Agent please invoke:
/etc/init.d/mysql-monitor-agent56 start
Press [Enter] to continue :

----------------------------------------------------------------------------
Setup has finished installing MySQL Enterprise Monitor Agent on your computer.

View Agent Readme File [Y/n]:

[ Didn’t see any passwords or asterisks, did you? Try and find them.. go on. “Bigger, Stronger…” ]

Also, remember that if no entry is given to any particular answer, it will take the default, i.e. the option in uppercase (Y/n) or if you’re relaunching a ctrl-c’ed agent install, it will read you back the previous config.

Now, I want to take a moment to point out that the README‘s are sometimes useful, so, I suggest doing what their name hints upon, because:

..
..
Agent:
------
Several differences from the 2.3 Agent installation are worth noting
(see the documentation for more details):Press [Enter] to continue :
  - Multiple connection levels (Admin, General, Limited) are now defined and used by the Agent
  - Groups play a central role in the new UI and can optionally be assigned at Agent install-time
  - The old Agent .ini files (mysql-monitor-agent.ini and agent-instance.ini) are no longer supported
  - Passwords in the .ini file are now stored in encrypted form
  - The Service Manager now includes a built-in Agent (described above)
  - The MySQL Proxy and Aggregator are no longer bundled with the Agent installer. See the complete
    documentation for guidance if you will continue to use them with MEM 3.0.
...
..

Adding instances

As you might have seen before, the agent automatically detected a 5.7 instance. So let’s go and monitor it:

Choosing whether to Monitor or not.

Choosing whether to Monitor or not.

Add the specifics:

install_Agent_autodetectremember, in this example I’m using memagent, who has the “with grant option” priv, so that it can create the lesser privileged General & Limited users. This will let MEM do the tasks it needs to do, without having to use root all the time, max out the connections for the root user, etc.

If a group already exists that we want to add this unmonitored instance to, we can also do so:

install_Agent_autodetect_groupand once we’ve added the instance, we refresh and wait a couple of minutes or so, and should then see if happily sitting in its group, and because it’s local the the MEM server, it’s also got o.s. monitoring:

All OK.

All OK.

When adding, deleting or whatever with the instances, and you’re not quite sure what’s going on, and refreshing like mad on the console, you might want to check things like user privileges, as I initially forgot to give my memagent the appropriate privileges, and I received an error in the /opt/mysql/enterprise/agent/logs/mysql-monitor-agent.log. The console itself will tell you also that Access Denied so there are hints as to what’s going wrong, as these issues will appear under Bad Configurations that appears.

Once I had deleted the instance, adjusted the privs, and added again, it was all ok.

Don’t ignore me

Now, in the previous image, you’ll see a highlighted instance that’s down. I know why it’s down, say we don’t have enough resources to keep them running all the time and/or it’s a rare occasion that we want to monitor it really, or maybe we’re evaluating 5.7.2 or something.

So let’s ignore it:

Selecting the 'ignore instance' option.

Selecting the ‘ignore instance’ option.

config_ignore_instance_dialogue

Making sure…

config_ignore_instance_done

It’s confirmed. 1 ignored instance.

but once we’re done, we want it back again. But how do we get it back? On the host itself, there’s not a “show me an ignored instance” option.
It might be a little subtle, but everything in MEM 3.0 is there for a reason. There’s little redundancy and not too many of those “more than one way to skin a cat” type accesses. I like the fact that it’s just ‘efficient’.
So, under the 3 buttons “Create Group”, “Add MySQL Instance”, “Add Bulk MySQL Instances”, you’ll find the “MySQL Instance Details” bar, where, on the far right is a magnifying glass. Hit it.

This is the filter, and why we see what we actually do.

And the last button is “Ignored Instance” with the “Disabled option” by default. Choose “(any)” or “Enabled” and then hit the Filter button.

“(any)” shows us all of the instances, ignored and not, and “Enabled” will only show us that poor little ignored instance.config_ignore_show

And now just choose the “Show Instance” option, and they’re back. Hello again!

config_ignore_notice

Deleting instances

Say we make a mistake upon config time when adding an unmonitored instance and it appears in the Bad configs section. We can delete them again, via the “Delete Instance” option in the dropdown meno next to the check box.

The mysql-monitor-agent.log will say:
Deleted connection to server com.mysql.etools.agent.collection.MysqlConnection@12fad5

And the recently deleted auto-detected instance reappears as an Unmonitored instance.

So I click on the down-pointing arrow for the “Monitor instance | Ignore instance” dropdown. Choose “Monitor..”.
Leaving the “Monitor from” dropdown as is, I enter the IP address for the port number I’m interested in, 3357, and away we go.

If we added an instance manually, i.e. remotely monitored, and then delete it, if we use the filter on the MySQL Instance Details search button, and use Ignored Instance = (any), then we’ll see that when an instance is deleted, it’s first ignored, i.e. appears in italics, and when we insist, and click on the name, is says

No MySQL Instance was selected or the connection details for the instance can not be obtained. (U0331)“.

We can be even more persistent, and click on “Refresh inventory”, but if we start clicking around and refreshing, then we’ll find that they will be removed before we can play around. Which is just what we wanted.. isn’t it now?

Remotely Monitored

Right, I’ve got a Raspberry Pi MySQL Cluster that I want to monitor. So, because ARM isn’t a supported platform, we’ll monitor that by some other means, and therefore, I can remotely monitor the cluster. Here’s how.

Adding:
“Add MySQL Instance” button, and on the Connection Settings tab, use the “Monitor From” entry of “ol63uek01: MEM Built-in Agent”. Mainly as you’re indicated to do so, by the ? question mark: “if there is no local Agent on the host and you are unable to install one, use the built-in or another Agent to monitor remotely.

The ? hint.

The ? hint.

Once I’m happy with all the info, hit “Add Instance” and we get notified (top right):config_add_remote_Notice

Create a group for the newly added remote instances

Very simple, as this is a Raspberry Pi MySQL Cluster, I hit the Create Group button, under the MySQL Instance Details section, and just enter the group name you desire.

Now, I can configure events and advisors per group, and make it specific. This could have been Dev, Test, Preprod or whatever.

Add to group

Now to add the 2x instances to the group.
Click on the dropdown beside the name of the group, and hit “Add to Group”. The dialogue box comes up, and asks us which of the discovered instances we want to add, and so I select the 2 I’m interested in.
config_add_instances_to_group and we’re fit.

Notice the “Ungrouped” instances group disappears.

All we need to do now, is look at all the already enabled Advisors for each of my instances and see if I want to make anything group specific, or not, startup some SNMP trap forwarding using the /opt/mysql/enterprise/monitor/support-files/MONITOR.MIB file, and so on and so forth.

Hope this helped.

Follow

Get every new post delivered to your Inbox.