When deleting a table in a MySQL database, I get the following error message MariaDB [thrkhztbpt]> drop table users; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails MariaDB [thrkhztbpt]> To fix the error, run SET FOREIGN_KEY_CHECKS=0; Now the drop table SQL command will work. After you dropped the … Read more
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, … Read more
MySQL Backup Backup Services MySQL Backup Tricks MySQL Restore Errors MySQL Backup Errors Back to MySQL
To backup all databases on a MySQL server, run 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. 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 … Read more
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 … Read more
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. … Read more
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; … Read more
MariaDB Replication MariaDB galera cluster https://www.percona.com/software/mysql-database/percona-xtradb-cluster Disable MySQL Replication on Slave
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 … Read more
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 … Read more
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