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 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