Unmanaged Tablet

This guide follows on from the local installation guide.

This guide uses the Vitess components vtctld, Topology Service and VTGate which have already been started in the local installation guide. It assumes that you have an existing MySQL Server setup that you would like to add to Vitess as a new keyspace, which we will call legacy. The same set of steps can be used to create a tablet that uses Amazon RDS, AWS Aurora, or Google CloudSQL.

Ensure all components are up

You should have previously executed ./101_initial_cluster.sh in the get-started guide. This will ensure that you have a Topology Service, vtgate, vtctld. For the unmanaged MySQL instance, I will be using an instance running on 127.0.0.1:5726:

  1. source env.sh
  3. # verify vtgate/vitess is up and running
  4. mysql commerce -e 'show tables'
  6. # verify my unmanaged mysql is running
  7. mysql -h 127.0.0.1 -P 5726 -umsandbox -pmsandbox legacy -e 'show tables'

Output:

  1. ~/vitess/examples/local$ source env.sh
  2. ~/vitess/examples/local$ 
  3. ~/vitess/examples/local$ # verify vtgate/vitess is up and running
  4. ~/vitess/examples/local$ mysql commerce -e 'show tables' 
  5. +-----------------------+
  6. | Tables_in_vt_commerce |
  7. +-----------------------+
  8. | corder                |
  9. | customer              |
  10. | product               |
  11. +-----------------------+
  12. ~/vitess/examples/local$ # verify my unmanaged mysql is running 
  13. ~/vitess/examples/local$ mysql -h 127.0.0.1 -P 5726 -umsandbox -pmsandbox legacy -e 'show tables'
  14. mysql: [Warning] Using a password on the command line interface can be insecure.
  15. +------------------+
  16. | Tables_in_legacy |
  17. +------------------+
  18. | legacytable      |
  19. +------------------+

Start a tablet to correspond to legacy

The variables TOPOLOGY_FLAGS and VTDATAROOT should already be in the environment from sourcing env.sh earlier. We will call the new tablet UID 401.

  1. mkdir -p $VTDATAROOT/vt_0000000401
  2. vttablet \
  3.  $TOPOLOGY_FLAGS \
  4.  --logtostderr \
  5.  --log_queries_to_file $VTDATAROOT/tmp/vttablet_0000000401_querylog.txt \
  6.  --tablet-path "zone1-0000000401" \
  7.  --init_keyspace legacy \
  8.  --init_shard 0 \
  9.  --init_tablet_type replica \
  10.  --port 15401 \
  11.  --grpc_port 16401 \
  12.  --service_map 'grpc-queryservice,grpc-tabletmanager,grpc-updatestream' \
  13.  --pid_file $VTDATAROOT/vt_0000000401/vttablet.pid \
  14.  --db_host 127.0.0.1 \
  15.  --db_port 5726 \
  16.  --db_app_user msandbox \
  17.  --db_app_password msandbox \
  18.  --db_dba_user msandbox \
  19.  --db_dba_password msandbox \
  20.  --db_repl_user msandbox \
  21.  --db_repl_password msandbox \
  22.  --db_filtered_user msandbox \
  23.  --db_filtered_password msandbox \
  24.  --db_allprivs_user msandbox \
  25.  --db_allprivs_password msandbox \
  26.  --init_db_name_override legacy \
  27.  --init_populate_metadata \
  28.  --disable_active_reparents &

Note that if your tablet is using a MySQL instance type where you do not have SUPER privileges to the database (e.g. AWS RDS, AWS Aurora or Google CloudSQL), you should omit the --init_populate_metadata flag. The --init_populate_metadata flag should only be enabled if the cluster is being managed through Vitess.

You should be able to see debug information written to screen confirming Vitess can reach the unmanaged server. A common problem is that you may need to change the authentication plugin to mysql_native_password (MySQL 8.0).

Assuming that there are no errors, after a few seconds you can mark the server as externally promoted to primary:

  1. vtctldclient TabletExternallyReparented zone1-401

Connect via VTGate

VTGate should now be able to route queries to your unmanaged MySQL server:

  1. ~/vitess/examples/local$ mysql legacy -e 'show tables'
  2. +------------------+
  3. | Tables_in_legacy |
  4. +------------------+
  5. | legacytable    |
  6. +------------------+

You can even join between the unmanaged tablet and the managed tablets. Vitess will execute the query as a scatter-gather:

  1. mysql> use commerce;
  2. Database changed
  4. mysql> select corder.order_id from corder inner join legacy.legacytable on corder.order_id=legacy.legacytable.id;
  5. Empty set (0.01 sec)

Move legacytable to the commerce keyspace

Move the table:

  1. vtctlclient MoveTables -- --source legacy --tables 'legacytable' Create commerce.legacy2commerce

Monitor the workflow:

use Show or Progress

  1. vtctlclient MoveTables -- Show commerce.legacy2commerce
  2. vtctlclient MoveTables -- Progress commerce.legacy2commerce

You can also use the Workflow show command to get the progress as it has much more info as well.

  1. vtctlclient Workflow commerce.legacy2commerce show

Switch traffic:

  1. vtctlclient MoveTables -- --tablet_type=rdonly,replica SwitchTraffic commerce.legacy2commerce

Complete the MoveTables:

  1. vtctlclient MoveTables Complete commerce.legacy2commerce

Verify that the table was moved:

  1. source env.sh
  3. # verify vtgate/vitess is up and running
  4. mysql commerce -e 'show tables'
  6. # verify my unmanaged mysql is running
  7. mysql -h 127.0.0.1 -P 5726 -umsandbox -pmsandbox legacy -e 'show tables'

Output:

  1. ~/vitess/examples/local$ source env.sh
  2. ~/vitess/examples/local$ 
  3. ~/vitess/examples/local$ # verify vtgate/vitess is up and running
  4. ~/vitess/examples/local$ mysql commerce -e 'show tables' 
  5. +-----------------------+
  6. | Tables_in_vt_commerce |
  7. +-----------------------+
  8. | corder                |
  9. | customer              |
  10. | legacytable           |
  11. | product               |
  12. +-----------------------+
  13. ~/vitess/examples/local$ # verify my unmanaged mysql is running 
  14. ~/vitess/examples/local$ mysql -h 127.0.0.1 -P 5726 -umsandbox -pmsandbox legacy -e 'show tables'
  15. mysql: [Warning] Using a password on the command line interface can be insecure.