Category: MySQL

MySQL Unknown collation: ‘utf8mb4_0900_ai_ci’
When restoring a MySQL 8 database backup on a MariaDB server, I get the following error
```
boby@sok-01:~/Downloads$ mysql mage2 < magento2-2021-01-27-1-54.sql
ERROR 1273 (HY000) at line 11762: Unknown collation: 'utf8mb4_0900_ai_ci'
boby@sok-01:~/Downloads$ 
```
To fix this error, open the file in a text editor, find utf8mb4_0900_ai_ci, and replace it with utf8mb4_general_ci.

I used the following sed command to do the replacement.
```
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' magento2.sql
```
See MySQL
January 27, 2021
How to Install MySQL 5.7 on Amazon Linux
Amazon Linux 2 come with MariaDB by default. To install MySQL 5.7, install repository with
```
sudo rpm -Uvh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
```
Install MySQL 5.7 with
```
sudo yum install mysql-community-server
```
Set MySQL to start on boot
```
sudo systemctl enable mysqld
```
Start MySQL server
```
sudo systemctl start mysqld
```
By default MySQL 5.7 installation generate a temporary password. To find the password, run
```
sudo grep "password" /var/log/mysqld.log
```
To set password and secure the installation, run
```
sudo mysql_secure_installation
```
See Amazon Linux
January 20, 2021

MySQL Your password does not satisfy the current policy requirements

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

mysql> GRANT ALL PRIVILEGES ON *.* TO 'centovacast'@'localhost' IDENTIFIED BY '*5B5F6EB22D64C7D8FE384BEF890B55964482A144' WITH GRANT OPTION;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql>

This is due to MySQL validate password settings

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

mysql>

To fix the error for current session, run

SET GLOBAL validate_password_mixed_case_count = 0;

Now password change will work.

mysql> SET GLOBAL validate_password_mixed_case_count = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'centovacast'@'localhost' IDENTIFIED BY '*5B5F6EB22D64C7D8FE384BEF890B55964482A144' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>

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

validate_password_mixed_case_count = 0

See MySQL

January 1, 2021

Change MySQL user password

To change the password for a MySQL user, run

mysqladmin -u user_name_here -p password new_password_here

Or via SQL

UPDATE mysql.user SET Password=PASSWORD('NEW_PASSWORD_HERE') WHERE User='USER_NAME_HERE';
FLUSH PRIVILEGES;

See MySQL

December 28, 2020

MySQL Install
- How to install MySQL 5.6 on CentOS 7
- Install MySQL as Service in Windows
MySQL Upgrade
- Upgrade MariaDB 10.3 to 10.5 on Ubuntu 20.04
MariaDB
- Install Latest MariaDB on CentOS
MySQL 8
- Install MySQL 8 on CentOS 6/7
- Install MySQL 8 on Ubuntu/Debian
MySQL 5.7
Back to MySQL
December 15, 2020

Reset MySQL root password on Bitnami server

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

mysql --version

Create a file

vi /tmp/mysql-init

Add following text

For MySQL 5.7 or MySQL 8

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD';
ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'NEW_PASSWORD';

For MySQL 5.6

UPDATE mysql.user SET Password=PASSWORD('NEW_PASSWORD') WHERE User='root';
FLUSH PRIVILEGES;

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

Stop MySQL

/opt/bitnami/ctlscript.sh stop mysql

Reset MySQL root password by running

MySQL 5.7/MySQL 8

/opt/bitnami/mysql/bin/mysqld_safe --pid-file=/opt/bitnami/mysql/data/mysqld.pid --datadir=/opt/bitnami/mysql/data --init-file=/tmp/mysql-init --lower_case_table_names=1 2> /dev/null &

If you are using MySQL 5.6 or older, run

/opt/bitnami/mysql/bin/mysqld_safe --pid-file=/opt/bitnami/mysql/data/mysqld.pid --datadir=/opt/bitnami/mysql/data --init-file=/tmp/mysql-init 2> /dev/null &

Restart MySQL

/opt/bitnami/ctlscript.sh restart mysql

Now you should be able to login to MySQL server with command

mysql -u root -p'NEW_PASSWORD'

See Bitnami, Reset MySQL root Password

December 13, 2020

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
```
mkdir /etc/systemd/system/mariadb.service.d/
vi /etc/systemd/system/mariadb.service.d/limitnofile.conf
```
Add
```
[Service]
LimitNOFILE=1048576
```
Reload systemctl
```
systemctl daemon-reload
```
Restart mariadb
```
systemctl restart mariadb
```
After doing this, it get changed to 32184 instead of 1048576 we specified in limitnofile.conf

To fix this edit

On Cpanel/RHEL
```
vi /etc/my.cnf.d/server.cnf
```
On Ubuntu/Debian
```
vi /etc/mysql/mariadb.conf.d/50-server.cnf
```
Under [mysqld], add
```
open_files_limit = 102400
```
Now after restarting MariaDB, i get 1048576 for open_files_limit.

This value depends on kernals fs.nr_open parameter. If the value is low, you can increase by editing
```
vi /etc/sysctl.conf
```
Add
```
fs.nr_open=1048576‬
```
Then make it active with command
```
sysctl -p
```
To see current value, you can run
```
sysctl -a | grep fs.nr_open
```
To set the value for the current session, run
```
sysctl -w fs.nr_open=1048576‬
```
Plesk Debian 8 General error: 23 Out of resources when opening

