MYSQLR: MySQLR Database Appliance - Test Plan

Preparation

For test cases below two test applications are needed:

simple-repl application:

Add following mandatory properties for the application: in_ip, netmask and =gateway=
client - branched LUX5 appliance

• Branch LUX5 appliance
• Resize both boot and usr volumes
• Install mysql-server, mysql-devel and sql-bench on it
• Install gcc and make
• Install dbt2 and create dbt2 database (see instructions how to do it here)
• Rename out terminal to db1
• Add two more outputs - db2 and db3
• Remove the in and mon terminals

• Create and set data (with raw fs) and binlogs volumes (100M size) for all the appliances
• Set different names for error logs - db1.err, db2.err, db3.err
• Set the web_pwd property.
• Set db1.Start Order=10, db2.Start Order=20 and db3.Start Order=30
• Switch on the standby property of db3 appliance

• Create and set user volume of the appliance
• Set Start Order=1

• Set out1_protocol=tcp, out1_in_port=8081, out1_out_port=80
• Set out2_protocol=tcp, out2_in_port=8082, out2_out_port=80
• Set out3_protocol=tcp, out3_in_port=8083, out3_out_port=80

• Redirect in_addr, netmask and gateway properties
• Set l7_accept=none and l3_accept_proto=tcp

external-repl application:

Add following mandatory properties for the application: in_ip, in_db_ip, out_ip, repl_ip, netmask, gateway, dns1, dns2, mysql_server_id, mysql_rpl_mode.
in - IN appliance

• Redirect in_addr -> in_ip, netmask and gateway properties

• Redirect in_addr -> in_db_ip, netmask and gateway properties
• Set iface1_port=3306

• Set content_on_fs=on
• Install phpBB software using user: admin, pass: admin123 (while doing it make sure you have mysql_rpl_mode = none)

• Create and set data and binlogs volumes
• Set custom error log - db-error.log
• Redirect server_id -> mysql_server_id
• Redirect rpl_mode -> mysql_rpl_mode
• Set value of the web_interface_pass property.
• Set Start Order=20

• Create and set user volume
• Set Start Order=10

• Redirect remote_host -> repl_ip, ip_addr -> out_ip, netmask, gateway, dns1 and dns2 properties

Appliance Recreation

1. Execute steps within the MYSQLR building procedure and recreate the MYSQLR appliance.
2. Move the just created appliance into the /user catalog to be used for the remaining tests.

Boundary Tests

appliance boundary

Verify that the db1 boundaries are according the appliance datasheet:

1. Check the appliance description and version.
2. Check the properties - verify their type, default value, available values.
3. Check the resources.
4. Check the terminals.
5. Check the user volume - the data volume should be mandatory.

Verify that the db appliance can be started/stopped with varying memory and CPU resources:

1. Start the test application and verify that it starts successfully
2. Stop the application and configure the db appliance to have minimum CPU and memory resources
3. Start the application and verify that it starts successfully

MySQL5 Functionality

1. Execute the MYSQL5 test plan using MYSQLR appliance to make sure the MYSQL5 functionality if fully covered in the new appliance considering notes below:
   • Skip "Boundary Tests -> "data" volume type" test case. The "data" volume in MYSQLR is mandatory and it must be raw volume.
   • Skip "Compatibility Tests -> SugarCRM? application" test case
   • In "Compatibility Tests -> Lamp and Lampx4 applications" test case you must use raw db.data volume.
2. In an application create instances of MYSQL and MYSQL5 classes, connect all their interfaces and parametrize all their placeholder volumes and properties. Now try to update their classes to MYSQLR using Shift + drop - it should succeed.

Properties Tests

Note: for test cases in this section use the simple-repl application.

"server_id" property

