Backing Up and Restoring Your MySQL Database - Easy Restore

Now that you've got backups of your database, let's learn how to restore your backup in case your database goes down. Here's how you can restore your backed up database using the mysql command.
Restore using mysql
If you have to re-build your database from scratch, you can easily restore the mysqldump file by using the mysql command. This method is usually used to recreate or rebuild the database from scratch.
Here's how you would restore your custback.sql file to the Customers database.

Backing Up and Restoring Your MySQL Database - What about Multiple Databases?

If you are a database administrator who has to look after multiple databases, you'll need to back up more than one database at a time. Here's how you can backup multiple databases in one shot.
If you want to specify the databases to backup, you can use the --databases parameter followed by the list of databases you would like to backup. Each database name has to be separated by at least one space when you type in the command. So if you have to backup 3 databases, let say Customers, Orders and Comments, you can issue the following command to back them up. Make sure the username you specify has permissions to access the databases you would like to backup.

Backup and Restor Mysql Server On Centos 5

Backing Up and Restoring Your MySQL Database

The easiest way to backup your database would be to telnet to the your database server machine and use the mysqldump command to dump your whole database to a backup file. If you do not have telnet or shell access to your server, don't worry about it; I shall outline a method of doing so using the PHPMyAdmin web interface, which you can setup on any web server which executes PHP scripts.

Playing with mysqldump
If you have either a shell or telnet access to your database server, you can backup the database using mysqldump. By default, the output of the command will dump the contents of the database in SQL statements to your console. This output can then be piped or redirected to any location you want. If you plan to backup your database, you can pipe the output to a sql file, which will contain the SQL statements to recreate and populate the database tables when you wish to restore your database. There are more adventurous ways to use the output of mysqldump.

Resetting your Mysql Root Account (Linux)

Have you ever forgotten the root password on one of your MySQL servers? No? Well maybe I’m not as perfect as you. This is a quick h00tow (how to) reset your MySQL root password. It does require root access on your server.

First things first. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.

mysqld_safe --skip-grant-tables

You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.

mysql --user=root mysql

update user set Password=PASSWORD('new-password') where user='root';
flush privileges;
exit;

Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.