/

Create MySQL Database


  1. From the left-hand navigation of the Digital Ocean dashboard, under the Manage section, choose Databases
  2. Click on Create in top left, then in pull-down choose Databases
  3. Under Create a database cluster, choose MySQL 8; choose a node plan - if you are going to have high-volume traffic, you will want more memory/CPU/disk space. For basic testing, you can choose the Basic 1G/1CPU/10GB Disk plan, but note that per this article the Basic machine types use Shared CPU.
  4. Choose the datacenter where your Stream Manager droplet resides. db01a
  5. Under Finalize and create choose a unique database cluster name (or accept the assigned name) and select your project, then click on Create a Database Cluster. db02
  6. After the database is provisioned, you can secure and configure the database cluster. NOTE: this can take 10-15 minutes, so now is a good time to get up and stretch your legs.
  7. Add trusted sources - from the Overview tab, cilck on Secure this database cluster by restricting access. in the TRUSTED SOURCES section. Click on the Edit button next to Trusted Sources. Find your stream manager and your terraform server in the Add Trusted Sources pull-down; you can also add your IP address. Then click on Allow these inbound sources only.
  8. Make a note of the time for scheduled updates; if you want to change that time you can modify that in settings.
  9. Click on the Users & Databases tab. Add a new database called cluster. db05
  10. You can either use the auto-generated doadmin account, or add an autoscaling admin User account to the database. The default, Default – MySQL 8+ encryption is OK. A secure password will be generated - make a note of that password.

Add cluster schema

To connect to the mysql instance which we just created, we suggest using the freeware MySQL workbench tool, and can be downloaded from https://dev.mysql.com/downloads/workbench/.

  • Launch MySQL Workbench tool on your system
  • Click on the “+” icon to the right of MySQL Connections to open the new connection configuration window.
  • Enter a connection name - (for example, “aws-mysql”)
  • Hostname: the database host URL
  • Port: 25060
  • Username: the Database Username configured above
  • Password: the Database password configured above
  • Default Schema: cluster (database schema for streammanager)
  • Click on Test Connection to verify you can connect. - if you are not on the system that was added as "my IP" when you created the RDS security policy, this may fail.
  • Click “ok” to save configuration and close window.
  • Download the Red5 Pro Server Distribution, and unzip to your desktop.
  • Double click on the new connection you created to open database workspace view.
  • Download the Red5 Pro Server Distribution, and unzip to your desktop.
  • From menu click => File => Open SQL Script, and browse the server distribution to find the cluster.sql file {red5prohome}/webapps/streammanager/WEB-INF/sql/cluster.sql
  • Click the execute icon to execute the sql script which populates the cluster database execute clusterdbpopulated

Database Connections

You will need 3 database connections for each node (origin, edge, etc). In addition, each incoming publish and subscribe request queries the database, so for production traffic you will likely want to choose the highest available database node type.

NOTE: For production it is suggested that you also enable a standby database node.

Digital Ocean-Hosted MySQL backend connections

Digital Ocean Hosted MySQL nodes can have up to 75 simultaneous connections per gigabyte of usable memory, rounded down to the nearest gigabyte. Usable memory is the total memory on the node minus approximately 350 MB of overhead for the operating system and management.

Plan SizeAvailable Backend Connections
1 GB RAM75
2 GB RAM150
4 GB RAM225
8 GB RAM525
16 GB RAM1,050
32 GB RAM2,175
64 GB RAM4,425

max_connections is set to one connection higher to accommodate a system process that does not count against the node’s connection limit. For example, a 4 GB node has roughly 3.6 GB of usable memory. It can have up to 3 * 75 = 225 connections and its max_connections is set to 226.

Optional - Run MySQL on a Droplet

You may choose to run MySQL on a droplet instead of via the Databases to minimize costs or for more management options (for example, you can set a higher max_connections value than the managed databases allow). The upside to using the Digital Ocean database is the standby node option, for database failover.

  1. Create a new droplet from the optimized image that you created. It is recommended that you use a c-4 or c-8 instance, depending on your anticipated user load.
  2. Install NTP and unzip: apt-get install unzip ntp
  3. Install MySQL: apt-get install mysql-server - during the MySQL installation, you will be prompted to set the root password - make a note of what you set this to.
  4. IMPORTANT: Modify /etc/mysql/mysql.conf.d/mysqld.cnf commenting out the line: bind-address = 127.0.0.1 by pre-pending with a #; Also, uncomment the max_connections line and set that to 100000 (max_connections = 100000). (NOTE: for a development environment, you may want to run Stream Manager, Terraform and MySQL all on the same droplet. In this case, you would leave the default bind-address setting)
  5. Restart MySQL service systemctl restart mysql to apply the above settings
  6. Add non-root user:
  7. Connect to database as root mysql -u root -p (enter root password).
  8. Create new user: at mysql> prompt: CREATE USER 'mynewuser'@'%' IDENTIFIED BY 'goodPassword'; where mynewuser is your dbadmin and goodPassword is your secure password.
  9. If you are successful, you will see Query OK, 0 rows affected (0.00 sec)
  10. Apply privileges to the new user: GRANT ALL PRIVILEGES ON * . * TO 'mynewuser'@'%';
  11. Reload the privileges to make sure the new ones are in place: FLUSH PRIVILEGES;
  12. Create cluster database:
  13. Connect with the new user: type quit to logout root user, then mysql -u mynewuser -p
  14. Create database: CREATE DATABASE cluster;
  15. Add Cluster schema:
  16. Download the Red5 Pro Server Distribution, and unzip on your server.
  17. Navigate to {red5prohome}/webapps/streammanager/WEB-INF/sql/ to find the cluster.sql file.
  18. Run the sql script: mysql -u mynewuser -p cluster < cluster.sql (you will be prompted to enter the password that you set above).