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 = password of the read-only user.
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, X Protocol won't be accessible' [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of socket: '/var/run/mysqld/mysqlx.sock' failed, can't create lock file /var/run/mysqld/mysqlx.sock.lock' [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. [ERROR] [MY-010273] [Server] Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock. [ERROR] [MY-010268] [Server] Unable to setup unix socket lock file. [ERROR] [MY-010119] [Server] Aborting [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29-0ubuntu0.20.04.3) (Ubuntu).
The error is
[ERROR] [MY-010273] [Server] Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock.
To fix the error, create the folder, and change its ownership to user “mysql”.
mkdir /var/run/mysqld/ chown mysql:mysql /var/run/mysqld/
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 this package. Check that the correct key URLs are configured for this repository.
To fix the error, run the command
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
See MySQL
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' /var/log/mysqld.log
This initial password is expired, you should change this password before you can start using MySQL server.
To set MySQL password and secure MySQL server, run the command
mysql_secure_installation
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
systemctl start mysqld
To secure MySQL run
mysql_secure_installation
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 a limit of 767 bytes prefix limit.
https://dev.mysql.com/doc/refman/5.6/en/create-index.html
Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix length limit is 1000 bytes.
From the error message, the error was on line 745, when checking the SQL file, I found the following SQL statement.
CREATE TABLE `wpk4_gla_merchant_issues` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `product_id` bigint(20) NOT NULL, `issue` varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL, `code` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL, `severity` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'warning', `product` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL, `action` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL, `action_url` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL, `applicable_countries` text COLLATE utf8mb4_unicode_520_ci NOT NULL, `source` varchar(10) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'mc', `type` varchar(10) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'product', `created_at` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `product_issue` (`product_id`,`issue`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
To fix it, find
UNIQUE KEY `product_issue` (`product_id`,`issue`)
Replace with
UNIQUE KEY `product_issue` (`product_id`,`issue`(191))
This will limit the length of the issue column to 191 chars. Each character takes 4 bytes to store in utf8mb4 character set. The limit 191 is found by trying with default length for issue field 200, then reducing it until the SQL gets created properly. The calculation is 191 * 4 + length of id column < 767 The correct solution is to use newer MySQL versions like MySQL 5.7, which allows length up to 3072 bytes.
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 ol7_MySQL55/x86_64 MySQL 5.5 for Oracle Lin disabled ol7_MySQL56/x86_64 MySQL 5.6 for Oracle Lin disabled ol7_MySQL57/x86_64 MySQL 5.7 for Oracle Lin disabled ol7_MySQL80/x86_64 MySQL 8.0 for Oracle Lin enabled: 281 ol7_MySQL80_connectors_community/x86_64 MySQL 8.0 Connectors Com enabled: 56 ol7_MySQL80_tools_community/x86_64 MySQL 8.0 Tools Communit enabled: 15 [root@sql2 ~]#
Let’s disable MySQL 8 repo and enable MySQL 5.7 repository.
yum install -y yum-utils yum-config-manager --disable ol7_MySQL80 ol7_MySQL80_connectors_community ol7_MySQL80_tools_community yum-config-manager --enable ol7_MySQL57
Before you can install MySQL 5.7, if your server has any MySQL 8 related packages installed, you need to uninstall them.
On my server, I had the following packages installed.
[root@mysql1 ~]# rpm -qa | grep mysql mysql-community-client-plugins-8.0.26-1.el7.x86_64 mysql-community-common-8.0.26-1.el7.x86_64 mysql-community-libs-8.0.26-1.el7.x86_64 mysql-release-el7-1.0-5.el7.x86_64 mysql-community-libs-compat-8.0.26-1.el7.x86_64 [root@mysql1 ~]#
These are installed as dependency for the postfix mail server, so if you remove the packages with yum, postfix also get removed, so I removed it with command
rpm -e --nodeps mysql-community-client-plugins-8.0.26-1.el7.x86_64 rpm -e --nodeps mysql-community-common-8.0.26-1.el7.x86_64 rpm -e --nodeps mysql-community-libs-8.0.26-1.el7.x86_64 rpm -e --nodeps mysql-community-libs-compat-8.0.26-1.el7.x86_64
–nodeps option will remove packages without removing dependent packages. Make sure you don’t remove the package mysql-release-el7-1.0-5.
Now you can install MySQL server with command
yum install mysql-community-server
Enable and start MySQL server
systemctl enable mysqld --now
To find MySQL initial password, use
grep 'temporary password' /var/log/mysqld.log
Login to MySQL server using the temporary password and set a password
mysql -u root -p
Enter the MySQL server temporary password, then execute the following SQL to change the MySQL root password.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_MYSQL_ROOT_PW';
NEW_MYSQL_ROOT_PW – replace with your new secure MySQL root password.
You can secure MySQL installation by running
mysql_secure_installation
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 the error is due to the following SQL statement.
CREATE TABLE `catalog_product_relation` ( `parent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Parent ID', `child_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Child ID', PRIMARY KEY (`parent_id`,`child_id`), KEY `IDX_CATALOG_PRODUCT_RELATION_CHILD_ID` (`child_id`), CONSTRAINT `FK_CAT_PRD_RELATION_CHILD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`child_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_CAT_PRD_RELATION_PARENT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`parent_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Catalog Product Relation Table';
Fixed it by removing
ROW_FORMAT=FIXED
From the SQL.
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;
Stop MySQL
systemctl stop mysql
Remove relay logs
cd /var/lib/mysql rm -f master.info relay-*`
Start MySQL server
systemctl start mysql
You may need to re-enable events that are disabled on Slave.
select * from information_schema.events where status = 'SLAVESIDE_DISABLED';
Enable each disabled event with
alter eventenable;
Example
MariaDB [(none)]> show slave status; +----------------+---------------+-------------+-------------+---------------+--------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+-------------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_SSL_Crl | Master_SSL_Crlpath | Using_Gtid | Gtid_IO_Pos | Replicate_Do_Domain_Ids | Replicate_Ignore_Domain_Ids | Parallel_Mode | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | +----------------+---------------+-------------+-------------+---------------+--------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+-------------------------+ | | 144.76.16.199 | replication | 3306 | 10 | mariadb-bin.000134 | 715913856 | relay-bin.000014 | 617924553 | mariadb-bin.000028 | No | No | | | | | | | 0 | | 0 | 818117404 | 0 | None | | 0 | No | | | | | | NULL | No | 0 | | 0 | | | 0 | | | No | | | | conservative | 0 | NULL | | +----------------+---------------+-------------+-------------+---------------+--------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+-------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> RESET SLAVE; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> Bye root@ArthAppServer1:/etc/mysql# systemctl restart mysql root@ArthAppServer1:/etc/mysql# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.2.31-MariaDB-10.2.31+maria~xenial mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show slave status; Empty set (0.00 sec) MariaDB [(none)]>
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, use
mysql --skip-column-names --batch -e 'show processlist' | wc -l
See MySQL
Back to MySQL