The MysqlDump Stream

whmadvanced mysql

Valid for versions 88 through the latest version

Version:

88

Last modified: January 5, 2023

Overview

Warning:

The MysqlDump stream is experimental. The behavior of this endpoint may change in a future version of cPanel & WHM.

The MysqlDump stream allows you to produce a dump of a MySQL® database via WebSocket from any computer that has access to the server. You do not need to log in to your server. You can run a command to request that your server sends you the dump output. You can then use the dump output to create a backup.

Using the MysqlDump stream

To stream a dump output from cPanel or WHM, call it via any WebSocket client. You will also need an API token or authenticate via Basic HTTP Authentication to use the endpoint.

Use Basic HTTP Authentication

To use Basic HTTP Authentication, follow the directions in our Guide to API Authentication - Username and Password Authentication documentation. For more information about Basic HTTP Authentication, read the RFC 7617 documentation.

Generate an API token in cPanel

To generate a cPanel API token, use cPanel’s Manage API Tokens interface (cPanel » Home » Security » Manage API Tokens). You can also use the UAPI Tokens::create_full_access function. For more information, read our How to Use cPanel API Tokens documentation.

Generate an API token in WHM

To generate a WHM API token, use WHM’s Manage API Tokens interface (WHM » Home » Development » Manage API Tokens). You can also use the WHM API 1 api_token_create function. For more information, read our Guide to API Authentication - API Tokens in WHM documentation.

Important:

Only root or resellers with root-level privileges can access WHM’s MysqlDump stream. For more information, read our Edit Reseller Nameserver and Privileges interface (WHM » Home » Resellers » Edit Reseller Nameservers and Privileges) documentation.
In WHM, you can use the MysqlDump stream to dump any MySQL database. This includes a database not managed by cPanel & WHM.

MysqlDump parameters

You must use the following parameters:

Parameters	Description	Example
`dbname`	The name of the database to dump.	`dbname=username_example_db`
`character_set`	The value to give the default MySQL character set. Possible values: `utf8mb4` `utf8` Note: We recommend that you run `utf8mb4`, and only run `utf8` if you experience a collation error.	`character_set=utf8mb4`
`include_data`	A Boolean value that indicates whether to include table data in the dump.	`include_data=1`

Important:

Use the --no-tablespaces option if you do not need to stream tablespace information and receive the following error:

Incompatible Change: Access to the INFORMATION_SCHEMA.FILES table now requires the PROCESS privilege.

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.

Example

To stream a cPanel MySQL dump output via 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

In this example:

username represents the account’s username.
GG24IS0019Q8SGI6R5EATJHLMBY3UX6Z represents the cPanel API Token.
example.com represents any name in DNS that resolves to the server.
username_db1 represents the database’s name.
/$PATH/file.sql represents the directory and filename for the dump output.

The command will produce output similar to the following example:

Click to view...

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68


-- 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: