24
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 ...
- 577
22
votes
Changing host permissions for MySQL users
The best option on MySQL 8+ / MariaDB 10+ would be:
RENAME USER 'username'@'oldhost' TO 'username'@'newhost';
See https://dev.mysql.com/doc/refman/8.0/en/rename-user.html
- 459
15
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 ...
- 291
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=...
- 381
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.
...
- 18.6k
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 ...
- 1,270
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 ...
- 5,597
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 /...
- 171
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 ...
- 32.7k
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 ...
- 185
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 ...
- 12.9k
3
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='...
3
votes
SQL SSMS on development - clear ldf files
You can truncate the log file by issuing the following code block from SSMS:
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY ...
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. ...
- 46
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.
- 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 ...
- 21
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 ...
- 431
2
votes
How can I drop a MySQL user whose username contains unprintable characters?
How about this?
DELETE FROM mysql.user where user like "wiki4te%" and host like "rch%";
- 321
2
votes
Accepted
cassandra nodetool drain running for days
Problem is related with a bug in versions prior to 2.1 (https://issues.apache.org/jira/browse/CASSANDRA-5911). Commit logs are not removed after a flush.
- 149
2
votes
Accepted
Opening ports on a remote DB server
Network-level issues are not the problem here. You solved those with your iptables rule. Your MySQL grant is a problem, though.
MySQL grants have three parts you need to consider:
username
password
...
- 109k
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
...
- 178
2
votes
Giving permission 777 to folders/files Cons?
Who are the others, users within the server?
Others are non system users not accounted for by owner and/or group. Public users e.g. people browsing, access the files on the system via a process e.g. ...
- 116k
2
votes
Accepted
Cannot find my database when trying to import with mysql
Most probably you have a reference to a database 'rb' in the sql file rurdevdrpl.sql.
It could either be that rb has been defined as the default database with
USE rb;
or as an option to a sql ...
- 1,155
2
votes
Accepted
Why don't I need a certificate to connect to our AWS database?
This can be configured per-user in MySQL.
ALTER USER '{masteruser}'@'%' REQUIRE SSL;
See the ALTER USER documentation and look for REQUIRE SSL.
- 24.9k
2
votes
Is there any major performance issue if you switch default-storage-engine setting from InnoDB to MyISAM?
Changing the default storage engine affects only new tables that are created with CREATE TABLE statement, without specifying database engine.
So, unless you plan to create new tables, there is no ...
- 36.9k
2
votes
Is there any major performance issue if you switch default-storage-engine setting from InnoDB to MyISAM?
If multiple queries could run at the same time (e.g. multi user environment), then you should plan moving to InnoDB for all tables. I would also run MySQL on a Linux machine.
To change the engine, you ...
- 17.9k
2
votes
Accepted
Postgres Role required to grant CONNECT on a specific database
If you were given CONNECT WITH GRANT OPTION you can then grant connect to others.
If you were not given connect with grant option, you cannot grant it, you will need a superuser to grant you CONNECT ...
- 826
1
vote
connections to postgresql at particular time frame
I am not sure if I understand your problem correctly but generally speaking PostgreSQL so far ( pg 9.6 2016/12) does not show any history of these statistics. Therefore I use monitoring with ...
- 131
1
vote
Accepted
Staging & Production Sharing the Same Database server?
So the last thing to do would be to setup the staging database server
but do I need another droplet for it?
Well, that's really up to you. Best practices say that yes, indeed you should create a ...
- 109k
Only top scored, non community-wiki answers of a minimum length are eligible