Questions tagged with Amazon Relational Database Service
Content language: English
Sort by most recent
I observe that Aurora Mysql cluster data and cluster snapshot data can be exported to S3 in Apache Parquet format as described in https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/export-cluster-data.html and https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-export-snapshot.html. However, I am wondering whether there is a way to export incremental backup data in a similar fashion. My objective is to export an Aurora Mysql cluster to a local/on-prem server and keep it refreshed daily using incremental backup. Please let me know your comments/thoughts. Thanks!
I have tried various options of Disaster Recovery options for SQL Server. There are two parts to This
FULL BACKUPS and then On-going Replication
1. Back Up and Restore - Took Automated backups ,native S3,Manual snapshots - cross region copied
But cannot satisfy the ongoing Replication condition
To satisfy the on-going repication
1. Restore from transactional Logs - cross region copying them and restoring PiTR-Xregion
2. Promoting Read Replica in Cross region
3. Restoring the Database from Cross region Automated Back Ups
4. Create a Full Back up restore, Using DMS and CDC -On going Replication
At best Multi Region but Second Region is only Read-Replica with almost zero RTO, RPO
but I am not able to get a solution for AWS Active -Active For Cross region
Can I replicate the DMS - Ongoing Replication CDC on both sides ?
I will try this and let you know
Any other solutions ?
Can someone Please Help me in Fetching the AWS RDS Snapshots which are older than 1 month using "AWS CLI" Command.
Regards,
kalyan varma
As title, my snapshot is made from 5.6.
I know the support of the 5.6 one is ended, however, I can't see any higher version to restore it.
GRANT file ON `%`.* TO user@`%`with grant option Error Code: 1045. Access denied for user 'user'@'%' (using password: YES) 0.000
How do I connect to localhost from MySQL Workbench?
Cluster parameters:
1. Engine: Aurora (MySQL 5.7) 2.10.3
2. VPC security group: default and allow-prod-corp-port-81928200
3. Database authentication: password authentication
MySQL Workbench parameters:
1. Connection method: Standard (TCP/IP)
2. Hostname: using correct host address
3. Port: 8192
4. Username: using correct master user name
5. Password: using correct master password
Results:
1. From test connection: Failed to connect to MySQL at <database address>:8192 with user <master user name> . Unable to connect to localhost.
2. From open connection: Cannot connect to database server. Your connection attempt failed for <username> at <database address>:8192. Unable to connect to localhost.
I have enabled performance insight for my RDS.deadlock error occured in my database.I need to know about the querry which got deadlock.
I am unable to modify the RDS aurora cluster, located in Ireland region, due to unexpected error as in the screenshot attached. I tried to clear caching and cookies, using different browsers and devices and even different users but no luck.
Login request was received, the username and password were correctly extracted from the request body, and a user with ID 1 was found in the database. The form still 504 fails eventually.
my index.js, db.js, users.js, and login.html all seem fine.
I'm on Lightsail so unfortunately I've had to use SQL Workbench this whole time.
Not sure if there's an issue with the Lightsail to DB communication? It's been a pain to try to figure out Lightsail with the 'module' stuff like databases.
users.js :
```
const connection = require('./db');
const bcrypt = require('bcrypt');
const saltRounds = 10;
class User {
constructor(id, username, password, email, createdAt, updatedAt) {
this.id = id;
this.username = username;
this.password = password;
this.email = email;
this.createdAt = createdAt;
this.updatedAt = updatedAt;
}
static create(username, password, email) {
const now = new Date().toISOString();
const sql = `INSERT INTO loginserver (username, password, email, created_at, updated_at) VALUES (?, ?, ?, ?, ?)`;
bcrypt.hash(password, saltRounds, (err, hash) => {
if (err) {
console.error('Error hashing password:', err);
return;
}
const values = [username, hash, email, now, now];
connection.query(sql, values, (err, result) => {
if (err) {
console.error('Error creating user:', err);
return;
}
console.log('User created with ID', result.insertId);
const user = new User(result.insertId, username, hash, email, now, now);
return user;
});
});
}
static getByUsername(username) {
const sql = `SELECT * FROM loginserver WHERE username = ?`;
connection.query(sql, [username], (err, results) => {
if (err) {
console.error('Error getting user by username:', err);
return;
}
if (results.length === 0) {
console.log('User not found');
return null;
}
const { id, username, password, email, created_at, updated_at } = results[0];
console.log('User found with ID', id);
const user = new User(id, username, password, email, created_at, updated_at);
return user;
});
}
checkPassword(password) {
return new Promise((resolve, reject) => {
bcrypt.compare(password, this.password, (err, isMatch) => {
if (err) {
console.error('Error checking password:', err);
reject(err);
} else {
resolve(isMatch);
}
});
});
}
update() {
const now = new Date().toISOString();
const sql = `UPDATE loginserver SET username = ?, password = ?, email = ?, updated_at = ? WHERE id = ?`;
const values = [this.username, this.password, this.email, now, this.id];
connection.query(sql, values, (err) => {
if (err) {
console.error('Error updating user:', err);
return;
}
console.log('User updated with ID', this.id);
this.updatedAt = now;
return this;
});
}
delete() {
const sql = `DELETE FROM loginserver WHERE id = ?`;
connection.query(sql, [this.id], (err) => {
if (err) {
console.error('Error deleting user:', err);
return;
}
console.log('User deleted with ID', this.id);
return;
});
}
}
module.exports = User;
```
index.js :
```
const express = require('express');
const https = require('https');
const socketIO = require('socket.io');
const path = require('path');
const fs = require('fs');
const mysql = require('mysql');
const User = require('./server/users');
const bodyParser = require('body-parser');
const app = express();
const server = https.createServer({
key: fs.readFileSync('/etc/letsencrypt/live/ispeedrun.tv/privkey.pem'),
cert: fs.readFileSync('/etc/letsencrypt/live/ispeedrun.tv/fullchain.pem')
}, app);
const io = socketIO(server);
// Add this before the routes
app.use((req, res, next) => {
console.log('Request received');
next();
});
app.use(express.static(path.join(__dirname, 'views', 'public')));
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
app.get('/', (req, res) => {
res.sendFile(path.join(__dirname, 'views', 'index.html'));
});
app.get('/live', (req, res) => {
res.sendFile(path.join(__dirname, 'views', 'live.html'));
});
const connection = mysql.createConnection({
host: 'ls-7f5846c26112d5a110aa9ce17f20838297ce7c51.cdnunzehdfq0.us-east-2.rds.amazonaws.com',
port: '3306',
user: 'dbmasteruser',
password: '',
database: ''
});
connection.connect((err) => {
if (err) {
console.error('Failed to connect to MySQL:', err);
return;
}
console.log('Connected to MySQL database');
});
io.on('connection', (socket) => {
console.log('WebSocket connection established');
socket.on('message', (msg) => {
console.log('message: ' + msg);
io.emit('message', msg);
});
socket.on('disconnect', () => {
console.log('WebSocket connection closed');
});
});
// add this route to handle form submission
app.post('/login', (req, res) => {
console.log('Received login request');
console.log('Login request received:', req.body); // Log the received request
const { username, password } = req.body;
User.getByUsername(username, (err, user) => {
if (err) {
console.error('Error getting user:', err);
res.status(500).send('Internal server error');
return;
}
if (!user) {
res.status(401).send('Invalid username or password');
return;
}
user.checkPassword(password, (err, isMatch) => {
if (err) {
console.error('Error checking password:', err);
res.status(500).send('Internal server error');
return;
}
if (!isMatch) {
res.status(401).send('Invalid username or password');
return;
}
res.status(200).send(); // Send a 200 status code to indicate a successful login
});
});
});
// Add this after the routes
app.use((req, res, next) => {
console.log('Response sent');
next();
});
const PORT = process.env.PORT || 6611;
server.listen(PORT, () => {
console.log(`Server running on port ${PORT}`);
});
```
login.html :
```
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>iSpeedrun.TV - Login</title>
<link rel="stylesheet" href="styles.css">
<style>
/* Keep the same styles as index.html */
.main-container {
display: flex;
flex-direction: row;
}
.video-container {
width: 1280px;
height: 720px;
margin-right: 20px;
}
.video-container iframe {
width: 100%;
height: 100%;
}
.sidebar {
width: 300px;
height: 720px;
display: flex;
flex-direction: column;
justify-content: space-between;
}
.sidebar-item {
display: flex;
align-items: center;
padding: 10px;
background-color: #222;
color: #fff;
font-size: 14px;
}
.sidebar-item img {
width: 60px;
height: 60px;
margin-right: 10px;
}
header {
display: flex;
justify-content: space-between;
align-items: center;
background-color: #222;
color: #fff;
padding: 10px;
}
nav ul {
display: flex;
list-style: none;
padding: 0;
margin: 0;
}
nav li {
margin-right: 20px;
}
nav a {
color: #fff;
text-decoration: none;
font-weight: bold;
font-size: 16px;
text-transform: uppercase;
}
nav a:hover {
color: #ff0000;
}
.login-container {
background-color: #fff;
padding: 40px;
border-radius: 10px;
width: 70%;
margin: 20px auto;
box-shadow: 0 0 20px rgba(0, 0, 0, 0.5);
}
.login-container label {
font-size: 20px;
margin-bottom: 20px;
}
.login-container input[type="text"],
.login-container input[type="password"] {
width: 100%;
height: 40px;
margin-bottom: 30px;
padding: 10px;
font-size: 16px;
border-radius: 5px;
border: none;
box-shadow: 1px 1px 5px rgba(0, 0, 0, 0.3);
}
.login-container button[type="submit"] {
display: block;
width: 100%;
height: 50px;
background-color: #e74c3c;
color: #fff;
border: none;
border-radius: 5px;
font-size: 18px;
cursor: pointer;
transition: background-color 0.2s;
}
.login-container button[type="submit"]:hover {
background-color: #c0392b;
}
#message {
font-size: 18px;
color: red;
margin-bottom: 15px;
}
</style>
</head>
<body>
<header>
<h1>iSpeedrun.TV - Login</h1>
<nav>
<ul>
<li><a href="index.html">Home</a></li>
<li><a href="livestream.html">Live Streams</a></li>
<li><a href="about.html">About Us</a></li>
<li><a href="contact.html">Contact</a></li>
<li><a href="login.html">Login</a></li>
</ul>
</nav>
</header>
<main class="main-container">
<div class="sidebar">
<div class="sidebar-item">
<img src="https://via.placeholder.com/60x60.png?text=User+1" alt="User 1">
<p>User 1</p>
</div>
<div class="sidebar-item">
<img src="https://via.placeholder.com/60x60.png?text=User+2" alt="User 2">
<p>User 2</p>
</div>
<div class="sidebar-item">
<img src="https://via.placeholder.com/60x60.png?text=User+3" alt="User 3">
<p>User 3</p>
</div>
<div class="sidebar-item">
<img src="https://via.placeholder.com/60x60.png?text=User+4" alt="User 4">
<p>User 4</p>
</div>
</div>
<div class="video-container">
<form class="login-container" action="/login" method="post" id="login-form">
<label for="username">Username:</label>
<input type="text" id="username" name="username">
<label for="password">Password:</label>
<input type="password" id="password" name="password">
<div id="message"></div>
<button type="submit">Login</button>
</form>
</div>
</main>
<script>
const form = document.getElementById('login-form');
const message = document.getElementById('message');
form.addEventListener('submit', async function(event) {
console.log('Form submitted');
event.preventDefault(); // Prevent the form from submitting normally
const username = document.getElementById('username').value;
const password = document.getElementById('password').value;
try {
console.log('Sending request to server');
const response = await fetch('/login', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ username, password }),
});
console.log('Server responded with status:', response.status);
if (response.status === 200) {
localStorage.setItem('loggedIn', 'true');
window.location.href = 'index.html';
} else {
const error = await response.json();
message.textContent = error.message;
}
} catch (error) {
console.error('Error:', error);
message.textContent = 'An error occurred. Please try again.';
}
});
</script>
</body>
</html>
```
I had bought a reserved instance for RDS db instance db.r5.4xlarge on January-05-2022 for 3 years duration. In the month of December-2022, I had to upgrade that instance to db.r5.8xlarge because we were facing some high database load.
Since then I saw the increase in billing and I need your help to bring down these additional charges.
I'm expecting to save the overall cost by $1500.
I see that the reserved instance db.r5.4xlarge is being 100% utilized, at the same time $320.14 got charged in this month for USD 0.4303 hourly fee per Aurora MySQL, db.r5.4xl instance which is not there anymore in my account. so this $320.14 charges are extra for me.
Then, these charges USD 0.021 per GB-month of backup storage exceeding free allocation for Aurora MySQL costed me $283.38 so far.
Kindly advise how I can reduce the cost?
I followed this link
https://aws.amazon.com/getting-started/hands-on/create-mariadb-db/
to create and host my MariaDB on this platform, but even know I selected the free tier and disabled Multi-AZ deployment by selecting "do not create stand by instance", I was still charged $89, most of which are name:
'$0.342 per RDS db.m6i.large Multi-AZ instance hour'
'$0.2 per IOPS-month of provisioned io1 IOPS for Multi-AZ deployments running MariaDB'
'$0.25 per GB-month of provisioned io1 storage for Multi-AZ deploy'
Furthermore, under the configuration page, I can see that the value for Multi-AZ is no, according to this link
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZ.html
For Multi-AZ DB instance deployment characteristics, I only meet the first two characteristics, the third one, "The value of Multi-AZ is Yes." which does not apply because of the above. And the Multi-AZ DB cluster deployment is unrelated at all to my case.
why was I charged for a service I did not select to begin with?
I have hosted an AWS Managed MS Active Directory and joined to AWS RDS SQL Server to the AD Domain.
Option -1 : Create a SQL login for each user and ask the user to login to the SQL server with new login. And provide appropriate grants for the SQL Server login.
Option -2 : Enable Windows Authentication. Create a Database login for the each user in AD and provide grants individually.
For a large organization this could be laborious
Can you help me with how I can scale this user access method. Also how to give user access to a group and some roles on server or database level ? and also how to give fine grained access (both deny and allow).
IAM Roles and Groups cannot be used here as they are limited at DBA level but not inside the Database or SQL server instance level. Do provide with various possibilities too that are feasible. Please provide the relevant links and resources for the same