Connect GCP MySQL to MariaDB: An Easy Guide 101

MySQL is a SQL-based relational database management system. Data warehousing, e-commerce, and logging applications are just a few of the uses of the software. MySQL, on the other hand, is most commonly used to create a web database.

MariaDB is a fork of MySQL that was created by the community. MariaDB was founded by core members of the original MySQL team and actively collaborates with outside developers to deliver the industry’s most feature-rich, stable, and sanely licensed open SQL server.

This article discusses how to connect GCP MySQL to MariaDB in detail. It also explains MySQL and Maria DB extensively.

What is MySQL?

MySQL is a relational database management system available as an open-source project (RDBMS). A relational database divides data into one or more tables, each containing data types related to one another; these relationships help structure the data. SQL is a programming language that allows programmers to create, modify, and extract data from relational databases and manage user access. A relational database management system (RDBMS) like MySQL, in addition to relational databases and SQL, works with an operating system to implement a relational database in a computer’s storage system, manages users, allows for network access, and simplifies database testing and backup creation.

MySQL is available under a variety of proprietary licenses and is free and open-source software under the terms of the GNU General Public License.

MySQL has stand-alone clients that allow users to interact directly with a MySQL database using SQL, but it’s more common for MySQL to be used in conjunction with other programs to create applications that require relational database functionality. MySQL is part of the LAMP (Linux, Apache, MySQL, Perl/PHP/Python) web application software stack (along with other components). Drupal, Joomla, phpBB, and WordPress are just a few of the database-driven web applications that use MySQL. Facebook, Flickr, MediaWiki, Twitter, and YouTube are just a few of the popular websites that use MySQL.

MySQL works in a Client-Server Architecture. This model is intended for clients to use network services to access resources from a central computer known as a server. Clients submit requests via a graphical user interface (GUI), and the server responds with the desired output as soon as the instructions are validated.

The MySQL database is written in C and C++. Although its SQL parser is written in yacc, it uses a custom lexical analyzer. [16] MySQL is compatible with a variety of operating systems, including AIX, BSDi, FreeBSD, HP-UX, ArcaOS, eComStation, IBM I IRIX, Linux, macOS, Microsoft Windows, NetBSD, Novell NetWare, OpenBSD, OpenSolaris, OS/2 Warp, QNX, Oracle Solaris, Symbian, SunOS, SCO OpenServer, SCO UnixWare, Sanos, and Tru64 MySQL has been ported to OpenVMS.
The MySQL server software and client libraries are distributed with dual licensing. They’re available under either the GPL version 2 or a proprietary license.

Key Features of MySQL

• Maintained by Oracle: Oracle owns and maintains MySQL. It also offers premium versions of MySQL with additional services, proprietary plugins, user support, and extensions. 
• Long History: MySQL has been around for over 20 years since its initial release in 1995.
• Frequent Updates: MySQL is made more robust with frequent updates with new features and security improvements. The latest release is Version 8.0.23 released on 18 January 2021.
• MVCC Features: MySQL recently started offering MVCC (Multi-Version Concurrency Control) features. 
• A Supportive Community: A devoted community of developers is available to help with troubleshooting as and when needed.
• Open-Source: MySQL is also a free and Open-Source Relational Database Management System (RDBMS).
• Users: MySQL is used widely by Google, NASA, Flickr, GitHub, Netflix, Tesla, Twitter, Uber, Wikipedia, YouTube, and Zendesk to name a few.

Key Benefits of MySQL

MySQL is a lightweight database that can be installed and used by developers on production application servers with large multi-tier applications. Leveraging MySQL as a part of your workflow has several advantages:  

• Features like Master-Slave Replication and Scale-Out are supported by MySQL.
• Offload Reporting, Geographic Data Distribution is also supported by MySQL.
• There’s a very low overhead with the MyISAM Storage Engine when used for read-only applications.
• For frequently used tables, MySQL provides support for the Memory Storage Engine.
• For repeatedly used statements there exists a Query Cache. 
• MySQL is easy to learn and troubleshoot given a wide number of helpful sources like blogs, white papers, and books on the subject. 
• MySQL is a highly flexible and scalable Database Management System.

