• Tweet
• Email
• Sharebar

• Tweet
• Email

I was very much troubled by the tedious repetitive process of backing up and restoring databases from MySQL database server. Shiva found out a script which was good to take the daily backup according to dates, but restoring them was still the headache.

Today, I came across a switch to be used with MySQL so that we can easily backup all databases and restore them very easily as well.
Here is the trick that I wanted to share.

First of all backup the database into a SQL file by doing this:

crackjack@planetn# mysqldump -u <mysql_user> -p<mysql_pass> --all-databases > all_db_backup.sql

Then you will have the SQL file in your computer which you can copy to the machine where you need to restore the database to.

In the remote machine you can do this to restore the database:

crackjack@planetn# mysql -u <mysql_user> -p<mysql_pass>

Now you will be greeted with a prompt like this:

mysql>

There you need to import the SQL file like this:

mysql>source <path_to_sql_file>;

NOTE: You need to notice that there is no space between -p and password field.

mysqladmin command to change root password

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root.

To setup root password for first time, use mysqladmin command at shell prompt as follows:

$ mysqladmin -u root password NEWPASSWORD

However, if you want to change (or update) a root password, then you need to use following command

$ mysqladmin -u root -p'oldpassword' password newpass

For example, If old password is abc, and set new password to 123456, enter:

$ mysqladmin -u root -p'abc' password '123456'
Changing MySQL root user password

This is another method. MySQL stores username and passwords in user table inside
MySQL database.

You can directly update password using the following method to update or
change password.

1) Login to mysql server, type following command at shell prompt:

$ mysql -u root -p

2) Use mysql database (type command at mysql> prompt):

mysql> use mysql;

3) Change password for user vivek:

mysql> update user set password=PASSWORD("NEWPASSWORD") where User='vivek';

4) Reload privileges:

mysql> flush privileges;
mysql> quit
Recover MySQL root Password
Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
 Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

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

mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p