If you can get LOCK TABLES and UNLOCK TABLES commented out of the dumpfile. Then, there is no way to get a progress from with mysql until the table lock is released. *!40000 ALTER TABLE `a` DISABLE KEYS */ Since a standard mysqldump write-locks the table like this: LOCK TABLES `a` WRITE
Importing sql file into mysql workbench update#
I addressed something like this last year : How do I get % progress for "type db.sql | mysql" UPDATE 14:09 EDT If the table is InnoDB and you have innodb_file_per_table disabled, not even the OS point of view can help. Of course, you need to compare that with what the table size was before on the other DB server you imported from. The reason? The table is write-locked throughout the reload. If the table is InnoDB and you have innodb_file_per_table enabled, the only way to monitor is from the OS point of view. If the table is MyISAM, the only way to monitor is from the OS point of view. Sorry for not noticing that there is only one table. If you want to know what tables are in the dumpfile, run this against foobar.sql cat foobar.sql | grep "^CREATE TABLE" | awk '' When the dump is reloaded, the DB Connection will vanish. You could just do a SHOW PROCESSLIST and find out the DB Connection running the mysqldump.
Naturally, the reload of the mysqldump into a database would also be in alphabetical order. When you execute a mysqldump of a single database, all tables are dumped in alphabetical order. Non-clustered index sizes, in pages, multiplied by the InnoDB page Index size, in pages, multiplied by the InnoDB page size.įor MyISAM, INDEX_LENGTH is the length of the index file, in bytes.įor InnoDB, INDEX_LENGTH is the approximate amount of memory allocatedįor non-clustered indexes, in bytes. The MySQL 8.0 Reference states the following about the accuracy:įor MyISAM, DATA_LENGTH is the length of the data file, in bytes.įor InnoDB, DATA_LENGTH is the approximate amount of memory allocatedįor the clustered index, in bytes. SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MiB"įROM information_schema.TABLES GROUP BY table_schema
This can be helpful if you know the total size of the. If you've already started the import, you can execute this command in another window to see the current size of your databases. If you want tips on speedup, please comment or post other questions ( I loaded that 173 GB. Original post: How can I monitor the progress of an import of a large. Also, the table checks process can also happen, which can delay the importing. THIS IS NOT THE CORRECT N PRECISE TIME AS SOME INSERT STATEMENT MIGHT TAKE LONGER EXECUTION TiME THAN OTHER. : Rate At which statements are read from steam_activity.sqlĪ progress bar: ĮTA 9:52:30: Time required for completing the import. I am importing a 173 GB steam_activity.sql file into a newly created empty database named GAMEDATA.ħ7.9GiB: Amount of Data transferred from that 173 data file:ĩ:20:33 : Time Elapsed since the process started It has nothing to do with the internal working of MySQL server engines so it won't be able to give a precise ETA for completion. BUT IT CAN PROVIDE A GOOD APPROXIMATION OF TIME REQUIRED. THEREFORE THIS WON'T GIVE YOU PRECISE AMOUNT OF TIME LEFT FOR COMPLETION. PLEASE NOTE THAT ALL THE METRICS ABOVE ARE CALCULATED WITH RESPECT TO DATA TRANSFERRED FROM SQL FILE TO MYSQL SERVER (i.e. This will give a progress bar for SQL import which will look like this :ħ7.9GiB 9:20:33 48% ETA 9:52:3 Pv steam_activity.sql | mysql -u root -pPass GAMEDATA I request you to upvote, comment about improvements, or any other tips which can make this more helpful. It will also help you to assess the overall progress of the task.Īs I am new on StackExchange, I need to pass a reputation score to use more StackOverflow. It may not be of very much help, but using pv and pipes in Linux, you can create a progress bar that will at least help you observe the quantity of data that has been loaded into the tables.
0 kommentar(er)
