Archive | MySQL

Mysqldump TRIGGERS Only

By admin on September 17, 2015 in MySQL

Here’s the command to dump the triggers and stored procedures for a database:

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt [dbname]

1	mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt [dbname]

Revoke MySQL DROP Privilege On Plesk Database User

By admin on November 8, 2014 in MySQL, Plesk

Had a request from client to add database user on a Plesk server but to remove the DROP DATABASE privilege so that they could not accidentally delete their database. By default, database users created through Plesk do have the DROP DATABASE privilege. Plesk does not currently allow fine-grained control over database privileges so the only way to implement this change is to modify the privileges directly from a MySQL command line. After some testing we found these command will have the desired effect:

> REVOKE DROP ON testdb.* FROM 'testdbuser'@'%';
> FLUSH PRIVILEGES;

1 2	> REVOKE DROP ON testdb.* FROM 'testdbuser'@'%'; > FLUSH PRIVILEGES;

Of course replace “testdb” and “testdbuser” with your actual database and username.

Scripting CHECK / REPAIR TABLES

By admin on June 25, 2014 in MySQL

Here’s a one-liner that will check all tables in a database:

http://codeinthehole.com/writing/checking-all-mysql-tables/

mysql -p<password> -D<database> -B -e "SHOW TABLES" \
| awk '{print "CHECK TABLE "$1";"}' \
| mysql -p<password> -D<database>

mysql -p<password> -D<database> -B -e "SHOW TABLES" \

| awk '{print "CHECK TABLE "$1";"}' \

| mysql -p<password> -D<database>

And here’s an article that shows the use of mysqlcheck:

http://www.thegeekstuff.com/2011/12/mysqlcheck/

mysqlcheck, unlike myisamchk, can be run when the mysql daemon is running

This command will check, repair and optimize all tables in all databases:

mysqlcheck --debug-info --auto-repair -c -o --all-databases

1	mysqlcheck --debug-info --auto-repair -c -o --all-databases

MySQL Error: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted;

By admin on April 25, 2014 in MySQL

Encountered this error when dropping a table on a database that had recently been migrated from 5.0 to 5.5:

Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted;

1	Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted;

The migration used a “mysqldump –all-databases” so it included the “mysql” database. Apparently the “mysql.proc” table changed between the versions. The fix on the new server is to run:

mysql_upgrade --force -uroot -p

1	mysql_upgrade --force -uroot -p

I didn’t figure this out … instead Daniel Tillett gets the credit.

MySQL – Access Denied On Grants

By leerb on December 6, 2013 in MySQL

Interesting problem where we got access denied errors when trying to grant privileges on MySQL server:

mysql> grant all privileges on *.* to 'root'@'1.1.1.1' identified by 'asdasdfasdf' with grant option;
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

1 2	mysql> grant all privileges on . to 'root'@'1.1.1.1' identified by 'asdasdfasdf' with grant option; ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

But we’re logged in as root so it should work. Turns out the problem is the mysql database needs to be upgraded. Try this:

mysql> SHOW GRANTS FOR 'root'@'localhost';
| Grants for root@localhost |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*7C46059968B15C1068BCF97F1D1D8F6519E3533E' WITH GRANT OPTION |

mysql> SHOW GRANTS FOR 'root'@'localhost';

| Grants for root@localhost |

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*7C46059968B15C1068BCF97F1D1D8F6519E3533E' WITH GRANT OPTION |

Notice that it lists the individual privileges instead of saying all privileges. Now upgrade the mysql database by running mysql_upgrade:

[root@db01 ~]# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.servers OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Running 'mysql_fix_privilege_tables'...
OK

[root@db01 ~]# mysql_upgrade

Looking for 'mysql' as: mysql

Looking for 'mysqlcheck' as: mysqlcheck

Running 'mysqlcheck with default connection arguments

mysql.columns_priv OK

mysql.db OK

mysql.event OK

mysql.func OK

mysql.general_log OK

mysql.help_category OK

mysql.help_keyword OK

mysql.help_relation OK

mysql.help_topic OK

mysql.host OK

mysql.ndb_binlog_index OK

mysql.plugin OK

mysql.proc OK

mysql.procs_priv OK

mysql.servers OK

mysql.slow_log OK

mysql.tables_priv OK

mysql.time_zone OK

mysql.time_zone_leap_second OK

mysql.time_zone_name OK

mysql.time_zone_transition OK

mysql.time_zone_transition_type OK

mysql.user OK

Running 'mysql_fix_privilege_tables'...

Next restart mysql server, login and try showing grants again:

SHOW GRANTS FOR 'root'@'localhost';
| Grants for root@localhost |
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'asdfas' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

SHOW GRANTS FOR 'root'@'localhost';

| Grants for root@localhost |

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'asdfas' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

Notice now that it shows “ALL PRIVILEGES” and your grant statements will work.

← Previous 1 2 3 … 7 Next →

Top Nav

Navigation