Skip navigation

Tag Archives: mysql

Due to a disk failure on a server I needed to preserve several databases created for students. Some of them were empty, consecuently doing:
mysqldump with the option --all-databases yielded an error and didn’t work.

What worked instead, was iterating through and mysqldumping them individually with this shell oneliner:

for I in $(mysql -uroot -p****** -e 'show databases'); do  mysqldump -uroot -p****** $I > $I.sql; done;

I compressed those created files into one tar inmediately after:

ls -1 | xargs tar -cvzf backupOfDatabases.tgz

Darn!, this is nothing but a little note of caution, a sad admission of stupidity or maybe even a tip to someone: if you ever find yourself in a tight schedule doing extra hours to finish up a project, don’t waste precious minutes debugging your app only due to having foolishly picked the php ISO-8601 numeric representation of the day of the week “N” (mon=0 … sun=7). Completing the missing dates from a set of database records will work flawlessly just with date(“w”), specially if you had pulled the existing days using mysql date_format(“w”).

Now that I’m getting fond of using a heredoc to insert mysql scripts into the bash shell, have put this into my emacs initialization file. The same shortcut (Control+Shift+f11) will conveniently write different parameters depending in which shell I’m sitting in.

UPDATE: seems that I spoke too soon. Asking about the present working directory, formerly “(let ((a (shell-command-to-string “pwd | tr -d ‘\\n'”)))”, didn’t actually work when having opened shells of different servers via Tramp.
The correction below (less elegant) depends on the shell buffer being created/renamed with an identifiable name -which could be the hostname or whatever consistent nomenclature we choose-.
Anyway, until figuring a better way, this does the job of inserting the right parameters into each shell:

(defun my-heredoc-sql-invocation-from-shell ()
"Insert the appropiate parameters to run a heredoc mysql query depending on which shell I'm in"
  (let ((a (buffer-name (window-buffer (minibuffer-selected-window))))
        (b nil))
    (cond ((string-match "serverA" a) 
           (setq b "mysql -uroot mainDbToHitAt_A--password=`cat /etc/security/mysqlpassword` -t -vv <<\\!"))
          ((string-match "serverB" a) 
           (setq b "mysql -ualpha mainDbToHitAt_B --password=`cat /etc/security/mysqlpassword` -t -vv <<\\!"))
          ((string-match "serverC" a) 
           (setq b "mysql -uroot mainDbToHitAt_C  -t <<\\!")))
    (insert b)))

;; key shortcut to bind it to
(global-set-key (kbd "C-S-<f11>")  'my-heredoc-sql-invocation-from-shell)

Despite being dealing with databases for some years it was just recently that I started to run mysql queries straight from the shell by doing something like:

mysql -u(user) -p(password)  -t -e ‘whatever sql query here in one line’

Although a handy way to use every now and then, I still felt this sort of limited since the query to be passed in the “-e” argument must go inside a single line. In search of  a better alternative I was pleased to figure instead that it’s possible to combine heredoc sintaxis with the sql execution in the command line. Heredoc is a mechanism to break text into multiple lines, just as a file would be treated, after the “<<” sign, the parser says: “here follows a document” which gets wrapped inside the pair of whatever text delimeters we choose. The obvious convenience is that without having to create a file we could easily run multiline queries -which read more naturally with line breaks and indentation- in the same way we would write them inside a file.

To quickly see what I’m talking about type

mysql -u(yourDatabaseUserName) -p(your Password)   -t<<eof
show databases

Now hit “Control D” and then, “Enter” to see the output directly thrown at your shell

To speed things up, since  I use the shell inside emacs, I bound the following to C-S f11 adding  what is below into my .emacs file:

(set-register ?q “mysql -u kabul -p ******** -t <<eofsql”)
(defalias ‘Q
(read-kbd-macro “M-x insert-register RET Q C-e “))
(global-set-key [C-S-f11] ‘Q)

In a bash shell, I knew about the way to produce the output of a query into a tab delimited file by simply running:

mysql -uUSER -pPASSW < fileWithSQLquery.sql >

Now in this case what I needed was a CSV file, for which I piped it to sed reeplacing tabs by commas doing:

mysql -uUSER -pPASSW < fileWithSQLquery.sql | sed -e ‘s#\t#, #g’ > fileToSave.csv

I struggled the other day doing some sys admin work for recovering data from a single table of our database. Editing big files (of about several gigabites) is no-picnic even for vi(m) or emacs, so it wasn’t trivial to find a quick way to isolate the parts needed. For what is worth here’s the method I’ve followed with success resourcing to simple cat and sed commands in my command line:

  1. Get the creation statement for the table to be recovered

    cat your_entire_backup_file.sql | sed -ne '/Table structure for table `your_table`/,/-- Dump/p' > table_creation.sql
  2. Get the data

    cat your_entire_backup_file.sql | sed -ne '/INSERT INTO `your_table`/,/Table structure/p' > data_dump.sql
  3. Join the two into a single file

    cat table_creation.sql data_dump.sql > data_for_single_table_to_copy
  4. Optionally, in case you need to extract some rows only from that previous instance of the table, as it was my case with records deleted by mistake, you might want to create a temporary table from where to later perfom the selection of the desired rows. In order to do that, the table name should be altered from the creation and insertion statements:

    sed -i 's#your_table#your_temp_table#g' data_for_single_table_to_copy.sql
  5. Now we are ready to create that temporary table with its data inside our database:

    mysql -u 'your_username -p your_database_name < 'your_path_to_the_file/data_for_single_table_to_copy.sql
  6. Voila!, the table is there containing the information you needed. Now is up to you to extract and reinsert whatever you wanted inside the original table

Note: See that different parameters could be used to isolate and put together table creation and data parts in only one pass. Also the awk command might be used instead since, like sed, it permits collecting portions by matching from the beginning to the ending block of text. Just make sure you know the order of the table after the one you are picking.

awk '/Table structure for table `your_table`/,/Table structure for table `your_next_table`/{print}' your_entire_backup_file.sql > data_for_single_table_to_copy.sql

In case the table to extract happens to be the last one (which again, you could just know with a mysq “show tables” command, modify the last part of the regexp to match it accordingly.