Tag: mysql

MySQL backup all databases

To backup all databases on a MySQL server, run

mysql -e "show databases" | egrep -v "(-|Database|mysql|information_schema|performance_schema|phpmyadmin)"  > /tmp/sok-dbs.txt
for db in `cat /tmp/sok-dbs.txt`; do /usr/bin/mysqldump --events --routines --triggers $db > "${db}.sql"; done

This will create sql backup file for each database in your MySQL server on the current directory.

The first command will create a file with all database names. Second command loop through the database names and create backup using mysqldump command.

#!/bin/bash
# MySQL Backup Scipt
# Author: [email protected]
# @daily /usr/serverok/backup-mysql.sh > /var/log/backup-mysql.log 2>&1

MYSQL_USER="root"
MYSQL_PW="PASSWORD_HERE"

BACKUP_BASE_DIR="/backup/mysql/"
BACKUP_DATE="$(date +%Y%m%d-%H%M%S)"
BACKUP_DIR="${BACKUP_BASE_DIR}/${BACKUP_DATE}"

if [ ! -d $BACKUP_DIR ]; then
    mkdir -p $BACKUP_DIR
fi

if [ ! -d $BACKUP_DIR ]; then
    echo "Backup folder not found"
    exit 1
fi

# Backup MySQL Database

mysql -u ${MYSQL_USER} -p"${MYSQL_PW}" -e "show databases" | egrep -v "(-|Database|information_schema|performance_schema|phpmyadmin|sys)"  > /tmp/sok-dbs.txt

for db in `cat /tmp/sok-dbs.txt`
do
    /usr/bin/mysqldump -u ${MYSQL_USER} -p"${MYSQL_PW}" --events --routines --triggers $db > "${BACKUP_DIR}/${db}.sql";
done

# Remove .sql files older than 7 days
find "${BACKUP_BASE_DIR}" -mindepth 2 -maxdepth 2 -type f -name '*.sql' -mtime +7 -exec rm -f {} \;

# Remove empty directories
find "${BACKUP_BASE_DIR}" -mindepth 1 -type d -empty -exec rmdir {} \;

See MySQL Backup

August 23, 2021

bash: mysql_safe: command not found
On CentOS 7 server running MySQL 5.7, when trying to reset MySQL root password, I get an error
```
[root@SAU-8E161-OR ~]# mysqld_safe --skip-grant-tables
-bash: mysqld_safe: command not found
[root@SAU-8E161-OR ~]# 
```
This is because MySQL 5.7 installation using yum removed the mysqld_safe binary file.

To reset, you need to start MySQL using systemctl with skip-grant-tables options. So you don’t need mysqld_safe. To see how to reset MySQL root password on CentOS 7, follow instructions avaialble at

CentOS 7 MySQL 5.7 root password reset
July 9, 2021
CentOS 7 MySQL 5.7 root password reset
To reset MySQL 5.7 root password on CentOS 7 server, do the following

Stop MySQL Server
```
systemctl stop mysqld
```
Set the MySQL MYSQLD_OPTS environment to start MySQL with –skip-grant-tables
```
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
```
Start MySQL with –skip-grant-tables
```
systemctl start mysqld
```
Login as user root
```
mysql -u root
```
Update MySQL root password
```
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_MYSQL_PASSWORD_HERE';
```
Or
```
UPDATE mysql.user SET authentication_string = PASSWORD('NEW_MYSQL_PASSWORD_HERE') WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;
```
Exit MySQL command prompt
```
quit
```
Stop MySQL server
```
systemctl stop mysqld
```
Unset the MySQL environment option
```
systemctl unset-environment MYSQLD_OPTS
```
Start MySQL normally
```
systemctl start mysqld
```
Now you should be able to log in with new MySQL root password using
```
mysql -u root -p
```
See Reset MySQL root password, mysql_safe: command not found
July 9, 2021

ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded

When I try to connect to a MySQL 8 server, I get the following error

root@ok:~# mysql -u serverok -p'PW_HERE' -h db-mysqlserverok.in -P 3306
ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
root@ok:~#

This is because of MySQL 8 use more secure authentication. This is not supported by your MySQL installation. What you can do is connect using MySQL 8 client or change the authentication method to use old authentication.

To create a user with the old authentication method, use the following SQL commands

CREATE USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD_HERE';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;

To change a user to use mysql_native_password, run

ALTER USER ‘username’@’localhost’ IDENTIFIED WITH ‘mysql_native_password’ BY ‘PASSWORD_HERE’;

if you need to set this server wide, edit my.cnf and add

[mysqld]
default_authentication_plugin=mysql_native_password

See MySQL

July 8, 2021

Magento catalog_product_relation MySQL Error

On transferring a Magento website Database running on MySQL 5.7.34 to MariaDB 10.2.39, I get the following error

[root@server52 etc]# mysql buildingplans_m1 < db.sql
ERROR 1005 (HY000) at line 4080: Can't create table `buildingplans_m1`.`catalog_product_relation` (errno: 140 "Wrong create options")
[root@server52 etc]#

SQL Strict mode was already off on this MySQL server.

MariaDB [(none)]> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

To fix the problem, edited the db.sql file, added

set innodb_strict_mode = off;

at the top of the file. After this change, the restore worked without any error.

See Magento

July 7, 2021

MySQL Cluster

MariaDB Replication

MariaDB galera cluster

https://www.percona.com/software/mysql-database/percona-xtradb-cluster

Disable MySQL Replication on Slave

July 4, 2021
MySQL 8 server requested authentication method unknown to the client
On a server running MySQL 8 and PHP 7.3, I get the following error

boby@sok-01:~$ php 1.php
PHP Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /var/www/html/1.php on line 6
PHP Warning: mysqli_connect(): (HY000/2054): The server requested authentication method unknown to the client in /var/www/html/1.php on line 6
Failed to connect to MySQL: The server requested authentication method unknown to the client
boby@sok-01:~$

To fix this, create a user with MySQL native password.
```
CREATE USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD_HERE';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;
```
See MySQL
June 8, 2021
How to rename a MySQL database table
To rename a MySQL database table, login to phpMyAdmin or MySQL command line, then select the database, run following SQL command
```
ALTER TABLE TABLE_OLD_NAME RENAME TO TABLE_NEW_NAME;
```
Example

See MySQL
May 26, 2021
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