Self-managed MySQL vs Cloud SQL for MySQL

The self-managed MySQL option means that the user, in most cases a database administrator, is responsible for most of the activities involved in the setup, installation, configuration and maintenance of the database, the operation system (OS), networking, storage, the virtual machine (VM) instance, and the hardware, which hosts the database instance and server. Some of these tasks, like hardware, VM instance, storage, networking, and operating system tasks, can be accomplished more easily by leveraging a cloud service provider like Google Cloud. Regardless of whether a user is self managing a database on premises or self managing in the cloud, they are responsible for some major database management tasks necessary for a smooth running of the system. These tasks are listed below.

• Availability: Hardware and software components are bound to fail. However, the responsibility for designing the system in such a way that it is tolerant to hardware and software failures and ensures continuous availability of the database, the server, and the application lies with the user.  

• Database maintenance: In the self-managed MySQL option users need to keep the various layers of the application stack updated with the latest versions of the software, make decisions on support of end-of-life, unsupported, or legacy versions of the hardware, operating system, and other software. Additionally, users need to install software, OS, and MySQL patches regularly while keeping the database up and running without major interruptions.

• Scalability: As application usage increases over time, the need for improved database performance increases. Similarly, to support a large workload or customer base, hardware resources need to be scaled up or scaled out. This responsibility lies with the user in case of self-managed MySQL. Scaling hardware resources in an on-premises environment is a highly time consuming and complex activity, which sometimes incurs system downtime.
• Security: In recent times security has become the most important aspect which spans across multiple layers of the database system starting from physically securing the hardware to configuring networking firewalls, securing database connections, and more. These are complex activities that require significant effort and specialized expertise in cases of self-managed setup.

• Monitoring: Apart from the setup, installation, and configuration of MySQL, users will also need to worry about monitoring various components across all the layers of the stack, such as monitoring at hardware level, OS, MySQL, and taking corrective actions in case of failures.

The diagram below shows the responsibility of the user when opting for the self-managed setup. While self management on Google Cloud reduces some tasks related to hardware, compute, networking, and storage, the responsibilities for management of the remaining layers of the stack are still with the user. The database administrator and application developer still have to spend sufficient time, energy, and resources managing MySQL.

Cloud SQL is a fully managed database service that offers ease of use for setup configuration, maintenance, management, and administration of MySQL, PostgreSQL and SQL Server databases on Google Cloud. Cloud SQL manages all the aspects of hardware, networking, OS, security, monitoring, maintenance, availability, and scaling of the MySQL database. All these responsibilities are obscured from the users, allowing them to focus on application development and delivering value to their customers.

The diagram below is similar to the diagram above which shows the tasks involved in managing the various layers of the MySQL database, with the only difference being that all tasks that Cloud SQL manages on behalf of the user are illustrated in blue.

Other than managing the many tasks illustrated above, Cloud SQL offers various features and solutions that are not available natively in a self-managed environment. Some major features offered by Cloud SQL are listed below.

Reliability: Backup and recovery are the most important activities in database management. Cloud SQL offers automated backup and on-demand backups, which can be used to restore the database to a point in time in case of corruption, disaster, or unintended data modifications. 

Availability: Cloud SQL provides high availability by instantiating multiple servers hosting the database across multiple zones with automated failover capability to provide high availability in case of failure of a VM or a data center. Cloud SQL guarantees availability of greater than 99.95% anywhere in the world.

Apart from the high availability feature, Cloud SQL also offers a 24x7 Site Reliability Engineering (SRE) team that is responsible for ensuring reliability of the Cloud SQL for MySQL database instances on Google Cloud.

Cross region read replicas is another feature that can be used as a disaster recovery mechanism in case of any failures. It allows a user to quickly bring up the impacted database in another site (region) with minimal impact to the business. 

Scalability: Cloud SQL offers creation of MySQL read replicas, such as additional read-only instances of MySQL, in the same region or in remote regions allowing the servicing of requests from the replicas as well as primary instance, thereby providing better read performance. Read replicas can also be used for the reporting purposes and can be destroyed after business needs are met.

Cloud SQL enables scale up of resources (CPU, memory, and storage) and scale down of resources on the MySQL instance with very minimal downtime. Whereas, in case of self-managed setup, these complicated activities require significant effort, expertise, and planning. 

Security: Cloud SQL for MySQL database servers are located in Google Cloud secured data centers ensuring the highest level of security. Furthermore, Google Cloud offers private connectivity with Virtual Private Cloud and user-controlled network access with firewall protection, which ensures that user database servers are beyond the reach of malicious actors.

Another aspect of security is compliance with security regulations. Cloud SQL is compliant with many recognized regulations like SSAE 16, ISO 27001, PCI DSS, and HIPAA. 

Cloud SQL offers the ability to manage users and their access to the database through IAM integration. This integration manages authorization and authentication for users of Google Cloud and provides granular access to specific Google Cloud resources thereby preventing unauthorized access to the database. Furthermore, Cloud SQL makes identity and access control easy by providing predefined roles designed to control access to your Cloud SQL resources. 

Another security feature of Cloud SQL is the encryption of data at rest and in transit, ensuring that no unauthorized user will be able to access user data.

Cloud SQL offers a password validation feature for database users, which lets customers define password policy for password complexity, minimum password length, password change interval, and use of username in the password at the instance level. Customers can also define policy for password expiration days, number of failed attempts, and old password change requirements at the individual user level. These features in Cloud SQL provide security compliance best practices and reduce the risk of unauthorized access for the critical data.

