As a developer, you will find many features in MySQL 8.0 that not only improve your commanding and programming abilities but foster the overall management of all transactions. Developer features range from account management, security, plugins, upgrade processes to backup and data recovery. Let’s dive in.
Developers must assign servers or users privileges to render support of multiple applications and databases. It is useful when you want to grant a set of users certain rights to access and manage database objects. MySQL 8.0 recognizes that developers sometimes have to set similar rights for a group of users. Thus, the new database system allows for replication of GRANT statements enabling developers to reuse previously set privileges. This makes the addition of users effortless and straightforward.
The MySQL privilege system ensures that users can only perform operations on the database granted to them by the administrator. Account management capabilities have been enhanced in 8.0 with the introduction of the data dictionary. The dictionary supports standard SQL Roles allowing developers to grant, alter, drop or revoke privileges for particular users. Setting the global max_user_connections system variable to a nonzero value allows you to limit the number of numbers using resources in one instance.
MySQL 8.0 enables developers to set group attributes that define resource allocation for particular groups. For grants, two new features have been introduced:
RESOURCE_GROUP_ADMIN and ESOURCE_GROUP_USER. With RESOURCE_GROUP_ADMIN you can create, alter, and drop operations along with resource group assignments. Resource groups are invaluable for DBAs as it gives them control over CPU utilization. It enables the prioritization of threads so you can manage workflows better.
As complexity increases, risks grow. Features such as high availability, cloud access, database consolidation and outsourcing snowball the number of risks DBAs face. Developers across the board can rescind or approve a database depending on its security capabilities alone. MySQL’s security system has been the pillar to its adoption across many APIs, algorithms and applications. Security features in MySQL 8.0 are better and superior to its previous versions.
Authentication Plugin verifies users who access the MySQL Server and defines the specific authentication protocols configured for the user. The authentication plugin is now caching_sha2_password and not mysql_native_password. The new system employs the SHA-256 hashing, which is resilient and not susceptible to collision attacks. For developers that use native plugins, Datavail recommends the use of caching_sha2_password for all new users.
Password policies in MySQL 8.0 enable developers to define how users access database objects. By invoking password_require_current=ON, you are ensuring that new non-privileged users have to use the current password. This new password policy is fundamental as it deters attackers from changing the password and logging you out once they compromise the application host machine. The option “Change Current Password” policy is off by default, but you may need to turn it on whenever you want to assign tasks to non-privileged users. Also, MySQL 8.0 rejects the reuse of previous passwords (based on history settings) thus making it difficult for users who have access to old passwords to access the system.
DBAs with Dynamic Privileges can now leverage operations that were previously possible with SUPER Privilege users only. SUPER is under deprecation and is set to be removed in the future versions of MySQL. Additionally, the table encryption feature in MySQL 5.7 is of less importance if you cannot encrypt InnoDB ‘redo’ and ‘undo’ data sets. MySQL 8.0 allows developers to encrypt such data, thus offering full-scale security.
Also, the CONNECTION CONTROL plugin introduced in MySQL 8.0 and backported to MySQL 5.7 and MySQL 5.6. is imperative in preventing brute force attacks. It enables developers to invoke an increasing delay in server response during instances where a client cannot log in after a particular set of attempts.
Developers are now relieved that the longstanding issues with ACL Statements have been resolved in MySQL 8.0. ACL tables reside in MySQL 8.0 InnoDB data dictionary enabling a range of table operations and memory caches. When altering the ACL tables and caches, developers can utilize the custom metadata lock (MDL) lock to block.
New Default Character Set: utf8mb4
Aside from security enhancements, 8.0 offers a significant milestone in the development and introduction of new character sets. The utf8mb4 predecessor, utf8mb3 only stored a maximum of three-character sets. Utf8mb4 stores four-character sets to match up with the array of new characters arising from the revolutionary web and mobile applications. It now supports symbols and emojis. While utf8mb4 is not a new MySQL feature, it has been set as default in 8.0 as it now accepts numerous collations and charsets. Since MySQL 8.0 supports the latest Unicode 9.0, it accommodates new collations based on accent, DUCET and case sensitive collations as well as languages such as Japanese and Russian.
You can utilize these advanced features of MySQL 8.0 to make database administration much more manageable. Other developer features in SQL DML and DLL such as LATERAL derived tables and resource groups provide DBAs with better control of the system. Combination of new functions including REGEXP_INSTR, REGEXP_REPLACE, and UUID_TO_BIN with new features in InnoDB, Optimizer and Replication, gives you ultimate flexibility.
There are so many new features in MySQL 8.0 and it’s not just geared toward developers. Check out our latest white paper: Top 6 Standout Features in MySQL 8.0. There you’ll see why MySQL remains a leader in the industry. If you’re looking for MySQL support our IT experts have already installed the new system for some of our clients. The reception and outcome thus far have been largely positive. We’ve also enabled clients to migrate from MySQL 5.7 to MySQL 8.0 with ease. Contact us to learn more.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.