Category: Databases

MySQL – Access Denied On Grants

December 6, 2013

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.

Convert MyISAM Tables to InnoDB

December 5, 2013

The command to convert a single table from MyISAM to InnoDB looks like:

ALTER TABLE tablename ENGINE = INNODB;

1	ALTER TABLE tablename ENGINE = INNODB;

This is described suscintly by Major.IO: http://major.io/2007/10/03/convert-myisam-tables-to-innodb/ Kevin van Zonneveld goes further in his blog: http://kvz.io/blog/2010/04/27/convert-all-tables-to-innodb-in-one-go/ and provides some scripts to convert all tables instead of doing one at a time. I like the following:

echo "SELECT CONCAT('SELECT \"',table_schema,'.',table_name,'\";ALTER TABLE ',table_schema,'.',table_name,' 
ENGINE=InnoDB;')
FROM information_schema.tables
WHERE 1=1
AND engine = 'MyISAM'
AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');" | \
mysql | grep -v CONCAT | mysql

echo "SELECT CONCAT('SELECT \"',table_schema,'.',table_name,'\";ALTER TABLE ',table_schema,'.',table_name,'

ENGINE=InnoDB;')

FROM information_schema.tables

WHERE 1=1

AND engine = 'MyISAM'

AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');" | \

mysql | grep -v CONCAT | mysql

This variation shows the table name before starting the conversion so I can see progress. The query output is pipped right back in to mysql to execute.

Percona XtraDB Cluster On CentOS

November 14, 2013

This article is built off of a similar article we published last month: Percona XtraDB Cluster on Ubuntu The primary difference is this time we’re going to use CentOS instead of Ubuntu. In this article we’re going to build a Percona XtraDB Cluster using a pair of RackSpace Cloud Servers. Percona XtraDB Cluster is a MySQL compatible replacement supporting multi-master replication. For this project we’ll use the latest CentOS release and we’re going to use RackSpace Cloud Networks to setup an isolated segment for the replication between the cluster nodes. We’ll do two nodes in the cluster but you can add additional nodes as desired. Finally we’ll use a RackSpace Cloud Load Balancer to distribute traffic between the nodes. To get started, create the cloud servers from the RackSpace control panel using the following guidelines: Make sure you choose a Next Gen region for the servers. For the Image choose

Percona XtraDB Cluster on Ubuntu

September 2, 2013

In this article we’re going to build a Percona XtraDB Cluster using a pair of RackSpace Cloud Servers. Percona XtraDB Cluster is a MySQL compatible replacement supporting multi-master replication. For this project we’ll use the latest Ubuntu release and we’re going to use RackSpace Cloud Networks to setup an isolated segment for the replication between the cluster nodes. We’ll do two nodes in the cluster but you can add additional nodes as desired. Finally we’ll use a RackSpace Cloud Load Balancer to distribute traffic between the nodes. To get started, create the cloud servers from the RackSpace control panel using the following guidelines: Make sure you choose a Next Gen region for the servers. For the Image choose “Ubuntu 13.04 (Raring Ringtail)” Select a size based on your requirements. We’re using 1GB for this article. In the Networks section, when creating the first server, click the “Create Network” button and

MySQL Monitoring With myq_gadgets

September 1, 2013

Great set of utilities for monitoring MySQL performance: https://github.com/jayjanssen/myq_gadgets/ Here are the steps to install:


cd /root
wget https://github.com/jayjanssen/myq_gadgets/archive/master.zip
unzip master.zip

cd /root

wget https://github.com/jayjanssen/myq_gadgets/archive/master.zip

unzip master.zip

The tools will be in the myq_gadgets-master folder. There’s no installer so you can just start using the tools immediately. For instructions see the enclosed README.txt. Also this presentation demonstrates many of the features: http://www.percona.com/live/mysql-conference-2013/sites/default/files/slides/myq_gadgets%20talk.pdf