Posted on June 15th, 2010 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.
Posted on June 29th, 2009 No comments
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.
Posted on June 1st, 2009 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.
Posted on April 2nd, 2009 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