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.

When you can't access one or multiple file access to SQL server, there may be issue SQL server is corrupted. The error may be due to: The database couldn't be shut down properly. Due to insufficient space or hard disk space. If the primary database files are corrupted, the user will face this problem. It may be when you change the state from offline to online. In this article, I will show you how to fix recovery pending state in SQL server. Follow bellow steps to resolve SQL server database issue. Open SQL command line and run the below query one by one to resolve it. ALTER DATABASE db_name SET EMERGENCY; ALTER DATABASE db_name set single_user; DBCC CHECKDB (db_name, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS; ALTER DATABASE db_name set multi_user; Now refresh the database and you will see the error is fixed. I hope you liked this article.

Recently I was working on Node.js application. In my application, I faced connection issue with MySQL server. Everything was good still I was getting following error. Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client In this article, I will show you why this error occured and how to solve this error. While going through Googling the issue, I came to know that MySQL 8.0 is using caching_sha2_password authentication plugin rather than mysql_native_password and Node.js Mysql package uses mysql_native_password authentication plugin. Solution: In this situation, either you can change current user password according to mysql_native_password authentication plugin or you need to create new user with mysql_native_password authentication plugin. Change current user password to authentication plugin To change current user password to mysql_native_password authentication plugin, you need to login to MySQL command line and run the following command. Note that root is username and password is the password for that user. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; Don't forget to flush privileges: FLUSH PRIVILEGES; And that's it, logout to MySQL command line: quit; Add new user with mysql_native_password authentication plugin: To create a new user, login to MySQL command line and run the following command with username and password. CREATE USER 'newuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; You may need to grant privileges to new user. If you want to give all privileges, run the following query. GRANT ALL PRIVILEGES ON *.* TO ‘newuser’@'localhost'; Or you may grant specific privileges with following query: GRANT CREATE, SELECT, INSERT ON *.* TO ‘newuser’@'localhost'; And reload the privileges: FLUSH PRIVILEGES; And close the MySQL connection: quit; After that, you can connect Node.js application with MySQL. I hope this article will help you on your development.

While working on web application, often you need to insert data from sql file. Also when changing your server, you need to export database from old server and import to new one. There are so many ways you can import database from sql file. There are so many GUI applications like MySQL Workbench as well as web application like phpmyadmin available to work with Mysql server. We can also use command line to import file from remote desktop. We will go through few ways to import database from SQL file in MySQL. Import from MySQL Workbench software MySQL Workbench is open-source as well as Community Edition visual database management software developed by Oracle Corporation. The Community Edition is a full featured product still we can do all basic stuff in Open-source software. We will use Open-source software to import sql file. First create the connection on MySQL Workbench dashboard and lauch the server. If you want to create new database, click Create New Schema button from menubar, write database name and apply the changes. To import the SQL file from local system, click File > Run SQL Script... Select the database name and default character set like utf8 and click Run button. This will execute the query. Import from phpMyAdmin phpMyAdmin is free and open-source web application written in PHP. It is easy to manage MySQL and MariaDB database through phpMyAdmin. Many web hosting providers provides phpMyAdmin tool to manage database. If they not provide phpMyAdmin, we can simply install from the official phpMyAdmin Link[https://docs.phpmyadmin.net/en/latest/setup.html]. This is simple way to import SQL file. First, from your browser login to server phpMyAdmin portal either through Cpanel menu or from your database server link. Now from the left bar select the database where you want to import the sql file. Now click Import option from menubar. Select the file from file input and click Go button. This will run SQL file to database server. Import through Command line This is fast way to import SQL script but it is sometimes not easy and takes time to run proper command line. If you have enough knowledge about bash script and MySQL commands, this will be easiest way. To import the SQL script file, run below command from your Terminal. mysql -u database_user -p -h remote.server_domain.com database_name < path/to/sql_file.sql If this not work for you, first connect to ssh and append mysql command. ssh remote_user@remote_server mysql -u database_user -ptestpass database_name < path/to/sql_file.sql There is also one method to import sql file through command line. First upload the sql file to remote server through git or FTP application like FileZilla and you can directly import database. mysql -u database_user -p database_name < path/to/sql_file.sql So, this way sql file can be import to remote server. These are just few ways, there are many softwares available to import the sql file. I hope it will help you.