Cloud SQL also provides a way to audit actions and operations on the MySQL database using an audit log plugin. Audit log provides insights into who did, what action, on which object, and when. Database auditing lets you track specific user actions in the database, such as table updates, read queries, user privilege grants, and others. 

Observability: Cloud SQL offers various monitoring solutions and has a growing list of out of the box metrics, which provide insights into the state of the database. Users can make use of the metrics to create alerts to keep administrators aware of the health of the database. 

Cloud SQL also provides features like Query insights and recommenders, allowing users to troubleshoot and identify the performance bottlenecks of a query or workload. These features are hard to replicate in a self-managed MySQL environment.

Integration: Cloud SQL offers easy networking connectivity and integration with other services within Google Cloud like Google Kubernetes Engine, Compute Engine, and Cloud Run which make it easy to develop deeply integrated solutions. Cloud SQL also offers native integration with BigQuery and Looker which can be used for the analytical processing of the data present in Cloud SQL.

Patching: As a managed service, Cloud SQL automatically updates instances to ensure that the underlying hardware and operating system are reliable, performant, secure, and up-to-date. Most of these updates are performed while your Cloud SQL instance is up and running. However, certain system updates may require a brief service interruption, for which the users can define maintenance windows for the upgrade activity. 

Version upgrades: Cloud SQL also provides an automated process of upgrading the database (MySQL) version. Users can also upgrade the major version by migrating the data to an instance of a higher version.  

Learn how to create MySQL instance in Cloud SQL here.

Pros

1. Ability to use any MySQL fork from the MySQL ecosystem like MariaDB or Percona to set up MySQL.
2. Flexibility on the configuration and setup of MySQL as users have complete control of the instance, resources, and operating system. Furthermore, users can use the OS of their choice, configure the OS as they like, and configure MySQL according to their needs, without any limitations.
3. Users are not constrained by the limitations of Cloud SQL, like maximum storage size of 64 TB. Users can exceed the 64 TB storage limit and use much higher compute infrastructure to host MySQL.
4. Users have full access to the MySQL instance, including SUPER privileges, and complete access to the operating system, including sudo permissions.
5. Users have the ability to set up high availability and replication topologies according to their requirement even if they are not supported in Cloud SQL.  

Cons

1. Users are responsible for managing complex hardware infrastructure, networking, and data center operations in an on-premises environment. Even when self-managing MySQL in the cloud, users need to deal with the complexity of setup, configuration of persistence disks and instances, and dealing with design and management of high availability clusters, security, disaster recovery, and scalability.
2. Keeping database versions, operating systems versions, and hardware systems updated with latest patches to avoid bugs and security vulnerabilities can incur significant costs, resources, complexity, and downtime.
3. Along with the setup of the database, users have to configure and manage security at all the layers, not just the database itself. For example, ensuring connections are encrypted in transit using SSL and keeping certificates up to date, encrypting the data at rest, and managing login access for users and service accounts to databases.
4. To ensure the availability of the instance, various layers of the database system have to be monitored for any failures. Users have to spend additional resources on the setup and configuration of the monitoring tools. Alerts have to be defined to notify in case of errors, and corrective actions need to be taken to fix any failures.
5. Users need to spend significant time, resources, and effort on the setup and management of databases instead of focusing on the application development.

Pros

1. Cloud SQL for MySQL provides a user-friendly interface for creating, managing, accessing, and monitoring the MySQL database for developers and engineers, even for those without database expertise. To get started quickly, Cloud SQL also offers an easy way to migrate existing MySQL databases from on-premises or other clouds to Google Cloud’s Cloud SQL for MySQL.
2. Cloud SQL for MySQL offers a rich set of the features required for hosting MySQL databases for varied applications ranging from databases for microsites to highly critical and huge enterprise applications, gaming applications, and data for analytics use cases. Using these rich features users can build highly reliable and scalable applications.
3. Cloud SQL shares the responsibility of security with the users. Cloud SQL ensures security at lower levels of infrastructure and network allowing users to focus on configuring and developing higher level applications. 
4. Cloud SQL provides various security features like password validation plugins and audit logging which are not readily available in the community versions and are complex to implement in self-managed environments. Cloud SQL simplifies and makes these features readily available to the users.
5. Cloud SQL offers various ways of interacting with the database. Users can use GUI, CLI, and API to customize operations according to their need with very minimal effort.
6. Cloud SQL provides easy integration with other solutions and products like BigQuery allowing development of analytical solutions with minimal effort.
7. Cloud SQL takes over responsibility for the reliability of the database instance with its highly available infrastructure, self healing solutions, and most importantly 24X7 SRE teams.
8. Users spend less time and effort on operational tasks of the database and can instead focus on the application development and thereby increasing their ability to deliver solutions that meet their business needs more efficiently.
9. Apart from helping manage the database, Cloud SQL  also provides tips and recommendations to improve the performance of databases using features like Query insights and recommendations. These features make hosting and managing the database on cloud simple, secure, and cost effective.
10. Cloud SQL offers customer care services to simplify and streamline the support required for any issues or requests related to databases.

Cons

1. MySQL forks from the MySQL ecosystem like MariaDB and Percona are not supported in Cloud SQL. 
2. Some MySQL features are not supported. Learn more about unsupported features.
3. Machine types with up to 624 GB of RAM and 96 CPUs are supported in Cloud SQL. Storage up to 64 TB is supported. If the database needs higher capacity, a self-managed option would be the only option today.