MySQL Unknown collation: ‘utf8mb4_0900_ai_ci’

When restoring a MySQL backup taken on MySQL 8 server, i get following error

To fix this error, open the file in a text editor, find utf8mb4_0900_ai_ci, replace with utf8mb4_unicode_ci. I used following sed command to do the replace.

See MySQL […]

Read More…

How to Install MySQL 5.7 on Amazon Linux

MySQL

Amazon Linux 2 come with MariaDB by default. To install MySQL 5.7, install repository with

Install MySQL 5.7 with

Set MySQL to start on boot

Start MySQL server

By default MySQL 5.7 installation generate a temporary password. To find the password, run

To set password and secure the installation, run […]

Read More…

MySQL Your password does not satisfy the current policy requirements

MySQL

When i try to create MySQL user on a server, i get following error

This is due to MySQL validate password settings

To fix the error for current session, run

Now password change will work.

If you need it made permanent, then edit MySQL configuration file and add the line under […]

Read More…

Change MySQL user password

To change the password for a MySQL user, run

Or via SQL

See MySQL […]

Read More…

Reset MySQL root password on Bitnami server

MySQL

To reset MySQL root password on Bitnami server, first check MySQL server version you are running.

Create a file

Add following text For MySQL 5.7 or MySQL 8

For MySQL 5.6

In above code, replace NEW_PASSWORD with your new MySQL root password. Stop MySQL

Reset MySQL root password by running […]

Read More…

MariaDB Change Open Files Limit

When using systemctl, you can set value for open_files_limit in my.cnf file. Default installation of MariaDB 10 have open_files_limit set to 16384. To increase value for open_files_limit, create file

Add

Reload systemctl

Restart mariadb

After doing this, it get changed to 32184 instead of 1048576 we specified in limitnofile.conf To fix […]

Read More…

MySQL Can’t create new tempfile

When i try to repair a crashed database table, i get error

Check the table with myisamcheck

It reported some errros. To fix, run

When i run, i get following error

Since the error said “Can’t create new tempfile”, i checked disk usage to make sure disk is not full. Since […]

Read More…

Split mysqldump backup file into tables

MySQL

I had to restore a large MySQL backup file. When restoring one of the table resulted in error. To debug the error, i wanted to split the MySQL backup taken using mysqldump into tables. You can use csplit command to do this

This will generate files with name tableXX. First file table00 contains SQL […]

Read More…

Auto Restart MySQL if Crashed

MySQL

This bash script is used to auto restart MySQL or MariaDB database if it crash/stop for any reason. Create file

Add

In the code, replace YOUR_ROOT_PW_HERE with your actual root password. If you are not using “root”, replace root with whatever username you use. Make it executable

Create cronjob to run the […]

Read More…

Find MySQL Database and Table Size

To find size of database using SQL command, run following SQL in MySQL promt.

Example FInd disk usage by tables To find disk usage by tables in database, run

In above SQL, replace DB_NAME_HERE with actual name of the database. Example

See MySQL […]

Read More…