MySQL root can’t login to phpMyAdmin

On Debian/Ubuntu MySQL root user can only login using socket. This is done with Plugin.

To fix this, first set a password for user root. Change MySQL root password To disable plugin, run following commands in MySQL command prompt as user root.

MySQL phpMyAdmin […]

Read More…

MySQL Optimize

Optimize MariaDB Edit file

Find

On this section, you will see “#skip-external-locking”, add below.

This is some basic settings. Now run MySQL tuner, make suggestions as required. To make the settings live, you need to restart MariaDB server.

MySQL Tuner

innodb_buffer_pool_size On a server with 32 GB RAM, set

[…]

Read More…

MySQL

MySQL Cluster MySQL 8 Find all MySQL databases using InnoDB engine Find MySQL Server Version Find MySQL Database and Table Size MySQL see all charsets MySQL show processlist MySQL Installation Install MySQL 8 on CentOS 6/7 Install MySQL 8 on Ubuntu/Debian How to Install MySQL 5.7 on Amazon Linux Install Latest MariaDB on CentOS MySQL […]

Read More…

Reset MySQL root Password

To reset MySQL root password, stop MySQL

Start MySQL with

Start another terminal, login to MySQL as root

Run following commands to change password.

Now you need to kill running MySQL processes and start MySQL as normal.

See MySQL Password Reset/Change […]

Read More…

MySQL User

Create a user For local mysql access

To allow remote access, run

To allow a user to create another user

Now this user will be able to create new users. Create a user with root privilages

For MySQL 8

[…]

Read More…

Debian Allow MySQL root user to login using phpMyAdmin

On Debian/Ubuntu by default MySQL root user can only login from local accounts.

To enable login from phpMyAdmin, you need to set plugin to “mysql_native_password”. To do this, login to MySQL as user root, then run

[…]

Read More…

Configuring MySQL .my.cnf

Add following content

Replace DBPASSWORD with your MySQL root password. Now set permission for this file to 600, so no user other than root can see it.

[…]

Read More…

InnoDB: space header page consists of zero bytes in tablespace

On a server, starting MySQL i get following error

This was because the server used InnoDB database engine and my.cnf was not configured properly. First i removed the log files.

Now i edit /etc/my.cnf as follows

This MySQL config is based on /usr/share/doc/mysql-server-5.1.73/my-innodb-heavy-4G.cnf provided my MySQL.

You can also see this […]

Read More…

myisamchk: Argument list too long

When repairing a large database, i got following error

To fix the error, run SOLUTION 1

SOLUTION 2

See MySQL […]

Read More…