{"id":95064,"date":"2022-11-19T17:13:23","date_gmt":"2022-11-19T17:13:23","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95064"},"modified":"2022-11-19T17:13:23","modified_gmt":"2022-11-19T17:13:23","slug":"backing-up-mysql-part-3-mysqlpump","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/backing-up-mysql-part-3-mysqlpump\/","title":{"rendered":"Backing up MySQL Part 3: mysqlpump"},"content":{"rendered":"

In the MySQL world, there are a couple of ways to take backups of your data. Backups in MySQL can be categorized into two distinct categories: logical or physical. MySQL also comes with a lot of tools helping us achieve our backup objectives: we have walked you through one of the most popular offerings in this space – Percona XtraBackup – in the second part of this series<\/a>, and <\/span>the first part of this series covered one of the main logical MySQL backup tools called <\/span>mysqldump<\/span><\/code><\/a>. As far as backups are concerned, though, we are far from done \u2013 for example, <\/span>mysqldump<\/span><\/code> alone has a brother called <\/span>mysqlpump<\/span><\/code>. Since the two CLI-based backup tools are frequently confused with each other, the aim of this blog is to clear the confusion up a little.<\/span><\/p>\n

What is mysqlpump?<\/span><\/h2>\n

mysqlpump<\/span><\/code> is a backup utility that is used via the command-line interface. The tool is very similar to <\/span>mysqldump<\/span><\/code> in that it provides us with the ability to take logical backups, but also different at the same time \u2013 the goal of <\/span>mysqlpump<\/span><\/code> is to be an extendable, parallel-supporting replacement of <\/span>mysqldump<\/span><\/code>. <\/span>In their blog from 2015<\/span><\/a>, MySQL team said that one of the primary aims of introducing <\/span>mysqlpump<\/span><\/code> was not be forced to implement legacy functionality that is provided by <\/span>mysqldump<\/span><\/code>.<\/span><\/p>\n

mysqlpump<\/span><\/code> was introduced to the world of MySQL in 2015 \u2013 the tool was added to MySQL 5.7.8.<\/span><\/p>\n

Using mysqlpump<\/span><\/h2>\n

The most basic use case of <\/span>mysqlpump<\/span><\/code> looks like this (the tool can be used in the same fashion both on Linux and on Windows):<\/span><\/p>\n

\"\"<\/p>\n

mysqlpump<\/span><\/code> is designed in such a way that it will only dump tables that were created by a user \u2013 in other words, it will refrain from backing up tables that were created internally by MySQL (it won\u2018t touch any of the internal tables.)<\/span><\/p>\n

The one feature that is exclusive to <\/span>mysqlpump<\/span><\/code>, though, is the ability to issue backups using multiple threads at once \u2013 to figure out how many threads your system has, follow these steps:<\/span><\/p>\n

    \n
  1. Linux users \u2013 issue a command like <\/span>lscpu<\/span><\/code> or <\/span>lscpu | egrep \u2018Thread|CPU\\(s\\)\u2019<\/span><\/code> and look at the output (you should see something similar to \u201cThread(s) per core.\u201d)<\/span><\/li>\n
  2. Windows users \u2013 open up task manager and look at the number next to \u201cLogical processors\u201d \u2013 they represent the amount of threads in the system:
    \n\"\"<\/span><\/li>\n<\/ol>\n

    Once you know the number of threads in your system, think about the number of threads you wish (and can) allocate to <\/span>mysqlpump<\/span><\/code> \u2013 things might seem difficult and complex, but your thought process shouldn\u2019t take more than a couple of seconds \u2013 <\/span>mysqlpump<\/span><\/code> is usually quick to complete too.<\/span><\/p>\n

    Once you know the number of threads in your infrastructure, configure the number of threads that can be used together with <\/span>mysqlpump<\/span><\/code>. Threads are to be configured using the <\/span>--default-parallelism<\/span><\/code> and <\/span>--parallel-schemas<\/span><\/code> options. Also, consider providing the username and password that you\u2019re using to access MySQL itself through my.cnf for secure access: <\/span>passwords provided through the CLI can be observed by accessing the history of issued commands<\/span><\/i>. If you don\u2019t want to do that, feel free to provide a username and a password, but be aware that <\/span>mysqlpump<\/span><\/code> itself will issue you warnings if you do so:<\/span><\/p>\n

    mysqlpump --default-parallelism=[threads] > data.sql<\/span><\/code><\/p>\n

    \"\"<\/p>\n

    Since the \u201cspecialty\u201d of <\/span>mysqlpump<\/span><\/code> is to back up data using multiple threads, we can also define the number of threads that we want the tool to use to dump a specific database \u2013 this functionality of <\/span>mysqlpump<\/span><\/code> can be exceptionally useful if we\u2019re working with bigger sets of data, but need a logical backup to be taken.<\/span><\/p>\n

    mysqlpump \u2013 the Details<\/span><\/h2>\n