Change mysql root password

# mysqladmin -u root -pcurrentpassword password ‘newpassword’

OR
$ mysqladmin -u root -p’oldpassword’ password newpass

OR

mysql> UPDATE user SET password=PASSWORD(‘newpassword’) WHERE user=’root”;

OR

# mysqladmin -u root password ‘newpassword’
[Note: There is no currentpassword for root in this example]

Change MySQL Regular User (non-root) Password

# mysqladmin -u nur -pcurrentpassword password ‘newpassword’

OR

mysql> UPDATE user SET password=PASSWORD(‘newpassword’) WHERE user=’nur’;

http://www.learn-mysql-tutorial.com/

php mysql and java script learning

http://lpmj.net/

Web tutorial: http://www.tizag.com/mysqlTutorial/mysqlquery.php 

Auto mysql backup setting

/usr/bin/mysqldump -uUSERNAME -pPASSWORD DATABASE > FILEPATH/db_backup_$(date +\%Y-\%m-\%d-\%H-\%M).sql

1. The command that is to be executed (often a user written script or a UNIX command)
2. How often the command is to be executed (what days, months, dates, hours etc etc)
There are following different format to get the backup of mysql database using cron job
1) Simple cron job syntax to take database backup
* * * * * /usr/bin/mysqldump -u databaseusername databasename -ppassword > /path of database store.sql
2) Compress the database backup in gz format
* * * * * /usr/bin/mysqldump -u databaseusername databasename -ppassword|gzip > /path of database store.gz
3) Compress the database backup in bz2 format
* * * * * /usr/bin/mysqldump -u datbaseusername databasename -pdatbasepassword|bzip2 -c > /path of database store.bz2

/usr/bin/mysqldump -u [user] -p [pass] [database] > /usbdrive/backup-$(date +'\%F').sql

http://ubuntuforums.org/showpost.php?p=4329469&postcount=6
http://blog.webhosting.uk.com/web-hosting/how-to-set-the-mysql-database-backup-cronjob/
http://www.dagondesign.com/articles/automatic-mysql-backup-script/
http://abdussamad.com/archives/54-Using-a-cron-job-to-backup-your-MySQL-Database.html
http://help.joyent.com/index.php?pg=kb.page&id=113
http://www.webmasterworld.com/databases_sql_mysql/3277413.htm

Replace and insert data in mysql
update items set barcode = replace(barcode, ‘MBA’, ‘MBA00’) where barcode like ‘MBA%’ and length(barcode)=6;

Add leading zeros with existing data
update items set barcode = concat(’00’,barcode) where length(barcode) =4;
update items set barcode = concat(’00’, barcode) where (length(barcode)=4 and barcode not like ‘PH%’);

Update field in mysql
update items set location = ‘REF’ where barcode like ‘%R%’;

Advertisements