What is MariaDB?

One of the most well-known open-source relational databases is MariaDB Server. It was created by MySQL’s original creators and will remain open-source indefinitely. Most cloud providers use it, and most Linux distributions use it as the default.

It is based on the principles of performance, stability, and openness, and the MariaDB Foundation ensures that contributions are accepted based on technical merit. Advanced clustering with Galera Cluster 4, Oracle Database compatibility, and Temporal Data Tables, which allow you to query data as it was at any point in the past, are just a few of the recent new features.

MariaDB is a commercially supported fork of the MySQL relational database management system (RDBMS), which is under the GNU General Public License. Some of MySQL’s original developers are leading the project, which was forked in 2009 due to concerns about Oracle Corporation’s acquisition of the database.

MariaDB is designed to be a drop-in replacement for MySQL, with library binary parity and exact match with MySQL APIs and commands.

MariaDB is a database that adapts to the modern world in a fundamentally different way. Its pluggable, purpose-built storage engines can now support workloads that used to require a variety of specialized databases. Organizations can now rely on a single comprehensive database to meet their requirements, whether on commodity hardware or in their preferred cloud. MariaDB provides unmatched operational agility without sacrificing key enterprise features like real ACID compliance and full SQL and can be deployed in minutes for transactional, analytical, or hybrid use cases.

Key Features of MariaDB

MariaDB is a lightweight version of MySQL and provides improved and newer features for MySQL. Some of the key features of MariaDB are given below:

• It is open-source software that offers backward compatibility.
• Maria DB has 12 new storage engines that include PBXT, XtraDB, and Maria.
• It has a larger connection pool and supports up to 200,000 connections and also has faster data replication.
• MariaDB is faster than MySQL and does not support Data Masking and Dynamic Columns.
• It comes with newer commands such as WITH and KILL and also supports compatibility with JSON.
• It comes with plug-ins to address some missing features that are available in the MySQL Enterprise Edition.
• MariaDB is supported by many server operating systems such as FreeBSD, Linux, Solaris, and Windows.
• It offers routing by using the MariaDB MaxScale router and also supports Analytics by using MariaDB ColumnStore.
• The Data Structures, Syntax, and Indexes are similar to MySQL, so it is easy to convert code from one language to another.
• MariaDB is written in C, C++, Bash, and Perl. 
• It has 868 fork processes built into it.

Connecting GCP MySQL to MariaDB

• GCP MySQL to MariaDB: Connect MariaDB Server
• GCP MySQL to MariaDB: Start MariaDb Service
• GCP MySQL to MariaDB: Missing Privileges in MariaDB
• GCP MySQL to MariaDB: Enable Remote Connections
• GCP MySQL to MariaDB: Allow MariaDB through Windows Firewall
• GCP MySQL to MariaDB: Checklist of Common Error Messages
• GCP MySQL to MariaDB: Guided MariaDB Installation on Windows

The JDBC URL can be found in the cloud console of any cloud provider, such as AWS, Oracle, or Google. Select ‘Manually Edit the JDBC URL’ from the DbSchema Connection Dialog and copy the URL.

GCP MySQL to MariaDB: Connect MariaDB Server

• MariaDB users are made up of a username and a hostname that allows them to connect to the database. You can make a database user who can only connect from ‘localhost’, for example:

CREATE USER 'sample'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'sample'@'localhost';
FLUSH PRIVILEGES;

• You might get the error ‘Host… is not allowed to connect’ if you try to connect from another host or outside a docker container. In this case, you should either create a user who can connect from any host (‘ percent ‘) or use the host that appears in the error message.

CREATE USER 'sample'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'sample'@'%';
FLUSH PRIVILEGES;

