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; 

to kill a whole bunch of mysql queries in one go, I used something like this that you can adapt for your own use:

select concat('KILL ',id,';') from information_schema.processlist where Host like 'some-sexy-server-ws%';

select concat('KILL ',id,';') from information_schema.processlist where Host like 'some-sexy-server-ws%' into outfile '/tmp/a.txt';

source /tmp/a.txt

How to set a mysql processlist to display by Time of query to help locate long running queries:

mysql> \P awk -F\| '$2 ~ /[0-9]+/ && $6 ~ /Sleep/' | sort -t \| -k 7 -n -r
PAGER set to 'awk -F\| '$2 ~ /[0-9]+/ && $6 ~ /Sleep/' | sort -t \| -k 7 -n -r'
mysql> show processlist;

Installing mySQL from a binary is very simple. Download the binary to your server.

tar zxvf mysql-binary.tar.gz

cd mysql-binary

mkdir /mysql/partition/here

./configure --prefix==/mysql/partition/here

make

make install

Alternatively, even easier:

tar zxvf mysql-binary.tar.gz

cd /mysql/partition/here

chown -R mysql .

chgrp -R mysql .

scripts/mysql_install_db --user=mysql;

cd .;

./bin/mysqladmin -uroot -pSoMePasSwOrdHeRe

SELECT table_schema “db_name_here”, sum(data_length + index_length)/1024/1024/1024 “size in GB” FROM information_schema.TABLES GROUP BY table_schema;

Installing mySQL from the command line couldn’t be easier. Installing from root would ensure that everything will be in place.


yum groupinstall "MySQL Database"
/usr/bin/mysql_install_db --user=mysql
service mysqld start
/usr/bin/mysql_secure_installation
chkconfig mysqld on