As I was importing a MySQL database, I got the following error.

ERROR 1153 (08S01) at line 16174: Got a packet bigger than ‘max_allowed_packet’ bytes

This can be quickly resolved when importing form the command line. During the import, I included “–max_allowed_packet”.

mysql --max_allowed_packet=100M -u root -p database < dump.sql

To resolve this error “Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist”, you just need to tell MySQL where to look for the database files. This can be changed with this command:

 mysql_install_db –user=mysql –ldata=/meltwater/mysql

Use the below command to display the size of each MySQL table in GB. This will also show the size of the index length, the number of rows and the total data length.

SELECT CONCAT(table_schema, ‘.’, table_name),
CONCAT(ROUND(table_rows / 1000000, 2), ‘M’) rows,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), ‘G’) DATA,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), ‘G’) idx,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), ‘G’) total_size,
ROUND(index_length / data_length, 2) idxfrac
information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;

When you have forgotten you MySQL root password, with root access it is easy enough to get back into the database and reset the password.

Firstly, bring down your MySQL database. This can be as simple as running:

/etc/init.d/mysqld stop

Then once you have confirmed your MySQL instance is no longer running:

ps -ef | grep -i mysql

You are free to restart the MySQL instance again without using the user tables:

mysqld_safe –skip-grant-tables &

Confirm that the MySQL instance is running again, and it is likely that you will have received a warning had it have not started properly anyway:

ps -ef | grep -i mysql

Then you are free to login to MySQL again:

mysql -uroot

Then once you are successfully back in the database, you can choose whether or not you want to reset the password again:

UPDATE mysql.user SET Password=PASSWORD(‘some_password_here’) WHERE User=’root’;
FLUSH PRIVILEGES;