Select Page

Month: September 2017

Setup Database and Create a Read-Only user in AWS Redshift and Mysql

 Even if you’re the only user of your data warehouse, it is not advised to use the root or admin password.  As a rule and as a precaution you should create additional credentials and a profile for any user that will have access to your DW.  Some systems provide an easier way of doing it than others. In the case of Amazon’s Redshift, it is not very straightforward.  Because sometimes, flexibility comes with complexity.  And that’s what we encountered when we tried to create a user with read-only access to a specific schema.  Here’s what you will need to achieve this task: Setup Database in Redshift  create database [db-name]; revoke all on schema public from public; create schema [schema-name]; create table [schema-name].[table-name](   … ) Create Read-only User in Redshift  create user [user-name] password ‘[password]’; grant usage on schema [schema-name] to [user-name]; alter user [user-name] set search_path to [schema-name]; grant select on table [schema-name].[table-name] to [user-name]; Refer: Amazon Redshift. http://awsdocs.s3.amazonaws.com/redshift/latest/redshift-dg.pdf Create read-only user on MySQL CREATE USER r_only_user identified by ‘password’; GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON *.* TO ‘r_only_user’@’%’ IDENTIFIED BY ‘password’; GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON *.* TO ‘r_only_user’@’localhost’ IDENTIFIED BY ‘password’; FLUSH...

Read More

Steps for installing Mysql-5.6 on linux

Install Steps Install dependencies sudo yum install ncurses-devel Download MySQL source code # cd /usr/local/src/ # wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.19.tar.gz # tar xvfz mysql-5.6.19.tar.gz # cd /usr/local/src/mysql-5.6.19 Build mysql # mkdir build # cd build # cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql # make # make install Initial Setup Create group and user # groupadd mysql # useradd -g mysql -d /usr/local/mysql mysql Create data directory # mkdir /var/lib/mysql # chown -R mysql:mysql /usr/local/mysql Modify /etc/my.cnf [client] socket=/tmp/mysql.sock [mysqld] datadir=/var/lib/mysql #socket=/var/lib/mysql/mysql.sock socket=/tmp/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mysqld according to the # instructions in http://fedoraproject.org/wiki/Systemd collation-server=utf8_unicode_ci character-set-server=utf8 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Create database # /usr/local/mysql/scripts/mysql_install_db –user=mysql –datadir=/var/lib/mysql -basedir=/usr/local/mysql Change owner # chown mysql:mysql -R /var/lib/mysql/* Copy initd script # cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld Retart mysql-server # /etc/init.d/mysqld restart Confirm $ mysql –ssl-ca ca-cert.pem mysql> show variables like ‘%ssl%’; +—————+———————————-+ | Variable_name | Value                            | +—————+———————————-+ | have_openssl  | YES                              | | have_ssl      | YES                              | | ssl_ca        | /usr/local/mysql/ca-cert.pem     | | ssl_capath    |                                  | | ssl_cert      | /usr/local/mysql/server-cert.pem | | ssl_cipher    |                                  | | ssl_crl       |                                  | | ssl_crlpath   |                                  | | ssl_key       | /usr/local/mysql/server-key.pem  | +—————+———————————-+ 9 rows in set (0.00 sec) mysql> show status like ‘Ssl_cipher’; +—————+——————–+...

Read More

AWS RDS Backup Methods

RDS provides two backup methods: Automated backups and user-initiated DB snapshots. Automated backups are initiated during the creation of an RDS instance. You set the backup window and the retention period for the backups and you’re ready. Although automated backups seem attractive because they are easy to manage, they have some constraints. 1.   Retention: There is a 35-day retention period limit. After exceeding that limit, the snapshot is then deleted. 2.   Deleted database: If you accidentally delete a database for any reason, automated backups are going to be removed too. 3.   Disaster Recovery: Automated backups can only be restored from within the same region. If you have a DR strategy, you might want to move the snapshots between multiple regions. These 3 constraints can be resolved with the DB snapshots. 1.   DB snapshots can be retained for as long as you wish. Means RDS manual snapshots will only be deleted when the admin specifically deletes the rds snapshots. 2.   DB snapshots are not removed if you accidentally delete the database. 3.   DB snapshots can be moved from one region to another without any constraint. Amazon Web Services gives us three ways to take manual DB Snapshots: the management console, the awscli command line utility and the various Amazon SDKs. As long as manual snapshots are taken regularly and kept secure, it’s easy to recover your database within a few minutes. Ultimately, you have...

Read More