Category Archives: MySQL

ERROR 1932 (42S02): Table doesn’t exist in engine

The last day I copied all /var/lib/mysql directory to another server to move the database files to a more powerful server. However when I started mysql in the new server and I typed “select * from users” to see if the table was loaded correctly, I received this error: ERROR 1932 (42S02): Table doesn’t exist […]

ERROR 1018 (HY000): Can’t read dir of ‘/dbname/’ (errno: 13)

If you get this error while you try to dump or show tables: ERROR 1018 (HY000): Can’t read dir of ‘/dbname/’ (errno: 13 – Permission denied)ERROR 1018 (HY000): Can’t read dir of ‘/dbname/’ (errno: 13 – Permission denied) Try to change the owner\group to the mysql directory: chown -R mysql:mysql /var/lib/mysql/chown -R mysql:mysql /var/lib/mysql/ Change […]

[MySQL] TIMESTAMP with implicit DEFAULT value is deprecated

If mysql service can’t start due to this warning: [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details). You just need to edit your my.cnf file and add: explicit_defaults_for_timestamp […]

Fastest Way to Import Large SQL Dump File

Do you have a huge SQL dump file and you need to import it in a new MySQL database? The first thing I generally do when I need to import a large SQL file is: Login to your mysql server: mysql -u root -pmysql -u root -p Increase net buffer length and allowed packets: set […]

Grant remote root access to MySQL

Connect via SSH to the MySQL server, login as root to MySQL and type: GRANT ALL ON *.* to root@’%’ IDENTIFIED BY ‘your-root-password’;GRANT ALL ON *.* to root@’%’ IDENTIFIED BY ‘your-root-password’; The above command allows any IP address to connect as root to the MySQL server. To grant root access to a specific IP address […]

Access remote MySQL server with local phpMyAdmin

Edit the phpMyAdmin config.inc.php file as this: $cfg[‘Servers’][$i][‘host’] = ‘DB.SERVER.IP.ADDRESS’; $cfg[‘Servers’][$i][‘port’] = ‘3306’; $cfg[‘Servers’][$i][‘socket’] = ”; $cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’; $cfg[‘Servers’][$i][‘extension’] = ‘mysql’; $cfg[‘Servers’][$i][‘compress’] = FALSE; $cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;$cfg[‘Servers’][$i][‘host’] = ‘DB.SERVER.IP.ADDRESS’; $cfg[‘Servers’][$i][‘port’] = ‘3306’; $cfg[‘Servers’][$i][‘socket’] = ”; $cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’; $cfg[‘Servers’][$i][‘extension’] = ‘mysql’; $cfg[‘Servers’][$i][‘compress’] = FALSE; $cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’; Make sure phpMyAdmin’s IP address is allowed […]

How to use MySQL LOAD DATA LOCAL INFILE

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. It can be used to import a large bulk of data into MySQL and it is really very fast and uses less CPU compared with the default INSERT statements, you can read more information on the […]

Import large bulk of data into MySQL InnoDB

You can import large bulk of data into InnoDB very quickly. These settings can also be used to insert millions of rows with INSERT statement. Edit the file /etc/mysql/my.cnf with these values: innodb_doublewrite = 0 innodb_buffer_pool_size = *set to 50% of system memory* innodb_log_file_size = 512M log-bin = 0 sync_binlog = 0 innodb_support_xa = 0 […]

Percona Server 5.6 high memory usage problem

Yesterday I installed Percona Server 5.6 on a fresh Debian Wheezy VM and I noticed that the memory usage was very high, approximately from 1.2GB to 1.8GB with a small database of 50K rows. By default MySQL 5.6 has the parameter performance_schema enabled, so I disabled it by adding this line in /etc/mysql/my.cnf file: performance_schema […]

Mysql headers and client library minor version mismatch

Example warning message that can be found in the web server error.log file: Warning: mysqli::mysqli(): Headers and client library minor version mismatch Warning: mysqli_connect(): Headers and client library minor version mismatchWarning: mysqli::mysqli(): Headers and client library minor version mismatch Warning: mysqli_connect(): Headers and client library minor version mismatch To fix these type of warning messages, […]