How to Change the root Password in MySQL and Backup with mysqldump

I’m not DBA, but most Linux system administrators need to at least know a little about SQL. Like adding users, making simple databases, changing passwords, backups, etc… This article describes backing up a database to a fresh machine. In my case I happen to be backing up my wordpress blog. However these steps can applied to Sugar CRM, Joomla, or any database driven site that uses MySQL. The following tutorial does not completely cover backing up a wordpress blog; it only covers the database portion.  Which applies to many LAMP projects.  Enjoy!

The Goals of this Post:

– to remind myself how to backup databases

LAB: Backup WordPress Blog Database to new machine

I’m changing the default password of nothing to the same password as my root user. Please note that mysql ‘root’ user is not the same as Linux system root. Also, they can be different passwords and probably should.

[root@mattcom1 ~]# mysqladmin -u root password mynewpassword
[root@mattcom1 ~]# mysql -u root -p
Enter password:
[root@mattcom1 ~]#

Create a blank database on the machine we will repopulating.

Welcome to the MySQL monitor. Commands end with ; or \g
Your MySQL connection id is 10
Server version: 5.0.77 Source distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> create database savelono;

Use mysqldump command to back up the database on the original server. The database can be named anything, I usually back up to savelono.com with a ‘DATE-NAME.sql’ convention.

[root@savelono ~]# mysqldump -u root savelono > savelono-04-24-09.sql

Now copy your sql database to the new server and import it.

[root@mattcom1 ~]# mysql -u root -p -D savelono < savelono-04-24-09.sql

That’s it!  Piece of cake.

If you like this article tell Google!!

Written by mattb in: MySQL | Tags: , , , , , , , , , , ,

No Comments »

RSS feed for comments on this post. TrackBack URL

Leave a comment