Connect to a MySQL Managed Database

You can connect to a MySQL Managed Database using:

• CLI,
• MySQL workbench,
• DBeaver.

Before you begin:

To connect to a MySQL Managed Database, you need to know your username, password, a host or IP, and a MySQL client. To learn how, see View connection details.

View connection details

To view connection details:

1. Log in to Cloud Manager

2. From the main menu, select Databases.

3. Select the Managed Database cluster of interest from the list.

   The Connection Details section contains information and credentials needed to connect to your database.

   • username. The default user for all MySQL Managed Databases is linroot with superuser admin privileges. This replaces the non-accessible root user.
   • password. The randomly generated password for your database cluster.
   • host. The fully qualified domain name you can use to reach your database cluster through the public network.

   📘

   IPv6 support

   Use the IPv6 address (AAAA record) for this hostname to avoid network transfer charges when connecting to your database from Linodes within the same region.

   • port. The default port for your database is 3306.
   • ssl. This field is set to ENABLED, which means that it is required to use an encrypted TLS/SSL connection.

4. To verify the Certificate Authority (CA) certificate when connecting to the database, download the certificate by clicking the Download CA Certificate link.

Connect using a CLI

To connect directly to the database using a CLI, you can use the mysql tool. This tool is typically not available by default on most operating systems, but is included along with many MySQL clients (and servers).

Before you begin:

• Make sure the IP address assigned to your system is included within your database's access controls. If not, add it. To learn how, see Edit access control settings.
• Verify that the mysql tool is installed on your system by running the mysql --version command. If it is not installed, install it. To learn how, see Installing MySQL: A Definitive Guide.

To connect to a MySQL Managed Database:

