Mysqldump Backup Restore Steps

How to Restore MySQLdump

In this tutorial i will show you how to restore mysqldump.

giphy (2)

1- Connect/SSH MySQL Server, Add extra parameter if MySQL server is not in the localhost "-h server_name or --host= server_name"

mysql -u username -p password

2- Create a database.

create database database_name ;

If MySQL backup file ends with .sql
Run below command from command line:
( Add "-h server_name or --host= server_name" if MySQL server is not in the localhost )

mysql -u user -p password database_name

If MySQL backup file ends with .tar.gz, it needs to be extracted.
Following command will do both extract and database restoration:

zcat /folder/of/database/database.tar.gz | mysql -u user -p password -h server database_name_created_earlier

MySQL connection basic troubleshooting

1- Getting error while connecting to external server ? Check if port is opened between your host and external server, be sure that the mysql port shows open, if it's closed then it's your firewall or destination server firewall rules.

[root@hostname]# nmap -sS -O host_name
Interesting ports on server_name (ip.ip.ip.ip):
Not shown: 1676 filtered ports
PORT STATE SERVICE
22/tcp open ssh
80/tcp closed http
3306/TCP closed mysql

2- Getting error when connecting to MySQL in the same server, try to see if the server is up and MySQL is running by checking below command output:

netstat -ln | grep mysql

Expected output if server is up:

unix 2 [ ACC ] STREAM LISTENING 16488079 /mysql/data/mysql_data/mysql.sock

3- If you have an access to the MySQL server, it's always a good idea to check the error logs, if you don't know where is the location of errorlogs then try below command:

[root@hostname ~]# cat /etc/my.cnf | grep -i error
log-error = /mysql/data/mysql_logs/mysql-error-log.err