The Codebelay Blog

Category: Databases

  • How To Back Up WordPress with mysqldump

    Here’s a quick how to:

    1. Tar up the directory:

    tar cvf WordPress.tar wordpress/.
    bzip2 WordPress.tar

    2. Dump the database.

    mysqldump --user user_name --password --host example.com --extended-insert=FALSE database_name > database_name.sql

    I personally use extended-insert set to false because I often have to read the SQL.

    Here is how to restore from backup:


    tar zxvf WordPress.tar.bz
    mysql --user user_name -p -hexample.com database_name < database_name.sql

    Be sure to replace user_name, example.com and database_name with the appropriate values for your system.

    I hope this helps.

  • Oracle Breaks MySql.com’s Search

    If you go to the MySQL web site, and do a search on data type integer, you’ll notice something strange. The first result is a MySQL newsletter from May 2010.

    It wasn’t always like this. A few years ago I blogged about how Sun broke MySQL. Sun went on to claim that MySQL working out of the box was something that was broken with MySQL, even though lots of sysadmins every where relied on this to get servers up and running quickly.

    If you compare the search with what they have on Google, you’ll see that the first result and the many results below are *all* relevant.

    Why do large organizations break what works?

  • Notes on adding more MySQL databases

    Just notes for myself on adding more MySQL databases without shutting down the master database.

    on existing slave:

    /etc/init.d/mysqld stop

    copy data dir from /var/lib/mysql and data from /var/run/mysqld to new slave database:

    cd /var/lib
    tar cvf Mysql_slave.tar mysql/*
    scp Mysql_slave.tar root@new-db.com:/var/lib/.
    cd /var/run
    tar cvf Mysqld_slave.tar mysqld/*
    scp Mysqld_slave.tar mysqld/*
    scp Mysqld_slave.tar root@new-db.com:/var/run/.

    copy /etc/my.cnf from old slave to new slave
    add entry for new server-id

    start existing slave:

    cd /var/lib
    tar xvf Mysql_slave.tar
    cd /var/run
    tar xvf Mysqld_slave.tar
    /etc/init.d/mysqld start

    start new slave:

    /etc/init.d/mysqld start
    mysql
    start slave;

    on masterdb:
    e.g.:

    grant replication slave on *.* to ‘repl’@’192.168.107.33’ identified by ‘password’;

    test on master:
    create database repl;

    check on slave:
    show databases; /* should show new database */

    test on master:
    drop database repl;

    check on slave:
    show databases; /* new database should be dropped */

    Now it’s time to turn this into an automated shell script with Expect in there.

  • Scaling from 100 to 100000 concurrent users in a day?

    Well, it looks pretty bad right now. A vendor just ceded control for web application architecture. Initial tests say that the site won’t do no more 100 users concurrently.

    Who the hell makes a web app without a slave database and calls themselves website architects? Apparently these guys did.

    Please start following if you want to see if this web app can make it to launch.

  • Benchmarking Inserts on Drizzle and MySQL

    I’m not comparing apples to apples yet… but out of the box, drizzle does inserts faster than MySQL using the same table type, InnoDB.

    Here’s what I’m comparing:
    drizzle r1126 configured with defaults, and
    MySQL 5.1.38 configured with 

    ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex \
--enable-thread-safe-client --enable-local-infile --enable-shared \
--with-plugins=partition,innobase

    which is really nothing complicated.

    SQL query caching is turned off on both database servers. Both are using the InnoDB engine plug-in.

    I’m running these benchmarks on a MacBook Pro 2.4 GHz Intel Core 2 Duo with 2GB 1067 MHz DDR3 RAM.

    I wrote benchmarking software about 2 years ago to test partitions but I’ve since abstracted the code to be database agnostic.

    You can get the benchmarking code at Github.

    At the command-line, you type:

    php build_tables.php 10000 4 drizzle

    where 10000 is the number of rows allocated total, and 4 is the number of partitions for those rows.

    You can type the same thing for mysql:

    php build_tables.php 10000 4 mysql

    and get interesting results.

    Here’s what I got:

    MySQL

    bash-3.2$ php build_tables.php 10000 4 mysql
Elapsed time between Start and Test_Code_Partition: 13.856538
last table for php partition: users_03
Elapsed time between No_Partition and Code_Partition: 14.740206
-------------------------------------------------------------
marker           time index            ex time         perct   
-------------------------------------------------------------
Start            1252376759.26094100   -                0.00%
-------------------------------------------------------------
No_Partition     1252376773.11747900   13.856538       48.45%
-------------------------------------------------------------
Code_Partition   1252376787.85768500   14.740206       51.54%
-------------------------------------------------------------
Stop             1252376787.85815000   0.000465         0.00%
-------------------------------------------------------------
total            -                     28.597209      100.00%
-------------------------------------------------------------
20000 rows inserted...

    drizzle

    bash-3.2$ php build_tables.php 10000 4 drizzle
Elapsed time between Start and Test_Code_Partition: 7.502141
last table for php partition: users_03
Elapsed time between No_Partition and Code_Partition: 7.072367
-------------------------------------------------------------
marker           time index            ex time         perct   
-------------------------------------------------------------
Start            1252376733.68141500   -                0.00%
-------------------------------------------------------------
No_Partition     1252376741.18355600   7.502141        51.47%
-------------------------------------------------------------
Code_Partition   1252376748.25592300   7.072367        48.52%
-------------------------------------------------------------
Stop             1252376748.25627400   0.000351         0.00%
-------------------------------------------------------------
total            -                     14.574859      100.00%
-------------------------------------------------------------
20000 rows inserted...

    MySQL: 699 inserts per second
    drizzle: 1372 inserts per second
    As far as inserts go, drizzle is about 2 times faster out of the box than MySQL.