To create a MySQL database user with read-only access to a database, use the following command GRANT SELECT, SHOW VIEW ON DB_NAME.* TO USER_NAME@’localhost’ IDENTIFIED BY ‘PASSWORD’; In the above SQL statement DB_NAME = name of the database to which you need to give read-only access. USER_NAME = username of the read-only user PASSWORD = … Read more
When trying to reset MySQL 8 user root password, I got the following error message in the log file (/var/log/mysql/error.log). [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.29-0ubuntu0.20.04.3) starting as process 265880 [System] [MY-013576] [InnoDB] InnoDB initialization has started. [System] [MY-013577] [InnoDB] InnoDB initialization has ended. [ERROR] [MY-011292] [Server] Plugin mysqlx reported: ‘Preparation of I/O interfaces failed, … Read more
On a CentOS 7 server, when updating software packages with the yum update command, it fails with the following error message. warning: rpmts_HdrFromFdno: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql The GPG keys listed for the “MySQL 5.7 Community Server” repository are already installed but they are not correct for … Read more
To install MySQL 5.7 on CentOS 7 server, install the repository rpm -ivh http://repo.mysql.com/mysql57-community-release-el7.rpm import MySQL GPG key with rpm –import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 Install MySQL with the command yum install mysql-community-server.x86_64 Enable MySQL to start on boot systemctl enable mysqld Start MySQL with systemctl start mysqld Find the initial MySQL password with the command grep ‘password’ … Read more
CentOS 7 come with MariaDB, a drop-in replacement for MySQL. In some cases, you need to install the Oracle MySQL server. Download and install MySQL repository rpm -ivh http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm Next, you can install the MySQL server by running the command yum install mysql-community-server Enable MySQL to start on boot systemctl enable mysqld Start MySQL with … Read more
When restoring a MySQL database, I got the error “Specified key was too long; max key length is 767 bytes”. user@host [~]$ mysql new_db < wordpress-2021-12-19-b51f8a6.sql ERROR 1071 (42000) at line 745 in file: 'wordpress-2021-12-19-b51f8a6.sql': Specified key was too long; max key length is 767 bytes user@host [~]$ MySQL version 5.6 and older versions have ... Read more
Oracle Linux 7 comes with MySQL yum repository pre-installed. By Default MySQL 8 repository is enabled. You can disable MySQL 8 repository and enable MySQL 5.7 repository to install MySQL 5.7. You can find enabled repositories with command yum repolist all | grep -i mysql Example [root@sql2 ~]# yum repolist all | grep -i mysql … Read more
When I try to restore the MySQL database of a Magento 1.x site on MariaDB 10.3.32 (source MySQL 5.7.36), I get the following error boby@sok-01:/www/magento/amazingplans/backup$ mysql amazingplans < amazingp_demo.sql ERROR 1005 (HY000) at line 4080: Can't create table `amazingplans`.`catalog_product_relation` (errno: 140 "Wrong create options") boby@sok-01:/www/magento/amazingplans/backup$ On checking the line in the error message, I found ... Read more
To disable MySQL Replication on the Slave server, first, edit MySQL configuration file, usually in /etc/mysql folder, remove entries like server-id = 2 log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index relay_log = /var/lib/mysql/relay-bin relay_log_index = /var/lib/mysql/relay-bin.index binlog_format=row Also, remove any entries starting with master- replicate- Stop slave with command STOP SLAVE; Stop replication with RESET SLAVE; … Read more
To export a MySQL database table as an XML document, you can use mysqldump command like mysqldump DB_NAME TABLE_NAME –xml phpMyAdmin table export feature also gives the option to save Data in multiple formats. Back to MySQL
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