1. Edit the followingmysql command replacing [host] and [username] with the corresponding values from the Connection Details section.

   mysql --host=[host] --user=[username] --password --ssl-mode=required
   

   If your system is using MariaDB instead of MySQL (such as when using the default packages in Debian's own repository), replace the --ssl-mode=required parameter with --ssl=true.

   📘

   If you are connecting to the private network host, ensure your Compute Instance is located within that same data center and you have added a Private IPv4 address to that instance. See Managing IP Addresses.

2. Enter your password at the prompt. The MySQL prompt appears and you're connected to your database and can enter SQL queries. For examples, see Introduction to SQL Commands.

To learn more, see:

• Connect to a MySQL Database Using the mysql Command
• Connecting to the MySQL Server Using Command Options.

Connect using MySQL workbench GUI

The MySQL Workbench provides a graphical interface for connecting to MySQL databases. Using this tool, you can visualize your database, its structure, and the data it contains.

Before you begin:

• Make sure the IP address assigned to your system is included within your database's access controls. If not, add it. To learn how, see Edit access control settings.
• Install the MySQL Workbench software from the MySQL Community Downloads page. Select the operating system you're using locally.

To connect using MySQL workbench GUI:

1. Open MySQL Workbench and go to Database > Manage Connections.

2. In the Manage Server Connections window, enter a Connection Name.

3. In the Parameters tab:

   1. Enter Hostname, Username, and Port with the corresponding values from the Connection Details section and Password.

   2. Optional: If you want to store the password so that you don't have to enter it manually every time you connect, click Store in Keychain... and enter your password. For security reasons, it's recommended not to store your password.

      

4. In the SSL tab, set Use SSL to Require or Required and Verify CA. The Required and Verify CA verifies the CA certificate each time you connect. If you choose this option, download the CA certificate from Cloud Manager and in the SSL CA File field, enter the path to the downloaded file. To learn how to download the file, see View connection details .

   

5. Click Test Connection to verify you can successfully connect to the database and then click Close to store the connection settings. Return to the main screen.

6. To connect to the database, go to Database > Connect to Database and select the stored connection. Click OK.

To learn more, see:

• Install MySQL Workbench for Database Administration
• MySQL Workbench Manual.

Connect using DBeaver

DBeaver is a free and open source universal database tool for developers and database administrators. DBeaver provides a powerful SQL-editor, administration features, ability to migrate data and schema, monitor database connection sessions, and others.

Before you begin:

• Make sure the IP address assigned to your system is included within your database's access controls. If not, add it. To learn how, see Edit access control settings.
• Install the DBeaver Community or Pro software from the DBeaver Downloads page. Select the operating system you're using locally.

1. Open DBeaver and go to Database > New Connection.

2. In the Connect to a database window, select MySQL and click Next.

   

3. In the Main tab, enter Server Host (hostname) Port, and Username with the corresponding values from the Connection Details section and Password. If you want to store the password so that you don't have to enter it manually every time you connect, click Save password locally and enter your password. For security reasons, it's recommended not to store your password.

   

4. In the SSL tab:

   1. Select the Use SSL and Require SSL options.

   2. Deselect the Verify server certificate option.

      

5. Click Test Connection.

Migrate a MySQL database to a Managed Database

This guide covers how to migrate an existing MySQL or MariaDB database to a Managed Database. When migrating a database, there are two important terms to keep in mind: the source database and the target database.

• Source database: The original database running on a software, system, or machine that you wish to decommission. This could be MySQL running within your own Linux server, a development database on your local machine, or even a cloud database.
• Target database: The new replacement database that you wish to use. For this guide, the target database will be a Managed Database running on Linode's platform.

Your individual migration workflow could deviate from the instructions provided here. You may need to consult your own developers or application's documentation to learn how to perform some of these steps and to gather any best practices. You should also perform the migration on a staging server first or during a time when downtime least affects your users and/or business.

Before you begin

• Create a Managed Database: To minimize downtime, make sure to create your Managed Database database cluster before continuing. This ensures that your database has been fully provisioned (which can take up to 30 minutes) and that you have the new database credentials available. See Create a Managed Database.

• Ensure proper MySQL user grants: The MySQL user you intend to use to export your existing database must have SELECT, LOCK TABLES, SHOW VIEW and TRIGGER grants.

Export the source database

Export the data from the source database into a .sql file. While this file is eventually used to import your data to a different machine, it can also be stored as backup. The best method for generating a backup of your data highly depends on the applications you are using and what other databases are also stored on that same system.

• mysqldump: In most cases, you can export the data using the mysqldump command-line tool.

  • For Linux/UNIX/Mac OS: The following command exports the specified databases within the local mysql instance into a file called db-backup.sql. Replace [host] , [port] , [user], and [password] with the appropriate values for your database cluster.

    JSON

    mysqldump -h [host] -P [port] -u [user] -p'[password]'  \
    --compress --skip-lock-tables --single-transaction --hex-blob --routines --triggers --events --force --set-gtid-purged=OFF --ssl-mode=REQUIRED \
    --skip-column-statistics --databases [database1 database2…]  \
    |sed -e '1i SET SQL_REQUIRE_PRIMARY_KEY = 0;' \
    -e '/SET @@SESSION.SQL_LOG_BIN= 0;/d' \
    -e '/SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF;/d' \
    -e '/SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC;/d' \
    -e '/SET @@GLOBAL.GTID_PURGED=/,/;/d' \
    -e '/SET @@GLOBAL.GTID_PURGED=.*;/d' \
    > db-backup.sql  
    

    Notes on additional command options:

    • -h: If you prefer to run this command remotely and have access to MySQL from a remote system, add -h [hostname], where [hostname] is the IP address or hostname of the remote database server.

    • --ssl-mode=REQUIRED: Force SSL when your existing database has SSL enabled.

    • --set-gtid-purged=OFF: Use this option if you have GTID-based replication enabled.

    • --databases: List your databases. Do not use the --all-databases option.

  • For Windows: The following command exports the specified databases within the local mysql instance into a file called db-backup.sql. Replace [host] , [port] , [user], and [password] with the appropriate values for your database cluster.

  JSON

  mysqldump -h [host] -P [port] -u [user] -p'[password]' `
  --compress --skip-lock-tables --single-transaction --hex-blob --routines --triggers --events --force --set-gtid-purged=OFF --ssl-mode=REQUIRED `
  --skip-column-statistics --databases [database1 database2…] `
  > temp-backup.sql
  
  # Modify the output file in PowerShell
  Get-Content temp-backup.sql |
  ForEach-Object {
      if ($_ -notmatch 'SET @@SESSION.SQL_LOG_BIN= 0;' -and
          $_ -notmatch 'SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF;' -and
          $_ -notmatch 'SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC;' -and
          $_ -notmatch 'SET @@GLOBAL.GTID_PURGED=' -and
          $_ -notmatch 'SET @@GLOBAL.GTID_PURGED=.*;') {
          $_
      }
  } | Set-Content -Path db-backup.sql
  
  # Prepend the line "SET SQL_REQUIRE_PRIMARY_KEY = 0;" to the final backup file
  Add-Content -Path db-backup.sql -Value "SET SQL_REQUIRE_PRIMARY_KEY = 0;" -PassThru -AsByteStream 
  

  
  

See Backing Up MySQL Databases Using mysqldump for more details on running the mysqldump command.

• cPanel: See Backup Wizard > Create a partial backup and How to Back Up and Restore MySQL® Databases in cPanel.

• Plesk: See Exporting and Importing Database Dumps.

Preventing corruption

If data is modified during the export, your dataset may become inconsistent or corrupted. Because of this, you may want to prevent new data from being written during the time. This can be accomplished by stopping any services or applications that are currently using your database. In many cases, stopping the web server software is one of the quickest ways to do this, though its not recommended if that web server is also running other websites that need to maintain access. The following instructions cover stopping the two most popular web services, NGINX and Apache.

• Stop NGINX:

  sudo systemctl stop nginx
  

• Stop Apache on Ubuntu/Debian:

  sudo systemctl stop apache2
  

• Stop Apache on RHEL/CentOS:

  sudo systemctl stop httpd
  

Alternatively, you can activate a maintenance mode (or whatever it may be called for your application) on any applications or services using your database. This typically disables some of your site's functionality and may present a web page to visitors to notify them of the downtime. The process for this varies greatly depending on the application you may be using.

Import the database

Next, you'll need to import the .sql file to your Managed Database (the target database). This process can be accomplished through the mysql command-line tool. Run the following command on a system that has the MySQL client or server software installed. Replace [host] , [port], [user] , and [password] with the appropriate values for your database cluster. To learn more, see Connect to a MySQL Database.

mysql -h [host] -P [port] -u [user] -p'[password]' --compress --force < db-backup.sql  

Update the database connection details within your application

After the data has been imported into the Managed Database, you should update any applications that were using the original source database so that they use the new Managed Database instead. This typically involves editing the database connection details (such as the host, username, password, and port) within the code or within your application's GUI. Please consult the documentation for your application to learn how to adjust the database settings. In WordPress, for instance, the database connection details are stored within the wp-config.php file on your web server (see Editing wp-config.php > Configure Database Settings).

Enable your application

If you turned off your web server or placed your application in a maintenance mode, you can now enable your application again. While the instructions for turning off maintenance mode vary depending on your application, here are the commands for starting the two most common web servers:

• Start NGINX:

  sudo systemctl start nginx
  

• Start Apache on Ubuntu/Debian:

  sudo systemctl start apache2
  

• Start Apache on RHEL/CentOS:

  sudo systemctl start httpd