SysAdmin

Could happen to have a mysql database with a lot of tables and we want dump all of them, but we also want to make the logical dump process faster. Some of you could say that mydumper is the solution. It could be. But what if we don't have the permission to install it or whatever?

Let's say we have these tables and we need to dump all of them:

[codesyntax lang="bash"]

MariaDB [database]> show tables like "objects_2015%";
+-----------------------------------------------+
| Tables_in_xxxxxxxxxx_logs (objects_2015%)     |
+-----------------------------------------------+
| objects_2015_12_01                            |
| objects_2015_12_02                            |
| objects_2015_12_03                            |
| objects_2015_12_04                            |
| objects_2015_12_05                            |
| objects_2015_12_06                            |
| objects_2015_12_07                            |
| objects_2015_12_08                            |
| objects_2015_12_09                            |
| objects_2015_12_10                            |
| objects_2015_12_11                            |
| objects_2015_12_12                            |
| objects_2015_12_13                            |
| objects_2015_12_14                            |
| objects_2015_12_15                            |
| objects_2015_12_16                            |
| objects_2015_12_17                            |
| objects_2015_12_18                            |
| objects_2015_12_19                            |
| objects_2015_12_20                            |
| objects_2015_12_21                            |
| objects_2015_12_22                            |
| objects_2015_12_23                            |
| objects_2015_12_24                            |
| objects_2015_12_25                            |
| objects_2015_12_26                            |
| objects_2015_12_27                            |
| objects_2015_12_28                            |
| objects_2015_12_29                            |
| objects_2015_12_30                            |
| objects_2015_12_31                            |
+-----------------------------------------------+
74 rows in set (0.00 sec)

[/codesyntax]

Dumping, let's say, 6 tables in parallel will speed up the dump process.
Note: Too many mysqldump instances could overload the server and make the dump process slower (if the server is a production server this could have quite an impact on the performance).

We need the list with all the commands we need to execute:

[codesyntax lang="bash"]

for i in $(mysql -h host -uuser -ppassword database -Bsqe "show tables like \"objects_2015%\""); do echo "mysqldump -h host -uuser -ppassword --add-drop-table --quick --quote-names --disable-keys --extended-insert database $i | gzip > /path/to/backup/$i.sql.gz"; done > /tmp/things_to_do/tables.txt

[/codesyntax]

Start the dump process:
[codesyntax lang="bash"]

parallel -j 6 < /tmp/things_to_do/tables.txt

[/codesyntax]