Story about mysqlpump, a successor tool to mysqldump that makes dumping fun
table of contents
Hello.
My name is Mandai and I am in charge of free materials for the development team.
Although I'm quite late to the game, I tried using mysqlpump, which is a successor to mysqldump, so I've put together some recommended points for those who haven't used it yet.
As you would expect from a successor, it has a design that is friendly to those who have migrated from mysqldump, with the basic options how to use it the same, so I would like you to give it a try.
The good thing about it is that it is included with MySQL.
This alone is worth switching to, the progress display
The database I tried this time was about 200MB at most, so it didn't have much benefit, but mysqlpump displays the progress of the dump step by step.
mysqlpump -uroot -p --databases xxxxx > xxxxx_20XX0X0X.sql Enter password: Dump progress: 1/6 tables, 0/352 rows Dump progress: 73/145 tables, 551291/1274767 rows Dump completed in 2073 milliseconds
The basic command looks like this.
At first glance, it looks like it's a typo? I think it's mysqldump.
When I tried dumping the same database using mysqldump, mysqlpump was a little slower.
I couldn't help but feel a little disappointed at this, but at this capacity it was about 0.2 seconds, so it's safe to say it's a margin of error.
Has the option to compress the output file
Dump files are usually compressed, whether they are backed up or transferred to another server for duplication.
On that point, mysqlpump has an option to output in a compressed state, so there is no need for troublesome post-processing.
mysqlpump -uroot -p --compress-output=LZ4 --databases xxxxx > xxxxx_20XX0X0X.lz4
You can choose the compression format with the --compress-output option.
However, the only supported formats are his LZ4 and openssl zlib, which are rarely seen formats.
Embarrassingly, I wondered what openssl zlib was, and when I looked it up, I found out that it was a zip file.
zip is no fun, so I tried consolidating it with LZ4, but now I'm wondering how to unzip it.
What I found was the MySQL documentation .
The LZ4 format seems to be a compression format supported by the Linux standard, and a decompression command was provided.
lz4_decompress xxxxx_20XX0X0X.lz4 xxxxx_20XX0X0X.sql
Just like that. And long.
I wanted to request something similar to unzip.
Aside from such complaints, you can import the file created by answering the questions by simply typing in the mysql command.
mysql -uroot -p < xxxxx_20XX0X0X.sql
When it comes to file compression, one is concerned about the compression rate, but LZ4 doesn't seem to have much of an advantage in terms of compression rate, and the compression speed seems to be the key point.
However, the compression rate doesn't seem to be that bad, so when using mysqlpump, I'm thinking of compressing it in LZ4 format for a while.
Can be processed in parallel
The concept of parallel processing is very different from mysqldump.
However, reading the documentation, it seems that you cannot use it effectively unless you understand mysqlpump's dump processing.
Specify the number of threads to launch with the --default-parallelism option. Default is 2.
mysqlpump -uroot -p \ --default-parallelism=4 \ --databases xxxxx \ > xxxxx_20XX0X0X.sql
Create a queue with the --parallel-schemas option and specify which queue should process which table.
mysqlpump -uroot -p \ --parallel-schemas=xxxxx \ --parallel-schemas=yyyyy \ > xxxxxyyyyy_20XX0X0X.sql
Now you can process the xxxxx and yyyyy tables in separate threads. It's easy.
Additionally, you can specify the number of threads to process the queue by using the --default-parallelism option.
mysqlpump -uroot -p \ --default-parallelism=4 \ --parallel-schemas=xxxxx \ --parallel-schemas=yyyyy \ > xxxxxyyyyy_20XX0X0X.sql
In the above example, two queues are created, and each queue is processed by four threads.
It's a good idea to keep in mind that creating a queue for each table is a good idea, and if you can distribute the processing load well, it will probably shorten the execution time.
At first, I thought that it would be possible to dump data while doing such calculations, but it seems like the idea is to have the huge table and small tables processed in separate queues.
Since the --parallel-schemas option allows you to specify the number of threads to start at the same time, it is also possible to bias the number of threads.
mysqlpump -uroot -p \ --parallel-schemas=2:xxxxx \ --parallel-schemas=4:yyyyy \ > xxxxxyyyyy_20XX0X0X.sql
This command means that 2 threads are used to dump the xxxxx table and 4 threads are used to dump the yyyyy table.
I got a strange error when trying to dump
When I ran it for the first time, an incomprehensible error occurred and I got nervous, so I'll post it here as a memorandum.
mysqlpump -uroot -p --databases xxxxx > xxxxx_20XX0X0X.sql Enter password: mysqlpump: [WARNING] (1356) View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker lack of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_file_io_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys .host_summary_by_stages' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_statement_latency' references invalid table(s) or column(s) or function(s) or definer/invoker lack of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_statement_type' references invalid table(s) or column(s) or function(s ) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.innodb_buffer_stats_by_schema' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.innodb_buffer_stats_by_table' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] ( 1356) View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.io_by_thread_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.io_global_by_file_by_bytes' references invalid table(s) or column(s ) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.io_global_by_file_by_latency' references invalid table(s) or column(s) or function(s) or definer/ invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.io_global_by_wait_by_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump : [WARNING] (1356) View 'sys.io_global_by_wait_by_latency' references invalid table(s) or column(s) or function(s) or lack of definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View ' sys.latest_file_io' invalid references table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them mysqlpump: [WARNING] (1356) View 'sys.memory_by_host_by_current_bytes' references invalid table(s ) or column(s) or function(s) or definer/invoker of view rights lack to use them mysqlpump: [WARNING] (1356) View 'sys.memory_by_thread_by_current_bytes' references invalid table(s) or column(s) or function( s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.memory_by_user_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.memory_global_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.memory_global_total' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.schema_index_statistics' references invalid table(s) or column( s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer /invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.schema_tables_with_full_table_scans' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.session' references invalid table( s) or column(s) or function(s) or definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.statement_analysis' references invalid table(s) or column(s) or function (s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.statements_with_errors_or_warnings' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.statements_with_full_table_scans' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING ] (1356) View 'sys.statements_with_runtimes_in_95th_percentile' references invalid table(s) or column(s) or function(s) or definer/invoker of lack of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.statements_with_sorting ' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.statements_with_temp_tables' references invalid table(s) or column (s) or function(s) or definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.user_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_file_io_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_stages' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_statement_latency' references invalid table (s) or column(s) or function(s) or definer/invoker of view rights lack to use them mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_statement_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.wait_classes_global_by_avg_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.wait_classes_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights to use them mysqlpump: [ WARNING] (1356) View 'sys.waits_by_host_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys. waits_by_user_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.waits_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of lack view rights to use them mysqlpump: [WARNING] (1356) View 'sys.x$ps_schema_table_statistics_io' references invalid table(s) or column(s) or function( s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.x$schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.x$schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights to use them mysqlpump : [WARNING] (1356) View 'sys.x$schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker lack of view rights to use them mysqlpump: [ERROR] (1356) View 'sys.host_summary' invalid references table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.host_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.host_summary_by_file_io_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.host_summary_by_stages' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.host_summary_by_statement_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.host_summary_by_statement_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.innodb_buffer_stats_by_schema' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.innodb_buffer_stats_by_table' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.io_by_thread_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.io_global_by_file_by_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.io_global_by_file_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.io_global_by_wait_by_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.io_global_by_wait_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.latest_file_io' invalid references table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.memory_by_host_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.memory_by_thread_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.memory_by_user_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.memory_global_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.memory_global_total' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.schema_index_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.schema_tables_with_full_table_scans' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.session' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.statement_analysis' invalid references table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.statements_with_errors_or_warnings' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.statements_with_full_table_scans' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.statements_with_runtimes_in_95th_percentile' references invalid table(s) or column(s) or function(s) or definer/invoker of lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.statements_with_sorting' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.statements_with_temp_tables' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.user_summary' invalid references table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.user_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.user_summary_by_file_io_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.user_summary_by_stages' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.user_summary_by_statement_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.user_summary_by_statement_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.wait_classes_global_by_avg_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.wait_classes_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.waits_by_host_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.waits_by_user_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.waits_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.x$ps_schema_table_statistics_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] ( 1356) View 'sys.x$schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR ] (1356) View 'sys.x$schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Dump process encountered error and will not continue. mysqlpump: [ERROR] (1356) View 'sys.x$schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker lack of view rights to use them Dump process encountered error and will not continue. mysqlpump: [WARNING] (1356) View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_file_io_type' references invalid table( s) or column(s) or function(s) or definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_stages' references invalid table(s) or column(s) or function (s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_statement_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_statement_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING ] (1356) View 'sys.innodb_buffer_stats_by_schema' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.innodb_buffer_stats_by_table ' references invalid table(s) or column(s) or function(s) or definer/invoker of lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.innodb_lock_waits' references invalid table(s) or column (s) or function(s) or definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.io_by_thread_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.io_global_by_file_by_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.io_global_by_file_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.io_global_by_wait_by_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.io_global_by_wait_by_latency' references invalid table (s) or column(s) or function(s) or definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.latest_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.memory_by_host_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.memory_by_thread_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them mysqlpump: [ WARNING] (1356) View 'sys.memory_by_user_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys. memory_global_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.memory_global_total' references invalid table(s) or column(s) or function(s) or definer/invoker of lack view rights to use them mysqlpump: [WARNING] (1356) View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.schema_index_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356 ) View 'sys.schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.schema_tables_with_full_table_scans' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them mysqlpump: [WARNING] (1356) View 'sys.session' references invalid table(s) or column(s) or function(s) or definer/invoker lack of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.statement_analysis' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.statements_with_errors_or_warnings' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys .statements_with_full_table_scans' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.statements_with_runtimes_in_95th_percentile' references invalid table(s) or column(s) or function(s) or lack of definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.statements_with_sorting' references invalid table(s) or column(s) or function(s ) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.statements_with_temp_tables' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.user_summary' references invalid table(s) or column(s) or function(s) or lack of definer/invoker of view rights to use them mysqlpump: [WARNING] ( 1356) View 'sys.user_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights to use them mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_file_io_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_stages' references invalid table(s) or column(s ) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_statement_latency' references invalid table(s) or column(s) or function(s) or definer/ invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.user_summary_by_statement_type' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them mysqlpump : [WARNING] (1356) View 'sys.wait_classes_global_by_avg_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View ' sys.wait_classes_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view rights lack to use them mysqlpump: [WARNING] (1356) View 'sys.waits_by_host_by_latency' references invalid table(s ) or column(s) or function(s) or definer/invoker of view rights lack to use them mysqlpump: [WARNING] (1356) View 'sys.waits_by_user_by_latency' references invalid table(s) or column(s) or function( s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.waits_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of lack view rights to use them mysqlpump: [WARNING] (1356) View 'sys.x$ps_schema_table_statistics_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [ WARNING] (1356) View 'sys.x$schema_table_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View ' sys.x$schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysqlpump: [WARNING] (1356) View 'sys.x$schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
I don't know what I did wrong, but it seems like it's a MySQL table compatibility issue. I'm not bad.
The solution is simple, just upgrade the system tables.
I used this blog as a reference.
It's only a one-line blog, but I feel like it gave me courage.
[FIXED] mysqlpump: [WARNING] (1356) – Biesma.net
As I said, it simply says "mysql_upgrade", but in reality, it probably won't work unless you are the root user.
So, I tried executing it as follows.
mysql_upgrade -uroot -p
After output similar to when repairing a table (I forgot to capture it), it ended normally.
Just to be sure, back up all your data with mysqldump before executing.
I think I've reached my goal at this point...
I had no idea what I was doing, but after that I was able to run mysqlpump without any problems.
summary
This time we introduced mysqlpump, a successor tool to the mysqldump command.
I think the progress display and compression options alone are enough to make it easy to use.
In addition to that, it's parallel processing.
I'd like to dump a much larger database, but I'm having trouble finding a target.
Lastly, I would like to introduce a problem that occurred with the customer.
I think I was able to solve the problem cleanly, but since I am restoring the table, don't forget to make a backup just in case.
It is included with MySQL 5.7.8 and later versions, so please give it a try.
lastly
I have opened the system development service site "SEKARAKU Lab" to which I belong.
Beyond is a one-stop service for everything from server design and construction to operation, so if you have any trouble with server-side development, please feel free to contact us.
SEKARAKU Lab: [https://sekarakulab.beyondjapan.com/](https://sekarakulab.beyondjapan.com/)
That's it.