[Osaka/Yokohama/Tokushima] Looking for infrastructure/server side engineers!

[Osaka/Yokohama/Tokushima] Looking for infrastructure/server side engineers!

[Deployed by over 500 companies] AWS construction, operation, maintenance, and monitoring services

[Deployed by over 500 companies] AWS construction, operation, maintenance, and monitoring services

[Successor to CentOS] AlmaLinux OS server construction/migration service

[Successor to CentOS] AlmaLinux OS server construction/migration service

[For WordPress only] Cloud server “Web Speed”

[For WordPress only] Cloud server “Web Speed”

[Cheap] Website security automatic diagnosis “Quick Scanner”

[Cheap] Website security automatic diagnosis “Quick Scanner”

[Reservation system development] EDISONE customization development service

[Reservation system development] EDISONE customization development service

[Registration of 100 URLs is 0 yen] Website monitoring service “Appmill”

[Registration of 100 URLs is 0 yen] Website monitoring service “Appmill”

[Compatible with over 200 countries] Global eSIM “Beyond SIM”

[Compatible with over 200 countries] Global eSIM “Beyond SIM”

[If you are traveling, business trip, or stationed in China] Chinese SIM service “Choco SIM”

[If you are traveling, business trip, or stationed in China] Chinese SIM service “Choco SIM”

[Global exclusive service] Beyond's MSP in North America and China

[Global exclusive service] Beyond's MSP in North America and China

[YouTube] Beyond official channel “Biyomaru Channel”

[YouTube] Beyond official channel “Biyomaru Channel”

Story about mysqlpump, a successor tool to mysqldump that makes dumping fun

pump

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.

If you found this article helpful , please give it a like!
0
Loading...
0 votes, average: 0.00 / 10
6,787
X facebook Hatena Bookmark pocket
[2025.6.30 Amazon Linux 2 support ended] Amazon Linux server migration solution

[2025.6.30 Amazon Linux 2 support ended] Amazon Linux server migration solution

[Osaka/Yokohama] Actively recruiting infrastructure engineers and server side engineers!

[Osaka/Yokohama] Actively recruiting infrastructure engineers and server side engineers!

The person who wrote this article

About the author

Yoichi Bandai

My main job is developing web APIs for social games, but I'm also fortunate to be able to do a lot of other work, including marketing.
Furthermore, my portrait rights in Beyond are treated as CC0 by him.