Page tree
Skip to end of metadata
Go to start of metadata


Warning:

This feature is experimental. The behavior of this feature may change in a future version of cPanel & WHM.

Overview

The /websocket/MysqlDump endpoint feature allows you to produce a MySQL® dump output for a single database. The WebSocket connection provides a streaming connection instead of initiating a download. The streaming connection helps to avoid time out errors and packet loss. 

You can use this feature from any computer that has access to the server. You do not need to SSH or log in to your server to perform this function. You can run a command to request that your server send you the dump output. You can use this output to create a backup.

Run the command

To stream the MySQL dump output with the npm wscat2 utility, run the following command:

wscat -H 'Authorization: cpanel username:GG24IS0019Q8SGI6R5EATJHLMBY3UX6Z' -c 'wss://example.com:2083/websocket/MysqlDump?dbname=username_db1&include_data=1&encoding=utf8mb4' > /$PATH/file.sql


Note:

  • Use your preferred WebSocket utility. For example, wscat.
  • Replace the username with the cPanel account username.
  • Replace GG24IS0019Q8SGI6R5EATJHLMBY3UX6Z with the cPanel API Token.
  • Replace example.com with the cPanel account's primary domain, or the IP address.
  • Replace username_db1 with the name of your database.
  • Replace /$PATH/file.sql with the directory and filename for the dump output. Use the sql file extension.

The script will produce an output similar to the following example:

 Click to view...

-- MySQL dump 10.13 Distrib 5.7.25, for Linux (x86_64)
--
-- Host: localhost Database: username_db1
-- ------------------------------------------------------
-- Server version 5.7.26

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `username_db1`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `username_db1` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `username_db1`;

--
-- Table structure for table `example`
--

DROP TABLE IF EXISTS `example`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `example` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `example`
--

LOCK TABLES `example` WRITE;
/*!40000 ALTER TABLE `example` DISABLE KEYS */;
INSERT INTO `example` (`id`, `name`) VALUES (1,'Sample data');
/*!40000 ALTER TABLE `example` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database 'username_db1'
--

--
-- Dumping routines for database 'username_db1'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-07-10 9:07:57
1000:

Options

Use the following options with this command:

OptionsDescriptionExample

dbname

Required

The name of the database.

dbname=username_example_db
encoding

The formatting of the return. Possible values:

  • utf8mb4 
  • utf8 

Note:

We recommend that you run utf8mb4, and only run utf8 if you experience a collation error.

encoding=utf8mb4
include_data A Boolean value that indicates whether to include table data in the output.include_data=1
-cInclude the exit status code.-c
>

Create a file with the contents of the MySQL dump output.

Note:

Include the path and the filename with the sql filename extension.

> /tmp/backup_sunday_1_PM.sql

The WebSocket close frame will include one of the following status codes:

  • 1000 — Success.
  • 1011 — General Error.
  • 4000 — Collation error; try a different encoding value.

The 1011 and 4000 error codes contain an error ID. You can search the /usr/local/cpanel/logs/error_log file to locate more information about why the command failed.

Database schema

Run the UAPI Mysql::dump_database_schema function to return a string that you can give to MySQL to recreate a database’s schema.

Why WebSocket

This feature uses WebSocket protocol. While HTTP protocol can stream a response, HTTP doesn't produce a status code at the end of the stream. Using WebSocket also offers the following benefits:

  • WebSocket streaming helps avoid time out errors and packet loss.
  • WebSocket avoids complex solutions. For example, a custom-formatted SQL comment at the end of the dump output.
  • WebSocket does not need a custom client. Any standard WebSocket client can read a dump output and close status.
  • Uses less bandwidth because WebSocket compresses the data for transmission.

Additional documentation