How to Restore MySQL Database

To restore a backup of MySQL database or databases, the following commands would be used.

The format of the command is 

# mysqldump -u [username] –p[password] [database_name] < [dump_file.sql]

 To restore Remote MySQL Database

# mysqldump –h ipaddress -u [username] –p[password] [database_name] < [dump_file.sql]

 To restore a single or multiple database(s):

# mysql -u root  –proot123 MYDB1 < MYDB1.sql

 If you want to restore a database that already exists on targeted machine, then you will need to use the mysqlimport command.

 # mysqlimport -u root –proot123 MYDB1 < MYDB1.sql

Note:

To backup MYSQL DB, you can refer my blog

How to Backup MySQL Database using mysqldump utility?

To take a backup of MySQL database or databases, the database must exist in the database server and you should have access to it.

The format of the command would be. 

# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]

How to Backup Remote MySQL Database

# mysqldump –h ipaddress -u [username] –p[password] [database_name] > [dump_file.sql]

How to Backup a Single MySQL Database?

# mysqldump -u root -proot123 MYDB1 > MYDB1.sql

How to Backup Multiple MySQL Databases?

# mysqldump -u root -proot123 --databases MYDB1 MYDB2 MYDB3 > MYDB1_MYDB2_MYDB3.sql

How to Backup All MySQL Databases?

# mysqldump -u root -proot123 --all-databases > alldb.sql

How to Backup MySQL Database Structure Only?

# mysqldump -u root -proot123 --no-data MYDB1 > MYDB1structure.sql

How to Backup MySQL Database Data Only?

# mysqldump -u root -proot123 --no-create-db --no-create-info MYDB1 > MYDB1data.sql

How to Backup Single Table of Database?

# mysqldump -u root -proot123 MYDB1 TABLE1 > TABLE1_MYDB1.sql

How to Backup Multiple Tables of Database?

# mysqldump -u root -proot123 MYDB1 TABLE1 TABLE2 > TABLE1_TABLE2_MYDB1.sql

Note:
To restore a MYSQL Database, you can refer my blog

How to check the mysql connectivity from any Ubuntu Server

To check the mysql connectivity form any Ubuntu 12.04 server, you have to install the following package first,

apt-get install mysql-client

then type this command and press enter

mysql --host=<IP> -u <username> -p<password>

For example

mysql --host=172.168.10.58 -u admin -padmin123

MYSQL Port 3306 is blocked on Ubuntu server

After installing Mysql 5.5 on ubuntu 12.04 server, you cannot connect to the server using port 3306.

This is because mysql allows connections from localhost only, by default.

To disable this, remove or comment the line "bind-address = 127.0.0.1" from /etc/mysql/my.conf file

Note:
Also you should have a user which is allowed to connect to mysql from other host. To do this you can refer my old blog

Create Users in MYSQL

Creating Users in MYSQL

First, use the mysql program to connect to the server as the MySQL root user:

For Example:

shell> mysql --user=root --password=root mysql

or

shell> mysql -u root -p mysql

The following statements creates an user 'admin with password as 'admin'

% in the below syntax allow connections to MYSQL server from any host

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%'

    ->     IDENTIFIED BY 'admin' WITH GRANT OPTION;

    ->      flush privileges;

The following statements creates an user 'admin with password as 'admin'

'localhost' in the below syntax allow connections to MYSQL server from localhost only.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost'

    ->     IDENTIFIED BY 'admin' WITH GRANT OPTION;

    ->      flush privileges;

To create Read-only User:

mysql> use mydb

mysql> GRANT SELECT ON mydb.* TO 'roadmin'@'%'

    ->     IDENTIFIED BY 'roadmin';

 ->      flush privileges;

            flush privileges;

Creating database in mysql

Login as the mysql root user to create database:

 $ mysql -u root -p

Sample outputs:

Add a database called cars, and then enter:

mysql> CREATE DATABASE taxi1;

Now, the database is created. Use this database with use command:

 mysql> USE taxi1;

Next, create a table called drivers with name, email and id as fields:

 mysql> CREATE TABLE drivers (id INT, name VARCHAR(20), email VARCHAR(20));

To display your tables in taxi1 database, type:

mysql> SHOW TABLES;

Sample outputs:
+—————–+
| Tables_in_taxi1 |
+—————–+
| drivers         |
+—————–+
1 row in set (0.00 sec)

Finally, add a data i.e. row to table drivers using INSERT statement, run:

 mysql> INSERT INTO drivers (id,name,email) VALUES(1,”raja”,”xyz@abcde.com”);

Sample outputs:

Query OK, 1 row affected (0.00 sec)

Try to add few more rows to your table:

mysql> INSERT INTO drivers (id,name,email) VALUES(2,”kumar”,”kum@efgh.com”);

mysql> INSERT INTO drivers (id,name,email) VALUES(3,”emile”,”emile@ijkl.com”);

To display all rows i.e. data stored in drivers table, enter:

 mysql> SELECT * FROM drivers;

Sample outputs:

+——+——-+—————+
| id   | name  | email         |
+——+——-+—————+
|    1 | raja | xyz@abcde.com  |
|    2 | kumar | kum@efgh.com   |
|    3 | emile   | emile@ijkl.com |
+——+——-+—————+

3 rows in set (0.00 sec)