Backup Mysql Database:
you can use mysqldump to backup a database. If i want to backup the database mileage into the sql file mileage.sql.
$mysqldump -u root -p mileage > mileage.sql
You will be prompted to enter the password for the root. This will dump the data and the commands to create the table in the new machine.
You can use rsync to move the database file from one server to another server
$rsync -avz mileage.sql jc@192.168.0.185:/home/jc/
this will move the mileage.sql from the local server to the machine whose ip-address is 192.168.0.185 and put it in the folder /home/jc
Restoring the database
To restore a database from a previous backup you can use the output of the mysqldump.
First we have to create the database inside the mysql as follows
mysql> create database mileage;
Now get out of the mysql. issue the following command
$mysql -u root -p mileage < mileage.sql
You will be prompted to enter the password for root. This will create all the tables, and import all the data
Grant access to remote IP address
If the ip address of your server is 192.168.0.185, open the file /etc/mysql/my.conf
find the line
bind-address=127.0.0.1
and it should look like
bind-address=192.168.0.185
Connect to mysql server:
$ mysql -u root -p mysql
Grant access to a new database
If you want to add a new database called foo for user bar and remote IP 202.54.10.20 then you need to type the following commands at mysql> prompt:mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';
How Do I Grant Access To An Existing Database?
Let us assume that you are always making connection from remote IP called 202.54.10.20 for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database, enter:
mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';