{"id":399,"date":"2021-07-23T12:29:08","date_gmt":"2021-07-23T12:29:08","guid":{"rendered":"https:\/\/ssdsunucum.com\/blog\/the-mysqldump-stream\/"},"modified":"2021-07-23T12:29:08","modified_gmt":"2021-07-23T12:29:08","slug":"the-mysqldump-stream","status":"publish","type":"post","link":"https:\/\/ssdsunucum.com\/blog\/the-mysqldump-stream\/","title":{"rendered":"The MysqlDump Stream"},"content":{"rendered":"<\/p>\n
\n
\n
\n

Valid for versions 88 through the latest version<\/em><\/p>\n<\/div>\n

\n

Version:<\/h4>\n

82<\/h4>\n

88<\/h4>\n<\/div><\/div>\n
\n

Overview<\/h2>\n
\n
Warning:<\/div>\n
\n

The MysqlDump stream is experimental. The behavior of this endpoint may change in a future version of cPanel & WHM.<\/p>\n<\/p><\/div>\n<\/div>\n

The MysqlDump stream allows you to produce a dump of a MySQL\u00ae 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.<\/p>\n

Using the MysqlDump stream<\/h2>\n

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.<\/p>\n

Use Basic HTTP Authentication<\/h4>\n

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.<\/p>\n

Generate an API token in cPanel<\/h4>\n

To generate a cPanel API token, use cPanel\u2019s Manage API Tokens<\/em> interface (cPanel >> Home >> Security >> Manage API Tokens<\/em>). You can also use the UAPI Tokens::create_full_access<\/code> function. For more information, read our How to Use cPanel API Tokens documentation.<\/p>\n

Generate an API token in WHM<\/h4>\n

To generate a WHM API token, use WHM\u2019s Manage API Tokens<\/em> interface (WHM >> Home >> Development >> Manage API Tokens<\/em>). You can also use the WHM API 1 api_token_create<\/code> function. For more information, read our Guide to API Authentication – API Tokens in WHM documentation.<\/p>\n

\n
Important:<\/div>\n
\n
    \n
  • \n

    Only root<\/code> or root<\/code>-enabled resellers can access WHM\u2019s MysqlDump stream. For more information, read our Edit Reseller Nameserver and Privileges<\/em> interface (WHM >> Home >> Resellers >> Edit Reseller Nameservers and Privileges<\/em>) documentation.<\/p>\n<\/li>\n

  • \n

    In WHM, you can use the MysqlDump stream to dump any<\/strong> MySQL database. This includes a database not<\/strong> managed by cPanel & WHM.<\/p>\n<\/li>\n<\/ul><\/div>\n<\/div>\n

    MysqlDump parameters<\/h2>\n

    You must<\/strong> use the following parameters:<\/p>\n\n\n\n\n\n\n\n
    Parameters<\/th>\nDescription<\/th>\nExample<\/th>\n<\/tr>\n<\/thead>\n
    dbname<\/code><\/td>\nThe name of the database to dump.<\/td>\ndbname=username_example_db<\/code><\/td>\n<\/tr>\n
    character_set<\/code><\/td>\nThe value to give the default MySQL character set. Possible values: <\/p>\n
      \n
    • utf8mb4<\/code><\/li>\n
    • utf8<\/code> <\/li>\n<\/ul>\n
      \n
      Note:<\/div>\n
      \n We recommend that you run utf8mb4<\/code>, and only run utf8<\/code> if you experience a collation error.\n <\/div>\n<\/div>\n<\/td>\n
    		character_set=utf8mb4<\/code><\/td>\n<\/tr>\n
    include_data<\/code><\/td>\nA Boolean value that indicates whether to include table data in the dump.<\/td>\ninclude_data=1<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \n
    Important:<\/div>\n
    \n

    Use the --no-tablespaces<\/code> option if you do not<\/strong> need to stream tablespace information and receive the following error:\n<\/p>\n

    \n
    Incompatible Change: Access to the INFORMATION_SCHEMA.FILES table now requires the PROCESS privilege.<\/code><\/pre>\n<\/div><\/div>\n<\/div>\n
    The WebSocket close frame will include one of the following status codes:<\/p>\n
      \n
    • \n
      1000<\/code> \u2014 Success.<\/p>\n<\/li>\n
    • \n
      1011<\/code> \u2014 General error.<\/p>\n<\/li>\n
    • \n
      4000<\/code> \u2014 Collation error; try a different encoding<\/code> value.<\/p>\n<\/li>\n<\/ul>\n
      The 1011<\/code> and 4000<\/code> error codes contain an error ID. You can search the \/usr\/local\/cpanel\/logs\/error_log<\/code> file to locate more information about why the command failed.<\/p>\n
      Example<\/h2>\n
      To stream a cPanel MySQL dump output via the npm wscat2<\/em> utility, run the following command:<\/p>\n
      \n
      wscat -H 'Authorization: cpanel username:GG24IS0019Q8SGI6R5EATJHLMBY3UX6Z'<\/span> -c 'wss:\/\/example.com:2083\/websocket\/MysqlDump?dbname=username_db1&include_data=1&encoding=utf8mb4'<\/span> > \/$PATH\/file.sql<\/code><\/pre>\n<\/div>\n
      In this example:<\/p>\n
        \n
      • \n
        username<\/code> represents the account\u2019s username.<\/p>\n<\/li>\n
      • \n
        GG24IS0019Q8SGI6R5EATJHLMBY3UX6Z<\/code> represents the cPanel API Token.<\/p>\n<\/li>\n
      • \n
        example.com<\/code> represents any name in DNS that resolves to the server.<\/p>\n<\/li>\n
      • \n
        username_db1<\/code> represents the database\u2019s name.<\/p>\n<\/li>\n
      • \n
        \/$PATH\/file.sql<\/code> represents the directory and filename for the dump output.<\/p>\n<\/li>\n<\/ul>\n
        The command will produce output similar to the following example:<\/p>\n
        \n
                Click to view…<\/p>\n
        \n
        \n
        \n
        \n\n\n
        \n
         1\n<\/span> 2\n<\/span> 3\n<\/span> 4\n<\/span> 5\n<\/span> 6\n<\/span> 7\n<\/span> 8\n<\/span> 9\n<\/span>10\n<\/span>11\n<\/span>12\n<\/span>13\n<\/span>14\n<\/span>15\n<\/span>16\n<\/span>17\n<\/span>18\n<\/span>19\n<\/span>20\n<\/span>21\n<\/span>22\n<\/span>23\n<\/span>24\n<\/span>25\n<\/span>26\n<\/span>27\n<\/span>28\n<\/span>29\n<\/span>30\n<\/span>31\n<\/span>32\n<\/span>33\n<\/span>34\n<\/span>35\n<\/span>36\n<\/span>37\n<\/span>38\n<\/span>39\n<\/span>40\n<\/span>41\n<\/span>42\n<\/span>43\n<\/span>44\n<\/span>45\n<\/span>46\n<\/span>47\n<\/span>48\n<\/span>49\n<\/span>50\n<\/span>51\n<\/span>52\n<\/span>53\n<\/span>54\n<\/span>55\n<\/span>56\n<\/span>57\n<\/span>58\n<\/span>59\n<\/span>60\n<\/span>61\n<\/span>62\n<\/span>63\n<\/span>64\n<\/span>65\n<\/span>66\n<\/span>67\n<\/span>68\n<\/span><\/code><\/pre>\n<\/td>\n
        		\n
        -- MySQL dump 10<\/span>.13 Distrib 5<\/span>.7.25, for<\/span> Linux (<\/span>x86_64)<\/span>\n--\n-- Host: localhost Database: username_db1\n-- ------------------------------------------------------\n-- Server version 5<\/span>.7.26\n\n\/*!40101 SET @OLD_CHARACTER_SET_CLIENT=<\/span>@@CHARACTER_SET_CLIENT *\/;\n\/*!40101 SET @OLD_CHARACTER_SET_RESULTS=<\/span>@@CHARACTER_SET_RESULTS *\/;\n\/*!40101 SET @OLD_COLLATION_CONNECTION=<\/span>@@COLLATION_CONNECTION *\/;\n\/*!40101 SET NAMES utf8mb4 *\/;\n\/*!40103 SET @OLD_TIME_ZONE=<\/span>@@TIME_ZONE *\/;\n\/*!40103 SET TIME_ZONE=<\/span>'+00:00'<\/span> *\/;\n\/*!40014 SET @OLD_UNIQUE_CHECKS=<\/span>@@UNIQUE_CHECKS, UNIQUE_CHECKS=<\/span>0<\/span> *\/;\n\/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=<\/span>@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=<\/span>0<\/span> *\/;\n\/*!40101 SET @OLD_SQL_MODE=<\/span>@@SQL_MODE, SQL_MODE=<\/span>'NO_AUTO_VALUE_ON_ZERO'<\/span> *\/;\n\/*!40111 SET @OLD_SQL_NOTES=<\/span>@@SQL_NOTES, SQL_NOTES=<\/span>0<\/span> *\/;\n\n--\n-- Current Database: `<\/span>username_db1`<\/span>\n--\n\nCREATE DATABASE \/*!32312 IF NOT EXISTS*\/ `<\/span>username_db1`<\/span> \/*!40100 DEFAULT CHARACTER SET latin1 *\/;\n\nUSE `<\/span>username_db1`<\/span>;\n\n--\n-- Table structure for<\/span> table `<\/span>example`<\/span>\n--\n\nDROP TABLE IF EXISTS `<\/span>example`<\/span>;\n\/*!40101 SET @saved_cs_client =<\/span> @@character_set_client *\/;\n\/*!40101 SET character_set_client =<\/span> utf8 *\/;\nCREATE TABLE `<\/span>example`<\/span> (<\/span>\n`<\/span>id`<\/span> smallint(<\/span>5<\/span>)<\/span> unsigned NOT NULL AUTO_INCREMENT,\n`<\/span>name`<\/span> varchar(<\/span>20<\/span>)<\/span> NOT NULL,\nPRIMARY KEY (<\/span>`<\/span>id`<\/span>)<\/span>\n)<\/span> ENGINE=<\/span>MyISAM AUTO_INCREMENT=<\/span>2<\/span> DEFAULT CHARSET=<\/span>latin1;\n\/*!40101 SET character_set_client =<\/span> @saved_cs_client *\/;\n\n--\n-- Dumping data for<\/span> table `<\/span>example`<\/span>\n--\n\nLOCK TABLES `<\/span>example`<\/span> WRITE;\n\/*!40000 ALTER TABLE `<\/span>example`<\/span> DISABLE KEYS *\/;\nINSERT INTO `<\/span>example`<\/span> (<\/span>`<\/span>id`<\/span>, `<\/span>name`<\/span>)<\/span> VALUES (<\/span>1<\/span>,'Sample data'<\/span>)<\/span>;\n\/*!40000 ALTER TABLE `<\/span>example`<\/span> ENABLE KEYS *\/;\nUNLOCK TABLES;\n\n--\n-- Dumping events for<\/span> database 'username_db1'<\/span>\n--\n\n--\n-- Dumping routines for<\/span> database 'username_db1'<\/span>\n--\n\/*!40103 SET TIME_ZONE=<\/span>@OLD_TIME_ZONE *\/;\n\n\/*!40101 SET SQL_MODE=<\/span>@OLD_SQL_MODE *\/;\n\/*!40014 SET FOREIGN_KEY_CHECKS=<\/span>@OLD_FOREIGN_KEY_CHECKS *\/;\n\/*!40014 SET UNIQUE_CHECKS=<\/span>@OLD_UNIQUE_CHECKS *\/;\n\/*!40101 SET CHARACTER_SET_CLIENT=<\/span>@OLD_CHARACTER_SET_CLIENT *\/;\n\/*!40101 SET CHARACTER_SET_RESULTS=<\/span>@OLD_CHARACTER_SET_RESULTS *\/;\n\/*!40101 SET COLLATION_CONNECTION=<\/span>@OLD_COLLATION_CONNECTION *\/;\n\/*!40111 SET SQL_NOTES=<\/span>@OLD_SQL_NOTES *\/;\n\n-- Dump completed on 2019<\/span>-07-10 9<\/span>:07:57\n1000<\/span>:<\/code><\/pre>\n<\/td>\n<\/tr>\n<\/table>\n<\/div>\n<\/div><\/div>\n<\/p><\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"
        Valid for versions 88 through the latest version Version: 82 88 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\u00ae database via WebSocket from any computer that has access to …<\/p>\n","protected":false},"author":1,"featured_media":400,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/posts\/399"}],"collection":[{"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/comments?post=399"}],"version-history":[{"count":0,"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/posts\/399\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/media\/400"}],"wp:attachment":[{"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/media?parent=399"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/categories?post=399"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ssdsunucum.com\/blog\/wp-json\/wp\/v2\/tags?post=399"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}