See MySQL
December 7, 2020

MySQL Can’t create new tempfile

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

mysql> repair table visitorstats_sessions;
+-------------------------------------+--------+----------+------------------------------------------------------------------------+
| Table                               | Op     | Msg_type | Msg_text                                                               |
+-------------------------------------+--------+----------+------------------------------------------------------------------------+
| centovacastdb.visitorstats_sessions | repair | error    | Can't create new tempfile: './centovacastdb/visitorstats_sessions.TMD' |
| centovacastdb.visitorstats_sessions | repair | status   | Operation failed                                                       |
+-------------------------------------+--------+----------+------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql>

Check the table with myisamcheck

[root@centos7 ~]# myisamchk -a /var/lib/mysql/centovacastdb/visitorstats_sessions
Checking MyISAM file: /var/lib/mysql/centovacastdb/visitorstats_sessions
Data records:  704545   Deleted blocks:       0
myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check record links
MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions' is usable but should be fixed
[root@centos7 ~]#

It reported some errros. To fix, run

myisamchk -r /var/lib/mysql/centovacastdb/visitorstats_sessions

When i run, i get following error

[root@centos7 ~]# myisamchk -r /var/lib/mysql/centovacastdb/visitorstats_sessions
- recovering (with sort) MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions'
Data records: 704545
myisamchk: error: Can't create new tempfile: '/var/lib/mysql/centovacastdb/visitorstats_sessions.TMD'
MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
[root@centos7 ~]#

Since the error said “Can’t create new tempfile”, i checked disk usage to make sure disk is not full. Since disk usage is not fill, i run with -f option and it worked.

[root@centos7 ~]# myisamchk -rf /var/lib/mysql/centovacastdb/visitorstats_sessions
- recovering (with sort) MyISAM-table '/var/lib/mysql/centovacastdb/visitorstats_sessions'
Data records: 704545
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
[root@centos7 ~]#

See MySQL Repair

December 5, 2020

Split mysqldump backup file into tables
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
```
csplit -s -ftable MYSQLDUMP_BACKUP_FILE_HERE "/-- Table structure for table/" {*}
```
This will generate files with name tableXX. First file table00 contains SQL commands used by all tables. Other files starting with table01 contains table related SQL file.

To rename the files into table name, run
```
for FILE in `ls -1 table*`; do TABLE=`head -n 1 $FILE | cut -d$'\x60' -f2`; mv $FILE $TABLE.sql; done
```
If you need to handle larger SQL file, there is a node.js project on github, that do the spliting of mysqldump file into tables.

https://github.com/vekexasia/mysqldumpsplit

See MySQL
December 4, 2020

Auto Restart MySQL if Crashed

This bash script is used to auto-restart MySQL or MariaDB database if it crashes or stops for any reason.

Create file

mkdir /usr/serverok
vi /usr/serverok/mysql_monitor.sh

Add

#!/bin/bash
# Author: ServerOK
# Web: https://serverok.in/mysql-restart-bash

MYSQL_REPLY="$(mysqladmin ping)"
TIME_STAMP="$(date "+%Y-%m-%d %H:%M:%S")"

if [[ ! "$MYSQL_REPLY" =~ "mysqld is alive" ]]
then
    systemctl restart mariadb
    echo -e "${TIME_STAMP} MySQL Down\n"
fi

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

chmod 755 /usr/serverok/mysql_monitor.sh

Create cronjob to run the script every 5 minutes.

crontab -e

Add

*/5 * * * * /usr/serverok/mysql_monitor.sh >> /var/log/sok-mysql.log

See MySQL

December 4, 2020

Find MySQL Database and Table Size

To find the size of databases using SQL command, run the following SQL in MySQL prompt.

SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

Example

FInd disk usage by tables

To find disk usage by tables in database, run

SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as "size (MB)" FROM information_schema.TABLES WHERE table_schema = "DB_NAME_HERE"  ORDER BY data_length DESC;

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

Example

SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as "size (MB)" FROM information_schema.TABLES WHERE table_schema = "xogifts_db"  ORDER BY data_length DESC;

See MySQL

November 24, 2020

MySQL ERROR Unable to create or change a table without a primary key
When trying to restore a database backup to Managed DigitialOcean MySQL 8 database, i get following error
```
root@ocp:~# mysql -u doadmin -p'BKwsQcqEGbSV3w' -h db-sevrerok-do-user-8606188-0.b.db.ondigitalocean.com -P 25060 serverok_db < serverok_db.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3750 (HY000) at line 223: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
root@ocp:~# 
```
This is because one of the tables in your MySQL backup don't have primary key defined.

Solution

There are few ways you can fix this. Proper way is to define a primary key for the table. If this is not possible, you can set sql_require_primary_key to OFF.

To set sql_require_primary_key to OFF, edit file
```
vi /etc/mysql/mysql.conf.d/mysqld.cnf
```
Under [mysqld] section, add
```
sql_require_primary_key=0
```
Now restart MySQL server.
```
systemctl restart mysql
```
In the case of DigitalOcean, we can't edit MySQL configuration as it is Managed MySQL Database service, What you can do is edit the MySQL backup file. Add following code to top of the file.
```
SET sql_require_primary_key=0;
```
This should be added as first SQL statement. Now do restore again, it will work.
October 22, 2020

Category: MySQL

MySQL Upgrade

MariaDB

MySQL 8

MySQL 5.7

FInd disk usage by tables

Solution