Ran into dependency problems today trying to upgrade a Percona XtraDB server cluster. Yum shell to the rescue:
|
1 2 3 4 5 6 |
[root@dbcluster02 ~]# yum -q -q shell > remove percona-xtrabackup-20 > install percona-xtrabackup-2.2.3 > update Percona-XtraDB-Cluster-client > update Percona-XtraDB-Cluster-server > transaction run |
This way I can run the remove and installs as part of a single transaction.
Here’s a one-liner that will check all tables in a database:
http://codeinthehole.com/writing/checking-all-mysql-tables/
|
1 2 3 |
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:
|
1 |
mysqlcheck --debug-info --auto-repair -c -o --all-databases |
Encountered this error when dropping a table on a database that had recently been migrated from 5.0 to 5.5:
|
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:
|
1 |
mysql_upgrade --force -uroot -p |
I didn’t figure this out … instead Daniel Tillett gets the credit.
Interesting problem where we got access denied errors when trying to grant privileges on MySQL server:
|
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:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
[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 |
Next restart mysql server, login and try showing grants again:
|
1 2 3 4 |
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.
The command to convert a single table from MyISAM to InnoDB looks like:
|
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:
|
1 2 3 4 5 6 7 |
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.
