Tag Archives: mysql

Using MySQL + xargs to restore a Mydumper backup

Split the restore, first the schema, then the data:


dump# ls my_database.* | grep schema | xargs -I % -n 1 bash -c "mysql anotherDB < %"
dump# ls my_database.* | grep -v schema | xargs -I % -n 1 bash -c "mysql anotherDB < %"

Skip restoring data for a few tables:


dump# ls my_database.* | egrep -v 'tbl_a|tbl_b|tbl_c' | grep -v schema | xargs -I % -n 1 bash -c "mysql anotherDB < %"

Reference: http://www.dctrwatson.com/2010/07/using-mydumper-to-parallel-dumpimport-fromto-mysql/

Similar Posts:

Tagged ,

Django on Codio

Codio is awesome. Coolio would have been a better name, but I guess that’s taken!

It’s free, but don’t let that fool you. What you get is a beautifully simple yet powerful IDE in your browser. It works great even on my tablet, so I can edit/update/deploy even on the road (or on the couch half asleep, and here’s where git comes in handy!). It supports github out of the box. And has a cool terminal with SSH and everything. Yay!

Now if that was not awesome enough, these guys went out of their way and added an even cooler feature:

Every project gets its own Box: an instantly available server-side development environment with full terminal access.

So you get to do it all without leaving their site. I wanted to try playing with a pet project I’m working on using django. It was pretty easy to get it all setup (especially that the code was on github, but you could as simply upload the code).

Here’s a step by step tutorial to get django up and running on codio.

Open a terminal:
Tools > Terminal

I’m using MySQL, so:

parts install mysql
parts mysql start
mysqladmin -u root password StrongPassword
mysql -u root -pStrongPassword
mysq> create database dbname;
mysql> grant all privileges on dbname.* to 'dbuser'@'localhost' identified by 'dbpasswd';
mysql> flush privileges;
mysql> \q

Also make sure to install python using the ‘parts’ command:

parts install python2

(see the related issue on github.com: https://github.com/codio/boxparts/issues/80)

Create the requirements.txt file if you don’t have that already. Hint: run ‘pip freeze > requirements.txt’ to save those. So in requirements.txt:

Django
MySQL-python
#distribute
#wsgiref

Run the following in the codio.com terminal:
parts install pip
pip install -r requirements.txt

Upload the django project, or import it from GitHub then:
python manage.py syncdb
python manage.py runserver 0.0.0.0:8009

I still need to get it running through apache. I’ll get on that later tonight.

parts install apache2_mod_wsgi # also installs apache2 and apr_util
============ apache2 ============                  
To start the Apache server:
  $ parts start apache2 
To stop the Apache server:
  $ parts stop apache2    
Apache config is located at:
  $ /home/codio/.parts/etc/apache2/httpd.conf                                               
Default document root is located at:  
  $ /home/codio/workspace 
     
============ apache2_mod_wsgi ============                
If Apache2 httpd is already running, you will need to restart it: 
  $ parts restart apache2
Default configuration for wsgi is:  
  WSGIScriptAlias / /home/codio/workspace                                                  
  You can change default in /home/codio/.parts/etc/apache2/config/wsgi.conf file

So I changed the file in /home/codio/.parts/etc/apache2/config/wsgi.conf to:

LoadModule wsgi_module  /home/codio/.parts/packages/apache2_mod_wsgi/3.4/mod_wsgi.so
WSGIScriptAlias / /home/codio/workspace/conf/wsgi.py # <-- this is where I put my wsgi.py file               
WSGIPythonPath /home/codio/workspace                         
                          
                                             
Order deny,allow                                           
Require all granted                                       
                                                  

Then start apache, and visit your site at http://UNIQUE-NAME.codio.io:3000/:
parts start apache2

You will probably see a ‘500 Internal Server Error’ message. So tail the apache ErrorLog file in the terminal to see what went wrong:
tail -f /home/codio/.parts/var/apache2/log/error_log

Here’s what I see (and I haven’t figured the fix yet):

[] mod_wsgi (pid=912): Target WSGI script '/home/codio/workspace/conf/wsgi.py' cannot be loaded as Python module.                                                                  
[] mod_wsgi (pid=912): Exception occurred processing WSGI script '/home/codio/workspace/conf/wsgi.py'.                                                                             
[] Traceback (most recent call last):                                                  
[]   File "/home/codio/workspace/conf/wsgi.py", line 5, in                     
[]     from django.core.wsgi import get_wsgi_application                               
[]   File "/home/codio/.parts/packages/python2/2.7.6/lib/python2.7/site-packages/django/core/wsgi.py", line 1, in                                                          
[]     from django.core.handlers.wsgi import WSGIHandler                               
[]   File "/home/codio/.parts/packages/python2/2.7.6/lib/python2.7/site-packages/django/core/handlers/wsgi.py", line 6, in                                                 
[]     from io import BytesIO                                                          
[]   File "/home/codio/.parts/packages/python2/2.7.6/lib/python2.7/io.py", line 51, in                                                                                     
[]     import _io                                                                      
[] ImportError: /home/codio/.parts/packages/python2/2.7.6/lib/python2.7/lib-dynload/_io.so: undefined symbol: PyUnicodeUCS2_Replace 

to be continued…
… issue fixed by installing python using the ‘parts’ command above

Similar Posts:

Tagged , ,

Quick SSH Tunnel using Putty

Two easy steps to create a tunnel from a remote server port to your localhost. It’s handy if you’re trying to connect to a mysql, jboss, etc. installation bound to localhost only (for better security. Check out the screenshots after the break Continue reading

Similar Posts:

Tagged , , , , , ,

Table ‘mysql.servers’ doesn’t exist

That’s a bit confusing, but mainly all you need to do is:
mysql_upgrade

Or if there’s a password on the admin account:
mysql_upgrade -u admin -p

Similar Posts:

Tagged , ,

MySQL Tuning

I’ve always felt it was a hassle to gather the data needed for tuning mysql, let alone interpret it. Here’s a great tool to do just that: the MySQL Performance Tuning Primer Script. From the page:

Currently it handles recomendations for the following:

  • Slow Query Log
  • Max Connections
  • Worker Threads
  • Key Buffer
  • Query Cache
  • Sort Buffer
  • Joins
  • Temp Tables
  • Table (Open & Definition) Cache
  • Table Locking
  • Table Scans (read_buffer)
  • Innodb Status
  • Similar Posts:

    Tagged , ,

    MySQL Incremental Backups

    MySQL incremental backups can be acheived using the --log-bin option. For more information about that and for the original source of the following code, please see the mysql ref. manual.

    Now to the code:
    I added some logging to the script
    [ad#ad-1]
    #!/bin/bash
    MODE=$1
    MYSQLUSER=backup
    FTPUSER=ftpuser
    FTPPASS=ftppass
    BINLOGPATH=/var/lib/mysql
    BINLOGNAME=mysql-bin
    ARCHIVEPATH=/var/data/backups/mysql/
    Continue reading

    Similar Posts:

    Tagged , , ,