Koustuv Chatterjee
  • Member for 6 years, 11 months
  • Last seen this week
  • India
Backup/Restore Users/Passwords/Privileges
Accepted answer
20 votes

The 'mysql' database contains users/privileges/passwords. So take the dump of mysql database along with other databases mysqldump [options] --all-databases > all_databases_dump.sql mysqldump -u ...

View answer
How to solve "Can't connect to local MySQL server through socket" error?
14 votes

Check the folowing First check if another instance is runnig in the same machine ps ax | grep mysql Then, check if your file my.cnf (probably in /etc/mysql/ folder) is correctly configured with ...

View answer
grant select on all databases except one mysql
5 votes

Since you have 200 databases and you do not want to grant one by one. Fastest way to do that would be GRANT SELECT ON *.* TO 'test_user'@'localhost'; FLUSH PRIVILEGES; And then just revoke the ...

View answer
MySQL replication - ibdata1 huge
2 votes

If you want to shrink ibdata1, so that it should only contain the metadata, You may try these steps To shrink ibdata1 once and for all you must do the following: MySQLDump all databases into a SQL ...

View answer
Use Create Trigger to create New table using calculated values from common groupid column values
1 votes

We can use MySQL CREATE TABLE AS statement to create a TABLE_B from existing TABLE_A by copying the existing table's columns CREATE TABLE TABLE_B AS SELECT id, name, SUM(cost) AS 'cost', ...

View answer
What MySQL activity eats CPU?
1 votes

There are so many 3rd party tools, most popular are MySQL Enterprise Monitor MONyog for DBAs Percona Toolkit Both MONyog and MySQL Enterprise Monitor shows CPU usage information in Linux systems ...

View answer
User, host, and privileges
Accepted answer
1 votes

USAGE = no privilege https://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html#priv_usage Now you have to options, either delete the user or grant all privileges to the user, which ever make ...

View answer
Can I tell MySQL what to cache?
1 votes

You cannot. MySQL don't have that mechanism. The common featues which are misunderstood about query cache Only works for SELECT queries Queries should be absolutely identical It is not possible to ...

View answer
An easy way to select one db dump from multiple db MySQL dump
Accepted answer
1 votes

Take the difference between two 'CREATE DATABASE' using sed operation eg sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' alldatabases.sql > output.sql https://stackoverflow....

View answer
Possibly a silly question: Can I install MySQL on an External HDD connected to my Router?
Accepted answer
1 votes

It is not a silly question as these days when databases are growing beyond gigs this is becoming a common practice. You can do that by moving all your files in current data directory to the new ...

View answer
Remote MySQL connection fails (10060) - "Can't connect to MySQL server on domainname.com(10060)"
1 votes

Most probably this is a networking side issue and the database service provider needs to create a new rule in firewall (iptables). Still we just need to confirm few things from the provider. As the ...

View answer
MYSQL tuning to optimize CPU usage
0 votes

You may not have to change most in my.cnf as it is a part of the deal. Real devil s in the queries. More CPU usage means costly queries and more processing First check using top command if this is ...

View answer
Replication Issue - Changing the replication-do-db filter
0 votes

This is a bug when your my.cnf can be read twice (may be due to the existence of more than one my.cnf by symlinks) Mostly this happens in RPM installations where all path are default paths. Solution: ...

View answer
How to merge two large mysql tables into one with similar data keeping the latest record
0 votes

Use toad for MySQL 7.3 Freeware, there you will have option of sync Put the 2 tables in 2 different databases, Make a data compare and then sync Hope this helps !!

View answer
Fixing MyISAM database without losing data
0 votes

Have you tried CHECK TABLE and REPAIR TABLE REPAIR TABLE is the equivalent of myisamchk --recover (-r). If you want to rebuild the MYI file then the correct use of REPAIR TABLE is: REPAIR TABLE ...

View answer
how to cache a subset for cascading select queries in mysql
0 votes

If filter 110M is exactly a subset of filter 126M, then appending more AND with WHERE would have done the job. $sql1 = "SELECT ..... WHERE ..."; $sql2 = $sql1 . "AND column-name = ...."; $sql3 = $...

View answer
Query results that not are in previous query result
0 votes

You are missing the single quote closure after motherboard in both of your queries AND value != 'Motherboard AND AND a.value != 'Motherboard The first query can be more organized as SELECT ...

View answer
How to detect inactive tables?
-1 votes

SELECT update_time FROM information_schema.tables WHERE table_name='yourtablename' Or if you want a list of all tables, with last accessed time in order SELECT table_schema , table_name, ...

View answer
MySQL: List databases with tables
-1 votes

If you want to exclude MySQL internal databases, SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ( 'information_schema', 'performance_schema', 'mysql' ); No ...

View answer