• When connecting from DbSchema, the user sample should be used. MariaDB comes with a user named ‘root’@’localhost’ that you should have created during the installation process. This user will only work if DbSchema is installed on the same machine as MySql ( if you use docker containers it is a virtual separate machine ).

If this isn’t the case, you’ll need to create a user with a host that matches yours.

GCP MySQL to MariaDB: Start MariaDB Service

You will not be able to connect if the MariaDB service is not running.

Windows

Follow these steps to enable MariaDB Service:

• Step 1: Component Services can be found by going to Start -> Control Panel -> System and Security -> Administrative Tools -> Component Services.

• Step 2: “Service Local” should now be visible.
• Step 3: During installation, look for your MariaDB service name (For Example MariaDb56)
• Step 4: Start the service by right-clicking the service name.

Linux

Simply type service mysqld start into the console to start the MariaDB service on Linux.

GCP MySQL to MariaDB: Missing Privileges in MariaDB

If you forget the database user password, you can reset it by following these steps:

Windows

• Step 1: Service MySql or MariaDb should be shut down: Go to Start -> Run -> Services.msc -> MySQL -> right-click and select STOP from the drop-down menu.
• Step 2: Make a text file called reset.txt. In my case, the file is saved as “C:Tempreset.txt.” Copy and paste the following lines into this file:

UPDATE MySQL.user SET Password=PASSWORD('EnterPassword') WHERE User='root';
FLUSH PRIVILEGES;

Your new password should be entered in place of “EnterPassword.” Make sure the UPDATE and FLUSH commands are on separate lines, as well.

• Step 3: Navigate to your MySQL or MariaDB bin directory within the program folder (“C: Program FilesMySQLMySQL Server 5.0bin” or “C: Program FilesMariaDbbin”) and type the following command.

mysqld.exe --init-file=C:Tempreset.txt --console

Your root password will be replaced with the one you specified in the text file. As the daemon continues to run, the command may hang. You can terminate it (Ctrl-C) and restart MySql from the Windows Services menu.

• Step 4: Start -> Run -> Services.msc -> MySQL > right-click and select START from the drop-down menu.

Linux

• Step 1: Log in as root and use the following command to stop the MariaDB service(daemon): service stop MySQL
• Step 2: Start the MariaDB service (daemon) and enter the command mysqld safe —skip-grant-tables to skip the grant tables that store passwords.
• Step 3: You should now be able to connect to MariaDB without needing to enter a password. Execute these commands:

MySQL --user=root MySQL
update user set Password=PASSWORD('new-password') where user='root';
flush privileges;
exit

GCP MySQL to MariaDB: Enable Remote Connections

Enter the command: service mysqld start as root to start the MariaDB daemon.

If the MariaDB connection only works locally, double-check that the user has been permitted to connect to MariaDB remotely.

To give the user remote access or privileges, go through the steps below:

Windows

The most convenient time to do this is during the installation process.

Follow these steps to open the command prompt: 

• Step 1: Press Enter after selecting Start -> Run -> cmd.
• Go to the MariaDB installation directory (Default: C:Program FilesMariaDbMariaDb Server 12bin)
• Step 2: Enter the following: 

-u root -p MySQL

• Step 3: GRANT ALL RIGHTS TO ‘USERNAME’@’IP’ IDENTIFIED BY ‘PASSWORD‘ ON *.*; USERNAME is the account you want to create or use.
• Step 4: The IP address of the computer to which you want to grant remote access is the physical IP address of the computer.
• Step 5: If you use percent instead of an IP address, that user will be able to connect to MariaDB from any computer.
• Step 6: The password you want to create or change is PASSWORD. Execute the following command: PRIVILEGES OF THE FLUSH;
• Step 7: Type quit exiting.

Linux

