Sign our RSS

MySQL Fast Bulk Inserts

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.

Sign our RSS

Large MySQL Databases Index Creation

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.

Sign our RSS

Backup and Restore MySQL Databases

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.

 

Sign our RSS

Forget Mysql ROOT Password?

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