Since MySQL 5.5, InnoDB is the default storage engine due to its reliability, performance, and feature set. Switching from MyISAM to InnoDB is strongly recommended for most modern MySQL applications. InnoDB is a transactional storage engine, meaning it fully supports ACID (Atomicity, Consistency, Isolation, Durability) properties. You can safely roll back transactions in case of … Read more
Backing up and restoring large databases can be challenging at times. Recently, when I had to back up and restore a large database, I did a backup for each database table and restored them one by one using a bash script. In case of a restore failure, you only need to deal with the specific … Read more
To recover MySQL table structure from .frm file, you can use dbsake. You can find dbsake documentation at http://dbsake.readthedocs.org/en/latest/. To install dbsake, run To find the structure of a table from the .frm file, run Back to MySQL
Looking for a free MySQL hosting solution? In this blog post, I will list some of the MySQL hosting providers that offer free and paid MySQL hosting. These MySQL hosting is useful for smaller database work loads. If you have lot of data, you may need local MySQL server or a MySQL server to your … Read more
When taking backup of a MySQL database, i got error root@server1:~# mysqldump –opt serverok_wp > serverok_wp.sql mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `wp_options` at row: 60831 root@server1:~# This error happens when connection between your MySQL client and server is timing out during the mysqldump process. To fix, increase … Read more
I wanted to Migrate MySQL from one server to another. Usually, I could just copy over the MySQL data directory (/var/lib/mysql) to the new server, this will copy over MySQL users and data. On this server, the MySQL data file (ibdata1) was too large, 200 GB approx. If I do a mysqldump, the SQL file … Read more
After installing MySQL 8 and checking listening ports with “netstat -lntp”, you will see MySQL is listening on ports 3306 and 33060. Port 3306 is used by traditional MySQL protocol (MySQL Classic Protocol). MySQL X Protocol uses port 33060. What is MySQL X Protocol? MySQL X Protocol is a network protocol that allows communication between … Read more
You can use following config to keep 2 days bin log After modifying the configuration file, you will need to restart the MySQL server to apply the changes: You can also manually delete old binary log files to free up disk space: Replace ‘2024-02-01 00:00:00’ with the date before which you want to delete the … Read more
MySQL 5.7 is not supported on Ubuntu 22.04, but you can download MySQL 5.7 for Ubuntu 18.04 and install it on Ubuntu 20.04 or Ubuntu 22.04 First, install libtinfo5, this is available for download from https://launchpad.net/ubuntu/bionic/amd64/libtinfo5/6.1-1ubuntu1.18.04 The files are moved from the Offical MySQL site https://dev.mysql.com/downloads/mysql/5.7.html So we will download it from a mirror site. … Read more
On a Debian 7 server, the MySQL database got corrupted, which caused the MySQL database to keep crashing. I was able to take a backup of the database following InnoDB Recovery instructions at https://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html Once I have the Database backup taken, I took a backup of the MySQL data directory /var/lib/mysql and removed it. To … Read more
MyISAM tables (.MYI and .MYD) can be repaired using myisamchk command. To find list of corrupted tables myisamchk /var/lib/mysql/DB_NAME/*.MYI >> /root/1.txt To rapier a table cd /var/lib/mysql/DB_NAME/ myisamchk -r TABLE_NAME_HERE.MYI To check and repair all tables myisamchk –silent –force –fast –update-state /var/lib/mysql/DB_NAME/*.MYI Look like newer version of MySQL, you need to specify just table name, … Read more
When backing up a MySQL database using mysqldump command, I got the following error. root@server12:~# mysqldump –opt DB_NAME > DB_NAME.sql mysqldump: Got error: 1016: Can’t open file: ‘./DB_NAME/TABLE_NAME.frm’ (errno: 24) when using LOCK TABLES root@server12:~# The error is due to open_files_limit mysql> show variables like ‘open_files_limit’; +——————+——-+ | Variable_name | Value | +——————+——-+ | open_files_limit … Read more