• Step 1: Edit the file /etc/my.cnf on the MariaDB server by logging in.
• Step 2: bind-address=YOUR SERVER IP should be added or edited.
• Step 3: #skip-networking is commented out.
• Step 4: ‘/etc/init.d/mariadb restart’ will restart the server.
• Step 5: Using ‘MariaDB -u root -p MariaDB, login to the server and run the commands below, replacing the user, IP, and password as needed:
  • New Database:

CREATE DATABASE foo;
GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';

• Existing Database:

UPDATE DB SET Host='202.54.10.20' where Db='webdb';
UPDATE USER SET Host='202.54.10.20' WHERE user='webadmin';

GCP MySQL to MariaDB: Allow MariaDB through Windows Firewall

The error ‘Communications link failure‘ is a common cause of connectivity issues. This is because the checkbox to add a firewall exception was not enabled during MariaDB installation. If you want to enable it later, do the following:

• Step 1: Click the Start button to launch Windows Firewall. After clicking the Start button, go to Control Panel. Type firewall into the search box, then click Windows Firewall.
• Step 2: Click Advanced Settings in the left pane. Permission to be an administrator is required. If an administrator password or confirmation is requested, type the password or provide confirmation.
• Step 3: In the Windows Firewall with Advanced Security dialog box, click Inbound Rules in the left pane, then New Rule in the right pane.
• Step 4: Follow the wizard’s instructions to create a new inbound rule. Make sure that Add Port is selected.
• Step 5: Use MariaDB as the port name.
• Step 6: Use 3306 for the specific local port.
• Step 7: Make sure to cross everything off your list. Allow the Connection to Exist
• Step 8: Check the boxes for Domain, Private, and Public.
• Step 9: Make sure to follow the steps above to create an Outbound rule, except you’ll click Outbound Rules on the left pane instead of Inbound Rules.

SSL Connections

Add this ‘?’ if you want to use SSL encryption. In the JDBC Driver Manager’s URL Pattern, add useSSL=true&verifyServerCertificate=false.

GCP MySQL to MariaDB: Checklist of Common Error Messages

• Check to see if the MariaDB service on the server is running.
• Examine MariaDb’s Port and Hostname: The PING button in the Connection Dialog can be used to test the connection.MariaDB’s default port is 3306. This port, however, can be customized for each MariaDB instance. Check the MySQL section of the server files my.ini or my.cnf to see the configured port, such as port=3306. Right-click My Computer and select Properties to find out the computer’s name.
• Disable Firewalls on Server and Client: Connections may be blocked by client or server firewalls. Configure them to accept or reject incoming connections for the MariaDB port.
• Check the JDBC Driver Version: DbSchema includes a MariaDB JDBC driver. If the driver isn’t an actual version, look up ‘download MariaDB driver’ on the internet or go to http://dev.mariadb.com/downloads/connector/j/. The MariaDB-connector-java-x.x.x.zip file will most likely be downloaded. Look for the file MariaDB-connector-java-x.x.x.jar after decompressing it. In the DbSchema JDBC Driver Manager, upload this file.
• “Exception”: Failed Connection Test java.sql. “Host ‘YOUR IP’ is not allowed to connect to this MariaDB server,” SQLException: null, a message from the server.
• The MariaDB server does not allow connections from your IP address.
• The MariaDB server will not accept connections from remote hosts if the bind-address value is set to 127.0.0.1 in the MariaDB configuration file.
• To allow remote connections, the bind-address line in the MariaDB configuration file (my.ini or my.cnf) can be removed or commented out.

GCP MySQL to MariaDB: Guided MariaDB Installation on Windows

The password for the database user root must be entered during the installation. You might also want to make MariaDB accessible from other computers.

Conclusion

This blog describes the process of connecting GCP MySQL to MariaDB. In addition to that, it gives a brief introduction to MySQL and MariaDB.

visit our website to explore hevo

Hevo Data, a No-code Data Pipeline, provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations, with a few clicks. Hevo Data, with its strong integration with 150+ sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also look at the unbeatable pricing that will help you choose the right plan for your business needs.