Dec 01

Solved – AWS RDS MySQL ERROR 1227 (42000) at line : Access denied

Categories:

Amazon Web Services, MySQL

by

When your are trying to import your data into RDS MySQL, it may prompt with following error message. “ERROR 1227 (42000) at line xxx: Access denied; you need (at least one of) the SUPER privilege(s) for this operation”

 

Error!

ERROR 1227 (42000) at line xxx: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

This can be fixed by removing the DEFINER from MySQL dump. You can use following simple command to fix this issue.

 

there are alternative solution which provided by AWS premium support knowledge base , But that does not work for me. You can try that out if I above mentioned work around does not work.

Let’s look at why there is limited permission on RDS MySQL .

As you might be aware, AWS RDS (Relational Database Service) is a managed service  and hence in order to guarantee the stability of RDS instance, the permissions of master user (root user in RDS) are not same as root user in native mysql.

RDS Master user has the following permission:
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* WITH GRANT OPTION, REPLICATION SLAVE (Only For Amazon RDS MySQL versions 5.6 and 5.7, Amazon RDS MariaDB)

So if you need more permission other than above, you have to request / inform it to AWS support team. they will do the necessary arrangement.

If you have any questions please do comment below. 🙂

have nice time !!

3 comments

Jun 18

Letsencrypt ssl for a non standard web ports

Categories:

Apache

by

In this tutorial, I would like to demonstrate how to use Letsencrypt ssl for a non standard web ports other than 80, 443 to generate a  SSL certificate for an Apache. If you wish, you can follow same method to implement SSL on other web servers such as nginx  and Tomcat as well. If you are new to Letsencrypt SSL, here is the brief introduction . Letsencrypt is a free, and non-profit CA (certificate authority) which owned by Internet Security  Research Group (ISRG).

please note this is done on Centos 7

01) Install cerbot

first enable the EPEL repository

how to enable EPEL repo on RHEL / Centos read this  and enable EPEL optional channel

then install cerbot using yum as follows

#yum install certbot

02) Install SSL certificate

execute following as root
#certbot certonly –manual  –preferred-challenges dns

This is the most important command, because we generate certificate manually even though cerbot provide Apache plugin. manually generated certificates are flexible so we can integrate it to any preferred web server later 🙂 . preferred-challenges is set to dns, so domain verification is done using  TXT records

 

After that you will get similar wizard like following image. once you submit the domain. it will give DNS TXT record as challenge
. you must create it before continue. Then after it will generate ssl certificate for your domain.

certonly with dns

03) Configure SSL on Apache

you can use following Apache virtual-host config template

 

 

Letsencypt SSL for non standard port

 

04) SSL renewal

you can renew SSL certificate automatically. Add new cron just like following which runs renewal process every week. It’s recommended to reload / restart apache server, so in next line we do restart apache process as well

 

 

 

Leave comment

Jan 12

How to Move MySQL Data Directory to New Location on CentOS

Categories:

MySQL

by

In default MySQL installation, Data Directory pointed to “/var/lib/mysql/” . As a best practice, it’s recommended to move Data directory to new location
which contains more disk space than default root partition. This tutorial guides you how to Move MySQL data directory to new location on CentOS or RHEL. Even data directory contains data, you can still move it to another location, but you have to be careful if you try this on production environment. Let’s go through it quickly 🙂

1) Prepare new location

2) Find current Data Directory location

you can get it from /etc/my.cnf if it’s defined on. To verify it or it’s not mentioned on configuration file, most probably use default location.
let’s find that out

log into mysql server and run following command

As per above current location is “/var/lib/mysql/”

3) Shut down MySQL server

4) Copy MySQL data directory to new location

now new location is /opt/newmysql_datadir/mysql

5) Modify SELinux to allow MySQL to use the different (non default) path

This step is mandatory if your system enabled with SELinux, otherwise you can ignore this step

6) Update new settings to my.cnf

Find the line in the [mysqld] block that begins with datadir=. Change the path which follows to reflect the new location. In addition to that, socket was previously located in the data directory,  we’ll need to update it to the new location

It should like below after updating.

Apart from that we’ll need to add configuration for the MySQL client.  Insert the following settings at [client]  block which is at the bottom of the file.

7) Start MySQL server

If it won’t start  , you may need to troubleshoot by checking  MySQL  error log. You can make comments any issues if you have faced here, I’m always happy to assist you !!

Leave comment

Older posts «

» Newer posts

Fetch more items