• MySQL Fast Bulk Inserts

    Posted on June 15th, 2010 admin No comments

    This post will be very short, i just will share a command to enable MySQL to make fast bulk inserts on MyISAM tables.

    In some tables that you need to insert a lot of data, exists a right technique to do that.

    –  Befor starting the inserts go to mysql > alter table target_table disable keys;

    This command above will disable all indexs updates during the inserts, that speeds up the insert operation, after you end inserting all data you need to recreate does indexes typing the following command mysql > alter table target_table enable keys;

    Easy? Yeah. This command had helped me a lot, as i manipulate a lot of data, millions and millions of rows this is very useful and the gains are in hours, now i have more time to see Brazil playing on the World Cup. :)


  • Large MySQL Databases Index Creation

    Posted on June 29th, 2009 admin No comments

    Hello,

    Maybe some of you guys know that in the gwebtools.com website we index daily millions and millions of websites, its really a hard cpu and memory usage process and takes sometime.

    For the name server spy tool wich we update monthly we need to import some files to our database monthly big files more than 10GB of domains and name servers data.

    We have test a lot of ways to speeding up this process as much we can, if some visitor of the web site type a name server address in the ns spy tool, we need to check a database with 70,000,000 rows, for speeding up this search is necessary a btree index, another tool is the domain list where we separate all domains in prefixes.

    If you create a table name with the columns but with no index the process of inserting rows is really very fast, but if you create a table with indexes the process is slow.

    We suggest you if you have big tables, and big data files if you need to import they to a table create your index tables after inserting all that, this is what we do, we create all indexes after inserting all the data, it takes a lot of hours but is  faster.

    Just a suggestion from someone who have experience with large databases.


  • Backup and Restore MySQL Databases

    Posted on June 1st, 2009 admin No comments

    I will just show the most basic and used methods for mysql backup and restore.

    1st Method – Mysql Dump

    The mysqldump is the most common method for backuping a mysql database, a database dump is a text file with the create tables, columns, insert rows…

    For making a mysql dump (backup) use the follow command:

    single database: mysqldump -u user -p password database-name > backup-file-name.sql

    all databases: mysqldump -u user -p password -A > backup-file-name.sql

    For restoring a mysql dump use the following command:

    mysql -u user -p password database-name < backup-file-name.sql


    2nd Method – Mysql Folders Backup

    The folder backup is faster, all the mysql databases are on the folder /var/lib/mysql, each database is in a single folder.

    For backuping using this method use the follow command:
    tar -cf backup-file-name.tar /var/lib/mysql/database-name

    For restoring using this method just decompress the file:
    tar -xvf backup-file-name.tar

    That´s it, now you just need to use your method.

     


  • Forget Mysql ROOT Password?

    Posted on April 2nd, 2009 admin No comments

    Yesterday my friend had ask me how he can reset the mysql password, because he had forget his password.

    Here are the following steps that we made.

    On *nix systems, type the following commands on console

    1.  on console: service mysqld stop

    2.  on console: start the mysql daemon process with –skip-grant-tables

    3. on console: mysql -u root

    4.  on mysql: UPDATE mysql.user SET Password=PASSWORD(‘password’) WHERE user=’root’;

    5.  on mysql: FLUSH PRIVILEGES;

    6.  on console: service mysqld restart

    Now write the new password on a piece of paper :O