1,234
edits
Changes
no edit summary
* In VPC/Subnets, create a new one in vpc-ops345 named subnet2-ops345, in us-east-1b instead of 1a. 10.3.45.128/25
* Go back and try again to create the database.
* Assign temporary elastic IP to yum install mariadb, no need for server.
** Disassociate and release the elastic ip.
* Do the rest of the lab as the regular user, don't use root.
* In the AWS Console go to RDS, and find the FQDN ("Endpoint") for the database. Such as ops345db.cobdogt5aykb.us-east-1.rds.amazonaws.com - record that.
* Try to connect: mysql -u root -plongdbpassword -h ops345db.cobdogt5aykb.us-east-1.rds.amazonaws.com
** It won't work, the firewall (SG) won't allow it. Modify ops345sgprivate to allow incoming TCP port 3306 (MySQL) from ops345sgprivate.
* Should be able to log in now. If you get something like this: "ERROR 1045 (28000): Access denied for user 'root'@'10.3.45.11' (using password: YES)" then double-check the master username under RDS/ops345db/Configuration and you can reset the password via Modify (might take a few minutes to propagate).
** See also https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Troubleshooting.html
* Most mysql commands need to have a semicolon at the end.
* The mysql commandline is nothing like the linux shell. Have to use mysql (or plain SQL) commands.
* Out of the box show databases; shows 4 databses, all of which are used internally by mysql, they are not for you to store data.
* use mysql; show tables; select * from user; select user from user;
* mysql root is not the same as the linux root, but it is an administrator and you should only use it for creating users, databases, and assigning permissions.
* Typically you would use something like this: " MariaDB> grant all privileges on DATABASE_NAME.* TO 'USER_NAME'@'%' identified by 'PASSWORD'; " but that won't work on AWS RDS because your root user doesn't have ALL PRIVILEGES, so can't grant them to another user.
* Instead create a user first: CREATE USER 'andrewdb'@'%' IDENTIFIED BY 'andrewdbpassword';
* Find what privileges your root has: show grants for 'root';
* Give the most important ones to your db user: " GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON firstdb.* TO 'andrewdb'@'%'; "
* Log out from root
* mysql -u andrewdb -pandrewdbpassword -h ops345db.cobdogt5aykb.us-east-1.rds.amazonaws.com
* show databases; - note that the list is shorter.
* use firstdb;
* show tables;