Mysql admin tasks in aws

MySQL Administration tasks

• To start/stop/restart mysql
  • As root/sudo, invoke /etc/init.d/mysqld (start/stop/restart) (i.e. /etc/init.d/mysqld start)
• To make mysql accessible to outside world
  • Make sure you have a static ip address (like on ec2 instances, use elastic-ip)
  • Edit (as root/sudo) /etc/mysq.cnf and make sure bind-address is 0.0.0.0 (i.e. bind-address=0.0.0.0)
  • Then restart mysql
• To give a specific user outside world privilege access
  • For user xyz to xmlloader database do the following
    • GRANT ALL ON xmlloader.* to 'xyz'@'0.0.0.0' IDENTIFIED BY '[xyzpassword]'
      • The [xyzpassword] is something you need to set/specified (i.e. it's new)
• To export full database as sql inserts
  • As root/sudo, invoke mysqldump -u [user] -p[userpassword] [database] > [output file name]
    • i.e. sudo mysqldump -u dbuser -pdbuser feedloader > fl_loader.sql
• To import full database (i.e. new database) from export script
  • As root/sudo, invoke mysql -u [user] -p[userpassword] [database] < [input file name]
    • mysql -u dbuser -pdbuser feedloader < fl_loader.sql
• To create/grant user/new users to the database
  • GRANT ALL PRIVILEGES ON database.table or *.* TO 'username'@'destination, (i.e. %,localhost)' IDENTIFIED BY 'password of account' WITH GRANT OPTION;
    • e.g.GRANT ALL PRIVILEGES ON . TO 'dbuser'@'%' IDENTIFIED BY 'dbuser' WITH GRANT OPTION;