MySQL is a popular open-source RDBMS. It is an essential part of the LAMP stack used in lots of websites today. Though MySQL is generally used to store and manage structured data. As it is used regularly, there is high risk of getting corrupt MySQL Tables. MySQL tables become corrupted due to several reasons, like hardware failures, improper shutdowns, software bugs, etc. Regardless of the reasons, the issue can be fixed by using some effective solutions. In this optimized post, you will be got to know about repair corrupt MySQL table. What Causes Table Corruption in MySQL? 1. Crashing MySQL service after reboot 2. Due to Bad memory 3. Unexpectedly shutting off the machine because of a power failure 4. Hardware failure 5. Abruptly turn OFF the system while updating the SQL. 6. Software glitches in the operating system or SQL code. Now, let’s move on troubleshoot this issue with the possible approaches. How to Repair Corrupt MySQL Tables? Important Note- It’s highly recommended to create a valid backup of your important database before following the below steps. Step-1: First, stop MySQL service sudo systemctl stop mysql Once you have stopped the SQL service, now create a backup of all your current files into the new directory. You can use Ubuntu’s default data directory /var/lib/mysql/ for copying the system content: cp -r /var/lib/mysql /var/lib/mysql_bkp Step-2: Determine the MySQL Engine Type The earlier versions of MySQL uses MyISAM as a default storage engine, which is prone to db table corruption. However, to check MySQL engine, you have to enter the below query in a MySQL console: mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'database_name'; Please Note: Replace the 'database_name' with your database name. This will show an output like: +--------------------+-------------+-----------+--- | Table_Name | Engine | Version | ... +--------------------+-------------+-----------+--- | customers | MyISAM | 10 | ... +--------------------+-------------+-----------+--- | staff | InnoDB | 10 | ... +--------------------+-------------+-----------+--- Step-3: Check & Repair MyISAM Tables Firstly, locate the corrupted table. Well, if the db table is using MyISAM storage engine, then you can use CHECK TABLE statement & check the table status: mysql> CHECK TABLE table_name; After checking the table status, if there’s any error identified on the MyISAM table, then you can use the below command to solve the error: mysql> REPAIR TABLE table_name; Well, if the error fixing process was successful, you’ll get the error-free output like this: Output: +--------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------------+--------+----------+----------+ | database_name.table_name | repair | status | OK | +--------------------------+--------+----------+----------+ At last, restart MySQL service by using the below command in order to check if you can easily gain access to server: sudo systemctl restart mysql Step-4: Leverage the Effective “innodb_force_recovery” Option In case, if the server crashed, then you should use the best option “force_recovery” in the InnoDB. Enabling this option helps to restore the MySQL databases. However, you can do so by modifying “mysqld.cnf” file in a nano text editor: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf Simply add the below line beneath [mysqld] section: innodb_force_recovery=1 Now, save and then close the file just after restarting MySQL service.  Step-5: Now, Create mysqldump File At this time, if you can access the corrupt table, then use mysqldump option & put all dumps in a new file. After that, you can name the file as per your choice and also ensure to add .sql extension at the end of the name. Here I am naming a file as the demo.sql: mysqldump database_name table_name > demo.sql Now, use a DROP TABLE query in order to delete a table from database. To avoid needing to open MySQL prompt again, use the below syntax: mysql -u user -p --execute="DROP TABLE database_name.table_name" Next, use the dump file “demo.sql” that you have recently prepared, restore the db table: mysql -u user -p --database=database_name < demo.sql So, you have repaired your MySQL corrupted table successfully. Recommended Way for Repairing Corrupt MySQL Table If you want an instant way to repair a corrupt table in MySQL database, it is recommended to use an advanced software- Stellar Repair for MySQL. This MySQL repair utility can fix MyISAM & InnoDB database tables without downtime. Also, it saves much time and effort as compared to manual solutions. Furthermore, this feature-loaded program can restore all inaccessible MySQL database objects, such as tables, triggers, views, primary keys, relations, forms, a more in their original format. The best part about this MySQL database recovery software is- it is 100% safe & secure and is compatible with both Windows and Linux machines. Complete Steps to Fix MySQL DB Table using Stellar Repair for MySQL Tool Step 1: Download, install, and open Stellar Repair for MySQL program on Windows/Linux system. Step 2: Next, the program will display Select Data Folder dialog-box. Choose the version of the database that you’re currently using, click on the browse option to select the database file that you need to repair >> click OK. Step 3: Now, in the Select Database window, you have to select the db that you need to repair. Step 4: Then, to repair the corrupt MySQL database, you have to click the Repair button. Step 5: When the repairing process gets over, you will get a Repair Complete pop up on your screen. Then, click OK. Step 6: After that, the program will show the preview of the repaired database tables & other recoverable stuffs. Then, go to File menu and click on Save. Step 7: At this time, a Save Database dialog-box will appear on your screen. From Save As options, you have to select MySQL, enter the necessary details in order to Connect to the Server >> click Save button. Step 8: Finally, the “Save Complete” box will appear when the process gets over. All the chosen MySQL database tables will be repaired & saved at your desired location. Conclusion Once you go through this blog, you got complete overview of repair corrupt MySQL tables. In this article, I have mentioned both the manual and paid solutions to fix this issue. However, the manual technique to repair corrupt multiple MySQL database tables requires much time & effort. To save your precious time, effort, & resources, you can use the third-party Stellar Repair for MySQL tool. It is one of the best options that can fix corruption in both MyISAM and InnoDB tables effortlessly. Thanks for reading…!

