Wikipedia is the greatest encyclopedia which has ever existed, because everyone can contribute to the massive knowledge corpus. Analyzing this data with computers is becoming more and more indispensable, as nobody can survey the information by hand anymore. In order to work with the data, we have to import it into MySQL and here is how it works.
3 Older Entries »
In a previous blog post (Importing related MySQL tables into an Excel Data Model using MySQL for Excel) we covered in detail how an Excel Data Model can be created containing tables and their relationships so the data can be analyzed in Excel via a PivotTable. In this blog post we are going to talk about one of the features included since MySQL for Excel 1.3.0 that allows you to create PivotTables for data imported from MySQL tables, views or stored procedures, or more importantly for the whole Excel Data Model if it is created.[Read more]
As I mentioned on my last post, where I compared the default configurations options in 5.6 and 5.7, I have been doing some testing for a particular load in several versions of MySQL. What I have been checking is different ways to load a CSV file (the same file I used for testing the compression tools) into MySQL. For those seasoned MySQL DBAs and programmers, you probably know the answer, so you can jump over to my 5.6 versus 5.7 results. However, the first part of this post is dedicated for developers and MySQL beginners that want to know the answer to the title question, in a step-by-step fashion. I must say I also learned something, as I under- and over-estimated some of the effects of certain …[Read more]
In this blog post we are going to talk about one of the features included since MySQL for Excel 1.3.0.
Importing MySQL data into Excel is a common and important operation in MySQL for Excel. There may be times when you need to analyze the data stored in several MySQL tables or views, (possibly in an ExcelPivotTable which will be the subject of a future blog post), and to do it you need to dump the data into Excel as the first step. Starting with MySQL for Excel 1.3.0 we introduced a feature that allows you to import the data from multiple MySQL tables or views in a single operation.
Remember you can install the latest GA or maintenance version using the MySQL Installer or optionally you can download directly any GA or non-GA version from the …[Read more]
In this blog post we are going to talk about one of the features included since MySQL for Excel 1.2.0, we introduced some advanced options for the Import MySQL Data operation regarding Excel tables. You can install the latest GA or maintenance version using the MySQL Installer or optionally you can download directly any GA or non-GA version from the MySQL Developer Zone.
The performance of the mysqldbcopy, mysqldbexport, and
mysqldbimport utilities has been optimized in MySQL Utilities
1.3.6. In the case of export/import there have been significant
improvements. In particular, multiprocessing support has been
added to these utilities and can be enabled with the new
--multiprocess option. The option permits concurrent execution
and makes the most of the CPU resources available (number of
Multiprocessing is applied at different levels according to the operating system. For non-POSIX systems, multiprocessing is limited to the database-level whereas POSIX systems can make multiprocess at the table level.
More specifically, the mysqldbcopy and mysqldbexport utilities allow multiprocessing at the table level for non- Windows systems and database level for Windows system. The mysqldbimport utility allows multiprocessing at the file level independently from the OS.
Amazon recently announced support for 5.6, unfortunately, direct upgrade from lower versions is not yet supported. On a recent migration work – running mysqldump flat out would’ve meant 6+hrs of downtime. How did we cut it off to 1h45m? Simple, run dump per table and pipe it directly to the new 5.6 instance in parallel using Percona Server’s mysqldump utility to take advantage of –innodb-optimize-keys.
Here’s the base script we used – of course, YMMV and make sure to optimize the destination instance as well!
#!/bin/bash # export-run.sh # This is the wrapper script which builds up the list of tables to split into $parallel parts and calls export-tables.sh parallel=6 dblist="db1 db2 db3" smysql="mysql -hsource-55.us-east-1.rds.amazonaws.com" dmysql="mysql -hdest-56.us-east-1.rds.amazonaws.com" …[Read more]
If you are importing large CSV or SQL dumps to MySQL, chances are you were looking for ways to see how far the import has gone. If you know how many rows there are from the file being imported, you can do a SELECT COUNT(*) but that would take sometime for the query to finish especially on really big imports.
Using lsof, you can monitor the current file offset to which a process is reading from using the -o option. Knowing the size of the file and some snapshots of the offset, you can get a somewhat rough idea of how fast the import goes. Note though that this is only file-read-pace not actual import speed as MySQL import can vary depending on a number of conditions i.e. table growth, secondary indexes, etc.
Let’s say I am importing a 1.1G CSV file into a table.
[revin@forge msb_5_5_300]$ ls -al /wok/dta/samples/ft_history.csv -rw-rw-r--. 1 revin revin 1075456654 Nov 8 23:25 /wok/dta/samples/ft_history.csv …[Read more]
Fetching data from a database to then get it into an Excel spreadsheet to do analysis, reporting, transforming, sharing, etc. is a very common task among users. This task can be accomplished in several different ways and with different tools getting the same result; but users may find the process rather complicated, too technical and lengthy. With MySQL for Excel the task of importing data from a MySQL database to an Excel spreadsheet becomes an easy one and accessible to all types of users. Here is a quick guide describing how to import data to Excel using MySQL for Excel.
This is just a quick notice that there is a new version of the
JSON import tool available now. The main thing in it is that it
fixes an iddues with threadinig that caused a threaded load (and
this is the default) to hang of there was an error or the import
was interrupted. Also, there are some minor fixes and also a
runtime status printing, available by sending a SIGUSR1 signal to
the process, feature is available.
Download from sourceforge.
3 Older Entries »