60

If you've got access to the mysql database, you can change the grant tables directly: UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username'; FLUSH PRIVILEGES; ...and an analogous UPDATE-statement to change it back. Also you might need to make changes to the mysql.db table as well: UPDATE mysql.db SET Host='%' WHERE Host='localhost' ...


47

There are two methods for extracting SQL Grants from a MySQL Instance METHOD #1 You can use pt-show-grants from Percona Toolkit MYSQL_CONN="-uroot -ppassword" pt-show-grants ${MYSQL_CONN} > MySQLUserGrants.sql METHOD #2 You can emulate pt-show-grants with the following MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A -e"...


26

Change the recovery mode of the database named "model". From this MSDN doc: A new database inherits its recovery model from the model database. The default recovery model of the model database depends on the edition of SQL Server. But this can be changed by anyone that has ALTER permission on the database.


12

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=databasename,t=tablename --recursion-method=none --execute --user username --password password --alter "MODIFY someColumn newDataType" and it works great. It tells you ...


12

First, please read our canonical question on Capacity Planning. The specific advice you're asking for is capacity planning advice, and you're going to have to work that out on your own, for your particular environment. Second, You are looking at this wrong. The amount of memory (or any other resource) you have doesn't dictate the number of connections you ...


12

alter authorization on schema::[db_datareader] to [dbo] alter authorization on schema::[db_datareader] to [db_datareader] alter authorization on schema::[db_datawriter] to [dbo] alter authorization on schema::[db_datawriter] to [db_datawriter]


11

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. You definitely want to remember to put your system databases, especially tempdb, on the faster disk, however. In fact, it's also common to put tempdb by itself. ...


8

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 and here I have given all permission to the particular user. GRANT ALL PRIVILEGES ON * . * TO 'chaminda'@'%'; Reload all the privileges. FLUSH PRIVILEGES; ...


8

Yes, it will run. How fast however depends on your working data set. 3GB RAM (and about 1GB used for the OS) is not much to run SQL on.


8

Short version: Replace auto with manual in /etc/postgresql/9.1/main/start.conf Long version: Each PostgreSQL cluster in Debian/Ubuntu has a start.conf file that controls what /etc/init.d/postgresql should do. This is documented with pg_createcluster: STARTUP CONTROL The start.conf file in the cluster configuration directory controls the start/...


8

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 grepping through MongoDB logs, I would highly recommend using the scripts from the open source mtools project. NOTE: I'm not the original mtools author, but I am ...


7

Or, utilize percona-toolkit (former maatkit) and use pt-show-grants (or mk-show-grants) for that purpose. No need for cumbersome scripts and/or stored procedures.


7

Well, I figured it out - it was a combination of inexperience with PostgreSQL and very non-descriptive errors from phpPgAdmin. Turns out I did not have TCP/IP access enabled, which meant that no PHP applications could access the database(s). To fix this, I had to make two changes: It seemed to work better if I set listen_addresses to '*' in postgresql.conf....


7

Change the recovery model of the model system database.


6

For scheduled and ad hoc database synchronization you can use ApexSQL Diff (for object schema synchronization) and ApexSQL Data Diff (for record synchronization) They cannot be used for real time synchronization. IMO, it's best to use database mirroring for real-time


6

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 PRIVILEGES; and is required in architecture like Galera or Group Replication where MyISAM tables are not replicated.


6

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 and put your .mdf and .ndf files on the faster disk. If however your database is either one that is generally quite static (serving web content for example). ...


5

By default SQL Server 2008 keeps the Built In Administrators group out of SQL Server access wise whereas in 2005 and below the Built In Administrators group was automatically granted SQL Server sysadmin rights. As a local administrator you have a couple options though: Restart SQL Server in single user mode (as described in this post: http://...


5

My question is what are the best practices to monitor and / or test that the slave databases are up-to-date, For a simple testing, insert/update data on the master and make sure that it is replicated to the slaves. But for the consistency check, pt-table-checksum is what you're looking for. For e.g: pt-table-checksum localhost --empty-replicate-table --...


5

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/manual/tutorial/add-user-to-database/


4

I realize this is a little bit old, but I found a workaround today that worked for me and thought it might be helpful to others. If your NT Authority/System account has admin access, you can use the following trick to log in as the NT Authority/System account and create a new login/reset credentials on other logins. Steps: Download PsExec: http://technet....


4

You need to simply remove the anonymous user. In the future, running /usr/bin/mysql_secure_installation (Location in RPM Installation) will prompt you to remove these anonymous users. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended ...


4

In previous versions you would set that on the model database, which is the "model" for new databases. I'm assuming it's the same in SQL Server 2012.


4

The big problem with replication is checking that the nodes are all up, all nodes are communicating (not split brain) and processing replication logs and the replication lag 1, 3 and 4 can be captured usingf SHOW MASTER STATUS / SHOW SLAVE STATUS on the relevant nodes, although the replication delay only has 1 second accuracy and only across each hop. ...


4

You have problems, Microsoft provides: Rebuild System Databases.


4

mysqlcheck runs a number of actions: check, repair, analyze and optimize. You're currently jumping to "repair" (-r) but should really start with "check" just to see what's going on and to see if there's any response: mysqlcheck --check --quick user_motiva wp_options Add "-p" if a password is needed (eg, not in a config file). If that passes, try it ...


4

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'll inherit the appropriate file level permissions. Now, create a new database from the existing files, but using a new name: USE master GO CREATE DATABASE ...


4

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 the Networking-aspect. Shared storage is not needed, but you will need a file-share for the instances to sync the logs (only DB's in FULL recovery can be used) ...


4

This is your accessing from your machine as bob. ERROR 1045 (28000): Access denied for user 'bob'@'192.168.40.1' (using password: YES) You created a local user named bob but then you also need to create a bob user that can be user remotely see as follows and try to log in. CREATE USER 'bob'@'192.168.40.1' IDENTIFIED BY '12345'; GRANT ALL PRIVILEGES ON *.* ...


3

I cringe at this idea of database maintenance involving clearing the buffer to increase speed. If your system is crawling and this is the answer, I'm skeptical that things were done properly. The proper way to approach this is to find out what your bottle neck is. It's usually either disk (often an extension of insufficient memory allocation) or CPU. What'...


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