In this article, I will share with you how to import multiple SQL files in the database at the same time using a command line. many developers don't know how the import more than one file at the same time using the command line in ubuntu. so, here I will share one trick for this problem. If you have multiple SQL files in one folder and you want to import that all at the same time so, no need to waste your time. please follow the simple steps. Merge all file into one file First, we need to merge that all SQL files into one single file using the below ubuntu command. cat *.sql > all_files.sql then import that file in your selected database using the following command. Login mySql sudo mysql -u root -p then hit the Enter and put your MySQL password. Select Database use your_database_name Import selected SQL file source path_your_sql_file i hope you like this article.

In this tutorial article, we will see how to delete data from MySQL database using Node.js. We will use where condition to delete specific records from MySQL server.  Perform the following steps to delete data from MySQL database server. Step 1: First create connection between MySQL server and Node.js. Step 2: After database connection created, use conn.query() method to run DELETE query into MySQL database. var query = `DELETE FROM visitors WHERE percentage < ?`; var percentage = 50; After the query executed, close the connection using conn.end() method. We have created below delete_rows.js file with full example. const mysql = require('mysql'); const conn = mysql.createConnection({ host: 'localhost', port: 3306, database: 'charts', user: 'root', password: 'root', }); conn.connect((err) => { if (err) { return console.log(err.message); } else { // select query var query = `DELETE FROM visitors WHERE percentage < ?`; var percentage = 50; // query to database conn.query(query, percentage, function(err, response, result) { if (err) { return console.log(err.message); } else { console.log(response); } }); } // close the connection conn.end(); }); Now run the file into Node server. node delete_rows.js The application will response with object data. In the database all records will be deleted with percentage less than 50. OkPacket { fieldCount: 0, affectedRows: 6, insertId: 0, serverStatus: 34, warningCount: 0, message: '', protocol41: true, changedRows: 0 } You can get number of deleted records using response.affectedRows object property. Note: If you omit the WHERE condition, all records will be deleted.

In this tutorial article, we will learn how to update data from MySQL database using Node.js. We will use where condition to update specific records into MySQL server.  Perform the following steps to update data into MySQL database server. Step 1: First create connection between MySQL server and Node.js. Step 2: After database connection created, use conn.query() method to run UPDATE query into MySQL database. var query = `UPDATE visitors SET percentage = ? WHERE id = ?`; var percentage = [62.02, 1]; Step 3: After the query executed, close the connection using conn.end() method. We have created below update_rows.js file with full example. const mysql = require('mysql'); const conn = mysql.createConnection({ host: 'localhost', port: 3306, database: 'charts', user: 'root', password: 'root', }); conn.connect((err) => { if (err) { return console.log(err.message); } else { // select query var query = `UPDATE visitors SET percentage = ? WHERE id = ?`; var percentage = [62.02, 1]; // query to database conn.query(query, percentage, function(err, response, result) { if (err) { return console.log(err.message); } else { console.log(response); } }); } // close the connection conn.end(); }); Now run the file into Node server. node update_rows.js The application will response with object data. OkPacket { fieldCount: 0, affectedRows: 1, insertId: 0, serverStatus: 2, warningCount: 0, message: '(Rows matched: 1 Changed: 1 Warnings: 0', protocol41: true, changedRows: 1 } You can get updated records number using response.affectedRows object property. Note: If you omit the WHERE condition, all records will be updated.

MySQL is widely used database management system used in web application. It is popular and easy to connect with any web application. In this article, we will learn how to connect MySQL data base with Node.js app. Note: It is assumed that you have already installed MySQL server and Node.js. If you need to install into Linux system, you can download and install from MySQL and Node.js official website. After successfully installed Node.js and MySQL, we need to install mysql driver for Node.js to interact with database. We will use npm package manager to install node.js packages. You might want to create package.json file to save all dependencies installed in the package. You can run following command and input the few questions asked about application. npm init -y Now install mysql package using below command. npm install mysql This will create node_modules directory with all node package installed in it. package.json file saves all dependencies installed in the application. Now create server.js file, in which we will connect mysql database with Node.js. In the server.js file first import mysql module using require() method. const mysql = require('mysql'); Now, create mysql.createConnection() method and pass MySQL credentials object into it.  const conn = mysql.createConnection({ host: 'localhost', port: 3306, database: 'mydbname', user: 'root', password: 'secret' }); Now, we will connect database using connect() method. conn.connect((err) => { if (err) { // throw err; console.log(err.message); } else { // connection established console.log('Connected with mysql database.'); } }); You may close the connection programmatically using end() method. conn.end(function(err) { if (err) { // throw err; console.log(err.message); } else { console.log('Connection with mysql closed.'); } }); Or close the connection forcefully using destroy() method. conn.destroy(); So far in this article, we have connected MySQL database with Node.js. In the later articles, we will query to MySQL database from Node.js.

In the previous article, we have seen how to connect Mysql database with Node.js. In this article, we will see how to query MySQL database server and create table. After database created, use conn.query() method along with MySQL query parameter to run query into MySQL database. After the query executed, close the connection using conn.end() method. Below we have created create_database.js file with full example. const mysql = require('mysql'); const conn = mysql.createConnection({ host: 'localhost', port: 3306, user: 'root', password: 'root', }); conn.connect((err) => { if (err) { // throw err; return console.log(err.message); } else { // connection established console.log('Connected with mysql database.'); // query var query = 'CREATE DATABASE charts'; // query to database conn.query(query, function(err, response) { if (err) { return console.log(err.message); } else { console.log('Database created.'); } }); } // close the connection conn.end(function(err) { if (err) { // throw err; return console.log(err.message); } else { console.log('Connection with mysql closed.'); } }); }); Now run the file into Node server. node create_database.js The application will response with console messages we have set. Connected with mysql database. Database created. Connection with mysql closed. So far in this article, we have created MySQL database with Node.js. In the later articles, we will use more query to MySQL database from Node.js.

This article will guide you to how to create table into MySQL database server from Node.js. First create connection between MySQL server and Node.js. After database created, use conn.query() method to run CREATE TABLE query into MySQL database. var query = `CREATE TABLE visitors( id int NOT NULL, browser varchar(255) NOT NULL, percentage decimal(10,2) NOT NULL )`; After the query executed, close the connection using conn.end() method. Below we have created create_table.js file with full example. const mysql = require('mysql'); const conn = mysql.createConnection({ host: 'localhost', port: 3306, database: 'charts', user: 'root', password: 'root', }); conn.connect((err) => { if (err) { // throw err; return console.log(err.message); } else { // connection established console.log('Connected with mysql database.'); // query var query = `CREATE TABLE visitors( id int NOT NULL, browser varchar(255) NOT NULL, percentage decimal(10,2) NOT NULL )`; // query to database conn.query(query, function(err, response) { if (err) { return console.log(err.message); } else { console.log('Database table created.'); } }); } // close the connection conn.end(function(err) { if (err) { // throw err; return console.log(err.message); } else { console.log('Connection with mysql closed.'); } }); }); Now run the file into Node server. node create_table.js The application will response with console messages we have set. Connected with mysql database. Database table created. Connection with mysql closed. In this article, we have created MySQL database table with Node.js. In the later article, we will insert data into  MySQL database table.

In this article, we will guide you to how to select data from MySQL table using Node.js. We will use MySQL select, where, order by etc. statement to get data from MySQL server.  Perform the following steps to select data from MySQL database server. Step 1: First create connection between MySQL server and Node.js. Step 2: After database connection created, use conn.query() method to run INSERT query into MySQL database. var query = `SELECT * FROM visitors`; Step 3: After the query executed, close the connection using conn.end() method. We have created below select_rows.js file with full example. const mysql = require('mysql'); const conn = mysql.createConnection({ host: 'localhost', port: 3306, database: 'charts', user: 'root', password: 'root', }); conn.connect((err) => { if (err) { return console.log(err.message); } else { // select query var query = `SELECT * FROM visitors`; // query to database conn.query(query, function(err, response, result) { if (err) { return console.log(err.message); } else { console.log(response); } }); } // close the connection conn.end(); }); Now run the file into Node server. node select_rows.js The application will response with data. [ RowDataPacket { id: 1, browser: 'Chrome', percentage: 69.28 }, RowDataPacket { id: 2, browser: 'Edge', percentage: 7.75 }, RowDataPacket { id: 3, browser: 'Firefox', percentage: 7.48 }, RowDataPacket {id: 4, browser: 'Internet Explorer', percentage: 5.21 }, RowDataPacket { id: 5, browser: 'Safari', percentage: 3.73 }, RowDataPacket { id: 6, browser: 'Opera', percentage: 1.12 }, RowDataPacket { id: 7, browser: 'Others', percentage: 5.43 } ] WHERE and ORDER BY query We may also pass additional WHERE and ORDER BY statement in SELECT statement. This way, we can filter the data we want. var query = `SELECT * FROM visitors WHERE percentage > 6 ORDER BY percentage DESC`; We always want to pass dynamic value into where statement for filtering data. So change variable value from static value in the statement. var minimumPercentage = 6; var query = `SELECT * FROM visitors WHERE percentage > ${minimumPercentage} ORDER BY percentage ASC`; Here is the full code: const mysql = require('mysql'); const conn = mysql.createConnection({ host: 'localhost', port: 3306, database: 'charts', user: 'root', password: 'root', }); conn.connect((err) => { if (err) { return console.log(err.message); } else { // select query var minimumPercentage = 6; var query = `SELECT * FROM visitors WHERE percentage > ${minimumPercentage} ORDER BY percentage ASC`; // query to database conn.query(query, function(err, response, result) { if (err) { return console.log(err.message); } else { console.log(response); } }); } // close the connection conn.end(); }); This will return rows only which has percentage greater than 6. [ RowDataPacket { id: 3, browser: 'Firefox', percentage: 7.48 }, RowDataPacket { id: 2, browser: 'Edge', percentage: 7.75 }, RowDataPacket { id: 1, browser: 'Chrome', percentage: 69.28 } ] You can access specific row using array index. For example result[1] will return second item from array. RowDataPacket { id: 2, browser: 'Edge', percentage: 7.75 }, Or even you want specific property from the object property, For example, to get browser value from object, console.log(response[1].browser); // Edge I hope it will help you.