1. Start the simple-repl application (db1.server_id = db2.server_id = 1, db1.rpl_mode = db2.rpl_mode = none)
   • Verify "broken replication" error message does NOT appear on the dashboard (wait for more than 5 min).
   • Verify there are not error records in db2.err error log
   • Verify the replication is not working (execute commands below on the client):

     mysql -h db1 -D test -e "CREATE TABLE mysqlr_test1 (host_name VARCHAR (10), id INT)" 
     mysql -h db1 -D test -e "INSERT INTO mysqlr_test1 VALUES ('db1', 1)" 
     
     mysql -h db2 -D test -e "CREATE TABLE mysqlr_test2 (host_name VARCHAR (10), id INT)" 
     mysql -h db2 -D test -e "INSERT INTO mysqlr_test2 VALUES ('db2', 1)" 
     
     mysql -h db1 -D test -e "SELECT * from mysqlr_test2"  #should return "table doesn't exist" error message
     mysql -h db2 -D test -e "SELECT * from mysqlr_test1"  #should return "table doesn't exist" error message 

   • Execute the Web interface test test.
2. Create db2.rout -> db1.rin connection and restart the simple-repl application (db1.server_id = db2.server_id = 1, db1.rpl_mode = master and db2.rpl_mode = slave)
   • In 5 min db2 should send "broken replication" error message on the dashboard (one message per hour)
   • Login on the db2 UI (http://<simple-repl.in_ip>:8082/):
     • verify the replication is reported as NOT OK
     • find the problem reported in the error - equal mysql server ids values.
   • Verify the replication is not working (execute commands below on the client):

     mysql -h db1 -D test -e "CREATE TABLE mysqlr_test3 (host_name VARCHAR (10), id INT)" 
     mysql -h db1 -D test -e "INSERT INTO mysqlr_test3 VALUES ('db1', 1)" 
     
     mysql -h db2 -D test -e "CREATE TABLE mysqlr_test4 (host_name VARCHAR (10), id INT)" 
     mysql -h db2 -D test -e "INSERT INTO mysqlr_test4 VALUES ('db2', 1)" 
     
     mysql -h db1 -D test -e "SELECT * from mysqlr_test4"  #should return "table doesn't exist" error message
     mysql -h db2 -D test -e "SELECT * from mysqlr_test3"  #should return "table doesn't exist" error message

3. Set db2.server_id=2, delete the error messages on the dashboard and restart the application:
   • Verify "broken replication" error message does NOT appear on the dashboard.
   • Login on the db2 UI (http://<simple-repl.in_ip>:8082).
     • verify the replication is reported as NOT OK
     • press the "Fix / Initiate Replication" button - the action should pass successfully
     • verify the replication is reported as OK already
   • Verify the replication is working (execute commands below on the client):

     mysql -h db1 -D test -e "INSERT INTO mysqlr_test3 VALUES ('db1', 2)" 
     mysql -h db2 -D test -e "INSERT INTO mysqlr_test3 VALUES ('db2', 1)" 
     
     mysql -h db1 -D test -e "SELECT * from mysqlr_test3"  #should return 2 rows
     mysql -h db2 -D test -e "SELECT * from mysqlr_test3"  #should return 3 rows
     
     mysql -h db1 -D test -e "SELECT * from mysqlr_test4"  #should return "table doesn't exist" error message
     mysql -h db2 -D test -e "SELECT * from mysqlr_test4"  #should return "table doesn't exist" error message

"rpl_mode" property

1. Stop the application and switch on the standby attribute of all appliances excepting logs and db1.
2. Start an application setting db1.rpl_mode = none and no binlogs volume. Verify that the appliance starts successfully and no logs are written to /mnt/binlogs.
3. Set rpl_mode = master and verify that the appliance fails to start (no binlogs volume)
4. Set rpl_mode = slave and verify that the appliance fails to start (no binlogs volume)
5. Set rpl_mode = master_and_slave and verify that the appliance fails to start (no binlogs volume)
6. Add a binlogs volume. Set rpl_mode = none, connect the rout interface and verify that the appliance fails to start (connected 'rout' interface by not configured replication)
7. Remove the rout connection, set rpl_mode = slave and verify that the appliance fails to start (unconnected 'rout' interface by configured replication)
8. Set rpl_mode = master_and_slave and verify that the appliance fails to start (unconnected 'rout' interface by configured replication)
9. Switch off the db2.standby attribute. Make sure that db1.server_id = 1, db2.server_id = 2, db1.rpl_mode = master, db2.rpl_mode = slave, db2.rout -> db1.rin and start the applications:
   • Verify on db2 that replication is running, relay logs are written to /mnt/binlogs and no binary logs are written to /mnt/binlogs.
   • Verify on db1 that binary logs are written to /mnt/binlogs and no relay logs are written to /mnt/binlogs.
   • No replication related logs (binary, relay) should be written to /mnt/data or /var/log on any appliance.
10. Change rpl_mode to master_and_slave on both appliances, add blank data volumes and connect db1.rout to db2.rin. Restart the application and verify that replication is working and that binary and relay logs are written to /mnt/binlogs on both appliances.

"web_pwd" property

1. Verify the "web_pwd" is NOT mandatory. Verify you can start the the appliance with empty web password if the ui terminal is not connected.
2. Make sure the "web_pwd" property is empty. Connect the ui terminal and try to start the application - it should fail to start.
3. Set some password, start the application and verify the web interface is accessible with the specified password and no username.

Volumes tests

1. Stop the application and switch on the standby attribute of all appliances excepting logs and db1.
2. Set db1.rpl_mode = none.
3. Try to start the application configuring db1.data with a volume that has been formatted with some fs (try all available fs types) - the appliance should fail to start.
4. Set a raw data volume and start the appliance - it should start successfully.
5. Verify the appliance starts successfully with binlogs volumes that have been formatted with all kinds of file systems - ext2, ext3 and reiserfs.
6. Add an old_data volume (no content or fs is required) and restart the application - it should fail (you have supplied both a non-blank data volume and an old_data volume).
7. Verify the appliance starts successfully with old_data volumes that have been formatted with all kinds of file systems - ext2, ext3 and reiserfs (you must specify new raw data volume every time)
8. Test that disk space notifications work - if disk usage on data or binlogs volumes is > 80%, a notification should be sent to the dashboard (in 5 min after appliance starting).
   • Full binlogs notification should not be sent if you don't have setup replication and the binlogs volume is not set.
   • Notifications should not be sent more than once per hour.
   • Login to the db1 UI and verify you have red light for disk usage on the Status tab (start ps8 and in appliances).

Web interface test (basic tests)

This test plan includes just basic tests cases. More complex test will be run in the appliance use cases.

1. Request http://<simple-repl.in_ip>:8081 to access the db1 UI (login with empty username and set password).
2. Verify the application and appliance name are properly displayed on top of the page.
3. Status tab (the default tab):
   • Verify the displayed info
   • Verify the error log is properly displayed (you should see the last error message)
   • Verify you see green lamps if everything is OK and red lamps if somethings wrong.
   • Press the "Refresh page" button to verify it's working
   • Stop/start/restart the service
4. Manage Replication tab:
   • Verify the replication status
   • If there isn't configured replication you should see just the status and "Reset master log position" button
   • If the replication is configured you should see "Initiate/Fix Replication" notice and button.
   • Press the "Refresh page" button to verify it's working
5. Manage Database tab:
   • Verify the phpmyadmin is working - create a db, create a table, add content in the table, browse a db, destroy a table.
6. Migrate Content tab
   • Verify it's not shown if old_data volume is not configured
7. Help tab - link to the MYSQLR datasheet

Use Cases

Master-slave replication

1. Start the simple-repl application with new data and binlogs volumes (=db1.server_id = 1, db2.server_id = 2, db1.rpl_mode = master, db2.rpl_mode = slave).
2. Login to the UI of db1 and db2 and verify the replication status.
3. Verify the replication is working (execute commands below on the client):

   mysql -h db1 -D test -e "CREATE TABLE mysqlr_test1(host_name VARCHAR (10), id INT)" 
   mysql -h db1 -D test -e "INSERT INTO mysqlr_test1 VALUES ('db1', 1)" 
   
   mysql -h db2 -D test -e "CREATE TABLE mysqlr_test2(host_name VARCHAR (10), id INT)" 
   mysql -h db2 -D test -e "INSERT INTO mysqlr_test2 VALUES ('db2', 1)" 
   
   mysql -h db1 -D test -e "SELECT * from mysqlr_test2"  #should return "table doesn't exist" error message
   mysql -h db2 -D test -e "SELECT * from mysqlr_test1"  #should return 1  row

   
   

Add slave server to existing master-slave replication

1. Switch off the db3.standby attribute, connect db3.rout to db1.rin, set db3.server_id = 3 and db3.rpl_mode = slave and restart the application.
2. Login to the UI of db1, db2 and db3 and verify the replication status.
3. Verify the replication is working (execute commands below on the client):

   mysql -h db1 -D test -e "INSERT INTO mysqlr_test1 VALUES ('db1', 2)" 
   mysql -h db2 -D test -e "SELECT * from mysqlr_test1"  #should return 2  rows
   mysql -h db3 -D test -e "SELECT * from mysqlr_test1"  #should return 2  rows

   
   

Master-master replication

1. Switch on the db3.standby attribute, connect db1.rout to db2.rin and restart the application. (db1.server_id = 1, db2.server_id = 2, db1.rpl_mode = db2.rpl_mode = master_and_slave).
2. Login to the UI of db1 and db2 and verify the replication status (the replication status should be reported as OK, but db1 should have just one table in the test database and db2 should have two tables)
3. In the UI of db1, Manage Replication tab press the "Fix / Initiate Replication" button.
4. After replication is initiated, login to the UI of db2, Manage Replication tab and click "Reset Master Log Position" button.
5. Verify the replication is working (execute commands below on the client):

   
   mysql -h db1 -D test -e "INSERT INTO mysqlr_test1 VALUES ('db1', 3)" 
   mysql -h db1 -D test -e "INSERT INTO mysqlr_test2 VALUES ('db1', 1)" 
   mysql -h db2 -D test -e "INSERT INTO mysqlr_test1 VALUES ('db2', 1)" 
   mysql -h db2 -D test -e "INSERT INTO mysqlr_test2 VALUES ('db2', 2)" 
   
   mysql -h db1 -D test -e "SELECT * from mysqlr_test1"  #should return 4  rows
   mysql -h db2 -D test -e "SELECT * from mysqlr_test1"  #should return 4  rows
   mysql -h db1 -D test -e "SELECT * from mysqlr_test2"  #should return 3  rows
   mysql -h db2 -D test -e "SELECT * from mysqlr_test2"  #should return 3  rows

   
   
6. In the UI of both appliances verify the replication is in OK state.

Add master server to existing master-master replication

1. Switch off the db3.standby attribute and create new db3.data and db3.binlogs volumes.
2. Restart the simple-repl application (db1.server_id = 1, db2.server_id = 2, db3.server_id = 3, db1.rpl_mode = db2.rpl_mode = db3.rpl_mode = master_and_slave, db1.rout -> db3.rin, db2.rout -> db1.rin, db3.rout -> db2.rin).
3. Login to the UI of db3, Manage Replication tab and click "Initiate / Fix Replication" button.
4. After replication is initiated, login to the UI of db1, Manage Replication tab and click "Reset Master Log Position" button.
5. In the UI of all three appliances verify the replication is in OK state.
6. Verify the replication is working (execute commands below on the client):

   mysql -h db1 -D test -e "INSERT INTO mysqlr_test1 VALUES ('db1', 4)" 
   mysql -h db2 -D test -e "INSERT INTO mysqlr_test2 VALUES ('db2', 3)" 
   
   mysql -h db3 -D test -e "CREATE TABLE mysqlr_test3 (host_name VARCHAR (10), id INT)" 
   mysql -h db3 -D test -e "INSERT INTO mysqlr_test3 VALUES ('db3', 1)" 
   mysql -h db3 -D test -e "INSERT INTO mysqlr_test3 VALUES ('db3', 2)" 
   
   mysql -h db1 -D test -e "SELECT * from mysqlr_test1"  #should return 5 rows
   mysql -h db1 -D test -e "SELECT * from mysqlr_test2"  #should return 4 rows
   mysql -h db1 -D test -e "SELECT * from mysqlr_test3"  #should return 2 rows
   mysql -h db2 -D test -e "SELECT * from mysqlr_test1"  #should return 5 rows
   mysql -h db2 -D test -e "SELECT * from mysqlr_test2"  #should return 4 rows
   mysql -h db2 -D test -e "SELECT * from mysqlr_test3"  #should return 2 rows
   mysql -h db3 -D test -e "SELECT * from mysqlr_test1"  #should return 5 rows
   mysql -h db3 -D test -e "SELECT * from mysqlr_test2"  #should return 4 rows
   mysql -h db3 -D test -e "SELECT * from mysqlr_test3"  #should return 2 rows

   
   
7. In the UI of the three appliances verify the replication is in OK state.
8. Brake replication between two servers: start a large insert/update on db1 and cancel it before it completes.
9. Verify that db2 reports the replication as broken.
10. Login to the UI of db2, Manage Replication tab and press the "Initiate / Fix Replication" button.
11. Login to the UI of db3, Manage Replication tab and press the "Reset Master Log Position" button.
12. Login to the UI of all three appliances and verify that replication status is OK.

Remote master-slave replication

1. Make two copies of external-repl application - ext1 and ext2.
2. Configure mandatory properties of both applications:
   • Both application must have different IP addresses
   • ext1.rin_db_ip must be the same as ext2.repl_ip and ext2.rin_db_ip must be the same as ext1.repl_ip
   • Set ext1.mysql_server_id = 1 and ext2.mysql_server_id = 2.
   • Set ext1.mysql_rpl_mode = master and ext2.mysql_rpl_mode = slave.
3. Start both applications.
4. Login on ext1:main.web and execute mysql -h db -D php -e 'update phpbb_config set config_value="<ext1.in_ip>" where config_name="server_name"';
5. Login on ext2:main.web and execute mysql -h db -D php -e 'update phpbb_config set config_value="<ext2.in_ip>" where config_name="server_name"';
6. Request http://<ext1.in_ip>/phpBB2 address in a browser and add few threads/posts. Open http://<ext2.in_ip>/phpBB2 - you should see the new threads/posts there.
7. Request http://<ext2.in_ip>/phpBB2 address in a browser and add few threads/posts. Open http://<ext1.in_ip>/phpBB2 - you should NOT see the new threads/posts there.
8. Stop and destroy the applications.

Remote master-master replication

1. Make two copies of external-repl application - ext1 and ext2.
2. Configure mandatory properties of both applications:
   • Both application must have different IP addresses
   • ext1.rin_db_ip must be the same as ext2.repl_ip and ext2.rin_db_ip must be the same as ext1.repl_ip
   • Set ext1.mysql_server_id = 1 and ext2.mysql_server_id = 2.
   • Set ext1.mysql_rpl_mode = master_and_slave and ext2.mysql_rpl_mode = master_and_slave.
3. Start both applications.
4. Login on ext1:main.web and execute mysql -h db -D php -e 'update phpbb_config set config_value="<ext1.in_ip>" where config_name="server_name"';
5. Request http://<ext1.in_ip>/phpBB2 address in a browser and add few threads/posts. Open http://<ext2.in_ip>/phpBB2 - you should see the new threads/posts there.
6. Login on ext2:main.web and execute mysql -h db -D php -e 'update phpbb_config set config_value="<ext2.in_ip>" where config_name="server_name"';
7. Request http://<ext2.in_ip>/phpBB2 address in a browser and add few threads/posts. Open http://<ext1.in_ip>/phpBB2 - you should see the new threads/posts there.
8. Stop and destroy the applications.

Remote multi-master replication

1. Make three copies of external-repl application - ext1, ext2 and ext3.
2. Configure mandatory properties of all applications:
   • All application must have different IP addresses.
   • ext1.rin_db_ip must be the same as ext2.repl_ip, ext2.rin_db_ip must be the same as ext3.repl_ip and ext3.rin_db_ip must be the same as ext1.repl_ip
   • Set ext1.mysql_server_id = 1, ext2.mysql_server_id = 2 and ext3.mysql_server_id = 3.
   • Set mysql_rpl_mode = master_and_slave on all applications.
3. Start the applications.
4. Login on ext1:main.web and execute mysql -h db -D php -e 'update phpbb_config set config_value="<ext1.in_ip>" where config_name="server_name"';
5. Request http://<ext1.in_ip>/phpBB2 address in a browser and add few threads/posts. Open http://<ext2.in_ip>/phpBB2 and http://<ext3.in_ip>/phpBB2 - you should see the new threads/posts there.
6. Login on ext1:main.web and execute mysql -h db -D php -e 'update phpbb_config set config_value="<ext2.in_ip>" where config_name="server_name"';
7. Request http://<ext2.in_ip>/phpBB2 address in a browser and add few threads/posts. Open http://<ext1.in_ip>/phpBB2 and http://<ext3.in_ip>/phpBB2 - you should see the new threads/posts there.
8. Login on ext1:main.web and execute mysql -h db -D php -e 'update phpbb_config set config_value="<ext3.in_ip>" where config_name="server_name"';
9. Request http://<ext3.in_ip>/phpBB2 address in a browser and add few threads/posts. Open http://<ext1.in_ip>/phpBB2 and http://<ext3.in_ip>/phpBB2 - you should see the new threads/posts there.
10. Stop and destroy the applications.

MYSQL5 to MYSQLR migration in existing application

1. Provision the SugarCRM template creating scrm application.
2. Start the scrm application, login and add some accounts/contacts/etc.
3. Stop the scrm application.
4. Create data_new raw volume.
5. Change the dbase class to MYSQLR.
6. Set server_id = 1, rpl_mode = none, volume data = data_new, volume old_data = data. Set the web_pwd property.
7. Add and configure IN gateway connected to dbase.rin.
8. Start the application, login to the web interface of dbase and migrate the content from old_data volume.
9. Login to the SugarCRM application and add some new accounts/contacts/etc.
10. Verify all created accounts/contacts are there.
11. Stop and destroy the application.

Add DB replication to existing application

1. Provision the SugarCRM template creating scrm application.
2. Start the scrm application, login and add some accounts/contacts/etc.
3. Stop the scrm application.
4. Create data_new1 and data_new2 raw volumes and blog1 and blog2 ext3 volumes.
5. Change the dbase class to MYSQLR.
6. Set server_id = 1, rpl_mode = master, volume data = data_new1, volume binlogs = blog1, volume old_data = data. Set the web_pwd property.
7. Add appliance db_repl of class MYSQLR with data_new2 and blog2 user volumes. Set server_id = 2, rpl_mode = slave and Start Order = 20. Set the web_pwd property.
8. Connect db_repl.rout output to dbase.rin.
9. Add and configure IN gateway connected to dbase.rin.
10. Add and configure second IN gateway connected to db_repl.rin.
11. Start the application, login to the UI of dbase and migrate the content from old_data volume.
12. Login to the UI of db_repl, Manage Replication tab and press "Fix / Initiate Replication" button
13. Login to the SugarCRM application and add some new accounts/contacts/etc.
14. Login to the UI of db_repl and verify the replication is working.
15. Stop and destroy the application.

Misc

Renaming of replication appliances

1. In simple-repl application setup master with two slaves replication using db1, db2 and db3 appliances.
2. Start the simple-repl application.
3. Execute on the client appliance:

   mysql -h db1 -D test -e "CREATE TABLE mysqlr_test1(host_name VARCHAR (10), id INT)" 
   mysql -h db1 -D test -e "INSERT INTO mysqlr_test1 VALUES ('db1', 1)" 
   
   mysql -h db2 -D test -e "CREATE TABLE mysqlr_test2 (host_name VARCHAR (10), id INT)" 
   mysql -h db2 -D test -e "INSERT INTO mysqlr_test2 VALUES ('db2', 1)" 
   
   mysql -h db3 -D test -e "CREATE TABLE mysqlr_test3 (host_name VARCHAR (10), id INT)" 
   mysql -h db3 -D test -e "INSERT INTO mysqlr_test3 VALUES ('db3', 1)" 
   
   mysql -h db1 -D test -e "SELECT * from mysqlr_test2"  #should return "table doesn't exist" error message
   mysql -h db1 -D test -e "SELECT * from mysqlr_test3"  #should return "table doesn't exist" error message
   
   mysql -h db2 -D test -e "SELECT * from mysqlr_test1"  #should return 1 row
   mysql -h db2 -D test -e "SELECT * from mysqlr_test3"  #should return "table doesn't exist" error message
   
   mysql -h db3 -D test -e "SELECT * from mysqlr_test1"  #should return 1 row
   mysql -h db3 -D test -e "SELECT * from mysqlr_test2"  #should return "table doesn't exist" error message

1. Rename appliances (db1->db11, db2->db22 and db3->db33) and restart the application.
2. Execute on the client appliance:

   mysql -h db1 -D test -e "INSERT INTO mysqlr_test1 VALUES ('db1', 2)" 
   mysql -h db2 -D test -e "INSERT INTO mysqlr_test2 VALUES ('db2', 2)" 
   mysql -h db3 -D test -e "INSERT INTO mysqlr_test3 VALUES ('db3', 2)" 
   
   mysql -h db1 -D test -e "SELECT * from mysqlr_test2"  #should return "table doesn't exist" error message
   mysql -h db1 -D test -e "SELECT * from mysqlr_test3"  #should return "table doesn't exist" error message
   
   mysql -h db2 -D test -e "SELECT * from mysqlr_test1"  #should return 2 rows
   mysql -h db2 -D test -e "SELECT * from mysqlr_test3"  #should return "table doesn't exist" error message
   
   mysql -h db3 -D test -e "SELECT * from mysqlr_test1"  #should return 2 rows
   mysql -h db3 -D test -e "SELECT * from mysqlr_test2"  #should return "table doesn't exist" error message

Binary logs rotation/purge

1. In simple-repl application setup master <-- slave replication using db1 and db2 appliances.
2. Set db3 appliance in standby mode.
3. Create two big data and two big binlogs volumes (4G) and set them as user volumes of db1 and db2.
4. Start the simple-repl application.
5. Login on db2 appliance and stop mysqld.
6. Login on db1 appliance and restart few times the mysqld daemon - each time a new binary log file should be created in /mnt/binlogs.
7. Login on the client appliance and execute:
   • Copy /mnt/data/dbt to /mnt/data on the db1 appliance
   • cd /src/dbt2-v0.23/scripts/mysql
   • for i in `seq 1 5`; do sh mysql_load_db.sh -d dbt$i -f /mnt/data/dbt -h db1; done
8. Monitor the /mnt/binlogs directory on db1 appliance and verify mysqld starts to write new binary log once the old one has reached ~100M size.
9. Login on db2 appliance and start mysqld - verify in 2 min db2 will send "slave too much behind master" error message on the dashboard (one message per hour).
10. Wait some minutes (use show slave status to verify the replication has been finished) and verify both appliances have the same databases.
11. Verify that all executed relay logs in db2:/mnt/binlogs have been removed.
12. Verify binary logs in db1:/mnt/binlogs have been removed once they get > 2days old (`mysql -e "show variables" | grep expire_logs_days` should return 2).

Mysql tuning

Verifies some performance tuning properties

1. Start the simple-repl application if not running.
2. Login on the db1 appliance and verify /etc/my.cnf file contains following properties:

   [mysqld]
   key_buffer = <available_memory> /2
   max_allowed_packet = 16M
   table_cache = 256
   sort_buffer_size = 1M
   read_buffer_size = 1M
   read_rnd_buffer_size = 4M
   myisam_sort_buffer_size = 64M
   thread_cache_size = 8
   query_cache_size= 16M
   thread_concurrency = 8
   
   tmpdir=/mnt/data
   

Test big DB migration

1. Create a 10GB volume and use in MYSQL or MYSQL5 appliance, fill with data (import sql dumps)
2. Add the full volume to the old_data placeholder of MYSQLR appliance, add a new blank data volume with 10G size
3. Start the application, login to the web interface of MYSQLR and do 'migrate content', it should complain of insufficient space (if you filled the old_data volume)
4. Add a new data volume with 15G size
5. Restart the application, login to the web interface of MYSQLR and do 'migrate content'. If you use INSSL for accessing MYSQLR, make sure the timeout property is set to a high value (36000) to avoid timeouts. Also make sure there are no timeouts in any proxies on the client side (better not use proxy at all).
6. Verify that all of the content is successfully migrated

Test replication initiation with big DB

1. Add a slave MYSQLR appliance to the application above with 15G data volume
2. Login to the web interface of the slave and do 'fix/initiate replication'. Verify that the operation succeeds and the replication is working. If you use INSSL for accessing MYSQLR, make sure the timeout property is set to a high value (36000) to avoid timeouts. Also make sure there are no timeouts in any proxies on the client side (better not use proxy at all).

Test Results

1st pass

Test Status

Test problems/notes