To see currently running MySQL processes, login to MySQL server, run in MySQL command prompt, run the command
show processlist;
To see the full SQL command, use
show full processlist;
You can also use the following command from the bash command prompt
mysql --skip-column-names --batch -e 'show processlist'
To see the number of SQL processes, use
mysql --skip-column-names --batch -e 'show processlist' | wc -l
See MySQL
Back to MySQL
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"; doneThis 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: admin@ServerOk.in
# @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
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
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
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
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
If you forget the MySQL root password on MySQL 8.0 server, you can follow the instruction here to reset the root user password.
First, stop the MySQL server if it is already running
systemctl stop mysql
Create folder
mkdir /var/run/mysqld/ chown mysql:mysql /var/run/mysqld/
Now start MySQL with –skip-grant-tables option
mysqld --skip-grant-tables --user=mysql
Take a new terminal, connect to MySQL
mysql -u root -p
When it asks for the root password, just press enter. No need to enter any password. To reset MySQL root password, run
FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL_NEW_ROOT_PW_HERE';
After running the above 2 SQL commands, exit MySQL command prompt with command exit or press CTRL+D.
Now you need to find the process ID of MySQL
ps aux | grep mysql
Kill the process with
kill -9 PROCESS_ID_OF_MYSQLD
Now start MySQL normally with
systemctl start mysql
Now you will be able to login to the MySQL server using the new PASSWORD
mysql -u root -p
See MySQL root password, MySQL Could not create unix socket lock file
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
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
To check and repair all databases in a database run
mysqlcheck -A --auto-repair -u root -p
Forcefully optimize all tables, automatically fixing table errors that may come up.
mysqlcheck -A --auto-repair -f -o -u root -p
To check all databases
mysqlcheck --all-databases -u root -p -c
To analyze all tables in all databases:
mysqlcheck --all-databases -u root -p -a
To repair all tables in all databases:
mysqlcheck --all-databases -u root -p -r
To optimize all tables in all databases:
mysqlcheck --all-databases -u root -p -o
To check all tables in a database, run
mysqlcheck --databases DB_NAME_HERESee MySQL Repair