Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.
19 votes

MySQL: creating a user that can connect from multiple hosts

Let's start by making a new user called "chaminda" within the MySQL shell: CREATE USER 'chaminda'@'%' IDENTIFIED BY 'password'; The first thing to do is to provide the user with necessary permission ...
user avatar
12 votes

Changing host permissions for MySQL users

Best answer on Stackoverflow suggesting to use RENAME USER which copy the user privileges. Using Data Control Language (statements as GRANT, REVOKE, RENAME and so on) does not require FLUSH ...
user avatar
12 votes

Modifying columns of very large mysql tables with little or no downtime

I just had to do this recently. What Amazon recommended was using the Percona Toolkit. I downloaded it and was able to run something like: ./pt-online-schema-change h=databasenameHostName,D=...
user avatar
  • 351
11 votes
Accepted

Importance of location of installation of Microsoft SQL Server

This is kind of opinion-ish, but I would put the SQL Server binaries on the slow disk. It's quite common to put the binaries on the OS disk (although some people hate that), or on a slower disk. ...
user avatar
8 votes
Accepted

Understanding IXSCAN and COLLSCAN in MongoDB logs

I'm attempting to grep through some Mongo logs in an attempt to find slow operations that I need to optimize. Slow query logging is at the default and is logging operations over 100ms. Rather than ...
user avatar
  • 1,250
6 votes

Importance of location of installation of Microsoft SQL Server

I'd like to follow up on the pretty good answer Katherine Villyard already put up. It somewhat depends on the intended usage of your database. If you're expecting a lot of write operations, go ahead ...
user avatar
  • 5,517
5 votes

MongoDB Auth, can be authenticated in the shell but not via the command line

Maybe I'm wrong but the correct form is like this: mongo --port 27017 -u manager -p 12345678 --authenticationDatabase admin You can read about this in mongo documentation: http://docs.mongodb.org/...
user avatar
4 votes
Accepted

MSSQL: Roll back DB from backed up .MDF/.LDF

I would attach the files to a new database, script the table and re-create it in your active database. First up, move the *.mdf and *.ldf files to the default database file locations. This way, they'...
user avatar
4 votes
Accepted

Microsoft SQL Server Standard High Availability without Shared Storage

You can use AlwaysOn. AlwaysOn is the 'new' way to do this, and the reason DB mirroring is becoming obsolete in SQL Server. It basically works like this: You need to create a Windows Cluster for ...
user avatar
  • 17.7k
4 votes

Upgrading PostgreSQL on EC2 with minimum impact to existing configuration

I found a way to do this, but I'm not sure if it's the proper/recommended way. My steps are below: 1. Save the existing configuration Run sudo vim /var/lib/pgsql9/data/postgresql.conf and sudo vim /...
user avatar
4 votes

What does "GC (Allocation Failure)" mean in my ElasticSearch 5.6 logs?

GC (Allocation Failure) is a JVM message (not an Elasticsearch-specific one) that can be a sign that there's memory pressure, but it's not catastropic to the JVM (that would cause a OutOfMemoryError ...
user avatar
  • 32.2k
3 votes

Moving MYSQL database Cache issue?

To reliably copy database tables, the database has to be stopped (not just flushed). If it is not, a consistent copy is not guaranteed, but is it impossible to say if and what data might be lost. ...
user avatar
  • 96.4k
3 votes
Accepted

Copy SQL Server Databases

In my experience, detach/attach is the fastest method. The bottleneck would probably be how quickly you could copy the files across the network. Assuming the two databases have identical Windows ...
user avatar
3 votes

Importance of location of installation of Microsoft SQL Server

You have things backwards. I know it's counter-intuitive, but you want the backups (especially including transaction log backups) on the fast disk, and the mdf/ldf files (with the notable exception of ...
user avatar
  • 12.9k
3 votes

What carries out SQL Server Mirroring?

SQL Server Mirroring is part of the core engine. When you setup mirroring, SQL Server enables a TCP endpoint to support the flow of log records from the primary server to the secondary server, and to ...
user avatar
3 votes
Accepted

"Access Denied" when trying to access AWS Lightsail database

I had the same problem when setting up the connection in MySQL Workbench. The cause was the following: When you double click to copy the password from the AWS page, it appends an enter at the end. ...
user avatar
2 votes

After setting root password why does MYSQL still allow me to login without a password?

Don't forget to FLUSH PRIVILEGES or you can still login without password.
user avatar
  • 121
2 votes

MySQL: creating a user that can connect from multiple hosts

Warner's answer can be refined by using CIDR ranges in your user specification (in 'network'/'netmask' notation), eg: CREATE USER 'bob'@'10.0.0.0/255.0.0.0' IDENTIFIED BY 'password123'; CREATE USER ...
user avatar
  • 21
2 votes

Changing host permissions for MySQL users

I stumbled across this one, too, and the proposed solution didn't work, since the database specific privileges wouldn't be moved as well. what I did: UPDATE mysql.user SET Host='%' WHERE Host='...
user avatar
2 votes
Accepted

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using Password:YES)

-H is an alias for --html, which will Produce HTML output. You're looking for -h or --host=.
user avatar
  • 1,076
2 votes
Accepted

Postgresql: missing data after crash

My first guess would be that you've upgraded to a new PostgreSQL or installed a patched one by hand, and your new version isn't set to autostart. So the old version started up with the old data in it. ...
user avatar
  • 10.3k
2 votes

Changing host permissions for MySQL users

The best option on MySQL 8 would be: RENAME USER 'username'@'oldhost' TO 'username'@'newhost'; See https://dev.mysql.com/doc/refman/8.0/en/rename-user.html
user avatar
2 votes
Accepted

What maintenance tasks should I run on my MS-SQL 2008/2012 servers?

You don't mention what you're using for maintenance, however... It's usually not necessary to reorganize indexes on all databases. Some of them will need it and some won't. You could probably ...
user avatar
2 votes
Accepted

MongoDB Auth, can be authenticated in the shell but not via the command line

The issue was resolved by removing any symbols. It appears that despite quotes [single or double] would cause the incoming password to be invalid.
user avatar
  • 357
2 votes

How can I drop a MySQL user whose username contains unprintable characters?

There is no side effects if you edit the mysql.user table directly, except one: you have to issue a FLUSH PRIVILEGES; command after you done (to re-read the privilege tables and put your changes ...
user avatar
  • 369
2 votes
Accepted

I set up a web application server, and a separate server for postgres on the same network. How should I secure the connection?

Both are on the same internal network at rackspace, so the connection is fast. I don't have any experience with Rackspace, but I would confirm if the "internal network" is one set up particularly for ...
user avatar
2 votes

Importing Data Dump Using pgAdmin

This function creates a data dump, not imports it, which is the opposite of what you want to do. The TEXT you are referring to is the path to the CSV file it will generate. PgAdmin 1.4 seems to have ...
user avatar
2 votes

How to implement table security without SUPER privilege?

As originally answered, the SUPER privilege simply allows a highly privileged user to set the definer in stored routines to a user other than themselves. You can log in as the definer in order to ...
user avatar
2 votes

How to move PostgreSQL data directory?

Depend on how your server was configured, you can simply change your PG_DATA variable. But you can easily do: service postgresql stop mv /var/lib/pgsql/ /data/pgsql ln -s /data/pgsql /var/lib/pgsql ...
user avatar
  • 178

Only top scored, non community-wiki answers of a minimum length are eligible