Quick tip on MySQL dump

Terminal command to dump MySQL data: 
mysqldump -u -p > /path/to/sqldump/db_database.sql

eg: mysqldump -uroot -padmin123 db_sports > /home/ec2-user/db_sports.sql

Import sql dump:
mysql -u -p < /path/of/sqldump/db_database.sql

ex: mysql -uroot -padmin123 db_sports < /home/ec2-user/db_sports.sql

Advertisements

Setting up MySQL server on aws EC2 instance

Launch an EC2 instance of type Amazon Linux AMI from your aws console.

SSH into your ec2 instance
ssh -i ec2-user@my_ec2_ip_address

Update the instance
sudo yum update -y

Install the mysqld server
sudo yum install -y mysql55-server

start the mysqld instance
sudo service mysqld start

the following command ensure launches mysqld on server restart
sudo chkconfig mysqld on

run the following command to set password for root user and delete test databases.
sudo mysql_secure_installation

make a note of the root password 🙂

Let’s try to create a user and database. This way we can control the database access levels.
mysql -uroot -pmy_root_password

I’m going to create a db_demo with demo_user having password demo123.
CREATE DATABASE db_demo;
USE db_demo;
CREATE USER 'demo_user'@'localhost' IDENTIFIED BY 'demo123';
GRANT ALL PRIVILEGES ON *.* TO 'demo_user'@'localhost' WITH GRANT OPTION;
CREATE USER 'demo_user'@'%' IDENTIFIED BY 'demo123';
GRANT ALL PRIVILEGES ON *.* TO 'demo_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

That’s it. You are all set.

Note:

  • details on mysql privileges can be found here
  • Don’t forget to open the default port 3306 if you want to access the database outside the ec2 instance boundary
  • useful mysqld commands
    • sudo service mysqld start
    • sudo service mysqld stop
    • sudo service mysqld restart
    • sudo service mysqld status