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
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
When restoring a MySQL 8 database backup on a MariaDB server, I get the following error 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. See MySQL
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 … Read more
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%’; +————————————–+——–+ … Read more
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